Md Tarek — Portfolio© 2026

Full-stack Developer

System Design — built to hold up.

dhaka → remote, worldwide

Loading experience

Md TarekTarek
Dev

PostgreSQL High Performance Indexing & Query Optimization Guide

By: Md Tarek · 21 Oct 2026

PostgreSQL high performance indexing and query optimization with EXPLAIN ANALYZE BUFFERS and PgBouncer architecture
PostgreSQL high performance indexing and query optimization with EXPLAIN ANALYZE BUFFERS and PgBouncer architecture

Executive Summary & Key Takeaways

  • EXPLAIN (ANALYZE, BUFFERS) is the minimum viable production profile: read shared read vs shared hit, temp read/written, Heap Fetches, Rows Removed by Filter, and estimated-vs-actual row skew. A >10x row estimate error is usually a statistics or data-skew bug, not an index bug.
  • Column order in composite B-tree indexes is an API contract: equality predicates first, then range/sort columns, and always add a deterministic tie-breaker such as id for cursor pagination. A correct (tenant_id, created_at DESC, id DESC) index typically turns a 500k-row window scan into a 3–8 ms index seek at 50M rows.
  • Partial indexes are the highest-leverage storage optimization for hot subsets: WHERE status IN ('paid','fulfilled') can shrink index size by 70–95%, reduce cache pressure, and cut write amplification; the query predicate must match the index predicate for the planner to use it.
  • Keyset cursor pagination is O(log N + page_size) and stable under concurrent writes; OFFSET 100000 LIMIT 50 is O(offset + limit) and commonly produces 1–2 s p95 late-page latency, temp-block spills, and duplicate/missing rows.
  • PgBouncer pool_mode = transaction multiplexes thousands of client connections onto a small server pool, but it removes session affinity: no session-level SET, no LISTEN/NOTIFY, no advisory locks across transactions, and named prepared statements require PgBouncer 1.21+ with max_prepared_statements or a driver-side simple/unnamed protocol.

Reading EXPLAIN (ANALYZE, BUFFERS) Like a Production Engineer

The planner chooses a plan from statistics, cost constants, and available indexes; the executor reports what actually happened. BUFFERS adds block-level accounting, which is where most production mysteries live: cache hits, physical reads, dirty/written blocks, and temporary spill files.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id, tenant_id, status, total_cents, created_at
FROM orders
WHERE tenant_id = 'acme'
  AND status IN ('paid','fulfilled')
ORDER BY created_at DESC
LIMIT 50;

Typical output highlights to inspect first:

Limit  (cost=0.43..128.90 rows=50 width=72) (actual time=18.221..18.244 rows=50 loops=1)
  Buffers: shared hit=42 read=6
  ->  Index Scan using idx_orders_active_tenant_created_id on orders
        (cost=0.43..25831.12 rows=10000 width=72) (actual time=0.041..17.902 rows=50 loops=1)
        Index Cond: ((tenant_id = 'acme'::text) AND (created_at < '2026-01-01'::timestamptz))
        Filter: (status = ANY ('{paid,fulfilled}'::text[]))
        Rows Removed by Filter: 0
        Heap Fetches: 50
        Buffers: shared hit=42 read=6

Interpretation rules that hold up under incident pressure:

  • shared read means PostgreSQL asked the OS for pages not in shared_buffers; sustained reads on a hot query indicate index or cache pressure.
  • temp read/written means a sort, hash, or bitmap spilled beyond work_mem; increase work_mem only for the session or query, never globally by default.
  • Heap Fetches on an Index Only Scan means the visibility map is not set for many pages; this is often vacuum lag, not a broken index.
  • Rows Removed by Filter above 10–20% of rows read usually means the index is not selective enough for the predicate, or the composite column order is wrong.
  • Estimated rows=10000 vs actual rows=50 means stale or insufficient statistics; run ANALYZE, add extended statistics for correlated columns, and check for skewed tenant IDs.
Client SQL
   |
   v
Parser/Rewriter -> Planner (pg_statistic, extended stats, cost model)
   |
   v
Executor -> Buffer Manager -> shared_buffers -> OS page cache -> storage
   |            |
   |            +-- shared hit/read, dirtied/written
   +--> Sort/Hash -> temp read/written when work_mem exceeded

War story: a multi-tenant dashboard used OFFSET $page * 50 on a 90M-row events table. Page 4000 took 1.4 s p95, wrote 220 MB of temp blocks, and returned duplicate rows when new events arrived between page loads. The fix was not a bigger instance: a partial covering index for active tenants plus a signed keyset cursor dropped p95 to 6–9 ms and removed temp I/O entirely.

Index Architecture: Composite, Partial, and Covering Indexes

B-tree indexes are ordered lexicographically by their key columns. For (tenant_id, status, created_at DESC), the left-prefix rule is strict:

  • tenant_id = ? uses the first column.
  • tenant_id = ? AND status = ? uses the first two columns.
  • tenant_id = ? AND status = ? AND created_at > ? uses all three columns.
  • status = ? alone cannot efficiently use this index because tenant_id is missing from the left prefix.

For OLTP queries, order equality columns first, then range/sort columns. A range predicate before an equality predicate prevents efficient use of later key columns. Add id as a deterministic tie-breaker for cursor pagination, and use INCLUDE for projection columns that should not affect index ordering.

-- Composite + partial + covering in one deliberate index.
-- Run outside a transaction; CREATE INDEX CONCURRENTLY cannot run inside BEGIN/COMMIT.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_active_tenant_created_id
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (status, total_cents, currency)
WHERE status IN ('paid','fulfilled');

-- Worker queue: status is in the predicate, so priority/run_at lead the key.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_ready_priority_run_at
ON jobs (priority DESC, run_at ASC)
WHERE status = 'ready';

-- Detect failed concurrent index builds before they silently waste space.
SELECT indexrelid::regclass, indisvalid, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_index
WHERE NOT indisvalid;

Partial indexes are not just smaller; they change the planner cost equation. If 2% of orders are active, indexing only status IN ('paid','fulfilled') keeps the hot B-tree in memory and reduces write amplification on the 98% cold path. Covering indexes help only when the visibility map is healthy; otherwise the executor still visits heap pages for visibility checks. Monitor n_dead_tup and autovacuum on high-churn tables before blaming the index.

Pagination at Scale: Keyset Cursors Beat OFFSET

OFFSET is not a cursor; it is a request to generate and discard rows. At offset 100k, PostgreSQL may read 100,050 rows to return 50 rows, and concurrent inserts shift the window, causing duplicates and skipped records.

Keyset pagination seeks directly to the last seen sort key. The sort order and tie-breaker must be unique and backed by an index whose key order matches the ORDER BY exactly.

-- Keyset seek: deterministic DESC order, id tie-breaker.
-- First page omits the row-comparison predicate.
SELECT id, tenant_id, status, total_cents, currency, created_at
FROM orders
WHERE tenant_id = $1
  AND status IN ('paid','fulfilled')
  AND (created_at, id) < ($2::timestamptz, $3::uuid)
ORDER BY created_at DESC, id DESC
LIMIT $4;

The row comparison (created_at, id) < ($2, $3) is lexicographic and matches the composite index order. For ascending feeds, flip the comparison to > and order ascending. Sign or encrypt the cursor payload so clients cannot forge tenant boundaries or arbitrary offsets.

Connection Pooling with PgBouncer: Transaction Mode Done Right

PostgreSQL is process-per-connection. Each backend has private memory, and thousands of direct connections create context-switch storms, lock contention, and unpredictable p99 latency. PgBouncer is an external multiplexer, not a query cache and not a substitute for indexing.

[databases]
app = host=postgres.internal port=5432 dbname=app

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
query_wait_timeout = 5
server_idle_timeout = 60
server_lifetime = 3600
# server_reset_query is for session pooling; transaction mode should not rely on session state.

Sizing guidance: set default_pool_size to roughly 2–4x vCPU for OLTP workloads, not thousands. max_client_conn is the client-side admission limit; default_pool_size is the server-side pool per user/database pair. Keep application pool max low per pod and let PgBouncer be the central queue.

Transaction pooling breaks session state. Avoid session-level SET, LISTEN/NOTIFY, session temp tables, and advisory locks that span transactions. Use SET LOCAL inside a transaction when a per-statement setting is required.

Prepared statements are the most common migration trap. PgBouncer before 1.21 cannot safely reuse named prepared statements across server assignments in transaction mode. Node pg parameterized queries use unnamed statements by default and are generally safe. pgx users should set DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol or disable the statement cache unless PgBouncer is 1.21+ and configured with max_prepared_statements.

Practical Implementation

-- migrations/001_orders_keyset_index.sql
-- Run outside a transaction. If it fails, drop the invalid index manually.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_active_tenant_created_id
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (status, total_cents, currency)
WHERE status IN ('paid','fulfilled');
import { Pool, type QueryResult } from 'pg';
import { createHmac, timingSafeEqual } from 'node:crypto';

type OrderRow = {
  id: string;
  tenant_id: string;
  status: 'paid' | 'fulfilled';
  total_cents: number;
  currency: string;
  created_at: Date;
};

type OrderCursor = { createdAt: string; id: string };

const CURSOR_SECRET = process.env.CURSOR_SECRET;
if (!CURSOR_SECRET) throw new Error('CURSOR_SECRET is required');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 10_000,
  connectionTimeoutMillis: 2_000,
  options: '-c statement_timeout=5000 -c idle_in_transaction_session_timeout=10000 -c application_name=orders-api',
});

function sign(payload: string): string {
  return createHmac('sha256', CURSOR_SECRET).update(payload).digest('base64url');
}

export function encodeCursor(cursor: OrderCursor): string {
  const payload = Buffer.from(JSON.stringify(cursor), 'utf8').toString('base64url');
  return `${payload}.${sign(payload)}`;
}

export function decodeCursor(raw: string): OrderCursor {
  const [payload, signature] = raw.split('.');
  if (!payload || !signature) throw new Error('INVALID_CURSOR');

  const expected = sign(payload);
  const actualBuf = Buffer.from(signature);
  const expectedBuf = Buffer.from(expected);
  if (actualBuf.length !== expectedBuf.length || !timingSafeEqual(actualBuf, expectedBuf)) {
    throw new Error('INVALID_CURSOR');
  }

  const parsed = JSON.parse(Buffer.from(payload, 'base64url').toString('utf8')) as unknown;
  if (typeof parsed !== 'object' || parsed === null) throw new Error('INVALID_CURSOR');

  const record = parsed as Record<string, unknown>;
  if (typeof record.createdAt !== 'string' || typeof record.id !== 'string') {
    throw new Error('INVALID_CURSOR');
  }
  if (Number.isNaN(Date.parse(record.createdAt))) throw new Error('INVALID_CURSOR');

  return { createdAt: record.createdAt, id: record.id };
}

export async function listOrders(input: {
  tenantId: string;
  cursor?: string;
  limit?: number;
}): Promise<{ rows: OrderRow[]; nextCursor: string | null }> {
  const limit = Math.min(Math.max(input.limit ?? 50, 1), 100);
  const cursor = input.cursor ? decodeCursor(input.cursor) : null;

  const params: Array<string | number> = [input.tenantId];
  let where = `tenant_id = $1 AND status IN ('paid','fulfilled')`;

  if (cursor) {
    params.push(cursor.createdAt, cursor.id);
    where += ` AND (created_at, id) < ($2::timestamptz, $3::uuid)`;
  }

  params.push(limit + 1);

  const sql = `
    SELECT id, tenant_id, status, total_cents, currency, created_at
    FROM orders
    WHERE ${where}
    ORDER BY created_at DESC, id DESC
    LIMIT $${params.length}
  `;

  const result: QueryResult<OrderRow> = await pool.query<OrderRow>(sql, params);
  const hasMore = result.rows.length > limit;
  const rows = hasMore ? result.rows.slice(0, limit) : result.rows;
  const last = rows.at(-1);

  return {
    rows,
    nextCursor: hasMore && last
      ? encodeCursor({ createdAt: last.created_at.toISOString(), id: last.id })
      : null,
  };
}

Performance Benchmarks & Trade-Offs

| Strategy | Asymptotic work | Representative p95 at 50M orders | Storage / write cost | Concurrency safety | Use when | |---|---|---|---|---|---| | OFFSET 100000 LIMIT 50 | O(offset + limit); scans and discards rows | 1.1–1.7 s | no extra index | duplicates/skips under writes | admin exports, bounded offsets | | Keyset (created_at,id) | O(log N + limit); ordered index seek | 2–6 ms | 1 B-tree on sort keys | stable cursor; no page jumps | user-facing feeds | | Composite (tenant_id, created_at DESC, id DESC) | equality + ordered range | 3–8 ms | wider index; write amp | deterministic for tenant | multi-tenant lists | | Partial WHERE status IN (...) | smaller index; less cache | 1–4 ms | 70–95% smaller than full index | predicate must match | hot subset | | Covering INCLUDE (...) | avoids heap fetch when VM set | 30–50% faster than heap-fetching plan | larger index; vacuum dependent | index-only scan | read-heavy projections | | PgBouncer transaction pool | multiplex clients to server pool | API p95 10–18 ms vs 40–70 ms direct under 2k conns | low backend RAM | no session state | stateless APIs |

Pro-Tips & Common Pitfalls

  • Always run EXPLAIN (ANALYZE, BUFFERS) in a transaction with rollback for writes: BEGIN; EXPLAIN ...; ROLLBACK;
  • Create indexes CONCURRENTLY outside a transaction; monitor pg_stat_progress_create_index and invalid indexes.
  • Do not index every foreign key blindly; index only real query paths. Low-cardinality single-column indexes are usually dead weight; use partial indexes instead.
  • Composite order: equality columns, then range/sort columns. Never put a range column before equality columns.
  • INCLUDE columns are projection-only; they are not filterable. Adding them does not replace key columns.
  • Keyset cursors need a unique tie-breaker; sign or encrypt cursor payloads and reject invalid cursors with a 400, not a 500.
  • OFFSET is not pagination for large offsets. If you must support it, cap page depth and reserve deep offsets for admin exports.
  • PgBouncer transaction mode: avoid session-level SET, LISTEN, UNLISTEN, session temp tables, and cross-transaction advisory locks. Use SET LOCAL inside transactions.
  • Prepared statements: use unnamed statements or PgBouncer 1.21+ with max_prepared_statements; for pgx, use simple protocol or disable statement caching when required.
  • Pool sizing: default_pool_size near 2–4x vCPU for OLTP; tune query_wait_timeout; never set app pool max to 500 per pod.
  • Monitor Heap Fetches and n_dead_tup; index-only scans are only fast when the visibility map is healthy.

Frequently Asked Questions (FAQ)

Q: Does PgBouncer transaction pooling support prepared statements? A: PgBouncer before 1.21 cannot safely reuse named prepared statements across server assignments in transaction mode. PgBouncer 1.21+ supports protocol-level prepared statements when max_prepared_statements is enabled. Node pg parameterized queries use unnamed statements by default and are generally safe; pgx should use QueryExecModeSimpleProtocol or disable its statement cache unless the PgBouncer version and configuration are verified.

Q: Why is PostgreSQL not using my index even though it exists? A: Common causes are low selectivity, a function or type cast on the indexed column, mismatched partial-index predicate, stale statistics, wrong composite column order, or an ORDER BY that does not match the index order. Run EXPLAIN (ANALYZE, BUFFERS), inspect Rows Removed by Filter, Heap Fetches, and estimated-vs-actual row counts, then verify the predicate matches the index definition exactly.

Q: Is keyset pagination always better than OFFSET? A: For user-facing feeds and large tables, keyset pagination is almost always faster and safer under concurrent writes. OFFSET remains useful for small bounded offsets, admin exports, and cases where arbitrary page jumps are a hard product requirement. If you need arbitrary jumps, keep OFFSET strictly bounded or use a separate search index with search_after semantics.