PostgreSQL · Self-assessment
Knowledge checks
Every knowledge check in this course, in curriculum order. Each link opens the page at its quiz. The questions are auto-graded in the browser and nothing is recorded — a wrong answer costs you only the explanation, which is the part worth reading.
- Knowledge checks
- 127
- Parts covered
- 19
- Of all lessons
- 100%
Part I
Architecture and the Process Model
7 checks
- What a PostgreSQL "database cluster" actually isPostgreSQL uses the word cluster to mean one server instance and the databases inside it, which is not what the rest of infrastructure engineering means by the word. Getting this boundary right decides what you can back up, replicate, restart and move independently.→
- The process model: one backend per connectionPostgreSQL forks an operating-system process for every client connection. That single design decision explains connection cost, the shape of its memory accounting, why max_connections is not a free number, and why connection pooling exists at all.→
- Shared memory and the buffer poolEverything PostgreSQL processes agree on lives in one shared memory segment allocated before any backend exists. Knowing what is in it, and that its size is fixed until restart, decides how you plan a memory change and how you read a cache-hit ratio.→
- The background processes and what each one is responsible forSeven background processes do the work no client asked for: writing dirty pages, flushing WAL, reclaiming dead rows, issuing asynchronous I/O. Knowing which one owns a symptom is what turns a vague slowdown into a specific investigation.→
- A connection from TCP to first querySix stages sit between a client calling connect() and PostgreSQL accepting a query, and a connection failure belongs to exactly one of them. Learning the sequence turns "the application cannot connect" from a guess into a bisection.→
- Inside the data directoryWhat each directory under PGDATA holds, how to find the file behind a table, and why every one of those files is off-limits to manual intervention. The map matters precisely so you never need to reach into it.→
- The operator's first instrumentsThree catalogue views answer most of the questions asked in the first five minutes of a database incident: what is this server configured to do, what is it doing right now, and where is its I/O going. Learning to read them is the difference between investigating and speculating.→
Part II
Installation, Packaging and Service Management
6 checks
- Choosing a version, and the support calendar that decides itPostgreSQL supports five major versions for five years each, on a published schedule. Reading that schedule turns version choice from a preference into a dated commitment, and it is the difference between an upgrade you plan and one you are forced into.→
- Packaging, and where the packaging decides things liveDebian, Red Hat and the official container image lay PostgreSQL out in three incompatible ways. A runbook that hard-codes one path fails on the others, and the difference is not cosmetic: it decides where the configuration is and which tools manage the service.→
- What initdb commits you to, permanentlyThree decisions made in the first second of a cluster's life cannot be changed later without rebuilding it: the encoding, the locale and collation provider, and — before PostgreSQL 18 — whether data checksums exist. Getting them wrong is a migration, not a configuration change.→
- Service management, and stopping PostgreSQL safelyPostgreSQL has three shutdown modes and they differ in what happens on the next start. systemd defaults, container stop signals and impatient operators all choose between them, usually without anyone deciding to.→
- Multiple clusters on one hostRunning several PostgreSQL clusters on one machine is the only way to give workloads independent restart windows and restore points without buying more machines. It is also how you discover which resources are still shared.→
- PostgreSQL in a container imageThe official image is a convenient development database and a set of decisions you inherit silently: an entrypoint that only initialises an empty volume, a server running as PID 1, and a stop signal that decides whether your next start is a recovery.→
Part III
Configuration Architecture
6 checks
- Where configuration comes fromA running PostgreSQL server assembles its configuration from a main file, whatever that file includes, and a file written by ALTER SYSTEM that is read last and wins. Knowing the assembly order is what makes "I edited it and nothing changed" a diagnosable statement.→
- Settings contexts, and planning a change from themEvery PostgreSQL parameter carries a context that states exactly what it takes to change it. Reading the context before proposing a change is what separates a five-second reload from an outage window, and two of the seven contexts behave in ways that surprise people.→
- The precedence ladder beyond the filesA setting can be attached to the server, to a database, to a role, to a role in one database, or to a session. The one that wins is the most specific one, which means a server-wide value is a default rather than a guarantee.→
- ALTER SYSTEM, and how it failsALTER SYSTEM validates the syntax of a value and not its feasibility, so it will happily write a setting that makes the server unable to start. The failure is deferred to the next restart, which may be weeks later and under incident conditions.→
- Proving a change took effectA configuration change has four states that look identical from a shell prompt: written, loaded, in effect, and in effect for the sessions that matter. Four queries separate them, and running them is the difference between a change and a belief.→
- A configuration you can hand overThe test of a configuration is not whether it performs well but whether the next person can tell which values were chosen deliberately, why, and what would have to change for them to be wrong.→
Part IV
Connections, Sessions and Pooling
7 checks
- What a connection costsA PostgreSQL connection is an operating-system process with private memory, a catalogue cache and a share of every contended structure in the server. Costing it properly is the prerequisite for every capacity decision in this part.→
- max_connections, and what it actually reservesThe connection ceiling is not the number of connections your application can open. Two reservation mechanisms sit below it, and PostgreSQL 16 added a second one specifically so that an operator can get in when an application has consumed everything else.→
- Reading pg_stat_activity as an instrumentFour timestamps, two identity columns and a wait event turn one row per session into a diagnosis. The timestamps in particular answer different questions, and using the wrong one is how an investigation ends up chasing a session that has been fine for hours.→
- Idle in transaction, and what it actually holdsAn idle-in-transaction session is routinely blamed for blocking VACUUM. Whether it does depends on its isolation level and whether it has written, and three sessions in the same state can hold three different things. Diagnosing the wrong one wastes the investigation.→
- Connection exhaustion and the storm that followsA saturated connection pool is rarely the disease. It is usually a symptom of slowness elsewhere, and the application's reaction to it — reconnecting harder — is what converts a degradation into an outage.→
- Why pooling exists, and the three modesA pooler lets many application connections share few database connections, which is the only way to reconcile a process-per-connection server with an application tier that scales horizontally. The three pooling modes trade compatibility for that benefit at very different rates.→
- PgBouncer in productionA pooler becomes a single point of failure in front of the database, an extra authentication boundary, and a component whose own saturation looks exactly like database saturation. None of that is a reason to avoid it, and all of it needs planning.→
Part V
Authentication, Roles and TLS
7 checks
- pg_hba.conf: first match winsPostgreSQL walks its access rules top to bottom and stops at the first one that matches on all four columns. That rule alone decides the outcome, and every rule below it is never consulted — which is why a broad rule added at the top silently changes authentication for everything beneath it.→
- Authentication methods and what each one provesEvery method in pg_hba.conf answers the question "is this client who it claims to be" with different evidence and different failure modes. Choosing one is choosing what you are willing to accept as proof.→
- SCRAM, password storage, and the MD5 deprecationPostgreSQL stores a verifier rather than a password, and the difference between the two verifier formats is the difference between a stolen hash being useless and a stolen hash being a working credential.→
- TLS and the sslmode ladderFour of the six sslmode values encrypt the connection and only one verifies who is on the other end of it. The gap between "encrypted" and "verified" is where every TLS misconception about PostgreSQL lives.→
- Roles, membership and inheritancePostgreSQL has one object type for users and groups, and whether a member automatically gets its group's privileges is a property of the grant rather than of either role. Getting that wrong produces permissions that appear absent until somebody runs SET ROLE.→
- Ownership, default privileges, and the permission disaster they causeGRANT ON ALL TABLES applies to the tables that exist at the moment you run it. Every table created afterwards is invisible to it, which is why permissions that were correct on Monday are wrong after Tuesday's migration.→
- Superuser, and what it really grantsA PostgreSQL superuser bypasses every permission check, and several of its capabilities reach outside the database entirely. Treating it as "an admin account" understates it: it is host access with extra steps.→
Part VI
Storage, Pages and TOAST
6 checks
- The durability chain from COMMIT to platterA COMMIT that returns successfully has made a promise, and that promise is only as good as every layer beneath it. Each layer can be configured to lie, and several of them are configured to lie by default.→
- Relations, forks and segmentsOne logical table is many files. Knowing which ones exist, what each holds and how they are named is what lets you interpret disk usage instead of guessing at it.→
- Pages and tuplesEvery table is a sequence of 8 KiB pages, and every row is a tuple within one. The page layout explains why an UPDATE does not overwrite a row, why deleted rows still occupy space, and why row width decides how much I/O a query costs.→
- The free space map and the visibility mapTwo small auxiliary forks decide whether your tables grow forever and whether your indexes can answer queries without touching the heap. Both are maintained by vacuum, and neither is optional.→
- TOAST and large valuesA row must fit in an 8 KiB page, and yet PostgreSQL stores gigabyte values. TOAST is how, and understanding it is what stops a disk investigation reaching the wrong table.→
- Choosing storageLocal NVMe, SAN, cloud block, distributed filesystem. Rather than naming a winner, this lesson gives you the measurement that decides it and the questions that disqualify a candidate.→
Part VII
MVCC, Transactions and Visibility
6 checks
- Why PostgreSQL keeps old row versionsAn UPDATE does not change a row, it writes a new one. That single design decision explains most of what you will spend your operational life dealing with, good and bad.→
- What UPDATE actually does to a pageThe difference between an update that touches one page and an update that touches every index on the table is a single condition, and it is worth 2.8 times the WAL.→
- Transaction IDs, snapshots and tuple visibilityA snapshot is three values. Once you can read them, most questions about what a session can see, and about why vacuum will not clean up, answer themselves.→
- Isolation levels as PostgreSQL implements themThree levels, each demonstrated with two concurrent sessions and the real errors they produce. Including the anomaly that repeatable read does not prevent, and why that matters more than the ones it does.→
- Long-running transactionsOne session that read a row count and then went quiet turned a 1.7 MB table into a 19 MB one, and ten vacuums removed nothing. This is the most expensive habit in the course.→
- Reading transaction ageSix queries that tell you where a cluster stands on transaction ids, what each number means, and which of them should page you at three in the morning.→
Part VIII
VACUUM, Autovacuum and Wraparound
7 checks
- What VACUUM doesTwo identical tables, the same number of rows deleted from each, one shrinks by half and the other does not move. The difference explains the most common complaint about vacuum.→
- Autovacuum: launcher, workers and thresholdsAutovacuum is a scheduler with a fixed budget. Knowing the arithmetic it uses to pick tables, and the throughput ceiling it works under, is what separates tuning from guessing.→
- Tuning autovacuum from evidenceTwo identical tables under an identical workload, one with per-table settings. One ends clean; the other is left sitting permanently on 67,206 dead tuples it will never collect.→
- When autovacuum cannot keep up: six failure modesSix distinct reasons a table stays dirty, each with a different symptom, a different diagnostic and a different fix. Applying the wrong fix is the usual reason the problem persists.→
- Transaction ID wraparound and anti-wraparound vacuumThe one PostgreSQL failure that stops the cluster accepting writes. It is entirely preventable, gives roughly three weeks of warning, and still happens.→
- Measuring bloat honestlyA table with zero dead tuples was 50% empty. A plain SELECT was refused while VACUUM FULL rebuilt a 4 GB table. Both facts change how you should think about bloat.→
- ANALYZE and planner statisticsThe same query on the same data cost 1003 buffers before ANALYZE and 4 after. Statistics are not a nicety, and the places they go wrong are predictable.→
Part IX
Locks, Blocking and Deadlocks
6 checks
- Lock modes and the conflict matrixEight table-level modes, one matrix, and the confusion between EXCLUSIVE and ACCESS EXCLUSIVE that makes migrations more disruptive than anyone predicted.→
- Row locks versus table locksFour row lock modes exist so that inserting a child row does not block updating its parent. Measured: a non-key update of a referenced row took 4.9 ms; a key update timed out.→
- Finding the blockerOne query that answers "who is blocking whom" in an incident, and the reason the session at the top of the chain is often not the one holding a lock at all.→
- DeadlocksA deadlock is the one lock problem PostgreSQL resolves for you. The log carries both halves; the client is only told its own, which is why the log line is the one that matters.→
- DDL and the lock queueA plain SELECT was refused on a table whose only granted lock was another plain SELECT. The lock queue is ordered, and that single fact turns a metadata change into an outage.→
- Cancel or terminateThe two verbs behave differently depending on what the session is doing, and the folklore has it backwards. Measured: cancelling an active transaction released its snapshot; cancelling an idle one did nothing at all.→
Part X
Query Planning, Indexes and Performance Method
8 checks
- How the planner choosesThe cost of a sequential scan is one line of arithmetic, and the planner reported it to the decimal place. Once you can reproduce the number, plan choices stop being mysterious.→
- Reading an EXPLAIN planPlans are read inside out and bottom up, loops multiply everything above them, and the number in parentheses after a nested node is per iteration rather than in total.→
- EXPLAIN ANALYZE and its safety boundaryEXPLAIN ANALYZE on a DELETE deleted 500 rows. It is not a dry run, it is the statement plus instrumentation, and the difference matters most at the moment you are least careful.→
- When estimates go wrongAn estimate of 12 against an actual of 1000 produced a nested loop that ran 83 times more often than it was costed for. Extended statistics moved the estimate to 1027 and the query from 289 ms to 102 ms.→
- B-tree indexes and what they costAdding six indexes to a table made an identical bulk insert 6.1 times slower and generated 3.6 times the WAL, and left index storage twice the size of the data.→
- Beyond B-tree, where it mattersA BRIN index on a time column was 32 kB against the B-tree's 107 MB — a factor of 3,400 — and 2.3 times slower on the query. That trade is the whole of when to reach for a different index type.→
- Index maintenanceAn idx_scan of zero means nothing without knowing when the counter started, and the query that finds duplicate indexes misses the most common kind of redundancy.→
- A performance methodologyMost performance work fails because it starts at the wrong end. This is an ordered procedure that begins with what the system is actually waiting on and ends with a measured comparison.→
Part XI
Memory and Resource Management
6 checks
- PostgreSQL's memory mapNine processes summing to 728 MB of RSS on a server with 128 MB of shared buffers. Understanding which memory is shared and which is per-backend is what makes that number readable.→
- shared_buffers and the OS page cacheOne table occupied 98.2% of the buffer pool with an average usage count of 0.23. That single measurement explains more about buffer pool sizing than any rule of thumb.→
- The work_mem trapRaising work_mem from 4 MB to 1 GB made a two-million-row sort slower, not faster, in three consecutive runs. The setting is not a performance dial and the risk it carries is not the one people manage for.→
- maintenance_work_mem and the operations that use itRaising it from 4 MB to 1 GB did not measurably change an index build. It does change how many times vacuum reads every index, and that is the number to check before touching it.→
- Temporary files and spilling to diskAn index on the sort column reduced peak memory from 111 MB to 111 kB and removed the spill entirely. That is a better answer than raising work_mem, and it was found by accident.→
- Linux memory pressure and the OOM killerfree(1) inside a container reported 47 GB of host memory on a process whose real budget is set elsewhere. Sizing PostgreSQL from that number is how a cluster gets killed.→
Part XII
WAL, Checkpoints and Crash Recovery
7 checks
- Write-ahead logging: the durability contractThree hundred thousand committed rows survived a SIGKILL with no clean shutdown, and recovery took 0.12 seconds. This lesson is the contract that makes that true and the settings that void it.→
- WAL segments, LSNs and generation rate2,218 transactions per second produced 4,359 kB of WAL per second — about 367 GB a day. Capacity planning for WAL starts with measuring that number rather than estimating it.→
- What retains WAL, and what releases itAn inactive replication slot pinned 169 MB and would have gone on pinning indefinitely — but only once it had reserved a position, which the ordinary way of creating one does not do.→
- Checkpoints: what they do and what they costThis cluster ran 54 requested checkpoints against 26 timed ones. That single ratio says max_wal_size is too small, and it is printed for free in a view nobody reads.→
- Background writing and the PostgreSQL 18 I/O subsystemThe background writer was capped 570 times on this cluster, and PostgreSQL 18 added an asynchronous I/O subsystem with its own settings and its own view. Both change what the numbers mean.→
- Crash recovery: what "consistent state" meansThree hundred thousand rows, a SIGKILL, and 65 MB of WAL replayed in 0.12 seconds. This is the log line by line, including the one that reads like corruption and is not.→
- Shutdown modes and their recovery consequencesSmart shutdown waited ten seconds for one idle session, failed to complete, and refused every new connection while it waited. Fast shutdown finished in 119 milliseconds.→
Part XIII
Backup, Archiving and Point-in-Time Recovery
8 checks
- Backup success is not recovery capabilityA pg_dump of a database with roles produced zero CREATE ROLE statements. The backup succeeded. The restore would have failed, and nothing in the backup job would have said so.→
- Logical backups: pg_dump, pg_restore, scope and limitsA custom-format dump with 200 bytes overwritten was rejected before the restore began. The same corruption in a plain SQL dump is discovered part-way through applying it.→
- Physical backups and the low-level APIpg_backup_stop() returns the backup_label contents rather than writing them, and closing the session mid-backup produces a warning most operators never see.→
- pg_basebackup: its role and its limitsAn incremental backup sent 3% of the cluster: 24 MB against a 144 MB full backup. Started on its own it says "this is an incremental backup, not a data directory".→
- WAL archiving: archive_command, integrity, retentionALTER SYSTEM SET archive_command reported success, wrote the file, and changed nothing — because the setting came from the command line.→
- Point-in-time recovery, performedA DROP TABLE was undone by restoring to the second before it. The complete recovery log, including the three lines that look like failures and are not.→
- Recovery targets and the action taken on reaching onePaused at the recovery target, the startup process still held AccessExclusiveLock on the very table we were trying to inspect.→
- Backup tooling, restore testing, and proving recoverabilitypg_verifybackup says "backup successfully verified" and proves nothing about whether the cluster starts. Only a restore does that.→
Part XIV
Replication, Slots and Read Replicas
8 checks
- Physical streaming replication end to endTwo processes carry the whole mechanism. Everything else is configuration around a walsender, a walreceiver and the same startup process that performs crash recovery.→
- WAL sender and WAL receiverpg_stat_wal_receiver reports written_lsn and flushed_lsn, not received_lsn — and the conninfo it exposes masks the password as ********.→
- Building a standbypg_basebackup -R writes both the connection string and standby.signal. The password goes in as plain text, and the first attempt at this failed for a reason worth keeping.→
- Measuring lag properly: sent, written, flushed, replayedOn an idle cluster all three lag columns are NULL. That is a healthy standby, and a monitoring check that alerts on NULL alerts on health.→
- Synchronous replication and the trade-off it makesFour synchronous_commit levels benchmarked on the same pair: 1624 tps at local, 1474 at remote flush. And the failure mode where one standby going away stops every commit on the primary.→
- Replication slots and unbounded WAL retentionmax_slot_wal_keep_size does not prevent a failure. It chooses which failure you have: a bounded pg_wal and a broken standby, or an unbounded pg_wal and a recoverable one.→
- Replica conflicts and hot_standby_feedbackThree settings, three failure modes, and no combination that avoids all of them. With feedback on, the primary vacuumed and removed exactly zero of 66,667 dead tuples.→
- Read replicas and what the application must accept200 trials of write-then-read-from-the-replica produced zero stale reads. That is not reassurance — it is the reason the hazard reaches production.→
Part XV
High Availability, Failover and Disaster Recovery
8 checks
- Replication is not high availabilityA standby was promoted while the old primary kept running. PostgreSQL had no objection, no mechanism to object, and neither cluster knew the other existed.→
- Promotion, timelines, and what a timeline switch meansPromotion took under a second. pg_controldata still reported timeline 1 afterwards, because no checkpoint had run yet — the control file reports the last checkpoint, not the present.→
- Split-brain and why fencing is not optional120 committed, acknowledged rows were destroyed to resolve a split-brain. Not lost to a crash — deliberately discarded, because they were on the timeline that lost.→
- What an HA stack must supply beyond PostgreSQLSix responsibilities, none of which PostgreSQL provides. The checklist for evaluating any HA tool, and for judging whether you need one.→
- Patroni as one implementation — concepts firstloop_wait + 2 × retry_timeout ≤ ttl. One documented inequality, and understanding why it exists explains most of what an HA stack does.→
- Client routing: DNS, VIP, proxy, service discoverytarget_session_attrs=read-write found the primary even when it was listed second. It does nothing for a connection that is already open when the failover happens.→
- Validating a failover, and rejoining a failed primary with pg_rewindpg_rewind copied 196 MB of a 376 MB cluster and named the exact divergence LSN. It also refused to run until the target had been shut down cleanly.→
- HA versus backup versus DR; RPO and RTO with real arithmeticThree different problems that get one budget line. The failure that proves they are different: a DROP TABLE replicated to every standby in milliseconds.→
Part XVI
Observability, Logging and Alerting
7 checks
- Production logging configurationA log_line_prefix without a timestamp, a pid and a user is a log you cannot correlate with anything. Here is the configuration that earns its disk space.→
- Log security: what statement logging exposespg_stat_statements stored a password verbatim. Not the log — the view, readable by anyone you granted pg_monitor.→
- The statistics views that matter, and the ones that changedpg_stat_bgwriter has three columns left. Everything a dashboard used to read from it moved to pg_stat_checkpointer in PostgreSQL 17.→
- Wait events: what is this backend actually waiting on?Eleven of sixteen backends waiting on Lock/transactionid at once. The wait events and the statement view agreed on the diagnosis from opposite directions.→
- pg_stat_statementsEvery statement ran exactly 9,012 times. One of them consumed 85% of the time — and it was the one touching five rows, not the one touching half a million.→
- Correlating PostgreSQL evidence with the operating systempg_stat_activity.pid is the operating system pid. That one fact makes every OS tool applicable to a specific query.→
- Alerting that is actionableAn alert nobody acts on is worse than no alert, because it trains everyone to ignore the channel the real one will arrive in.→
Part XVII
Capacity, Maintenance and Upgrades
8 checks
- Capacity planning beyond current database sizeThe database is the smallest number in the plan. WAL at 4,359 kB/s is 367 GB a day, and a restore needs more space than the cluster it restores.→
- Disk-full: distinguishing data, WAL, temp, archive and backupA full pg_wal did not just stop writes. Recovery itself failed for the same reason, and the cluster would not start at all.→
- Planned maintenance and its resource costEvery maintenance operation costs I/O, locks, WAL and space. Knowing which of the four a given operation spends is how you schedule it safely.→
- DDL and schema change from an operations perspectiveA plain SELECT was refused after six seconds while the only granted lock was another plain SELECT. The waiting ALTER TABLE held nothing and blocked everything.→
- Minor version upgradesSwap the binaries, restart. No dump, no pg_upgrade, no data change — and the one class of exception that makes people distrust the rule.→
- Major version upgrade options comparedFour routes, and the choice is made by how much downtime you can take and how large the cluster is — not by which is technically nicest.→
- pg_upgrade in practiceA 17.11 to 18.6 upgrade, performed. The planner statistics survived to the last digit — and the cumulative statistics did not.→
- Upgrade rehearsal and post-upgrade validationA rehearsal on a restored copy is the only way to learn how long your upgrade takes and what it will break. The two-second measurement in this course establishes neither.→
Part XVIII
Platforms, Corruption and Production Architecture
8 checks
- Managed versus self-managed: what stays your responsibilityA managed service takes over the operations in Parts II, XIV and XV. Everything in Parts VII to XI, and the whole of Part XIII, stays yours.→
- PostgreSQL in containers: what a container does not solvefree reported 47 GB inside a container limited to far less. Everything in this course was measured in containers, including the traps they introduce.→
- PostgreSQL on Kubernetes: operators, StatefulSets, storage, fencingKubernetes will restart your database. That is the problem, not the feature — because restarting a demoted primary is how you get two.→
- PostgreSQL on distributed storageAn 854 microsecond fsync predicted 1,170 commits per second, and pgbench measured 842. On network storage that latency is the number that decides everything.→
- Data corruption: signals and careful responseA corruption check passed on a corrupt table because the damaged page was still in shared buffers. After a restart, the same check failed.→
- Checksums, index corruption, and storage failureThe same eight bytes, two clusters. With checksums: an error. Without: the string CORRUPTX returned inside a row, at the right length, with no complaint.→
- Automating PostgreSQL, and change management for database changeDatabase change is different from application change in one way that decides everything: you cannot roll back a dropped column.→
- A production reference architectureEverything measured in this course, assembled into one design — and the reasoning that lets you defend each choice or change it.→
Part Capstone
Production Capstone
1 check