PostgreSQL Performance Tuning#

Most PostgreSQL performance problems come from missing indexes, bad query plans, connection overhead, or table bloat. This covers how to diagnose each one.

Reading EXPLAIN ANALYZE#

EXPLAIN shows the query plan. EXPLAIN ANALYZE actually executes the query and shows real timings.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

Index Scan using idx_orders_customer on orders  (cost=0.43..8.45 rows=1 width=120) (actual time=0.023..0.025 rows=3 loops=1)
  Index Cond: (customer_id = 42)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 12
Planning Time: 0.152 ms
Execution Time: 0.048 ms

What to look for: Seq Scan on large tables means a missing index. Rows Removed by Filter means the index fetched extra rows that a composite index would eliminate. actual rows far from estimated rows means stale statistics – run ANALYZE tablename;. Nested Loop with high loops count usually wants a hash join; check the inner table’s indexes.

Database Connection Pooling: PgBouncer, ProxySQL, and Application-Level Patterns#

Database connections are expensive resources. PostgreSQL forks a new OS process for every connection. MySQL creates a thread. Both allocate memory for session state, query buffers, and sort areas. When your application scales horizontally in Kubernetes – 10 pods, then 20, then 50 – the connection count multiplies, and most databases buckle long before your application pods do.

Connection pooling solves this by maintaining a smaller set of persistent connections to the database and sharing them across many application clients. Understanding pooling options, deployment patterns, and sizing is essential for any production database workload on Kubernetes.