PostgreSQL Performance Optimization: The Practical Guide for 2026

Most PostgreSQL performance problems fall into a handful of categories: missing indexes, bad query plans, N+1 queries, and connection pool exhaustion. This guide covers each one with real diagnostic queries and fixes.

---

Step 1: Find the Slow Queries

Enable pg_stat_statements — the single most useful extension for performance work:

-- Enable the extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 20 slowest queries by total execution time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  calls,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
  left(query, 120) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 20;

-- Find queries with the highest average time (worst individual queries)
SELECT
  round(mean_exec_time::numeric, 2) AS mean_ms,
  calls,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10  -- ignore one-off queries
ORDER BY mean_exec_time DESC
LIMIT 20;

Reset stats after deploying changes:

SELECT pg_stat_statements_reset();

---

Step 2: Read EXPLAIN ANALYZE

EXPLAIN ANALYZE is the X-ray of query performance. Read it bottom-up.

-- Always use ANALYZE (executes the query) and BUFFERS (shows cache hits)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

What to look for in the output:

-- BAD: Seq Scan on a large table
Seq Scan on orders  (cost=0.00..48291.00 rows=23 width=89)
                     (actual time=0.043..892.312 rows=23 loops=1)
  Filter: ((status = 'pending') AND (created_at > ...))
  Rows Removed by Filter: 2100000    ← scanning 2M rows to find 23

-- GOOD: Index Scan
Index Scan using idx_orders_status_created on orders
                     (cost=0.43..127.12 rows=23 width=89)
                     (actual time=0.089..1.234 rows=23 loops=1)

Key metrics:

• Seq Scan on large table → need an index
• actual rows >> estimated rows → stale statistics, run ANALYZE
• Buffers: hit=X read=Y → high read means data isn't cached (needs more shared_buffers or query optimization)
• Nested Loop with many loops → potential N+1 problem at the SQL level

---

Step 3: Index Strategy

Composite Indexes (order matters)

-- Query: WHERE status = 'pending' AND created_at > '...' ORDER BY created_at DESC
-- Wrong index (only on status)
CREATE INDEX idx_orders_status ON orders(status);

-- Right index: matches the WHERE + ORDER BY
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- For range queries, put the equality column first, range column second
-- This index satisfies: WHERE tenant_id = $1 AND created_at BETWEEN $2 AND $3
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at);

Partial Indexes

Only index rows that match a condition — much smaller, much faster for filtered queries:

-- If 95% of orders are 'completed', only index the active ones
CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status IN ('pending', 'processing');

-- Index only non-deleted records
CREATE INDEX idx_users_active_email ON users(email)
WHERE deleted_at IS NULL;

-- Index only large orders
CREATE INDEX idx_orders_high_value ON orders(user_id, created_at)
WHERE total_cents > 100000;

Index on Expressions

-- Query: WHERE lower(email) = lower($1)
-- Regular index on email won't help this query
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Query: WHERE date_trunc('day', created_at) = '2026-01-15'
CREATE INDEX idx_orders_date ON orders(date_trunc('day', created_at));

-- JSONB queries
-- Query: WHERE metadata->>'country' = 'KG'
CREATE INDEX idx_orders_country ON orders((metadata->>'country'));

Find Missing Indexes

-- Tables with many sequential scans (candidates for new indexes)
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / NULLIF(seq_scan, 0) AS avg_rows_per_seqscan
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Unused indexes (wasting write performance and storage)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS scans
FROM pg_stat_user_indexes
JOIN pg_indexes USING (schemaname, tablename, indexname)
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey'    -- exclude primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

---

Step 4: Fix N+1 Queries

N+1 is the most common ORM-related performance issue. It happens when you load N records then query the database once per record.

// BAD: 1 query for orders + N queries for users
const orders = await db.query('SELECT * FROM orders LIMIT 100');
for (const order of orders.rows) {
  const user = await db.query('SELECT * FROM users WHERE id = $1', [order.user_id]);
  order.user = user.rows[0]; // N additional queries
}

// GOOD: 1 query with JOIN
const orders = await db.query(`
  SELECT
    o.id, o.total, o.status, o.created_at,
    u.id AS user_id, u.email, u.name
  FROM orders o
  JOIN users u ON u.id = o.user_id
  LIMIT 100
`);

For complex nested data, use json_agg to fetch related records in one query:

-- Get orders with their items — one query instead of N+1
SELECT
  o.id,
  o.total,
  o.status,
  json_build_object(
    'id', u.id,
    'email', u.email,
    'name', u.name
  ) AS user,
  json_agg(
    json_build_object(
      'id', oi.id,
      'product_name', oi.product_name,
      'quantity', oi.quantity,
      'unit_price', oi.unit_price
    )
    ORDER BY oi.id
  ) AS items
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'pending'
GROUP BY o.id, u.id
ORDER BY o.created_at DESC
LIMIT 50;

---

Step 5: Connection Pool Tuning

Connection exhaustion kills APIs silently — requests queue, then timeout.

-- Current connection state
SELECT
  state,
  count(*) AS connections,
  max(now() - state_change) AS max_duration
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

-- Long-running queries (check for stuck transactions)
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '30 seconds'
  AND state != 'idle'
ORDER BY duration DESC;

-- Kill a stuck query
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state = 'idle in transaction'
  AND query_start < now() - interval '10 minutes';

Pool configuration for Node.js:

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

  // Max connections: set to (core_count * 2) + disk_spindles
  // For a 4-core server: 10-20 is typical
  max: 20,

  // How long to wait for an available connection before throwing
  connectionTimeoutMillis: 5000,

  // Close idle connections after 30 seconds
  idleTimeoutMillis: 30000,

  // Maximum time a query can run before being cancelled
  statement_timeout: 30000, // 30 seconds
});

// Monitor pool health
setInterval(() => {
  console.log('Pool stats:', {
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  });
}, 60_000);

For high traffic: use PgBouncer in front of PostgreSQL:

# /etc/pgbouncer/pgbouncer.ini
[databases]
appdb = host=localhost port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction      # Best for most web apps
max_client_conn = 1000       # Max connections from your app
default_pool_size = 20       # Max connections to PostgreSQL per database
min_pool_size = 5
reserve_pool_size = 5

---

Step 6: Query Rewrites for Common Patterns

Avoid SELECT *

-- BAD: fetches all columns, including large text/bytea fields
SELECT * FROM products WHERE category_id = $1;

-- GOOD: only fetch what you need
SELECT id, name, price, stock_count FROM products WHERE category_id = $1;

Use CTEs for Readability, Not Performance

In PostgreSQL 12+, CTEs are inlined by default (they used to be optimization fences):

-- This is fine — the CTE is inlined and the planner optimizes the full query
WITH recent_orders AS (
  SELECT id, user_id, total
  FROM orders
  WHERE created_at > now() - interval '7 days'
)
SELECT u.email, sum(ro.total) AS revenue
FROM recent_orders ro
JOIN users u ON u.id = ro.user_id
GROUP BY u.email
ORDER BY revenue DESC;

Efficient Pagination

-- BAD: OFFSET is slow on large tables (must scan all rows to skip)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- GOOD: cursor-based pagination (keyset pagination)
-- First page:
SELECT id, created_at, total FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page (use last row's values as cursor):
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-03-15 10:00:00', 'uuid-of-last-row')
ORDER BY created_at DESC, id DESC
LIMIT 20;

Batch Inserts

// BAD: 1000 individual inserts = 1000 round trips
for (const item of items) {
  await db.query('INSERT INTO events (user_id, type, data) VALUES ($1, $2, $3)', [
    item.userId, item.type, item.data
  ]);
}

// GOOD: single multi-row insert
function buildBulkInsert(items: Array<{ userId: string; type: string; data: object }>) {
  const values: unknown[] = [];
  const placeholders = items.map((item, i) => {
    const base = i * 3;
    values.push(item.userId, item.type, JSON.stringify(item.data));
    return `($${base + 1}, $${base + 2}, $${base + 3})`;
  });

  return {
    text: `INSERT INTO events (user_id, type, data) VALUES ${placeholders.join(', ')}`,
    values,
  };
}

// Chunk to avoid parameter limit (max 65535 params in PostgreSQL)
const CHUNK_SIZE = 500;
for (let i = 0; i < items.length; i += CHUNK_SIZE) {
  const chunk = items.slice(i, i + CHUNK_SIZE);
  const { text, values } = buildBulkInsert(chunk);
  await db.query(text, values);
}

---

Step 7: Table Maintenance

-- Check table bloat (dead tuples that need vacuuming)
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  n_dead_tup AS dead_tuples,
  n_live_tup AS live_tuples,
  round(100 * n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- If autovacuum isn't keeping up, run manually
VACUUM ANALYZE orders;

-- For tables with heavy bloat, reclaim disk space (locks the table!)
VACUUM FULL orders;  -- use pg_repack for online bloat removal in production

---

postgresql.conf: Key Settings

# Memory (set to 25% of RAM for shared_buffers, 75% for effective_cache_size)
shared_buffers = 2GB                  # For an 8GB RAM server
effective_cache_size = 6GB
work_mem = 64MB                       # Per-sort/hash operation (can be high)
maintenance_work_mem = 512MB          # For VACUUM, CREATE INDEX

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9

# Query planner
random_page_cost = 1.1                # For SSDs (default 4.0 is for spinning disk)
effective_io_concurrency = 200        # For SSDs

# Logging (find slow queries in production)
log_min_duration_statement = 1000     # Log queries slower than 1 second
log_lock_waits = on

---

Aunimeda builds and optimizes production backend systems — from initial architecture through performance tuning and scaling.

Contact us to discuss your project. See also: Custom Software Development, DevOps, Web Development