postgres

Query pg_stat_user_indexes joined with pg_index and filter 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 rank queries by mean execution time, then check for missing indexes, N+1 patterns, functions on indexed columns, and OFFSET pagination. Rewrite with EXISTS, cursor pagination, and composite indexes on filtered columns.

Use PgBouncer on port 6432 for OLTP application traffic and high-concurrency workloads. Use direct connections on port 5432 for DDL, migrations, analytics, temp tables, and any session-level SET commands, since transaction pooling leaks session state.

work_mem is allocated per operation, not per query, so sorts, hashes, and parallel workers multiply memory across every connection. Reduce work_mem globally, add connection pooling, and set statement_timeout to prevent OOM kills.

Bloat comes from dead tuples left by UPDATE and DELETE when autovacuum cannot keep up, often due to long transactions. Tune autovacuum scale factors per table, set idle_in_transaction_session_timeout, and use REINDEX CONCURRENTLY or pg_repack for existing bloat.

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