PostgreSQL in Practice
The world’s favorite open-source database, beyond the SQL. Learn JSONB and rich types, index strategies, EXPLAIN, MVCC and vacuum, and day-two operations — and remember it with spaced repetition.
- flashcards
- 80
- flashcards
- per day
- ~10 min
- per day
- level
- Intermediate
- level
- modules
- 5
- modules
Why study PostgreSQL specifically?
PostgreSQL is the database the industry keeps converging on — fully open source, famously reliable, and rich enough to replace a document store, a queue and a search engine for many workloads. Generic SQL gets you querying; knowing how Postgres actually works is what gets you fast queries and calm on-call shifts.
That knowledge has a shape: choosing the right data types (JSONB vs JSON, arrays, ranges, UUID), backing queries with the right index kind (B-tree by default; GIN for JSONB and full-text; BRIN for huge append-only tables), reading EXPLAIN ANALYZE instead of guessing, and understanding MVCC — why readers never block writers, where dead tuples come from, and what vacuum really does.
This track assumes basic SQL fluency (the SQL Basics track is the natural prerequisite) and covers the Postgres-specific layer in five modules, with spaced repetition making the details stick — exactly the ones that surface in backend interviews and 2 a.m. incident calls.
5 modules, seed to bloom
Each module is a set of flashcards — 80 in total. Answer, review, and watch your knowledge grow from seed to full bloom.
Data Types in Practice
JSONB, arrays, enums, ranges, UUID, and choosing the right column types
16 cardsIndexes
B-tree, GIN, GiST and BRIN, partial and covering indexes, and when they pay off
16 cardsQuery Plans & Performance
EXPLAIN and ANALYZE, scan types, planner statistics, and autovacuum in practice
16 cardsMVCC & Transactions
Row versions, snapshots, isolation levels, VACUUM, locking, and bloat
16 cardsOperations & Ecosystem
Roles and privileges, extensions, backups, connection pooling, and daily operations
16 cardsSample questions
A taste of the real flashcards. Pick an answer, then reveal the explanation.
What is the key difference between JSON and JSONB?
- AJSON stores the exact text while JSONB stores a parsed binary form that is faster to query
- BJSON is the standard type while JSONB is deprecated in favor of the newer jsonpath
- CJSON allows nested objects while JSONB is limited to flat key-value documents
- DJSON validates the document while JSONB accepts any text without checking it
What is a partial index?
- AAn index built over only the rows matching a WHERE clause — smaller and cheaper to maintain
- BAn index covering only a subset of the table's columns — the others remain unindexed
- CAn index that is built gradually during idle periods — construction pauses under load
- DAn index storing only truncated key prefixes — longer values are cut to save space
Do plain SELECT readers block writers in PostgreSQL?
- ANo — SELECTs read a snapshot and take no locks that conflict with data modification
- BYes — every SELECT takes shared row locks that writers must wait for and acquire
- COnly inside explicit transactions — autocommitted reads skip the locking entirely
- DOnly on indexed tables — the index maintenance serializes readers with writers
Why does SELECT count(*) on a big PostgreSQL table take so long?
- AMVCC visibility must be checked per row — the count cannot come from a stored total
- Bcount(*) parses every column of each row — the narrower count(1) is the faster form
- CThe count triggers a full VACUUM first — cleanup runs before the rows are tallied
- DA table lock must drain all the writers first — counting waits for exclusive access
Learn it once, keep it for good
Answer a question
Each card is one practical concept with multiple options. Pick what you think is right.
Get the full answer
See the correct option plus a clear explanation, and a link to deeper docs when one is available.
Review at the right time
A spaced-repetition engine (SM-2 or FSRS) resurfaces each card just before you would forget it.
Why PostgreSQL is worth your time
The default choice
Postgres tops developer-survey rankings year after year. It is the database you are most likely to meet in your next job.
Read plans, not tea leaves
EXPLAIN ANALYZE plus planner statistics turn "the query is slow" from a mystery into a diagnosis.
Indexes that actually help
Knowing when GIN, BRIN or a partial index beats a plain B-tree is the difference between milliseconds and minutes.
Interview-ready
MVCC, vacuum, isolation levels and JSONB are perennial favorites in backend and data engineering interviews.
Common questions
Do I need to know SQL first? +
Yes — this track assumes you can write basic queries with joins and aggregation. The SQL Basics track covers exactly that prerequisite and links back here when you are ready.
Which PostgreSQL version does it cover? +
The concepts — MVCC, index types, EXPLAIN, vacuum, roles — are stable across modern versions, so the track is not tied to a specific release. Anything version-sensitive is phrased to hold for currently supported versions.
Is it free? +
Yes, completely free. No registration or credit card is required, and all your progress is stored locally in your browser.
Does it cover administration topics? +
The Operations & Ecosystem module covers the day-two essentials — roles and privileges, extensions, pg_dump vs pg_basebackup, and why connection pooling matters. Deep HA and replication setups are beyond its scope.
Ready to master PostgreSQL?
Plant your first seed today. Ten minutes a day is all it takes to grow real, lasting PostgreSQL depth.