PostgreSQL Performance Tuning in Production: Query Plans, Index Strategy, and Connection Management

Most PostgreSQL performance problems fall into three categories: queries that scan more rows than they need to, indexes that exist but do not get used, and connection handling that saturates the database before the queries even run. Configuration tuning matters, but it is downstream of those three. Fix the query first, fix the index second, fix the pooling third, then tune configuration around what is left.

This guide walks through each layer in that order, with real EXPLAIN ANALYZE output, concrete index decisions, and production-tested configuration values.

Reading EXPLAIN ANALYZE Output

EXPLAIN ANALYZE executes the query and returns the actual plan, not just the estimated one. Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) in production investigations because the BUFFERS option shows how much was read from shared_buffers (cache hits) vs. disk.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
  AND o.status = 'pending';

The output nodes to focus on:

Seq Scan: The planner chose to read the entire table. This is correct when selectivity is low (you are fetching more than 5-15% of rows). It is a problem when selectivity is high and no index exists, or the index exists but the planner’s row estimate is wrong.

Index Scan: Uses the index to find rows, then fetches the heap page for each. Good for high-selectivity predicates returning a small number of rows.

Bitmap Index Scan + Bitmap Heap Scan: PostgreSQL collects all matching TIDs from the index first, sorts them by physical location, then fetches heap pages in order. This avoids random I/O for medium-selectivity queries. The Recheck Cond line appears when the bitmap is lossy (too many matches to hold in work_mem).

Index Only Scan: The query is satisfied entirely from the index without touching heap pages. Requires all projected columns and filter columns to be in the index. The Heap Fetches: N line tells you how many pages were fetched anyway due to visibility checks. A high heap fetch count means your autovacuum is not keeping the visibility map current.

Hash Join vs. Merge Join vs. Nested Loop: Hash joins are good for large unsorted inputs. Merge joins require sorted inputs but can be fast when indexes provide sort order. Nested loop works well when the outer relation is small and the inner side has an efficient index lookup. If you see a nested loop with a seq scan on the inner side, the join order or indexes are wrong.

The key numbers in each node:

->  Index Scan using idx_orders_user_id on orders  (cost=0.56..8.58 rows=1 width=16)
                                                    (actual time=0.021..0.023 rows=3 loops=1)
      Index Cond: (user_id = u.id)
      Buffers: shared hit=4

cost=start..total is the planner’s estimate. actual time=start..total is measured wall time in milliseconds. rows=1 vs rows=3 shows the planner underestimated by 3x here, which is fine. If the estimate is off by 100x or more, ANALYZE the table or adjust statistics targets.

Index Strategy

B-tree Indexes

The default. Use them for equality, range, ORDER BY, and prefix matching (LIKE 'prefix%'). Composite B-tree indexes follow left-prefix rules: (a, b, c) satisfies filters on a, (a, b), or (a, b, c), but not b alone.

Column order in a composite index matters. Put the highest-cardinality equality column first when you have both equality and range predicates:

-- Query: WHERE tenant_id = $1 AND created_at > $2
-- Good: equality column first
CREATE INDEX idx_events_tenant_created ON events (tenant_id, created_at);

-- Also good: if you also query created_at alone, keep a separate index
CREATE INDEX idx_events_created ON events (created_at);

Covering Indexes

A covering index includes all columns referenced in the query so PostgreSQL can use an Index Only Scan. The INCLUDE clause adds non-key columns to the index leaf pages without making them part of the sort key.

-- Query projects id, status, created_at filtered by user_id
CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (status, total, created_at);

This avoids heap fetches entirely for that query pattern. The tradeoff: larger index, more write amplification. Worth it on hot read paths where the table is large and the columns are not wide.

Partial Indexes

Index only the rows you actually query. A partial index on status = 'pending' is dramatically smaller than a full index on status:

CREATE INDEX idx_jobs_pending
  ON background_jobs (created_at)
  WHERE status = 'pending';

The planner will use this index only when the query includes status = 'pending' in the WHERE clause. Partial indexes are particularly effective for soft-delete patterns, queue tables, and any table where one status value dominates and you only query a minority subset.

GIN and GiST Indexes

GIN (Generalized Inverted Index) is the right choice for:

• Full-text search (tsvector)
• JSONB containment (@>) and key existence (?)
• Array element lookups (@>, &&)

GiST (Generalized Search Tree) covers:

• Geometric types and spatial queries (PostGIS)
• Range types (tsrange, int4range)
• Nearest-neighbor search with <-> operator

-- JSONB attribute filtering
CREATE INDEX idx_products_attrs_gin ON products USING GIN (attributes);

-- Query: WHERE attributes @> '{"color": "red"}'
-- Uses the GIN index above

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));

GIN indexes are expensive to write and large in memory. Do not add them speculatively. Add them when you have a measured query that cannot use a B-tree.

Avoiding Unnecessary Index Creation

Every index you add is a write penalty on INSERT, UPDATE, DELETE. Before creating an index, check whether one already covers the predicate via pg_stat_user_indexes:

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Indexes with idx_scan = 0 for more than a week are candidates for removal. Unused indexes consume disk, slow writes, and inflate backup size.

Query Optimization Patterns

Avoiding Sort Operations

When a query includes ORDER BY, the planner either sorts in memory/disk or uses an index that provides the order. Prefer the latter on large tables:

// Drizzle ORM: paginated query on a table with (user_id, created_at DESC) index
const orders = await db
  .select({
    id: ordersTable.id,
    status: ordersTable.status,
    total: ordersTable.total,
    createdAt: ordersTable.createdAt,
  })
  .from(ordersTable)
  .where(eq(ordersTable.userId, userId))
  .orderBy(desc(ordersTable.createdAt))
  .limit(20)
  .offset(page * 20);

The index (user_id, created_at DESC) makes this an Index Scan with no sort node. Without it, PostgreSQL sorts the filtered rows in memory with a Sort node.

For deep pagination, OFFSET becomes expensive because PostgreSQL must scan and discard rows. Use keyset pagination instead:

// Keyset pagination: pass the last seen (created_at, id) from the previous page
const orders = await db
  .select({ id: ordersTable.id, status: ordersTable.status, total: ordersTable.total, createdAt: ordersTable.createdAt })
  .from(ordersTable)
  .where(
    and(
      eq(ordersTable.userId, userId),
      or(
        lt(ordersTable.createdAt, lastCreatedAt),
        and(eq(ordersTable.createdAt, lastCreatedAt), lt(ordersTable.id, lastId))
      )
    )
  )
  .orderBy(desc(ordersTable.createdAt), desc(ordersTable.id))
  .limit(20);

Fixing Row Estimate Problems

When EXPLAIN ANALYZE shows large discrepancies between estimated and actual rows, the statistics are stale or the default statistics target (100 buckets) is too coarse for the column’s distribution.

Increase the statistics target for high-cardinality or skewed columns:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

For JSONB columns with complex nested structures, the default statistics are nearly useless. Consider extracted generated columns for frequently queried JSONB paths:

ALTER TABLE events ADD COLUMN event_type TEXT GENERATED ALWAYS AS (payload->>'type') STORED;
CREATE INDEX idx_events_type ON events (event_type);

Connection Pooling

Why PostgreSQL Connections Are Expensive

Each PostgreSQL backend is a separate process. Forking a process costs roughly 5-10ms and 5-10 MB of RAM. At 100 connections, you are using 500 MB just for process overhead before any query memory. Beyond ~200-300 connections, the scheduler overhead degrades throughput for everyone.

The practical limit for most production databases is 100-200 active connections. If your application tier has 10 servers each opening 20 connections, you are at the limit before any traffic spike.

PgBouncer

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. It multiplexes many application connections onto a small number of server connections.

Three pooling modes:

Mode	Connection reuse	Constraint
Session	Connection held for the full client session	No multiplexing until client disconnects
Transaction	Connection returned to pool after each transaction	Cannot use session-level features (prepared statements in most configs, SET LOCAL, advisory locks)
Statement	Connection returned after each statement	Rare; breaks multi-statement transactions

Transaction mode gives the highest connection multiplexing but requires your application to not rely on session state. Most ORM-based applications work in transaction mode if you disable server-side prepared statements or use server_reset_query = DISCARD ALL.

A minimal PgBouncer configuration for transaction mode:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_reset_query = DISCARD ALL
log_connections = 0
log_disconnections = 0

default_pool_size = 20 means PgBouncer maintains at most 20 server connections per database/user pair. Tune this based on max_connections in PostgreSQL minus connections reserved for replicas, admin tasks, and monitoring.

Serverless Poolers

In serverless environments (Vercel Functions, Cloudflare Workers, AWS Lambda), each function invocation may open its own connection. PgBouncer running alongside your database handles this, but managed options like Neon’s connection pooling and Supabase’s Supavisor are designed for this workload.

Neon uses a connection string with ?pgbouncer=true that routes through a pooler. Supavisor is Supabase’s Elixir-based pooler with tenant-aware routing. Both handle the connection lifecycle transparently from your application’s perspective.

// Drizzle with Neon serverless driver (transaction-mode pooler)
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";

const sql = neon(process.env.DATABASE_URL!);
const db = drizzle(sql);

// Each request uses a fresh HTTP connection to the pooler.
// No persistent connection held between invocations.

For long-running Node.js services, use the node-postgres driver with a local PgBouncer or a Pool with a bounded max:

import { Pool } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,              // max server connections from this process
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2_000,
});

export const db = drizzle(pool);

Configuration Tuning

These are starting values for a dedicated PostgreSQL server. Adjust based on your actual workload measurements.

shared_buffers: The shared memory cache for table and index pages. Set to 25% of total RAM. On a 16 GB server: shared_buffers = 4GB. PostgreSQL also relies on the OS page cache, so you do not set this higher than needed.

effective_cache_size: Tells the planner how much total cache (shared_buffers + OS page cache) is available. This affects cost estimates for index usage. Set to 75% of total RAM: effective_cache_size = 12GB on a 16 GB server. This is a hint to the planner only, not allocated memory.

work_mem: Memory per sort or hash operation, per connection. The default (4 MB) causes many disk spills. On OLTP workloads with 100 connections, setting this to 64 MB means you could allocate 6.4 GB just from sort operations. Start at 16-32 MB and increase only for known heavy query workloads, or set it per-session with SET work_mem:

-- For a known heavy analytics query in a specific session
SET work_mem = '256MB';
SELECT ... complex aggregation ...;
RESET work_mem;

maintenance_work_mem: Memory used by VACUUM, CREATE INDEX, and ALTER TABLE. Set to 256 MB or more. This does not multiply per connection the way work_mem does. Faster vacuums and index builds are worth the memory.

max_connections: Set this to the actual number of connections you plan to have, not an inflated safety number. Extra connections consume shared memory. With PgBouncer in front, 100-150 max_connections is usually sufficient.

checkpoint_completion_target: Set to 0.9 to spread checkpoint writes over more of the checkpoint interval, reducing I/O spikes.

wal_buffers: Default is auto-tuned to 1/32 of shared_buffers, minimum 64 kB. On high-write workloads, set explicitly to 64MB.

random_page_cost: Default is 4.0 (assumes spinning disk). For SSDs and cloud block storage, set to 1.1 or 1.2. This makes the planner more willing to use index scans over seq scans on larger result sets.

# postgresql.conf for a 16 GB dedicated server on SSD
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 32MB
maintenance_work_mem = 512MB
max_connections = 150
checkpoint_completion_target = 0.9
wal_buffers = 64MB
random_page_cost = 1.1

Vacuum and Autovacuum

PostgreSQL uses MVCC: old row versions accumulate as dead tuples after UPDATE and DELETE. VACUUM reclaims dead tuple space. Without it, tables bloat, scans slow down, and eventually transaction ID wraparound stops the database.

Autovacuum runs automatically but its defaults are conservative. On high-write tables, tune the triggers:

-- High-write orders table: vacuum more aggressively
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- trigger at 1% dead tuples (default 0.2)
  autovacuum_analyze_scale_factor = 0.005, -- trigger analyze at 0.5% changes
  autovacuum_vacuum_cost_delay = 2         -- ms of delay per cost unit (default 2, lower = faster)
);

The scale factor controls the threshold as a fraction of table size. A 10-million-row table with autovacuum_vacuum_scale_factor = 0.2 will not vacuum until 2 million rows are dead. Setting it to 0.01 triggers vacuum at 100,000 dead rows instead.

Monitor vacuum activity with:

SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Tables with dead_pct > 10 and no recent last_autovacuum timestamp need attention. Either autovacuum is not keeping up (increase autovacuum_max_workers or lower cost delay), or explicit VACUUM ANALYZE is needed.

For tables with heavy churn, also watch bloat in indexes. REINDEX CONCURRENTLY rebuilds an index without locking reads or writes.

Monitoring Slow Queries with pg_stat_statements

pg_stat_statements tracks query performance across all executions. It normalizes queries (replaces literals with placeholders) so you see aggregate stats per query pattern.

Enable it in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

After a restart, create the extension in your database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query the top time consumers:

SELECT
  LEFT(query, 100) AS query_snippet,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

The combination of mean_exec_time and calls tells you where to focus. A query that averages 500ms but runs 10 times per day costs less than a query averaging 20ms that runs 50,000 times per day. Sort by total_exec_time to find the actual wall-clock cost.

High stddev_exec_time relative to mean_exec_time points to queries with inconsistent plans, often caused by parameter sniffing or bloat in specific partitions.

Reset the stats after you deploy an optimization to get a clean baseline:

SELECT pg_stat_statements_reset();

Tradeoffs Table

Dimension	Approach A	Approach B	Notes
Index coverage	Separate indexes per query pattern	Composite covering index	Composite reduces index count but increases write amplification per index
Connection pooling	Application-level pool (pg Pool)	PgBouncer transaction mode	PgBouncer necessary above 200 connections; app pool is simpler for small services
work_mem	Low global (16MB), raise per session	High global (64MB+)	High global is unsafe with many connections; per-session raise is targeted but requires coordination
Vacuum	Default autovacuum settings	Per-table autovacuum tuning	Default is fine for low-write tables; high-write tables need explicit thresholds
Statistics target	Default (100)	Raised per column (300-500)	Raise only for columns with skewed distributions or known estimate problems
random_page_cost	Default (4.0)	1.1 for SSD/cloud storage	Wrong cost model causes planner to prefer seq scans over index scans on cloud VMs

Production Considerations

Index bloat: B-tree indexes accumulate dead pages on tables with frequent deletes. pg_relation_size(indexname::regclass) compared to the equivalent pg_indexes_size will show bloat over time. REINDEX CONCURRENTLY resolves it without downtime.

Lock contention during maintenance: CREATE INDEX CONCURRENTLY avoids a write lock but takes longer and uses more resources. Always use it in production. It requires two passes over the table and can fail if a concurrent transaction is open for too long.

Statistics on partial columns: The planner cannot use column statistics effectively for expressions. If you filter on LOWER(email), create a functional index: CREATE INDEX ON users (LOWER(email)). Without it, you get a seq scan regardless of table size.

Transaction ID wraparound: PostgreSQL’s 32-bit transaction ID space wraps at approximately 2 billion transactions. Autovacuum tracks and freezes old XIDs. Watch pg_database.datfrozenxid and alert when any database is within 200 million transactions of the limit. Wraparound prevention vacuums will run aggressively and can impact performance.

Parallel query: Available for seq scans and hash joins since PostgreSQL 9.6. Controlled by max_parallel_workers_per_gather. Useful for analytics queries on large tables. Set parallel_setup_cost and parallel_tuple_cost appropriately for your hardware to let the planner choose parallelism correctly.

---

PostgreSQL gives you enough instrumentation to find the real problem. Start with pg_stat_statements to identify the query, use EXPLAIN (ANALYZE, BUFFERS) to understand the plan, then fix the index or query structure. Configuration values matter, but they matter less than a query that reads 10 million rows to return 10.

The patterns that recur in production: missing indexes on foreign keys used in joins, ORDER BY columns not covered by the index, wide tables with fat pages causing bitmap heap scans to go lossy, and autovacuum falling behind on high-write tables. Address those four and you handle the majority of the load problems that show up at scale.