PostgreSQL performance tuning when the database is the bottleneck

How I find a Postgres slow query

1. pg_stat_statements first. Sort by total execution time, not mean. A 5 ms query called a million times an hour matters more than a 2-second report run twice a day.
2. EXPLAIN (ANALYZE, BUFFERS) on the top offenders, using real parameter values. I look for sequential scans on large tables, row estimates far from actual rows, sorts and hashes spilling to disk, and nested loops over large inputs.
3. auto_explain with a duration threshold to catch plans that only go bad with certain parameters or data skew.
4. pg_stat_activity and lock waits during the slow periods. Sometimes the query is fine and it’s waiting on a lock held by a migration or a long transaction.
5. Application traces to tie each query back to the endpoint and the line of ORM code that issues it.

Indexing that actually helps

• Composite indexes in the right column order – equality columns first, then the range or sort column.
• Partial indexes for queries that always filter the same way, such as only rows where deleted_at IS NULL, or only jobs still pending.
• Covering indexes with INCLUDE so hot queries can use index-only scans.
• Dropping unused indexes. pg_stat_user_indexes shows indexes with zero scans. Each one slows every write and wastes cache.
• Building safely. CREATE INDEX CONCURRENTLY on live tables, so you don’t block writes during business hours.

Connections, vacuum and the problems that aren’t queries

Every Postgres connection is a backend process with its own memory. Hundreds of app workers each holding connections will hurt long before CPU does. PgBouncer in transaction pooling mode lets thousands of client connections share a small pool – check how your driver and ORM handle prepared statements and session state before switching modes.

Vacuum is the other quiet killer. Updates and deletes leave dead tuples behind; if autovacuum can’t keep up, tables and indexes bloat and queries slow down. Large, busy tables usually need per-table settings such as a lower autovacuum_vacuum_scale_factor. And watch for transactions left idle in transaction – they hold back cleanup for the whole database and, in the worst case, push you towards transaction ID wraparound.

Then the configuration basics: shared_buffers, effective_cache_size, work_mem sized for your concurrency, and random_page_cost lowered for SSD storage. Useful, but rarely the main fix.

Partitioning, replicas and when to scale up

Declarative partitioning by time pays off for large append-mostly tables like events, logs and audit trails: queries prune to recent partitions, and retention becomes dropping a partition instead of a huge DELETE. It doesn’t help tables you query by something other than the partition key.

Read replicas take reporting and read-heavy endpoints off the primary, as long as the app tolerates a little replication lag.

And sometimes the honest answer is a bigger instance. If the working set no longer fits in memory and the queries are already good, scaling vertically is cheaper than an engineer’s month. Re-architecture – caching, splitting a hot table, moving analytics elsewhere – comes in when vertical scaling stops being linear. See caching strategy and multi-tenant SaaS architecture.

MySQL performance tuning too

The method carries over: slow query log and performance_schema instead of pg_stat_statements, pt-query-digest to rank them, EXPLAIN ANALYZE in MySQL 8, InnoDB buffer pool sizing. If you’re considering moving engines, that’s a separate decision – see MySQL to PostgreSQL migration.

Related

• MySQL to PostgreSQL migration without a long outage
• Choosing a caching strategy that won’t bite you later
• Hire a senior Django developer for the hard parts
• Ruby on Rails consultant for apps that have outgrown version one
• AWS cost optimization without breaking production