Should I choose NoSQL or PostgreSQL for flexible product attributes?
Keep products, prices and orders in PostgreSQL and put the variable attributes in one GIN-indexed JSONB column; reporting alone rules out splitting.
Tag
Database choice, schema, query and operational decisions that outlive the engine. questions under this tag, answered directly. (Page 2/2)
20 answered questions carry this tag.
Keep products, prices and orders in PostgreSQL and put the variable attributes in one GIN-indexed JSONB column; reporting alone rules out splitting.
Keep the invariant in the DB with an atomic UPDATE ... WHERE balance >= 40 or a SELECT ... FOR UPDATE, and save Redlock for non-DB resources.
Stand up monthly RANGE declarative partitioning on created_at, backfill history in batches while the app writes, then swap names in a single transaction.
Acquire locks in one global order such as ascending PK, keep transactions short, target FOR UPDATE at the fewest rows, and retry the rest with backoff.
RPO 5min buys continuous cross-region replication and PITR, RTO 30min an IaC-provisioned warm standby behind Route 53 failover, rehearsed on a game-day.
Don't hand-roll failover: put promotion behind a Patroni + etcd quorum, and let a primary cut off from the majority demote itself by fencing.
Drop wildcard for autocomplete; build the prefixes at index time with edge_ngram and keep a standard search analyzer so a keystroke is a plain term lookup.
Pick Transaction mode for a web fleet; the price is protocol-level prepared statements, so disable them in the app or turn on PgBouncer 1.21+ support.
Build one (user_id, status, created_at DESC) index with equality columns first and the sort column last, then drop the now-redundant user_id index.
Database-per-tenant does not scale operationally at 10k; keep tenant_id on every table and bury isolation in Postgres Row-Level Security instead.