postgres-pro

Run EXPLAIN (ANALYZE, BUFFERS) on the query to identify sequential scans, high buffer reads, or bad row estimates. Then create a targeted index with CREATE INDEX CONCURRENTLY, run ANALYZE to refresh statistics, and re-run EXPLAIN to verify the index is used.

Use B-tree for equality and range queries, GIN for JSONB containment and arrays, GiST for spatial data and ranges, and BRIN for large naturally ordered tables like time-series logs. Verify the choice with EXPLAIN before deploying to production.

Create a GIN index on the JSONB column for containment queries using the @> operator, or use jsonb_path_ops for a smaller index. For frequently filtered single keys, a B-tree expression index on data ->> 'key' is more selective.

Yes, standby servers replay WAL asynchronously by default, so reads can return stale data. Monitor lag via pg_stat_replication on the primary and pg_last_xact_replay_timestamp on the standby, and use synchronous replication if freshness is required.

Bloat comes from dead tuples left by updates and deletes that autovacuum has not reclaimed. Check n_dead_tup in pg_stat_user_tables, tune autovacuum_vacuum_scale_factor for high-churn tables, and use pg_repack for online bloat removal without locking.

Never disable autovacuum globally, since it prevents transaction ID wraparound and controls bloat. Instead, tune per-table settings like autovacuum_vacuum_scale_factor and cost delays to balance vacuum frequency against I/O impact.