Skip to content

Interactive Engineering Tool

PostgreSQL Index & Query Latency Advisor

Select your table volume, filter predicate, write throughput, and sort pattern to generate production-safe CREATE INDEX DDL, EXPLAIN verification SQL, memory/VACUUM tuning notes, and latency trade-offs grounded in a 50M-row migration.

Updated 2026-11-22 · PostgreSQL 13–17

Load a production workload preset

1. Row volume
2. Primary WHERE predicate
3. Write amplification sensitivity
4. Sort & projection pattern
Recommended StrategyGIN + tsvectorCONCURRENTLY (zero write lock)Blocks HOT on indexed cols
Stored Generated tsvector + GIN Inverted Index
Tokenizes text once at write time into a persisted tsvector column and indexes lexemes in a Generalized Inverted Index. This is the exact pattern behind our 50M-row 60s → 200ms migration.

Unindexed Seq Scan

42s – 65s

Target Indexed p95

65ms – 200ms

Estimated Footprint

~12% – 22% of heap

Write Amplification

Elevated (tune pending list / vacuum)

-- 1. Persist a weighted tsvector so queries never recompute to_tsvector() per row
ALTER TABLE property_records
  ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', coalesce(owner_and_address, ''))) STORED;

-- 2. Build the GIN inverted index without blocking writes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_property_records_search_vec_gin
  ON property_records USING gin (search_vector)
WHERE archived_at IS NULL;

Engineering trade-offs

  • Handles multi-word search, stemming, prefix lexemes ('oak':*), and boolean websearch_to_tsquery across tens of millions of rows in <200ms.
  • GIN cannot return rows pre-sorted by a timestamp column; combining unselective FTS terms with ORDER BY created_at DESC requires bitmap heap scans or btree_gin scoping.
  • Every insert/update tokenizes multiple lexemes, increasing WAL volume and pending-list flush work.

Memory, WAL & VACUUM tuning

  • Tune gin_pending_list_limit (e.g. 4MB) or run frequent autovacuum so fastupdate pending-list flushes never stall foreground INSERT latency.
  • Materialize tsvector as GENERATED ALWAYS AS (...) STORED so queries never recompute to_tsvector() during heap rechecks.
  • Set maintenance_work_mem >= 1GB before CREATE INDEX CONCURRENTLY. GIN builds sort millions of Posting Tree entries in RAM.

Production Proof · 50M+ Records

Real numbers from a 60s → 200ms legacy SQL Server to PostgreSQL migration

These recommendations come directly from re-architecting a brittle Classic ASP and Microsoft SQL Server property & county-clerk platform holding 50M+ records into NestJS, PostgreSQL, and AWS RDS serving 30,000+ requests per minute.

60s+ → 200ms

p95 search latency

Replaced unanchored LIKE scans with persisted tsvector + GIN and composite tenant B-Trees.

50M+ rows

Migrated zero-downtime

Online backfill with checksum verification, dual-read validation, and CONCURRENTLY index builds.

30K req/min

Sustained API load

NestJS connection pooling, partial active-row indexes fitting in RDS RAM, and Redis hot-query caching.

What actually moved the needle in production

  • Persisting a STORED generated tsvector column eliminated per-row CPU tokenization during bitmap heap rechecks.
  • Splitting hot active county records from cold historical archives via partial WHERE predicates cut B-Tree RAM pressure by over 65%.
  • Raising maintenance_work_mem to 1.5GB per build worker turned multi-hour GIN index creation into a predictable online step without stalling writes.

Architecture Reference

PostgreSQL index families compared side by side

Use this matrix when deciding whether a query needs an ordered B-Tree, heap-free covering INCLUDE columns, an inverted GIN index, or a lightweight BRIN block summary.

Index familyBest query predicateDisk / RAM sizeWrite costORDER BY scanCritical PostgreSQL knob
B-Tree (Composite)= , IN , BETWEEN , >= , prefix 'abc%' (C/pattern)14% – 24% of heapLow–ModerateYes (ASC / DESC)maintenance_work_mem, deduplicate_items
B-Tree + INCLUDEHigh-QPS equality/sort feeds needing Index-Only Scan22% – 36% of heapModerate (blocks HOT on INCLUDE cols)Yes (heap-free when all-visible)autovacuum_vacuum_scale_factor (Visibility Map)
Partial Index (WHERE)Skewed tables where <25% of rows are active/open4% – 12% of heapMinimal on cold rowsMatches base indexExact predicate match in application ORM
GIN + Stored tsvectorMulti-word full-text search (@@ websearch_to_tsquery)24% – 42% of heapElevated (multi-lexeme posting trees)No (bitmap scan + sort)gin_pending_list_limit, STORED generated column
GIN + pg_trgmUnanchored ILIKE '%term%' & fuzzy similarity38% – 70% of heapElevated (3-gram fan-out)No (bitmap scan; KNN via GiST)work_mem (prevent lossy bitmap blocks)
GIN (jsonb_path_ops)JSONB containment (payload @> '{...}')15% – 28% of heapModerate (~50% lighter than jsonb_ops)Nofastupdate = on, cap nested array sizes
BRINAppend-only timestamps on 50M–1B+ correlated rows~0.05% – 0.2% of heapNear-zeroNo (prunes block ranges)pages_per_range (32–64), pg_stats correlation

FAQ

PostgreSQL indexing questions from production systems

Direct answers on GIN vs B-Tree, ILIKE sequential scans, lock-free CONCURRENTLY builds, BRIN selectivity, and covering indexes.

When should I choose a GIN index over a B-Tree index in PostgreSQL?

Use B-Tree whenever each row maps to a single scalar key or composite tuple (=, IN, BETWEEN, >=, ORDER BY). Choose GIN (Generalized Inverted Index) when a single column contains multiple searchable tokens per row, such as full-text lexemes (tsvector @@), 3-character trigrams for ILIKE '%substring%' (pg_trgm), array elements (&& / @>), or nested JSONB keys/paths.

Why does PostgreSQL ignore my B-Tree index on ILIKE '%search%' queries?

A B-Tree sorts strings left-to-right from the first character, so a leading wildcard ('%search%') has no starting prefix in the tree and forces a full-table sequential scan. Even anchored LIKE 'search%' can skip a standard B-Tree under non-C UTF-8 collations unless built with text_pattern_ops. For unanchored case-insensitive ILIKE '%search%', enable pg_trgm and create a GIN (col gin_trgm_ops) index.

How do I create an index on a 50M+ row PostgreSQL table without locking writes?

Always run CREATE INDEX CONCURRENTLY outside an explicit transaction block, raise maintenance_work_mem (for example SET maintenance_work_mem = '1536MB') in that session so sorting happens in RAM, and ensure statement_timeout and lock_timeout are configured safely. If a concurrent build fails mid-way, check pg_indexes / pg_class for INVALID indexes and DROP INDEX CONCURRENTLY before retrying.

When does a BRIN index outperform a B-Tree on large tables?

BRIN shines on append-only tables with 50M+ rows (audit logs, IoT telemetry, ledger events) where physical heap insertion order matches the timestamp column (pg_stats.correlation > 0.95). A BRIN index is roughly 99% smaller than a B-Tree. It fits in a few megabytes of RAM with near-zero write overhead and still prunes 98%+ of irrelevant disk blocks on time-window queries.

What is the difference between composite key columns and INCLUDE columns?

Composite key columns (CREATE INDEX ON t (a, b)) are stored in both upper branch nodes and leaf pages to filter and sort rows. INCLUDE columns (CREATE INDEX ON t (a) INCLUDE (c, d)) are stored only in leaf pages as non-searchable payload so PostgreSQL can satisfy SELECT a, c, d via an Index-Only Scan without fetching heap pages, provided autovacuum keeps the Visibility Map marked all-visible.

PostgreSQL performance

Queries that used to take a minute can take milliseconds

If a production query is blocking the product, I can walk through indexing, caching, and schema work from similar 50M-row systems. Email me with the constraint.

Open to full-time and part-time roles (remote or onsite)

Talk through the queryGitHubLinkedIn

Also on Toptal. Email is the best way to start a conversation.