Sponsored by
Keep up with AI in 5 minutes a day. MavSource aggregates updates from all major AI newsletters, podcasts, companies, AI labs, and hundreds of other sources — then summarizes the key updates.
Billing lag costs more than you think. See exactly how much with the Tabs Billing Lag Calculator — benchmarked against top SaaS companies.
The World's Biggest Dev Event Hits Silicon Valley
One trip, and your team stops guessing on AI adoption, platform, security, and tooling.
WeAreDevelopers World Congress — San José, CA, September 23–25, 2026, in the heart of Silicon Valley. 500+ speakers and 10,000+ developers working through what actually ships in production: coding agents, evals, MCP, observability, agent security.
Kelsey Hightower. Thomas Dohmke (fmr. CEO, GitHub). Christine Yen (CEO, Honeycomb). Mathias Biilmann (CEO, Netlify). Olivier Pomel (CEO, Datadog). The people actually building the tools you use every day — all on one stage.
Bring a shortlist of decisions; leave with answers and contacts you can still reach after the event. Prices rise as we get closer.
Use code GITPUSH26 for 10% off.
Welcome to Grind Engineer, your guide to becoming a better software engineer! No fluff. Pure engineering insights.
Added Job Opening in the end of the article!
A 2.3 billion row orders table at a fintech company I consulted for was running a simple lookup query in 47 seconds. One composite index later, it dropped to 4ms. The fix took 90 seconds to write. The knowledge behind it took me years to collect.
Most engineers know they should "add an index." Few know which index. PostgreSQL alone offers over a dozen index types, and picking the wrong one is sometimes worse than having none at all. I've watched teams slap a B Tree index on a JSONB column and wonder why nothing improved.
TL;DR
Index Type | Best For | Worst For |
|---|---|---|
B Tree | Range queries, sorting, equality | High write tables with low selectivity |
Hash | Exact equality lookups | Range queries, sorting |
GIN | Full text search, JSONB, arrays | Simple equality on scalar columns |
BRIN | Huge tables with natural ordering | Randomly ordered data |
Covering | Eliminating table lookups entirely | Wide indexes with many columns |
Partial | Indexing a small subset of rows | Queries that hit most of the table |
Composite | Multi column WHERE and ORDER BY | Single column queries (wastes space) |
Take a look at Fig 1 below for the visual grid of all seven patterns before we break each one down.
Pattern 1: B Tree — The Default Workhorse
Every time you write CREATE INDEX without specifying a type, PostgreSQL builds a B Tree. It handles equality (=), range (<, >, BETWEEN), sorting (ORDER BY), and even IS NULL checks.
When it shines: Range queries on columns with high cardinality.
-- Before: Sequential scan on 50M rows → 48s
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-06-30'
ORDER BY created_at DESC
LIMIT 100;
-- After: B Tree index → 3ms
CREATE INDEX idx_orders_created_at
ON orders (created_at DESC);
B Trees store data in sorted order across balanced tree nodes. That's why they're so good at range scans. The database walks the tree to the start point and reads sequentially from there.
Don't use it when your column has only 2 or 3 distinct values (like a status field with active/inactive). The index won't narrow things down enough to beat a sequential scan.
Pattern 2: Hash — Pure Equality, Nothing Else
Hash indexes do exactly one thing: fast equality lookups. They compute a hash of the value and jump straight to the bucket. No range support. No sorting. Just =.
-- Perfect for: exact match on UUIDs or session tokens
CREATE INDEX idx_sessions_token
ON sessions USING hash (session_token);
-- This query goes from 12s → 2ms
SELECT * FROM sessions
WHERE session_token = 'abc123def456';
Before PostgreSQL 10, hash indexes weren't even crash safe. Now they are, and they're about 30% smaller than equivalent B Tree indexes on the same data. If you only ever query with =, hash wins.
Skip it when you need ORDER BY, <, >, or BETWEEN. Hash indexes literally cannot help with those.
Pattern 3: GIN — The Full Text and JSONB Specialist
GIN (Generalized Inverted Index) is what makes PostgreSQL competitive with Elasticsearch for many use cases. It breaks a composite value (text document, JSONB object, array) into individual elements and indexes each one.
-- Full text search: find articles containing "distributed" AND "consensus"
CREATE INDEX idx_articles_search
ON articles USING gin (to_tsvector('english', body));
-- From 34s full table scan → 8ms
SELECT title, ts_rank(to_tsvector('english', body), q) AS rank
FROM articles, to_tsquery('english', 'distributed & consensus') q
WHERE to_tsvector('english', body) @@ q
ORDER BY rank DESC LIMIT 20;
GIN also handles JSONB beautifully:
-- Index all keys and values inside a JSONB column
CREATE INDEX idx_events_payload
ON events USING gin (payload jsonb_path_ops);
-- Query nested JSON in 5ms instead of 41s
SELECT * FROM events
WHERE payload @> '{"user": {"plan": "enterprise"}}';
💡 Key Insight: GIN indexes are expensive to build and slow down writes because every insert might touch dozens of index entries. Use gin_pending_list_limit to batch updates if write throughput matters.
Pattern 4: BRIN — Block Range Index for Massive Tables
This is the most underused index type I've seen. BRIN stores summary info (min/max values) for each block of table pages rather than indexing every row. The result? An index that's 1000x smaller than a B Tree on the same column.
-- On a 500M row time series table
-- B Tree index size: 11GB
-- BRIN index size: 48KB (yes, kilobytes)
CREATE INDEX idx_sensor_readings_ts
ON sensor_readings USING brin (recorded_at)
WITH (pages_per_range = 32);
-- Time range query: 50s → 120ms
SELECT avg(value) FROM sensor_readings
WHERE recorded_at BETWEEN '2026-06-01' AND '2026-06-15';
The catch? BRIN only works well when the physical order of rows on disk correlates with the column values. Time series data where rows are inserted chronologically is the perfect fit. A user_id column with random inserts? Terrible fit.
Factor | B Tree | BRIN |
|---|---|---|
Index size (500M rows) | ~11 GB | ~48 KB |
Build time | Minutes | Seconds |
Query speed | Fastest | Slightly slower |
Works on random data | Yes | No |
Write overhead | Medium | Near zero |
I've seen BRIN save teams from provisioning larger disks purely because the B Tree index was bigger than the table itself.
Pattern 5: Covering Index — Zero Table Lookups
A regular index finds the row location, then PostgreSQL goes to the table heap to fetch the actual data. A covering index stores the extra columns inside the index itself, eliminating that second trip entirely. This is called an "index only scan."
-- Dashboard query that runs 200 times per minute
-- Needs: user_id (filter), order_total (aggregate)
CREATE INDEX idx_orders_covering
ON orders (user_id) INCLUDE (order_total, status);
-- Index only scan: 45s → 2ms
SELECT user_id, sum(order_total), count(*)
FROM orders
WHERE user_id = 12345
GROUP BY user_id;
The INCLUDE keyword (added in PostgreSQL 11) puts order_total and status in the index leaf pages without affecting the sort order. You get the speed of an index only scan without the overhead of sorting on those extra columns.
The tradeoff: Your index gets wider. If you include too many columns, you're basically duplicating the table. I keep it to 2 or 3 included columns max for hot queries.
Pattern 6: Partial Index — Index Only What Matters
Why index 100 million rows when your query only ever touches 50,000 of them? A partial index adds a WHERE clause to the index definition itself, so only matching rows get indexed.
-- Only 0.1% of orders are in 'pending' state
-- but the support dashboard queries them constantly
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
-- 200x smaller than a full index
-- Support dashboard: 38s → 1ms
SELECT * FROM orders
WHERE status = 'pending'
AND created_at > now() - interval '24 hours'
ORDER BY created_at DESC;
I've used partial indexes for:
Active user sessions (ignoring expired ones)
Unprocessed queue items
Error records in a sea of successes
Soft deleted rows (
WHERE deleted_at IS NULL)
The index is smaller, faster to build, faster to scan, and imposes almost zero overhead on inserts that don't match the condition.
Pattern 7: Composite Index — Column Order Is Everything
A composite index covers multiple columns. It's the most commonly used pattern after single column B Trees. And the most commonly misused one.
The golden rule: column order follows your query pattern, not your table schema.
-- Query pattern: filter by tenant, then status, then sort by date
-- WRONG order:
CREATE INDEX idx_bad ON orders (created_at, tenant_id, status);
-- RIGHT order: equality columns first, range/sort column last
CREATE INDEX idx_orders_tenant_status_date
ON orders (tenant_id, status, created_at DESC);
-- Multi tenant SaaS query: 52s → 5ms
SELECT * FROM orders
WHERE tenant_id = 'acme_corp'
AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 50;
The rule of thumb for column ordering:
Position | Column Type | Example |
|---|---|---|
First | Equality conditions ( |
|
Middle | Additional equality |
|
Last | Range/sort columns |
|
If you reverse this order, PostgreSQL can't use the index efficiently. It's like a phone book sorted by first name then last name. Try finding all the Smiths quickly.
When to Use What
After years of production database tuning, here's my decision tree:
Start with the query. Not the table. Not the column. The query.
Is it an equality only lookup? → Hash index (or B Tree if you might add ranges later)
Is it a range query or needs sorting? → B Tree
Is the table huge with time ordered data? → Try BRIN first, it's nearly free
Are you searching inside JSONB, arrays, or full text? → GIN
Does a hot query keep going back to the table for 1 or 2 extra columns? → Add a covering index with
INCLUDEDoes the query only touch a tiny fraction of rows? → Partial index
Does the WHERE clause filter on multiple columns? → Composite index (equality columns first)
These patterns compose. You can create a partial composite covering index:
-- All three patterns combined
CREATE INDEX idx_ultimate
ON orders (tenant_id, created_at DESC)
INCLUDE (order_total)
WHERE status = 'active';
That's a partial (only active orders), composite (tenant + date), covering (includes total) index. It serves one specific query pattern extremely well while being tiny and cheap to maintain.
One final thing: indexes are not free. Every index slows down writes. Every index consumes disk space. Every index must be maintained during VACUUM. The best database engineers I've worked with don't ask "what should I index?" They ask "what query is worth paying the index cost for?"
Run EXPLAIN (ANALYZE, BUFFERS) before and after. Measure. Don't guess.
Sources
Job Openings
Software Engineer @Amazon: Apply Here
Software Engineer II @Google: Apply Here
Software Engineer @Microsoft: Apply Here
SDE1 SDE2 @Flipkart: Apply Here
Software Engineer @Instahyre: Apply Here
See you in the next one!
Scortier, Signing Off!