software-engineer-blog logoSoftware Engineer Blog

Module 6 · Databases

Unit 20 of 49

Unit 20 · Module 6 · Databases

Tables, indexes, and how a query is planned

The planner decides whether your query is instant or a full scan.

Unit 20 of the free 49-unit computer-science course, in databases. 6 topics to watch or read, 3 interview questions answered in full and a short self-check.

Watch or read

6 topics make up this unit. Take each one whichever way suits you, then answer the questions below.

How a database index works

The same query, 4.2 seconds then 3 milliseconds — and the only thing that changed was one line of SQL. Most explanations stop at 'it's like the index in a book.' This one goes a level below: what a table actually is on disk, why a full table scan is the database's only option without an index, how a B-tree gets you there in three hops, and the cost nobody mentions — every write has to update every index.

ReelRead

Inverted indexes (FTS5)

You have 10,000 markdown notes and you search for 'postgres backup'. grep takes 400 ms and returns 40 files in folder order. SQLite FTS5 takes 3 ms and puts the right note first. Same files, same query — the difference is an inverted index and BM25 ranking. Here's how both actually work, the negative-score gotcha everyone hits once, the staleness trap of a derived index, and where lexical search ends and vector search begins — including why RAG pipelines still run BM25 next to embeddings.

ReelRead

Connection pooling

Your API falls over at 200 concurrent users while Postgres sits at 4% CPU. That paradox is what connection pooling exists to fix. Here's what opening a database connection actually costs, the max_connections=100 wall, how a pool turns connections into a borrowed-and-returned resource, why it's a queue and not a multiplier, how to size it, and why 'pool exhausted' is almost always a slow query — with code, and the same idea applied to LLM serving.

ReelRead

Proximity search and spatial indexes

You tap "restaurants near me" and get an answer in 50 ms — out of ten million rows the database never measured the distance to. That's spatial indexing: stop indexing points, start indexing space. Here's the grid trick, the 3×3 neighbor lookup, the cell-size trade-off with both extremes, why fixed grids go lopsided, and how the exact same prune-then-measure idea powers vector search for LLMs.

ReelRead

PostGIS vs DuckDB

Read

GeoParquet and row-group pruning

Read

Interview questions this unit unlocks

Asked out loud, answered out loud. Read the answer, then say it in your own words.

How does a database index actually make a query faster?

It is a separate, sorted structure — normally a B-tree — mapping column values to row locations. Without it the planner has to read every page of the table and test each row. With it, the tree is descended in a handful of page reads to find exactly the rows that match. The win is not "it is faster", it is that the work stops growing with the size of the table.

The cost nobody mentions: every index is another structure to update on every write, and another thing to keep in memory. Indexes are a read optimisation paid for by writes.

When will the database ignore your index and scan anyway?

When the planner estimates the scan is cheaper, and it is often right. If a query matches a large fraction of the table, jumping to scattered rows through the index costs more random reads than reading the whole thing sequentially. It will also skip the index when the column is wrapped in a function, when the leading column of a composite index is not constrained, or when the statistics are stale enough that its estimate is wrong.

`EXPLAIN ANALYZE` is the whole answer to "how would you find out" — it shows the estimated rows next to the actual, and a wide gap between them is your bug.

What is a covering index?

One that contains every column the query needs, so the answer is served from the index alone and the table is never touched. It turns two structures into one, which on a hot query is a large win — at the cost of a wider index that is slower to write and larger to cache.

Self-check — 3 questions

Answer alone, at 2am, with no interviewer in the room.

Part of Everything You Need to Know About Computer Science.