postgres

Run EXPLAIN (ANALYZE, BUFFERS) on the slow query and look for sequential scans on large tables, sorts without indexes, or nested loops on unindexed joins. Add an appropriate index, then re-run to confirm the plan changed. Use pg_stat_statements to find the worst queries by total_exec_time.

Use CREATE INDEX CONCURRENTLY to build indexes without blocking writes. For NOT NULL columns, add the column as nullable, backfill in batches, then set the default and NOT NULL constraint—Postgres 11+ makes constant defaults instant without a table rewrite.

Yes for OLTP workloads, because Postgres forks a process per connection and direct connections are expensive. Use transaction pooling mode for web traffic, but note that prepared statements, LISTEN/NOTIFY, temp tables, and session-level SET commands do not survive transaction-mode pooling.

Bloat comes from dead tuples that VACUUM has not reclaimed, often caused by long-running transactions or lagging autovacuum. Check n_dead_tup in pg_stat_user_tables, kill idle-in-transaction sessions, and tune autovacuum per-table. Avoid VACUUM FULL in production since it takes an exclusive lock.

Consider partitioning when tables exceed roughly 100GB or 20M rows, or around 50GB for time-series and log data with retention needs. Partitioning mainly benefits maintenance and data retention via dropping old partitions rather than raw query speed, so do not apply it prematurely.

XID wraparound occurs when 32-bit transaction IDs exhaust ~2 billion values, forcing the database into emergency shutdown to prevent data appearing invisible. Prevent it by never disabling autovacuum, keeping transactions short, and alerting when age(datfrozenxid) exceeds 40-50% of the wraparound limit.