Tag
Database choice, schema, query and operational decisions that outlive the engine.
18 answered questions carry this tag.
Build the partial index on `WHERE status='pending'`, include the `ORDER BY` column, pair it with `FOR UPDATE SKIP LOCKED`, and pass 'pending' literally.
Take the key out of .env, keep it in KMS or Vault and use envelope encryption; for national IDs and card data, prefer tokenization wherever you can.
Take a periodic base backup, archive WAL to S3 continuously, replay to a recovery_target_time seconds before the bad statement, and rehearse the restore.
Add the column nullable, move the backfill to a separate throttled step, run migrations in their own stage, and drop the old column in a later release.
100M rows a day breaks a plain table, so chunk it by time with hypertables, materialize the averages as continuous aggregates, and compress the old chunks.
Instead of a blocking `ALTER TABLE` on 20M rows run expand/contract: add nullable columns, dual-write, backfill in throttled batches, then drop the old.
Use a Sorted Set instead of plain string keys sorted in the app, since `ZADD` updates in O(log N) and `ZREVRANGE 0 99` returns the top 100 already ordered.
Move reporting to a read replica and adopt CQRS only when query shapes outgrow one schema, feeding the read model from outbox events, not dual-writes.
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.