VISEN GROUP
Technology & Engineering

Optimizing PostgreSQL Performance: Beyond Basic B-Trees

November 4, 20263 min readUncategorized

As relational database tables scale past tens of millions of rows, unoptimized queries quickly exhaust CPU and I/O capacity. While developers routinely add indexes to fix slow queries, naive indexing creates new problems: bloated storage, degraded write performance (INSERT/UPDATE overhead), and indexes that the query planner completely ignores.

This engineering runbook explores advanced PostgreSQL indexing strategies to achieve sub-millisecond query performance under heavy concurrent workloads.

1. The Mechanics of Composite Index Ordering

When creating a multi-column (composite) index, column order determines whether the index can be utilized. PostgreSQL evaluates composite B-Tree indexes strictly from left to right.

The Rule of Cardinality & Filter Types:

  1. Equality First (=): Place columns used in exact equality matches first.
  2. High Cardinality Second: Among equality columns, place those with the highest number of unique values earlier in the index.
  3. Range Filters Last (>, <, BETWEEN, IN): Place range columns at the end. Once PostgreSQL encounters a range condition, it cannot use subsequent index columns for index scans.

SQL

-- OPTIMAL: Equality on status first, followed by range scan on created_at
CREATE INDEX idx_orders_status_created_at 
ON orders (status, created_at DESC);

-- Query that fully utilizes the composite index:
SELECT id, user_id, total_amount 
FROM orders 
WHERE status = 'COMPLETED' 
  AND created_at >= '2026-01-01 00:00:00'
ORDER BY created_at DESC 
LIMIT 50;

2. Drastically Reducing Storage with Partial Indexes

Indexing every single row in a massive table wastes disk space and degrades write throughput. If your application frequently queries a small fraction of rows (e.g., pending tasks, active sessions, or unfulfilled orders), use a Partial Index.

SQL

-- Indexes ONLY rows that require processing (e.g., 2% of a 100M row table)
CREATE INDEX idx_jobs_pending_execution 
ON background_jobs (scheduled_at, priority DESC) 
WHERE status = 'PENDING';

Performance Comparison:

  • Full Index Size: ~4.5 GB for 50 million rows.
  • Partial Index Size: ~45 MB for 500,000 pending rows (99% reduction in disk and RAM overhead).

3. Covering Indexes with INCLUDE Clauses (Index-Only Scans)

An Index-Only Scan occurs when PostgreSQL retrieves all required query columns directly from the index B-Tree without fetching the table tuple from disk (Heap). You can append non-key columns to an index using the INCLUDE clause:

SQL

-- Key columns define the B-Tree structure; INCLUDE columns carry payload data
CREATE INDEX idx_users_email_covering 
ON users (email) 
INCLUDE (id, first_name, last_name, role);

-- Execution plan triggers a pure Index-Only Scan (Zero Heap Fetches):
SELECT id, first_name, last_name, role 
FROM users 
WHERE email = '[email protected]';

4. Verification Runbook with EXPLAIN (ANALYZE, BUFFERS)

Never assume an index works without inspecting the PostgreSQL Query Planner output. Run your query with BUFFERS enabled to measure actual memory/disk page reads:

SQL

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, total_amount 
FROM orders 
WHERE status = 'COMPLETED' AND created_at >= '2026-01-01'
LIMIT 50;

What to look for in execution logs:

  • Index Scan / Index Only Scan: Desired behavior.
  • Shared Read/Hit: High Read values indicate disk reads; high Hit values indicate cache hits from shared_buffers.
  • Seq Scan: Red flag. Indicates the query planner ignored available indexes (often caused by type mismatches, functions on indexed columns like WHERE LOWER(email) = ..., or stale table statistics). Run ANALYZE orders; to refresh table statistics.

Leave a Reply

Your email address will not be published. Required fields are marked *