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.
Load a production workload preset
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 family | Best query predicate | Disk / RAM size | Write cost | ORDER BY scan | Critical PostgreSQL knob |
|---|---|---|---|---|---|
| B-Tree (Composite) | = , IN , BETWEEN , >= , prefix 'abc%' (C/pattern) | 14% – 24% of heap | Low–Moderate | Yes (ASC / DESC) | maintenance_work_mem, deduplicate_items |
| B-Tree + INCLUDE | High-QPS equality/sort feeds needing Index-Only Scan | 22% – 36% of heap | Moderate (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/open | 4% – 12% of heap | Minimal on cold rows | Matches base index | Exact predicate match in application ORM |
| GIN + Stored tsvector | Multi-word full-text search (@@ websearch_to_tsquery) | 24% – 42% of heap | Elevated (multi-lexeme posting trees) | No (bitmap scan + sort) | gin_pending_list_limit, STORED generated column |
| GIN + pg_trgm | Unanchored ILIKE '%term%' & fuzzy similarity | 38% – 70% of heap | Elevated (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 heap | Moderate (~50% lighter than jsonb_ops) | No | fastupdate = on, cap nested array sizes |
| BRIN | Append-only timestamps on 50M–1B+ correlated rows | ~0.05% – 0.2% of heap | Near-zero | No (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)
Also on Toptal. Email is the best way to start a conversation.