Tag
The PostgreSQL database: index strategies, the query planner, vacuum and performance.
11 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.
Run EXPLAIN (ANALYZE, BUFFERS) in production, then ANALYZE the table and check n_dead_tup; tune autovacuum per-table for sessions and add a partial index.
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.
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.
Keep products, prices and orders in PostgreSQL and put the variable attributes in one GIN-indexed `JSONB` column; reporting alone rules out splitting.
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.
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.
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.