PostgreSQLpsqlSQLAdvanced~4h

PostgreSQL

The engine under the query

Two queries can be the same SQL and behave completely differently — and the reason is never in the query. It is the type of the column, the kind of index on it, and the isolation level around it. Five modules of predicting what the engine does, then a server of your own and two terminals driven into a conflict.

Start this project See the modules

Modules: 6
Drills: 9
Build steps: 8
Time: ~4h

Walk out able to

Say what a type yields, which index kind can answer a predicate, what a plan line measured, and what two interleaved transactions leave behind.

What you drill

Types that decide

Exact against approximate, padding, instants and jsonb.

Six index kinds

B-tree, hash, GIN, GiST, BRIN — and when none of them helps.

What EXPLAIN measured

Rows per loop, filters, estimates against what happened.

Isolation, as outcomes

Two transactions, a stated level, the number left behind.

The pitch

What you practise, and what you leave with

A PostgreSQL server of your own, 200,000 generated rows, a plan read before and after an index, and a serialization failure you caused on purpose.

You will practise

What a type yields, and what it quietly changes
Which index kind answers a predicate, if any
What a plan line measured, per loop and per block
What two interleaved transactions leave behind

Afterwards you can

Say what an expression yields from its types alone
Name the index kind a predicate needs
Compute the work a plan node really did
Predict what two interleaved transactions leave

Modules

6 modules, 26 items

Lessons explain one idea. Drills repeat it until it sticks. Build steps make something that exists afterwards.

1
Types that decide the answer
~40 min
Predict the value an expression yields from the types in it, before you run it.
- LessonWhat the type decides for you11 min
- DrillPredict the number a type gives10 min
- LessonWhat the column hands back9 min
- DrillPredict what the column gives back10 min
2
Index kinds, and what they answer
~36 min
Name the index a predicate needs, and count how much of a composite one it can use.
- LessonWhich index can answer this9 min
- DrillName the index kind it needs9 min
- LessonHow much of a composite is used8 min
- DrillCount the columns it can use10 min
3
Reading what EXPLAIN measured
~28 min
Work out what a node really did from the numbers the plan prints beside it.
- LessonWhat EXPLAIN is measuring8 min
- LessonIndex Cond, Filter, and buffers9 min
- DrillWork out what the node did11 min
4
Isolation levels, as outcomes
~39 min
Predict what two interleaved transactions leave behind at a stated level.
- LessonWhat a level actually promises10 min
- LessonWhat a level does instead of lying8 min
- DrillPredict what the interleaving leaves13 min
- DrillPlace the anomaly on the table8 min
5
Extensions, and what they add
~20 min
Say what CREATE EXTENSION changes, and name the one a stated need wants.
- LessonThe seam in the engine12 min
- DrillName the extension it wants8 min
6
Your own server, two terminals
~1h
Spell the statements, put PostgreSQL in front of you, and break it on purpose.
- DrillSpell the statement it accepts8 min
- Build stepGet a server you can break8 min
- Build stepLoad the ledger and the events11 min
- Build stepMake the types answer for themselves8 min
- Build stepPlan it before you index it8 min
- Build stepIndex it, and read the plan again9 min
- Build stepWatch Read Committed give way13 min
- Build stepMake Repeatable Read refuse14 min
- Build stepAsk it something of your own8 min