Backend & APIsSeptember 1, 2026•9 min read

PostgreSQL Indexing Strategies: B-Tree, GIN, Partial Indexes & Query Planning

Deep dive into PostgreSQL query optimization. How to diagnose sequential scan bottlenecks with EXPLAIN ANALYZE, pick appropriate index types, and prevent index bloat in high-throughput applications.

Rohit Bhatu — Lead Systems Architect

1. The Hidden Cost of Sequential Table Scans

In relational databases like PostgreSQL, queries without matching index coverage force the query planner to initiate a Sequential Scan (`Seq Scan`). During a sequential scan, the database engine must load every disk page containing table rows into buffer memory and evaluate the `WHERE` condition row by row.

While sequential scans perform acceptably on tables with a few thousand rows, table sizes exceeding hundreds of thousands of rows lead to catastrophic I/O contention, lock queue stalls, and multi-second p99 latency spikes during peak traffic hours.

Understanding how PostgreSQL indexes work at the disk block and page level is the foundational prerequisite for designing scalable multi-tenant architectures.

Sequential scans are not always bad; if a query returns more than 15-20% of the entire table, PostgreSQL's cost estimator intentionally chooses a sequential scan over random disk seeks.

2. Decoding EXPLAIN (ANALYZE, BUFFERS) Output

Never guess why a query is slow—always inspect the execution plan using `EXPLAIN (ANALYZE, BUFFERS)`. This command executes the query inside a transaction, collects true hardware execution times, and reports the exact number of memory buffer hits versus physical disk reads.

Analyzing query execution cost and buffer page cache hitssql

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM "AdminAuditLog"
WHERE "actorAdminId" = 'cm1234abc'
  AND "createdAt" >= NOW() - INTERVAL '30 days'
ORDER BY "createdAt" DESC
LIMIT 20;

-- Plan output before composite indexing:
-- Sort  (cost=1420.30..1420.35 rows=20 width=48) (actual time=48.210..48.215 rows=20 loops=1)
--   ->  Seq Scan on "AdminAuditLog"  (cost=0.00..1380.00 rows=850 width=48) (actual time=0.045..42.100 rows=920 loops=1)
--         Filter: (("createdAt" >= (now() - '30 days'::interval)) AND ("actorAdminId" = 'cm1234abc'::text))
--         Buffers: shared hit=410 read=1240

3. B-Tree vs. GIN: Selecting the Correct Index Engine

PostgreSQL supports multiple indexing algorithms, each optimized for specific data structures and equality operators:

• B-Tree (Balanced Tree): The universal default for scalar data types (integers, timestamps, strings). Extremely efficient for equality (`=`), range operators (`<`, `<=`, `>`, `>=`), and `ORDER BY` operations.

• GIN (Generalized Inverted Index): Essential for semi-structured data like `JSONB`, array fields, and full-text search vectors. Rather than indexing the row, GIN indexes every internal key and array element, allowing queries like `@>` (contains) to execute in sub-millisecond times.

Creating multi-column composite and GIN indexes for JSONB fieldssql

-- Composite index matching filter predicates and sort order:
CREATE INDEX idx_audit_admin_created 
ON "AdminAuditLog" ("actorAdminId", "createdAt" DESC);

-- GIN index for high-speed metadata payload queries:
CREATE INDEX idx_audit_metadata_gin 
ON "AdminAuditLog" USING gin (metadata jsonb_path_ops);

-- Query leveraging GIN index:
SELECT * FROM "AdminAuditLog"
WHERE metadata @> '{"action": "SECURITY_ALERT", "severity": "HIGH"}';

4. Partial Indexes: Trimming Storage Bloat by 80%

In many applications, only a small fraction of rows are actively queried. For instance, in an email queue or task processing system, 99% of rows are marked `STATUS = 'COMPLETED'`, while workers continuously poll for `STATUS = 'PENDING'`.

Indexing the entire table creates unnecessary index bloat and slows down every `INSERT` and `UPDATE`. A Partial Index includes a `WHERE` clause, indexing only the active subset of rows.

Lightweight partial index targeting unprocessed queue itemssql

-- Only index rows that workers actively query
CREATE INDEX idx_contact_submissions_open 
ON "ContactSubmission" (created_at DESC) 
WHERE status = 'OPEN';