PostgreSQL Query Performance: The Complete EXPLAIN ANALYZE Guide for Developers Who've Had Enough

Your API endpoint takes 3 seconds. Your dashboard times out loading. Your background job that "should take a minute" runs for 45. You open pgAdmin, stare at the query, and think: "This should be fast. It's just a SELECT."

Welcome to PostgreSQL performance debugging. Every backend developer hits this wall eventually, and most waste hours guessing — adding random indexes, rewriting queries blindly, or worse, blaming the ORM and moving on.

This guide gives you the systematic approach. We'll cover how to read EXPLAIN ANALYZE output like a pro, which indexes actually help (and which make things worse), how to find and kill N+1 queries, connection pooling mistakes that silently destroy throughput, and the exact debugging workflow that works in production.

No theory-first textbook approach. We start with the broken query and work backward.

The Query is Slow. Now What?

Before touching anything, you need data. Not guesses. Data.

Step one is always the same: run EXPLAIN ANALYZE.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50;

The ANALYZE flag actually executes the query (don't run this with destructive operations unless wrapped in a transaction). BUFFERS shows I/O activity — the single most important piece of information for diagnosing slowness. FORMAT TEXT gives the most readable output.

Here's what you get back:

Limit  (cost=1247.83..1247.96 rows=50 width=86) (actual time=342.118..342.127 rows=50 loops=1)
  Buffers: shared hit=128 read=15234
  ->  Sort  (cost=1247.83..1302.45 rows=21847 width=86) (actual time=342.115..342.120 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        Buffers: shared hit=128 read=15234
        ->  Hash Join  (cost=412.50..876.32 rows=21847 width=86) (actual time=45.821..298.456 rows=21847 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=128 read=15234
              ->  Seq Scan on orders o  (cost=0.00..398.75 rows=21847 width=24) (actual time=0.021..187.345 rows=21847 loops=1)
                    Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
                    Rows Removed by Filter: 478153
                    Buffers: shared hit=0 read=14891
              ->  Hash  (cost=212.50..212.50 rows=10000 width=62) (actual time=45.678..45.678 rows=10000 loops=1)
                    Buckets: 16384  Batches: 1  Memory Usage: 890kB
                    Buffers: shared hit=128 read=343
                    ->  Seq Scan on customers c  (cost=0.00..212.50 rows=10000 width=62) (actual time=0.012..22.345 rows=10000 loops=1)
                          Buffers: shared hit=128 read=343

Looks complex. Let's decode it.

Reading EXPLAIN ANALYZE: The Five Things That Actually Matter

Most guides give you a glossary of every possible node type. You don't need that. You need to know the five things that tell you exactly where the problem is.

1. Actual Time (not Cost)

Ignore cost. It's the planner's estimate, not reality. The actual time is what you care about:

actual time=0.021..187.345 rows=21847

The first number (0.021) is startup time. The second (187.345) is total time in milliseconds. This Seq Scan on orders takes 187ms — that's where 55% of total query time is spent.

2. Rows Removed by Filter

This is the smoking gun for missing indexes:

Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
Rows Removed by Filter: 478153

PostgreSQL scanned ~500,000 rows to find ~22,000 matches. That's a 95.6% waste rate. If you see Rows Removed by Filter that's 10x or more the returned rows, you almost certainly need an index.

3. Buffers: shared hit vs read

Buffers: shared hit=0 read=14891

shared hit = pages found in PostgreSQL's shared buffer cache (RAM). read = pages fetched from disk (or OS cache). When read is vastly larger than hit, the working set doesn't fit in memory — or it's the first run. High read with low hit on repeated executions means your shared_buffers is too small or the table is too large to cache.

4. Seq Scan on Large Tables

->  Seq Scan on orders o  (cost=0.00..398.75 rows=21847 ...)

A sequential scan on a table with 500K rows is almost always a problem (unless you're fetching most of the table). This is the #1 performance killer in PostgreSQL.

5. Estimated vs Actual Rows

(cost=... rows=21847 ...) (actual time=... rows=21847 ...)

When estimated and actual row counts differ significantly (10x+), the planner chose a bad strategy. This usually means stale statistics. Fix with:

ANALYZE orders;
-- or for all tables:
ANALYZE;

The Index Strategy That Actually Works

Most developers' index strategy is: "Query is slow → Add an index on the WHERE column → Hope it works." This is like putting a band-aid on a broken pipe.

Rule 1: Composite Indexes Beat Single-Column Indexes

For our slow query, adding a single-column index won't help much:

-- Mediocre: Single column
CREATE INDEX idx_orders_status ON orders(status);
-- PostgreSQL might still seq scan because 'completed' matches too many rows

-- Mediocre: Single column on date
CREATE INDEX idx_orders_created ON orders(created_at);
-- Better but still not optimal

-- Good: Composite index matching the WHERE clause
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- PostgreSQL can now do an Index Scan with both conditions

Column order in composite indexes matters. Put the equality condition first (status = 'completed') and the range condition second (created_at >= '2026-01-01'). PostgreSQL can efficiently seek to the exact status value, then scan the date range within that subset.

Rule 2: Covering Indexes Eliminate Table Lookups

Even with an index, PostgreSQL does an index scan to find row IDs, then goes back to the table (heap) to fetch the actual columns. This "heap fetch" is expensive for large result sets.

A covering index includes all needed columns:

-- Covering index: includes columns needed in SELECT and ORDER BY
CREATE INDEX idx_orders_covering ON orders(status, created_at DESC)
INCLUDE (id, total, customer_id);

Now PostgreSQL can answer the query using only the index — no heap access needed. The INCLUDE clause adds columns without affecting the index's sort order.

Let's verify:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50;

Limit  (cost=0.71..12.34 rows=50 width=86) (actual time=0.087..0.234 rows=50 loops=1)
  Buffers: shared hit=12
  ->  Nested Loop  (cost=0.71..5123.45 rows=21847 width=86) (actual time=0.085..0.228 rows=50 loops=1)
        Buffers: shared hit=12
        ->  Index Only Scan using idx_orders_covering on orders o  (cost=0.42..876.54 rows=21847 width=24) (actual time=0.034..0.067 rows=50 loops=1)
              Index Cond: ((status = 'completed') AND (created_at >= '2026-01-01'))
              Heap Fetches: 0
              Buffers: shared hit=4
        ->  Index Scan using customers_pkey on customers c  (cost=0.29..0.31 rows=1 width=62) (actual time=0.002..0.002 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
              Buffers: shared hit=8

342ms → 0.23ms. That's a 1,487x speedup. Notice Heap Fetches: 0 and Index Only Scan — the covering index paid off.

Rule 3: Partial Indexes for Skewed Data

If 90% of your orders are completed but you're frequently querying recent pending ones:

-- Only index the rows you actually query
CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'pending';

The index is dramatically smaller (10% of the table), fits entirely in cache, and PostgreSQL uses it when the WHERE clause matches.

Rule 4: Know When NOT to Index

Indexes are not free. Each index:

• Slows down every INSERT, UPDATE, and DELETE (the index must be maintained)
• Consumes disk space and memory
• Adds to vacuum overhead

Don't index columns with very low cardinality (like boolean flags with 50/50 distribution), tables under ~10,000 rows (seq scan is faster), or columns that are rarely filtered on.

Check which indexes are actually being used:

SELECT
  schemaname, tablename, indexname,
  idx_scan AS times_used,
  idx_tup_read AS rows_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

This shows unused indexes you can safely drop. On a real production database, I've seen teams with 40+ unused indexes consuming 30GB of disk and slowing writes by 20%.

The N+1 Problem: Death by a Thousand Queries

The N+1 problem isn't a PostgreSQL issue — it's an application-layer problem that makes PostgreSQL miserable. And it's the single most common performance issue in web applications.

What It Looks Like

// The classic N+1: 1 query for orders + N queries for customers
const orders = await db.query('SELECT * FROM orders LIMIT 100');

for (const order of orders) {
  // This fires 100 separate queries
  const customer = await db.query(
    'SELECT * FROM customers WHERE id = $1',
    [order.customer_id]
  );
  order.customer = customer;
}

Your application log shows:

Query: SELECT * FROM orders LIMIT 100                          -- 1 query
Query: SELECT * FROM customers WHERE id = 1                    -- +1
Query: SELECT * FROM customers WHERE id = 2                    -- +1
Query: SELECT * FROM customers WHERE id = 3                    -- +1
... (97 more)
Total: 101 queries, 340ms network round-trips

Each query might take only 1-3ms, but the network round-trip overhead adds up. 100 round-trips × 3ms = 300ms of pure waste.

Fix 1: JOIN (The Obvious One)

SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
LIMIT 100;
-- 1 query, ~2ms

Fix 2: Batch Loading (When JOINs Get Complex)

// Step 1: Get orders
const orders = await db.query('SELECT * FROM orders LIMIT 100');

// Step 2: Batch-load all customers in ONE query
const customerIds = [...new Set(orders.map(o => o.customer_id))];
const customers = await db.query(
  'SELECT * FROM customers WHERE id = ANY($1)',
  [customerIds]
);

// Step 3: Map in memory
const customerMap = new Map(customers.map(c => [c.id, c]));
orders.forEach(o => o.customer = customerMap.get(o.customer_id));
// 2 queries total, ~4ms

Fix 3: DataLoader Pattern (For GraphQL and Complex Trees)

When you have deeply nested relationships (orders → items → products → categories), the DataLoader pattern batches and deduplicates automatically:

import DataLoader from 'dataloader';

const customerLoader = new DataLoader(async (ids: number[]) => {
  const customers = await db.query(
    'SELECT * FROM customers WHERE id = ANY($1)',
    [ids]
  );
  // DataLoader requires results in the same order as input IDs
  const map = new Map(customers.map(c => [c.id, c]));
  return ids.map(id => map.get(id) ?? null);
});

// Now these all batch into a single query:
const [cust1, cust2, cust3] = await Promise.all([
  customerLoader.load(1),
  customerLoader.load(2),
  customerLoader.load(3),
]);
// 1 query: SELECT * FROM customers WHERE id = ANY([1, 2, 3])

Detecting N+1 in Production

You can't fix what you can't see. Enable query logging with duration thresholds:

-- postgresql.conf
log_min_duration_statement = 0   -- Log ALL queries (use temporarily!)
log_statement = 'none'            -- Don't double-log

-- Or, more practically, log only slow queries:
log_min_duration_statement = 100  -- Log queries taking > 100ms

Then look for patterns: if you see the same query template repeated 50+ times within a single request, you have an N+1.

For ORM users, most frameworks have N+1 detection tools:

// Prisma: use query events to detect N+1
const prisma = new PrismaClient({
  log: [{ emit: 'event', level: 'query' }],
});

let queryCount = 0;
prisma.$on('query', () => { queryCount++; });

// After a request:
if (queryCount > 10) {
  console.warn(`⚠️ N+1 detected: ${queryCount} queries in single request`);
}

Connection Pooling: The Silent Throughput Killer

You've optimized your queries, added the right indexes, killed N+1s. Response times look great in development. Then you deploy and under load, everything gets slower.

The problem is almost always connection pooling — or rather, the lack of it.

Why Connections Are Expensive

Each PostgreSQL connection spawns a process (~10MB RAM). A default max_connections = 100 means you can have at most 100 concurrent connections. But here's the part most developers miss: each connection holds a lock on shared resources, and past ~50 active connections, performance degrades due to lock contention.

┌─────────────────────────────────────────┐
│         Connection Impact               │
│                                         │
│  Connections    Throughput  Latency      │
│  ─────────    ──────────  ───────       │
│      10       100%        baseline      │
│      50       95%         1.1x          │
│     100       70%         2.5x          │
│     200       40%         8x            │
│     500       15%         25x           │
│                                         │
│  ⚠️  More connections ≠ more throughput  │
└─────────────────────────────────────────┘

The Fix: PgBouncer

PgBouncer sits between your application and PostgreSQL, multiplexing thousands of application connections into a small number of actual database connections.

┌───────────┐     ┌───────────┐     ┌───────────┐
│  App (50  │────▶│ PgBouncer │────▶│ PostgreSQL│
│  conns)   │     │  (pools   │     │  (20 real │
├───────────┤     │   into    │     │   conns)  │
│  App (50  │────▶│   20      │     │           │
│  conns)   │     │   real    │     │           │
├───────────┤     │   conns)  │     │           │
│  App (50  │────▶│           │     │           │
│  conns)   │     └───────────┘     └───────────┘
│           │
│  150 app connections → 20 real connections
└───────────┘

Configuration that works for most production setups:

; pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction          ; Release connection after each transaction
default_pool_size = 20           ; 20 real connections per database
max_client_conn = 1000           ; Accept up to 1000 app connections
reserve_pool_size = 5            ; Extra connections for burst traffic
reserve_pool_timeout = 3         ; Wait 3s before using reserve pool
server_idle_timeout = 300        ; Close idle server connections after 5min

The key setting is pool_mode = transaction. In this mode, a real database connection is assigned only for the duration of a transaction, then returned to the pool. This means 1,000 application connections can share 20 real PostgreSQL connections.

Application-Level Pooling

If PgBouncer feels like too much infrastructure, most database drivers support connection pooling natively:

// node-postgres (pg) with built-in pooling
import { Pool } from 'pg';

const pool = new Pool({
  host: 'localhost',
  database: 'myapp',
  max: 20,                    // Maximum pool size
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail if can't connect in 5s
});

// Use pool.query() instead of client.query()
const result = await pool.query('SELECT * FROM orders WHERE id = $1', [42]);

// IMPORTANT: For transactions, check out a client and release it
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release(); // ALWAYS release back to pool
}

The most common mistake: setting max too high. If you have 10 application instances each with max: 50, that's 500 real connections — well past the point where PostgreSQL performance collapses. Set it to max_connections / number_of_instances and leave room for admin connections.

Advanced Debugging: The Full Toolkit

pg_stat_statements: Your Production Performance Dashboard

This extension tracks execution statistics for every query pattern:

-- Enable it (requires restart)
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

 total_ms   │ calls  │ avg_ms │  pct  │ query_preview
────────────┼────────┼────────┼───────┼──────────────────────────────────────
 42847.32   │ 15234  │  2.81  │ 34.2  │ SELECT o.*, c.name FROM orders o JOIN customers c ON...
 18923.11   │ 892    │ 21.22  │ 15.1  │ SELECT * FROM products WHERE category_id = $1 AND...
 12445.67   │ 445623 │  0.03  │  9.9  │ SELECT id, name FROM sessions WHERE token = $1
  8234.55   │ 3421   │  2.41  │  6.6  │ UPDATE inventory SET quantity = quantity - $1 WHERE...

This reveals that the orders query, despite averaging only 2.81ms per call, accounts for 34.2% of total database time because it's called 15,000+ times. That's where your optimization effort should go first.

pg_stat_user_tables: Table Health Check

SELECT
  relname AS table,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 10;

If dead_pct is above 20%, autovacuum isn't keeping up. Dead rows cause table bloat, which causes seq scans to read more pages, which makes everything slower. Tune autovacuum:

-- For high-write tables, make autovacuum more aggressive
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,     -- Vacuum at 2% dead rows (default 20%)
  autovacuum_analyze_scale_factor = 0.01,    -- Analyze at 1% changes (default 10%)
  autovacuum_vacuum_cost_delay = 5           -- Less delay between vacuum rounds
);

Lock Monitoring: Finding Blocked Queries

SELECT
  blocked.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking_activity.query AS blocking_query,
  now() - blocked_activity.query_start AS blocked_duration
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking ON blocking.locktype = blocked.locktype
  AND blocking.database IS NOT DISTINCT FROM blocked.database
  AND blocking.relation IS NOT DISTINCT FROM blocked.relation
  AND blocking.page IS NOT DISTINCT FROM blocked.page
  AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple
  AND blocking.transactionid IS NOT DISTINCT FROM blocked.transactionid
  AND blocking.pid != blocked.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

If you see queries blocked for more than a few seconds, you either have long-running transactions holding locks (fix: keep transactions short) or schema migrations running without CONCURRENTLY (fix: use CREATE INDEX CONCURRENTLY).

The Complete Debugging Workflow

When a query is slow, follow this exact workflow:

┌────────────────────────────────────┐
│  1. EXPLAIN (ANALYZE, BUFFERS)     │
│     Run the suspicious query       │
└─────────────┬──────────────────────┘
              │
              ▼
┌────────────────────────────────────┐
│  2. Check for Seq Scans            │
│     on tables > 10K rows           │
│     → Add appropriate index        │
└─────────────┬──────────────────────┘
              │
              ▼
┌────────────────────────────────────┐
│  3. Check Rows Removed by Filter   │
│     If > 10x returned rows         │
│     → Composite or covering index  │
└─────────────┬──────────────────────┘
              │
              ▼
┌────────────────────────────────────┐
│  4. Check Estimated vs Actual Rows │
│     If off by > 10x               │
│     → Run ANALYZE on the table     │
└─────────────┬──────────────────────┘
              │
              ▼
┌────────────────────────────────────┐
│  5. Check Buffers hit vs read      │
│     If read >> hit repeatedly      │
│     → Increase shared_buffers      │
│     → Or query is touching too     │
│       much data (fix the query)    │
└─────────────┬──────────────────────┘
              │
              ▼
┌────────────────────────────────────┐
│  6. Check pg_stat_statements       │
│     Find top queries by total_time │
│     → Optimize in priority order   │
└─────────────┬──────────────────────┘
              │
              ▼
┌────────────────────────────────────┐
│  7. Check connection count         │
│     If > 50 active connections     │
│     → Add PgBouncer or reduce     │
│       pool sizes                   │
└────────────────────────────────────┘

Common PostgreSQL Configuration Mistakes

These settings affect every query. Get them wrong and no amount of index optimization will save you.

Setting	Default	Recommended	Why
shared_buffers	128MB	25% of RAM	PostgreSQL's main cache. Too small = constant disk reads
effective_cache_size	4GB	75% of RAM	Tells planner how much OS cache to expect. Affects index decisions
work_mem	4MB	64-256MB	Memory for sorts and hash joins. Too low = disk-based sorts
random_page_cost	4.0	1.1 (SSD)	For SSD storage, lower this so planner prefers index scans
effective_io_concurrency	1	200 (SSD)	Parallel I/O requests. Dramatically speeds up bitmap scans on SSD
maintenance_work_mem	64MB	1-2GB	Memory for VACUUM and CREATE INDEX. Higher = faster maintenance

The single most impactful change for most deployments: setting random_page_cost = 1.1 for SSD storage. The default 4.0 was designed for spinning disks and tells the planner that random reads are 4x more expensive than sequential reads. On SSDs, they're roughly equal. With the default, PostgreSQL often chooses seq scans over index scans because it overestimates the cost of random I/O.

-- Check your current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW random_page_cost;
SHOW effective_cache_size;

Real-World Example: From 12 Seconds to 15 Milliseconds

Let's walk through a real optimization scenario. An analytics dashboard query takes 12 seconds:

-- The original query
SELECT
  date_trunc('day', o.created_at) AS day,
  COUNT(*) AS order_count,
  SUM(o.total) AS revenue,
  COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.created_at BETWEEN '2025-01-01' AND '2025-12-31'
  AND o.store_id = 42
GROUP BY date_trunc('day', o.created_at)
ORDER BY day;

Step 1: EXPLAIN ANALYZE reveals the damage

GroupAggregate  (actual time=12847.234..12847.891 rows=365 loops=1)
  Buffers: shared hit=234 read=89234
  ->  Sort  (actual time=12845.123..12846.234 rows=187234 loops=1)
        Sort Method: external merge  Disk: 48672kB
        ->  Seq Scan on orders  (actual time=0.034..11234.567 rows=187234 loops=1)
              Filter: ((created_at BETWEEN ...) AND (store_id = 42))
              Rows Removed by Filter: 4812766

Three problems visible:

1. Seq Scan: Scanning 5M rows to find 187K
2. External merge sort: work_mem too small, sorting on disk
3. 89K buffer reads: Tons of disk I/O

Step 2: Add a targeted composite index

CREATE INDEX idx_orders_store_date ON orders(store_id, created_at)
INCLUDE (total, customer_id);

Step 3: Increase work_mem for this session

SET work_mem = '128MB';  -- Just for this session

Step 4: Verify

GroupAggregate  (actual time=14.567..15.234 rows=365 loops=1)
  Buffers: shared hit=892
  ->  Index Only Scan using idx_orders_store_date on orders  (actual time=0.045..8.234 rows=187234 loops=1)
        Index Cond: ((store_id = 42) AND (created_at >= '2025-01-01') AND (created_at <= '2025-12-31'))
        Heap Fetches: 0
        Buffers: shared hit=892

12,847ms → 15ms. 856x faster. Three changes made it happen:

1. Composite index matching the WHERE clause (equality first, range second)
2. Covering index avoiding heap fetches
3. Adequate work_mem for in-memory sort

Conclusion

PostgreSQL performance debugging isn't black magic. It's a systematic process:

1. Measure first. Run EXPLAIN (ANALYZE, BUFFERS) on every slow query. Don't guess.
2. Read the signals. Seq Scans, Rows Removed by Filter, Buffers read vs hit — these tell you exactly what's wrong.
3. Index strategically. Composite indexes with equality-first ordering. Covering indexes for high-frequency queries. Partial indexes for skewed data.
4. Kill N+1 queries. Use JOINs, batch loading, or the DataLoader pattern. Detect them in production with query logging.
5. Manage connections. Use PgBouncer or application-level pooling. Keep real connections under 50.
6. Configure properly. shared_buffers at 25% RAM, random_page_cost = 1.1 for SSDs, adequate work_mem for your workload.
7. Monitor continuously. pg_stat_statements for query performance, pg_stat_user_tables for table health, lock monitoring for contention.

The difference between a database that handles 100 requests per second and one that handles 10,000 isn't hardware — it's understanding what PostgreSQL is doing and giving it the information (indexes, statistics, configuration) to do it well.

Start with EXPLAIN ANALYZE. Follow the workflow. Measure everything. Your queries will thank you.

---

💡 Note: This article was originally published on the Pockit Blog.

Check out Pockit.tools for 60+ free developer utilities. For faster access, add it to Chrome and use JSON Formatter & Diff Checker directly from your toolbar.