Skip to content

Writing

Cutting PostgreSQL Query Time from 60s to 200ms on 50M Rows (B-Tree, GIN & tsvector)

Practical PostgreSQL indexing lessons from a 50M-row legacy migration: composite B-Tree column ordering, GIN tsvector vs pg_trgm, and keyset pagination.

By Sifat RabbiReviewed November 22, 20263 min readDatabases & Cloud Architecture

When I led the migration of a decades-old Classic ASP and SQL Server real-estate platform carrying 50M+ property and clerk records over to NestJS, PostgreSQL, and AWS RDS, our first naive port reproduced the legacy system's worst habit: multi-column ILIKE '%term%' filters combined with OFFSET pagination and unselective ORDER BY sorts that took 60+ seconds under concurrent user load.

Getting that core search endpoint down to 200ms–500ms at peak 30,000 requests/minute did not require exotic sharding or Elasticsearch clusters. It required reading EXPLAIN (ANALYZE, BUFFERS) honestly and picking the right PostgreSQL index structure (B-Tree, GIN + pg_trgm, or generated tsvector) for each predicate.

1. Choosing the Right PostgreSQL Index Type

Index TypeTarget PredicateWrite / Size CostWhen to Use on 50M+ Rows
Composite B-Tree (INCLUDE)Equality + range + sort (county_id = $1 AND recorded_at >= $2 ORDER BY recorded_at DESC)Low write overhead; compact when partialPrimary workhorse for tenant/region-scoped filtering & keyset pagination
GIN (`tsvector`)Tokenized full-text search (search_tsv @@ websearch_to_tsquery($1))Moderate size; batch-friendly updatesMulti-word party names, legal descriptions, and address token matching
GIN (`pg_trgm`)Substring / prefix-agnostic ILIKE '%abc%' or typo-tolerant similarityLarger index footprint; slower bulk writesPartial parcel IDs, fuzzy surname lookups, and wildcard search boxes
BRINAppend-only chronological logs (created_at physically correlated)Tiny (< 1% of B-Tree size)Time-window scans on immutable audit or event tables

2. Eliminating Sequential Scans on Wildcard Text Filters

A standard B-Tree index cannot accelerate a leading-wildcard WHERE grantor_name ILIKE '%smith%'. On 50M rows, Postgres falls back to scanning gigabytes of heap pages. Depending on whether users search whole words or arbitrary substrings, use a stored tsvector column or pg_trgm:

Zero-downtime GIN index creation on 50M rows:
-- Enable trigram indexing for substring ILIKE and generated tsvector for token queries
CREATE EXTENSION IF NOT EXISTS pg_trgm;

ALTER TABLE property_records
ADD COLUMN IF NOT EXISTS search_tsv tsvector
GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(grantor_name, '') || ' ' || coalesce(parcel_code, ''))
) STORED;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_property_records_search_tsv
ON property_records USING gin (search_tsv);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_property_records_parcel_trgm
ON property_records USING gin (parcel_code gin_trgm_ops);

3. Ordering Composite B-Tree Columns: Equality First, Sort/Range Last

When filtering by jurisdiction (county_id), status (is_active = true), and sorting by recorded_at DESC, column order in a composite B-Tree makes a 100x difference in buffer hits. Put high-selectivity equality columns first, the sort/range column next, and INCLUDE display columns if an Index-Only Scan eliminates heap fetches:

Partial covering B-Tree index for keyset pagination:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_property_county_recorded_active
ON property_records (county_id, recorded_at DESC, id DESC)
INCLUDE (document_number, grantor_name)
WHERE is_active = true;

4. Replacing OFFSET 50000 with Keyset (Cursor) Pagination

LIMIT 25 OFFSET 50000 forces Postgres to fetch, sort, and discard 50,025 rows on every single page click. Replacing page numbers with a keyset cursor (recorded_at, id) < ($lastDate, $lastId) kept page 2,000 just as fast (< 15ms DB execution) as page 1.

Always inspect Buffers: shared hit=... read=... inside EXPLAIN (ANALYZE, BUFFERS), not just execution milliseconds. A query that runs in 80ms only when cached in RAM can still stall for 12 seconds when cold disk reads spike.

Frequently Asked Questions

Should I use GIN tsvector or pg_trgm for PostgreSQL text search?

Use tsvector when queries consist of complete words or phrases (such as names, titles, and addresses), because tsvector GIN indexes are significantly smaller and faster to update. Use pg_trgm when users search mid-word substrings, partial serial/parcel codes, or misspellings.

Does CREATE INDEX lock writes on a 50-million-row PostgreSQL table?

Standard CREATE INDEX acquires a SHARE lock that blocks INSERT, UPDATE, and DELETE until the build finishes. Always run CREATE INDEX CONCURRENTLY outside an explicit transaction block in production so writes continue uninterrupted.

When do you actually need Elasticsearch or OpenSearch instead of PostgreSQL?

Stay on PostgreSQL when search predicates combine strict relational permissions/joins with structured filters, exact codes, and full-text or trigram matching up to tens of millions of rows. Introduce OpenSearch/Elasticsearch only when you need complex cross-field BM25 boosting, faceted aggregations across hundreds of millions of documents, or custom analyzers.

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.