The Ultimate Guide to PostgreSQL Performance

Before tuning anything, find out what is actually slow. Most teams spend a week optimising a query that runs hourly while a trivial one executes four thousand times a minute.

pg_stat_statements is the single highest-value extension in Postgres. Enable it and sort by total time, not by average, because frequency usually dominates.

```sql
SELECT calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round(total_exec_time::numeric / 1000, 1) AS total_s,
       left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Use EXPLAIN (ANALYZE, BUFFERS) so you see real timings and cache behaviour rather than estimates alone.

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 4821
  AND o.created_at >= now() - interval '30 days'
ORDER BY o.created_at DESC
LIMIT 20;

Three things to look for. First, a large gap between estimated and actual row counts, which means the planner has bad statistics and every downstream decision is suspect. Second, sequential scans on big tables where a filter is highly selective. Third, Rows Removed by Filter, which counts the work the database did for nothing. A sequential scan on a small table is fine and often optimal, so do not chase every one of them.

Column order in a composite index is not cosmetic. Put equality predicates first, then the range or sort column.

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

-- Only index the rows you query, when the rest are irrelevant CREATE INDEX CONCURRENTLY idx_orders_open ON orders (created_at DESC) WHERE status IN ('pending', 'processing'); ```

Always use CONCURRENTLY on a live table, otherwise you take a write lock. Add INCLUDE columns to enable index-only scans on your hottest read. And drop unused indexes, since every one slows writes and consumes cache; pg_stat_user_indexes tells you which ones are never scanned.

shared_buffers around a quarter of RAM, effective_cache_size around three quarters, work_mem sized per operation rather than per connection, and random_page_cost lowered to roughly 1.1 on SSDs because the default assumes spinning disks and discourages index use.

• Connection storms. Postgres uses a process per connection. Put PgBouncer in front and keep the pool small; hundreds of idle connections are pure overhead.
• Idle transactions. A forgotten open transaction blocks vacuum from cleaning dead rows, and bloat grows silently until performance collapses.
• N plus one queries. The database is not slow. Your ORM issued two thousand statements to render one page.
• Missing autovacuum tuning on high-churn tables. Default thresholds are too lax for tables receiving millions of updates a day.

Work in that order: measure, read the plan, fix the index, then touch configuration. Reversing it wastes days.