postgres

Query pg_stat_user_indexes joined with pg_index for indexes with idx_scan = 0, excluding unique and constraint-backing indexes. Verify stats age first, and always confirm with a human before dropping any index since some serve infrequent but critical workloads.

Start with pg_stat_statements to find queries with high mean_exec_time, then run EXPLAIN ANALYZE. Common fixes include adding composite or partial indexes, rewriting correlated subqueries as JOINs, replacing OFFSET pagination with cursor pagination, and avoiding functions on indexed columns.

Use PgBouncer (port 6432 on PlanetScale) for OLTP application traffic with many concurrent connections. Reserve direct connections (port 5432) for DDL, migrations, analytics, temp tables, and session-dependent features, since transaction pooling does not support them.

VACUUM marks dead tuple space as reusable but does not shrink files or compact index pages. Long-running or idle-in-transaction sessions also block cleanup. Use REINDEX CONCURRENTLY or pg_repack for bloat, and set idle_in_transaction_session_timeout to prevent blocking.

PostgreSQL uses 32-bit transaction IDs that wrap around at roughly 2 billion, making old rows invisible. Autovacuum freezes old XIDs to prevent this, so never disable it. Monitor age(datfrozenxid) per database and alert around 40-50% of the wraparound limit.

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