postgres

Query pg_stat_user_indexes joined with pg_index filtering for idx_scan = 0, excluding unique and constraint-backing indexes. Always verify stats age first and confirm with a human before dropping any index.

Enable pg_stat_statements to identify high mean_exec_time queries, then check for missing indexes, N+1 patterns, non-SARGable predicates, and OFFSET pagination. Use EXPLAIN ANALYZE to confirm index usage after changes.

Use PgBouncer for all OLTP application workloads with high concurrency, connecting on port 6432. Reserve direct connections on port 5432 for DDL, migrations, analytics, and session-dependent features like temp tables.

Each connection spawns an OS process consuming memory, and work_mem multiplies across operations, parallel workers, and connections. Fix this with connection pooling, lower work_mem, and statement_timeout rather than raising max_connections.

Partition general tables exceeding 100GB or 20M rows, and time-series or log tables exceeding 50GB or 10M rows. Partitioning mainly benefits maintenance and data retention; the partition key must be part of the primary key.

XID wraparound occurs when 32-bit transaction IDs exhaust ~2 billion values, making old rows invisible. Prevent it by never disabling autovacuum, keeping transactions short, and alerting when age(datfrozenxid) exceeds 800M to 1B.