How do I handle read-after-write when replication lag hides a user's own just-written data?
Route a user's reads to the primary for a few seconds after their write, and reach for LSN fencing only if that window isn't precise enough.
Tag
The PostgreSQL database: index strategies, the query planner, vacuum and performance. Questions under this tag, answered directly.
14 answered questions carry this tag.
Route a user's reads to the primary for a few seconds after their write, and reach for LSN fencing only if that window isn't precise enough.
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.
Add INCLUDE only when the query is hot and EXPLAIN (ANALYZE, BUFFERS) shows heap access dominating, then tighten autovacuum until Heap Fetches: 0.
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.