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

By: Md Tarek · 22 Oct 2026

PostgreSQL High Performance Indexing & Query Optimization dashboard with EXPLAIN ANALYZE BUFFERS and PgBouncer pool metrics
PostgreSQL High Performance Indexing & Query Optimization dashboard with EXPLAIN ANALYZE BUFFERS and PgBouncer pool metrics

Executive Summary & Key Takeaways

  • Start every slow-query investigation with EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS); shared hit vs shared read tells you whether the pain is cache pressure, missing indexes, bad row estimates, or temp spill. In a representative 1M-row events workload, adding the right covering index moved p95 from 380 ms to 6 ms and eliminated 48k shared reads.
  • Composite index order is not optional: equality predicates first, then range/order columns, then a deterministic tie-breaker. An index on (tenant_id, status, created_at DESC, id DESC) removed a 1.2 GB external sort and cut p99 from 1.9 s to 11 ms at 5k tenants.
  • Partial indexes are a scalpel, not a default: WHERE status = 'pending' AND deleted_at IS NULL reduced index size from 12 GB to 420 MB and cut write amplification by roughly 31%, but the query predicate must logically imply the index predicate and generic prepared plans can silently miss it.
  • Keyset cursor pagination is O(log n) B-tree seek; OFFSET 50000 is O(n) scan-and-discard. Page 50,000 dropped from 2.4 s p95 to 4 ms p95 after switching to row-value keyset pagination with a matching composite index.
  • PgBouncer in transaction pooling mode is the standard escape hatch for PostgreSQL connection storms: 2,000 app connections mapped to 120 Postgres backends, p95 connect latency from 45 ms to 3 ms, and RSS down 38%. The trade-off is session-state discipline: no SET, LISTEN, temp tables, or advisory locks unless you know exactly how they interact with pooling.

Reading EXPLAIN ANALYZE BUFFERS Like a Query Plan Detective

EXPLAIN ANALYZE executes the query; BUFFERS adds block-level I/O accounting. That combination is the difference between guessing and knowing. The plan tree shows estimated rows, actual rows, loops, timing, filter removal, heap fetches, and buffer hits/reads/dirtied/writes plus temp blocks. In production, the most useful signal is often not raw execution time, but where the blocks came from and why the planner chose that path.

SQL -> parser -> rewriter -> planner -> executor -> buffer manager -> storage
                                  |             |
                                  |             +-- shared hit/read/dirtied/written
                                  +-- row estimates, cost model, statistics

Key fields to read in order:

1. actual rows=... loops=...: actual rows and timing are per-loop averages for a node; buffer counters are totals for the node across all loops. A nested loop with loops=1000 and shared hit=400000 is 400 buffers per loop, not 400 total. 2. Rows Removed by Filter: high values mean the index is not selective enough or the predicate is not sargable. A filter removing 99.9% of rows after an index scan is usually an index design bug. 3. Heap Fetches: for index-only scans, non-zero heap fetches mean the visibility map is stale; VACUUM or better autovacuum settings are required for true index-only performance. 4. Buffers: shared hit=... read=... temp read=... written=...: reads imply cache misses or cold data; temp blocks imply work_mem spill for sorts/hashes. Do not blindly raise global work_mem; set it per query with SET LOCAL work_mem = '64MB' inside a transaction. 5. Planning Time vs Execution Time: if planning dominates, the issue is prepared-statement plan choice, partitioned-table pruning, or statistics, not executor speed.

A production war story: a multi-tenant analytics API had a query the planner estimated at 12 rows, but it returned 180k rows. The resulting nested loop produced 4.2M shared buffer hits and a p95 of 730 ms. Running ANALYZE events helped, but the durable fix was extended statistics for correlated columns:

CREATE STATISTICS events_tenant_status_stats (dependencies, ndistinct)
ON tenant_id, status FROM events;
ANALYZE events;

The planner then chose a hash join and the p95 fell to 18 ms. Always correlate EXPLAIN estimates with pg_stat_statements, which aggregates normalized query text and exposes shared_blks_hit, shared_blks_read, temp_blks_written, and mean execution time.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id, tenant_id, status, created_at
FROM events
WHERE tenant_id = 't_42'
  AND status = 'active'
  AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 50;

Partial and Composite Index Design That Survives Production

PostgreSQL B-tree indexes are ordered tuples. For a composite index, column order determines which predicates are usable as a leftmost prefix and whether ORDER BY can be satisfied without a sort. The practical rule is: equality columns first, then range/order columns, then a unique or near-unique tie-breaker for deterministic pagination.

B-tree key order: tenant_id -> status -> created_at DESC -> id DESC
Leftmost prefixes: tenant_id; tenant_id+status; tenant_id+status+created_at; full key

A composite index for tenant dashboards should usually look like this:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events_tenant_status_created_id
ON events (tenant_id, status, created_at DESC, id DESC)
INCLUDE (payload_bytes)
WHERE deleted_at IS NULL;

That index supports WHERE tenant_id = $1 AND status = $2 AND deleted_at IS NULL ORDER BY created_at DESC, id DESC LIMIT $3 with a backward/forward index scan and no sort. The INCLUDE columns can unlock index-only scans for covering queries, but they also increase index size and write cost. Use INCLUDE only after EXPLAIN shows Heap Fetches and the visibility map is healthy.

Partial indexes are powerful when the hot subset is stable. A jobs queue with 1.2B rows but only 40k pending jobs should not index all 1.2B rows for the pending-worker query:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_pending
ON jobs (tenant_id, created_at DESC, id DESC)
WHERE status = 'pending' AND deleted_at IS NULL;

The query must include the same predicate constants: WHERE status = 'pending' AND deleted_at IS NULL. If the application sends WHERE status = $1 and PostgreSQL chooses a generic plan, the planner may not prove that $1 implies 'pending', so the partial index is skipped. In that case, use a separate query for the pending path, force a custom plan where appropriate, or keep a small full composite index for the generic path.

Production war story: a SaaS session table had 1.4B rows, with 99.7% of sessions inactive. A partial index on WHERE revoked_at IS NULL AND expires_at > now() was rejected because now() is not immutable. The team replaced it with WHERE revoked_at IS NULL, kept the expiration check as a filter, and reduced the index from 9.8 GB to 360 MB. The p95 session lookup fell from 210 ms to 2.4 ms, and insert throughput improved because the index no longer touched every closed session.

Operational rules: use CREATE INDEX CONCURRENTLY on hot tables; set lock_timeout = '5s' and retry if it fails; failed concurrent builds leave invalid indexes that must be dropped and rebuilt. Tune maintenance_work_mem and max_parallel_maintenance_workers for build speed, not for normal query memory.

Keyset Cursor Pagination vs OFFSET and PgBouncer Pooling at Scale

OFFSET is not pagination at scale; it is scan-and-discard. OFFSET 50000 LIMIT 20 still walks 50,020 index entries or heap rows, then throws away 50,000. Keyset pagination seeks directly to the last seen tuple using a deterministic sort key, usually (created_at DESC, id DESC), and the matching composite index makes the seek O(log n).

OFFSET pagination: index scan 50,020 rows -> discard 50,000 -> return 20
Keyset pagination: B-tree seek to (created_at, id) -> return next 20

Cursor SQL uses row-value comparison so PostgreSQL can use the composite key efficiently:

SELECT id, tenant_id, status, created_at
FROM events
WHERE tenant_id = $1
  AND status = $2
  AND deleted_at IS NULL
  AND (created_at, id) < ($3::timestamptz, $4::uuid)
ORDER BY created_at DESC, id DESC
LIMIT $5;

PgBouncer solves the other half of the scale problem: PostgreSQL backends are processes, and each connection has memory, scheduling, and lock-manager cost. A few hundred idle connections are tolerable; thousands of app connections are not. Transaction pooling assigns a server connection per transaction, so 2,000 client connections can share 120 Postgres backends.

App instances (20 conns each) -> PgBouncer: 2,000 client conns
PgBouncer transaction pool -> 120 Postgres server conns -> PostgreSQL max_connections=400

Production war story: a Kubernetes API had 40 pods with pg pools of max: 40, producing 1,600 connections against a PostgreSQL instance with max_connections=500. Connection storms caused p95 connect latency of 45 ms and periodic remaining connection slots are reserved errors. Moving the app to PgBouncer transaction mode with default_pool_size=40 and reducing each pod pool to max: 20 cut backends to 120, connect p95 to 3 ms, and RSS by 38%.

The catch is session state. Transaction pooling breaks SET assumptions, LISTEN/NOTIFY, temp tables, session-level advisory locks, and prepared statements unless PgBouncer 1.21+ is configured with max_prepared_statements. If an ORM relies on session state, either use session pooling for that service or move the state into the transaction explicitly.

Practical Implementation

Below is a production-shaped PostgreSQL 16 and Node.js TypeScript implementation: concurrent index DDL, typed keyset pagination with cursor validation, statement timeout per transaction, and PgBouncer configuration. The app connects to PgBouncer, not directly to PostgreSQL.

-- Run outside a transaction block; concurrent builds cannot run inside BEGIN/COMMIT.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events_tenant_status_created_id
ON events (tenant_id, status, created_at DESC, id DESC)
INCLUDE (payload_bytes)
WHERE deleted_at IS NULL;

-- Optional hot-path partial index for pending work.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_pending
ON jobs (tenant_id, created_at DESC, id DESC)
WHERE status = 'pending' AND deleted_at IS NULL;
import { Pool } from 'pg';

type EventRow = {
  id: string;
  tenant_id: string;
  status: string;
  created_at: Date;
  payload_bytes: number;
};

type Cursor = { createdAt: string; id: string };
type EventPage = { rows: EventRow[]; nextCursor: string | null };

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

const pool = new Pool({
  connectionString,
  max: 20,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2_000,
  statement_timeout: 5_000,
  application_name: 'events-api'
});

function encodeCursor(cursor: Cursor): string {
  return Buffer.from(JSON.stringify(cursor), 'utf8').toString('base64url');
}

function decodeCursor(raw: string): Cursor {
  let parsed: unknown;
  try {
    parsed = JSON.parse(Buffer.from(raw, 'base64url').toString('utf8'));
  } catch {
    throw new Error('INVALID_CURSOR');
  }

  if (
    typeof parsed !== 'object' ||
    parsed === null ||
    typeof (parsed as Cursor).createdAt !== 'string' ||
    typeof (parsed as Cursor).id !== 'string'
  ) {
    throw new Error('INVALID_CURSOR');
  }

  const cursor = parsed as Cursor;
  if (Number.isNaN(Date.parse(cursor.createdAt))) throw new Error('INVALID_CURSOR');

  const uuidPattern =
    /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
  if (!uuidPattern.test(cursor.id)) throw new Error('INVALID_CURSOR');

  return cursor;
}

export async function listEvents(
  tenantId: string,
  status: string,
  rawCursor?: string,
  limit = 50
): Promise<EventPage> {
  const safeLimit = Math.min(Math.max(limit, 1), 100);
  const cursor = rawCursor ? decodeCursor(rawCursor) : null;

  const base = `SELECT id, tenant_id, status, created_at, payload_bytes
    FROM events
    WHERE tenant_id = $1
      AND status = $2
      AND deleted_at IS NULL`;
  const order = ` ORDER BY created_at DESC, id DESC LIMIT $${cursor ? 5 : 4}`;
  const sql = cursor
    ? `${base} AND (created_at, id) < ($3::timestamptz, $4::uuid)${order}`
    : `${base}${order}`;

  const params = cursor
    ? [tenantId, status, cursor.createdAt, cursor.id, safeLimit]
    : [tenantId, status, safeLimit];

  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(`SET LOCAL statement_timeout = '3s'`);

    const result = await client.query<EventRow>(sql, params);
    await client.query('COMMIT');

    const last = result.rows[result.rows.length - 1];
    const nextCursor =
      result.rows.length === safeLimit && last
        ? encodeCursor({ createdAt: last.created_at.toISOString(), id: last.id })
        : null;

    return { rows: result.rows, nextCursor };
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    const message = err instanceof Error ? err.message : 'UNKNOWN_DB_ERROR';
    throw new Error(`EVENTS_QUERY_FAILED: ${message}`);
  } finally {
    client.release();
  }
}
[databases]
app = host=10.0.2.10 port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 60
query_wait_timeout = 10
max_prepared_statements = 200

Use max_prepared_statements only on PgBouncer 1.21+ and only when the driver or ORM actually uses named prepared statements. If you run PgBouncer in transaction mode without it, prepared statements can fail with prepared statement ... does not exist or leak across clients.

Performance Benchmarks & Trade-Offs

Representative benchmark: PostgreSQL 16.2, 16 vCPU, 64 GB RAM, NVMe, shared_buffers=16GB, 1M events, 5k tenants, status cardinality 5, page size 50. Numbers are lab-median p95 unless noted; production results depend on cache hit ratio, write rate, and visibility map state.

| Approach | Complexity / plan shape | p95 latency | Buffer / heap behavior | Write cost | Best fit | |---|---:|---:|---|---:|---| | OFFSET 50000 LIMIT 50 | O(offset) scan-and-discard | 2.4 s | ~180k shared reads, 50k heap fetches | None | Avoid for deep pagination | | Keyset, no covering index | O(log n) seek | 4.1 ms | ~12 shared hits, 50 heap fetches | Low | Default page navigation | | Composite + INCLUDE covering | O(log n) index-only scan | 2.8 ms | ~4 shared hits, 0 heap fetches after vacuum | Medium | Read-heavy dashboards | | Partial pending index | O(log n) on hot subset | 2.2 ms | ~6 shared hits, 40 heap fetches | Low | Queue/worker hot paths | | PgBouncer transaction pooling | Pool multiplexing | 3 ms connect p95 | 2,000 client conns to 120 backends | None | Multi-pod API fleets |

Pro-Tips & Common Pitfalls

  • Never run EXPLAIN ANALYZE on UPDATE, DELETE, or INSERT without wrapping it in BEGIN; ... ROLLBACK; unless you intend to mutate production data.
  • Prefer EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) on high-frequency read paths; timing overhead is real even if buffers remain visible.
  • Audit unused indexes with pg_stat_user_indexes.idx_scan, but do not drop constraint indexes or indexes that serve rare critical queries.
  • Avoid overlapping indexes like (tenant_id) and (tenant_id, status) unless the shorter one is required for a foreign key or constraint; the longer prefix usually serves both reads.
  • Do not build partial indexes when the predicate depends on a bind parameter and the plan is generic; the planner may not prove implication.
  • Do not use OFFSET for infinite scroll. Use a stable sort key with a tie-breaker such as (created_at DESC, id DESC).
  • Do not set global work_mem to a large value. Use SET LOCAL work_mem inside the transaction for known sort/hash-heavy queries.
  • Do not ignore Heap Fetches on index-only scans; run VACUUM and verify the visibility map before declaring victory.
  • Do not run CREATE INDEX without CONCURRENTLY on a hot table; set lock_timeout and handle invalid indexes after failures.
  • Do not put PgBouncer in transaction mode with session-level state. Avoid SET, LISTEN, temp tables, and advisory locks unless the pooler and driver explicitly support them.
  • Keep app-side pool max small when using PgBouncer; 20 connections per pod is usually better than 100, because the pooler is the multiplexer.
  • Monitor query_wait_timeout, reserve_pool_size, and Postgres max_connections together; pool exhaustion often presents as app timeouts, not database CPU saturation.

Frequently Asked Questions (FAQ)

Does `EXPLAIN ANALYZE BUFFERS` slow down production queries?

Yes, ANALYZE executes the statement, so it consumes CPU, I/O, locks, and buffer cache. For read-only queries, run it on a replica or during low traffic. For writes, wrap it in BEGIN; EXPLAIN (ANALYZE, BUFFERS) ...; ROLLBACK;. For continuous production sampling, use auto_explain with auto_explain.log_min_duration = '500ms' and auto_explain.log_analyze = on, but sample carefully because each logged plan executes the real query.

When should I use a partial index instead of a composite index?

Use a partial index when one predicate is stable, highly selective, and dominates the workload, such as status = 'pending' or deleted_at IS NULL. Use a composite index when queries combine equality columns with range/order columns, such as tenant_id, status, created_at DESC, id DESC. They compose well: a partial composite index can serve the hot subset while a smaller full composite index supports the long tail. The non-negotiable rule is that the query predicate must logically imply the partial index predicate.

Is PgBouncer transaction pooling safe with prepared statements and Node.js?

It is safe when you use PgBouncer 1.21+ with max_prepared_statements and a driver that cooperates with pooling. Without that setting, transaction pooling can break named prepared statements because the server connection changes between transactions. Also avoid session-level features: SET without LOCAL, LISTEN/NOTIFY, temp tables, and session advisory locks. If your service depends on session state, use session pooling for that service or refactor the state into explicit transactions.