Back
Keentune
Databases curriculum 25 chapters
·
135 concepts
·
free
Everything the adaptive question bank can teach and test in Databases, from foundations through advanced practice. Work through it in order, or start practising and let the questions find your level.
Start practising Databases
New here? Read the Databases guide
A free 16-minute primer — the mental model, the mistakes beginners make, and what to practise first.
A. Engine architecture and the workload it serves
•
the client sends text over a socket; a server process owns the files, and no client ever touches them directly
•
PostgreSQL forks a backend per connection, so a connection costs memory and scheduling, not just a socket
•
shared buffers are one pool for the whole cluster; work_mem is charged per sort or hash, per backend
•
many tiny indexed transactions versus few huge scans; the two workloads want opposite physical designs
B. Physical storage: pages, rows and the heap
•
the engine reads and writes fixed-size pages (8 kB by default), so fetching one narrow row still costs a whole page
•
a heap table's row order is an artifact of insert and update history and can change under you
•
every row version carries a ~23-byte header of transaction ids, which on a two-column table outweighs the data
•
a value too big for a page is compressed and stored out-of-line, and is not read unless the column is selected
C. Table storage engines: heap, clustered and log-structured
•
a heap stores rows anywhere and treats every index equally; InnoDB stores the row inside the primary-key tree
•
in a clustered table a secondary index holds the primary key, so a non-covered lookup pays a second tree descent
•
random UUID keys scatter inserts across the tree; time-ordered keys append into one already-cached page
•
writes land in an in-memory table plus a log, flush as immutable sorted files, and are merged by background compaction
•
every engine picks a point on this three-way trade; LSM buys write throughput with read and compaction cost
D. Index structures and what each can answer
•
a balanced tree reaches any of millions of rows in three or four page reads and rebalances on every write
•
sorted leaves let one structure answer equality, ranges, prefix matches, MIN/MAX and ORDER BY
•
one probe for = , and no help at all for a range, a sort or a leading-prefix search
•
one entry per contained element, which is how arrays, JSONB keys and full-text documents become searchable
•
min/max per range of blocks: kilobytes instead of gigabytes, and worthless unless the data is physically correlated
E. Index design, selectivity and maintenance
•
an index on (a, b, c) serves a, (a, b) and (a, b, c); a predicate on b alone cannot use it
•
when the index holds every column the query needs, the heap is never visited at all
•
an index-only scan still reads the heap for pages vacuum has not marked all-visible, so it degrades on a churning table
•
indexing only the rows a predicate matches keeps the hot subset small enough to stay in cache
•
wrapping the column in a function or casting it disables a plain index; index the expression or rewrite the predicate
F. The planner: cost model and join algorithms
•
the same statement can get a different plan tomorrow when the data, the statistics or the settings move
•
cost is calibrated to one sequential page read, not to milliseconds, and is only meaningful against another plan
•
past a few percent of the table, reading it all in physical order beats scattered random index lookups
•
correct when the outer side is tiny and the inner side is indexed; catastrophic when the outer estimate is too low
•
builds a hash table on the smaller input; equality only, and it batches to disk once it exceeds work_mem
•
needs both inputs in sorted order, which is nearly free when an index already provides it
G. Statistics and cardinality estimation
•
every access path and join choice rests on a row-count guess, so a wrong estimate is the usual root cause of a bad plan
•
statistics come from a bounded random sample, so they are approximate by design and absent right after a bulk load
•
a most-common-values list plus an equal-frequency histogram is how the selectivity of a predicate is estimated
•
selectivities are multiplied, so correlated predicates like city and postcode underestimate by orders of magnitude
•
declaring a functional dependency or a joint n-distinct group tells the planner what a per-column sample cannot
•
EXPLAIN prints the planner's guess; EXPLAIN ANALYZE runs the statement and reports what actually happened
•
EXPLAIN ANALYZE on an UPDATE or DELETE performs it, so wrap it in a transaction you roll back
•
the ratio between them is the first thing to read; a large gap explains the plan better than the plan's shape does
•
the inner node of a nested loop reports time and rows per loop, so multiply by loops for the true cost
•
no observer ever sees half a transaction, including after a crash in the middle of commit
•
the C in ACID promises that declared rules hold at commit; it promises nothing about the data being meaningful
•
commit returns only once the log record is on stable storage, which is why commit latency is dominated by fsync
•
a bare statement is its own transaction, so a script that dies halfway leaves half its writes permanently committed
•
in PostgreSQL every later statement fails until rollback, while MySQL keeps going: a real portability trap
J. Isolation levels and the anomalies they permit
•
reading a row another transaction has written and not yet committed
•
reading the same row twice inside one transaction and getting different data
•
re-running the same predicate and matching rows that did not exist the first time
•
the standard permits dirty reads at this level; PostgreSQL has no such mode and silently gives read committed
•
each statement takes a fresh snapshot, so two statements in one transaction can legitimately disagree
•
one snapshot for the whole transaction, taken at its first statement rather than at BEGIN
•
the outcome must match some order of running the transactions one at a time, not merely avoid the named phenomena
K. Write skew, lost updates and true serializability
•
the standard defines levels by three phenomena, and the critique shows the loose reading permits behavior the strict one forbids
•
two read-modify-write cycles interleave and the second silently discards the first, with no error anywhere
•
two transactions read the same rows, write different rows, conflict with nothing, and jointly break an invariant
•
snapshot isolation blocks all three ANSI phenomena and still allows write skew, which is why it is not serializable
•
serializable engines resolve conflicts by aborting a transaction, so the application must be able to replay it safely
L. MVCC: row versions and snapshots
•
every reader works from a snapshot, so a long report does not stall the writes running underneath it
•
an UPDATE inserts a new row version and marks the old one dead; nothing is overwritten in place
•
each version records the transaction that created and the one that deleted it, and visibility is a comparison against your snapshot
•
InnoDB and Oracle keep the current row in place and rebuild old versions from undo, moving the cost from cleanup to rollback
•
two transactions can correctly see different row counts, which is why no engine keeps one true COUNT
M. Vacuum, bloat and wraparound
•
plain VACUUM makes space reusable inside the file; the file itself does not shrink, and DELETE alone frees nothing
•
VACUUM FULL does return space to the OS, at the price of an exclusive lock and a full second copy of the table
•
it fires on a fraction of changed rows, so the largest, busiest tables are exactly the ones it visits least often
•
a single idle-in-transaction session or an unconsumed replication slot stops cleanup for the whole cluster
•
dead space is still paged in and scanned, so a bloated table stays slower until it is rewritten
•
transaction ids are finite and rows must be frozen; an anti-wraparound vacuum cannot be cancelled and will arrive at the worst time
N. Locking, blocking and deadlocks
•
many readers or one writer; the conflict matrix between modes is the entire mechanism
•
take the write lock while reading, which is how a read-modify-write is made safe without an optimistic retry
•
each transaction holds what the other needs; the engine detects the cycle after a timeout and aborts one as the victim
•
acquiring rows in the same order everywhere prevents deadlocks; retrying the victim is the fallback, not the fix
•
the lock queue is ordered, so one waiting ALTER TABLE stalls every ordinary query that arrives behind it
•
the primitive that lets many workers pull disjoint jobs from one table without serializing on the first row
O. WAL, durability and crash recovery
•
the log record reaches durable storage before the data page does, and that ordering is the whole reason recovery is possible
•
on restart the engine replays the log forward from the last checkpoint to reach a consistent state
•
a checkpoint flushes dirty pages so replay has a starting point; frequent checkpoints trade steady I/O for shorter recovery
•
a page half-written during a power loss is repaired by logging the entire page the first time it is touched after a checkpoint
•
returning before the flush trades a bounded window of committed-then-lost transactions for much lower commit latency
P. Replication, lag, failover and distributed consistency
•
shipping byte-level block changes versus shipping decoded row changes that can cross versions and select tables
•
the primary commits without waiting, so a failover discards whatever had not yet shipped
•
waiting for a standby's acknowledgement adds network latency to every commit and makes a slow replica a production outage
•
a read replica serves stale data, and under write load the staleness has no ceiling
•
a read routed to a replica right after a write can miss it, so the session must be pinned or made to wait
•
promoting a standby requires first guaranteeing the old primary cannot accept another write
•
two nodes both believing they are primary is the failure every HA design exists to prevent, which is why voters are odd-numbered
•
a DROP TABLE replicates in milliseconds; only a time-delayed copy or a backup survives a logical mistake
Q. CAP, quorums and eventual consistency
•
when the network splits you either refuse writes or serve possibly-stale reads; the theorem constrains only that moment
•
with no partition at all, a distributed store still trades latency against consistency on every single request
•
with R + W > N a read set and a write set must intersect, which is how a quorum store returns a fresh value without contacting everyone
•
replicas converge only if writes stop; until then two reads can legitimately disagree
•
prepare-then-commit makes a write atomic across systems and leaves an in-doubt transaction holding locks if the coordinator dies
R. Partitioning and sharding
•
by an ordered key such as time, by discrete value, or by hash for even spread with no natural boundary
•
a query whose predicate names the partition key skips whole partitions without reading them
•
a query that omits the partition key touches every partition and is slower than the unpartitioned table would have been
•
dropping an old time partition is a metadata operation, while deleting the same rows is a bloat and vacuum problem
•
a primary key or unique constraint on a partitioned table must contain the partition key, because indexes are per-partition
•
partitioning is one server and one transaction; sharding is many servers, and only the second one adds capacity
•
the shard key determines which queries stay on one node, and it is the hardest decision in the system to reverse
•
a skewed key concentrates traffic on one node, so the cluster's real capacity becomes that node's capacity
S. Schema design, normalization and integrity
•
a comma-separated list inside a column defeats indexing, constraints, joins and statistics simultaneously
•
second normal form: with a composite key, a column that depends on only part of it belongs in another table
•
third normal form: a non-key column that depends on another non-key column belongs with that column
•
copying a value buys read speed and obliges every future writer to keep the copy correct
•
the engine rejects the orphan under concurrency; an application-side check races and eventually loses
T. NoSQL families and polyglot persistence
•
a persistent hash table: O(1) by key, and no way to ask a question you did not plan the key for
•
self-describing records queried by nested field, which moves schema enforcement into the application
•
rows addressed by partition key plus clustering columns, so you model the query rather than the entity
•
edges are first-class, so traversal depth costs a hop instead of another join
•
embedding wins when the child is always read with the parent and stays bounded; an unbounded array inside a document is the classic failure
•
a JSONB column gives the flexible part of the document model without a second system to back up, fail over and keep consistent
U. Buffer cache, memory and application caching
•
the engine already caches pages, so a slow query is often a cache-miss problem rather than a missing cache layer
•
performance falls off a cliff at the point where the hot data stops fitting in RAM, not gradually
•
the application reads the cache, misses, loads and populates; the database never learns the cache exists
•
writing both places synchronously versus acknowledging early and accepting a window of loss
•
a stale entry is a correctness bug the database cannot see, and a TTL is only a bound on how wrong you are willing to be
•
a hot key expiring sends every request to the database at once; jitter the TTL or let one request repopulate
V. Connections, pooling and resource limits
•
past roughly the core count, more concurrent backends means more context switching and less completed work
•
a small pool of server connections serves many application clients, turning a connection storm into a short queue
•
transaction pooling multiplexes far better and breaks session state, temp tables and server-side prepared statements
•
a per-statement ceiling is the cheapest protection against one runaway query taking the whole system down
•
a pooled connection abandoned mid-transaction holds locks and a snapshot until something kills it
W. Migrations and zero-downtime schema change
•
a migration is an ordered script applied exactly once and recorded in the database itself, which is what makes environments comparable
•
add the new shape, backfill, dual-write, switch reads, then remove the old one: five deploys, never one
•
old application code is still running during a rollout, so a rename must be done as add-then-drop
•
one giant UPDATE holds locks for its whole duration, bloats the table and blocks vacuum; batch it and make it resumable
•
builds without blocking writes, at the cost of two passes, no enclosing transaction, and a possible invalid index to clean up
X. Backups, PITR and restore
•
a portable SQL dump that rebuilds every index on restore, versus a byte copy of the data directory that restores fast but pins the version
•
a physical copy plus the archived log stream is what makes point-in-time recovery possible at all
•
recovering to a timestamp, an LSN or a named restore point is the only real answer to a mistaken DELETE
•
the number that matters is restore time under real data volume, and it is unknown until it is rehearsed
•
a backup writable from the compromised host is not a ransomware backup
Y. Observability and operations
•
a 5 ms statement run a million times costs more than a 2 s statement run twice, so rank by aggregate time
•
per-index scan counts are the evidence for dropping an index, and the count is cluster-lifetime, not recent
•
a backend is slow because it is waiting on a lock, on I/O, or on the client; the three have completely different fixes
•
during an incident, find the one root transaction everything else is queued behind rather than the loudest victim
•
the age of the oldest open transaction is the leading indicator of the next vacuum and bloat incident
Keentune is not affiliated with or endorsed by the organizations whose documentation informs these maps.
Start practising Databases
All about Databases practice
All exam, test, and product names and trademarks are the property of their respective owners and are used here for identification and reference only. Keentune is independent study practice — not affiliated with, authorized, or endorsed by any of these organizations.
© 2026 SportaApp LLC