Ekin
Should I use a GIN index or a generated column to filter on fields inside a JSONB column?
Create one expression index per fixed key and confirm the plan drops to an Index Scan; promote to a generated column if you join on the value.
All questions
Tag
#PostgreSQL
The PostgreSQL database: index strategies, the query planner, vacuum and performance. Questions under this tag, answered directly.
13 answered questions carry this tag.
Pelin
When should I switch to a covering index with INCLUDE to get an index-only scan?
Add `INCLUDE` only when the query is hot and `EXPLAIN (ANALYZE, BUFFERS)` shows heap access dominating, then tighten autovacuum until `Heap Fetches: 0`.
Yiğit
Should I use a partial index on a queue table where I only ever scan 'pending' rows?
Build the partial index on `WHERE status='pending'`, include the `ORDER BY` column, pair it with `FOR UPDATE SKIP LOCKED`, and pass 'pending' literally.
Zeynep
Why does a query use an Index Scan in staging but a Seq Scan in production, and how do I diagnose it with EXPLAIN?
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.
Yusuf
How do I rewind to seconds before a disaster with WAL archiving and PITR?
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.
Uğur
Time-series data: TimescaleDB or InfluxDB?
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.
Taylan
How do I coordinate a zero-loss schema migration with dual-writing on a live table?
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.
Halil
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.
Furkan
How do I migrate a table to PostgreSQL partitioning with zero downtime?
Stand up monthly RANGE declarative partitioning on `created_at`, backfill history in batches while the app writes, then swap names in a single transaction.
Sinan
What rules should I follow to prevent database deadlocks?
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.