SQL & Indexing
A Go service is rarely complex on its own — a goroutine takes a request, hits PostgreSQL over SQL, returns a response. The real fight for performance sits in how the query is written and which indexes back it. database/sql and pgx give you direct SQL access but protect you from no conceptual mistake: a redundant index, the wrong column order in a composite index, COUNT(*) instead of COUNT(col), a lagging autovacuum — all of it compiles, passes local tests, and surfaces only at real data volume under load.
The traps here are not about SELECT syntax. Candidates treat an index as a free read accelerator and forget its cost on every INSERT. They expect a composite (a, b, c) index to speed up a filter on b alone. They put a B-tree on jsonb and wonder why it is useless. They confuse RANK with DENSE_RANK, call partitioning sharding, and believe a plain VACUUM returns space to the operating system. This topic dissects SQL and indexes layer by layer — from B-tree internals to the cleanup of dead row versions — so you answer each of these questions with a mechanism, not a memorized phrase.
Topic Map
- Index Basics — what an index is under the hood, why
B-treeby default, and the cost it charges on every write. - PostgreSQL Index Types —
B-tree,Hash,GIN,GiST,SP-GiST,BRINand the query class each is built for. - Composite Index — one
B-treeover(a, b, c), the left-prefix rule, and why column order is a design decision. - SQL Aggregation —
GROUP BYwithCOUNT/SUM, the difference betweenWHEREandHAVING, and theCOUNT(*)vsCOUNT(col)trap on aLEFT JOIN. - Window Functions —
OVER (PARTITION BY ... ORDER BY ...), ranking without collapsing rows, andRANKvsDENSE_RANK. - Self-Join — joining a table to itself via two aliases for hierarchies and pairwise row comparison within one table.
- Anti-Join — finding rows with no match via
LEFT JOIN ... IS NULLorNOT EXISTS, and theNOT INwithNULLtrap. - Table Partitioning — splitting one table into children by key with partition pruning; why this is not sharding.
- VACUUM in PostgreSQL — cleaning up MVCC dead row versions, freezing
XID, and bloat from a long-running transaction. - The N+1 Problem — why one query for the list plus one query per row makes 1 + N database round-trips, and how to collapse them into one with a
JOINor a batch.
Common Mistakes and Traps
| Mistake | Consequence |
|---|---|
| Treating an index as a free read accelerator | Missing the write cost — every INSERT/UPDATE/DELETE also updates every index |
Believing any index is a B-tree | Mismatching the type — jsonb and arrays need GIN, not B-tree |
Using Hash for a range query | Hash serves equality only; a range falls back to a seq scan |
Expecting an (a, b, c) index to speed up a filter on b alone | The index serves the leading prefix only — the rest goes to a seq scan |
| Putting a range column before an equality column in a composite index | After a range, columns to its right are not narrowed — the index works at half power |
Treating COUNT(*) and COUNT(col) as interchangeable | COUNT(col) skips NULL — on a LEFT JOIN it gives a false 1 instead of 0 for empty groups |
Filtering by an aggregate in WHERE | No aggregate exists yet — filtering by SUM/COUNT belongs in HAVING |
Confusing RANK and DENSE_RANK | After a tie RANK leaves a gap (1,1,3), DENSE_RANK does not (1,1,2) |
Filtering by a window function in the WHERE of the same SELECT | Windows are computed after WHERE — wrap it in a subquery |
Writing an anti-join via NOT IN with a subquery | One NULL in the subquery zeroes the whole result — use NOT EXISTS |
| Calling partitioning sharding | Partitions stay on one server; sharding spreads data across nodes |
Believing a plain VACUUM returns disk space to the OS | It only frees space for reuse; only VACUUM FULL returns it to the OS |
| Loading related rows in a loop (N+1) | 1 + N database round-trips instead of one JOIN or an IN (...) batch |
Interview Relevance
SQL and indexes are a mandatory topic on any backend interview, and the question is not "do you know the word index" but whether you can reason about the write cost and about which queries an index speeds up and which it does not.
What interviewers check:
- What an index is, why
B-treeby default, and the cost it charges on writes. - Which index types PostgreSQL offers and the query class each is built for.
- How a composite index works — the left-prefix rule and why column order decides.
- The difference between
WHEREandHAVINGand theCOUNT(*)vsCOUNT(col)trap after aLEFT JOIN. - How a window function differs from an aggregate and the difference between
RANK/DENSE_RANK. - How a self-join and an anti-join are expressed and why
NOT INwithNULLis dangerous. - How partitioning differs from sharding and when it is justified.
- What
VACUUMcleans, why it does not return space to the OS, and how a long transaction holds the cleanup horizon.
A typical wrong answer: "an index is always good, the more of them the faster". That triggers a discussion of how every index is a tax on every write, how a redundant unused index only slows inserts down, and how the index type must match the query class (GIN for jsonb, BRIN for huge ordered tables) rather than putting a B-tree on everything.