What PostgreSQL Actually Is
PostgreSQL is a relational database that never overwrites a live row. An UPDATE writes a brand-new version of the row and marks the old one as expired at the current transaction id, and a query that started a moment earlier keeps reading the old version without waiting for anything. Nothing is edited in place. No reader ever queues behind a writer.
Postgres is also catalogue-driven: data types, functions, operators and even index access methods are rows in system tables rather than compiled-in server internals, so one statement can add a vector type with its own index method to a relational database. And the project answers to no company — a permissive licence, no open-core edition, no per-core invoice, and a release calendar published years ahead. Row versions, extensibility and independent governance account for most of what surprises engineers arriving from MySQL or Oracle.
A Row Is a Version, Not a Slot
The documentation says it plainly: an UPDATE or DELETE does not immediately remove the old version of the row, because that version must not be deleted while it is still potentially visible to another transaction. A table therefore does not hold rows. It holds row versions, and several versions of the same logical row can sit in it at the same moment, each carrying the id of the transaction that created it and the id of the transaction that expired it.
Cartwheel's checkout path decrements one small integer on a hot, narrow table.
-- inventory: product_id, warehouse_id, on_hand
UPDATE inventory SET on_hand = on_hand - 1
WHERE product_id = 4471 AND warehouse_id = 2;
That statement did not modify four bytes on a disk page. It copied the whole row into free space elsewhere, both key columns and the new on_hand and the tuple header in front of them, and stamped the previous copy as expired. Both copies are physically present the instant the statement returns. Unless the update qualifies for the heap-only optimization Chapter 7 covers, every index on inventory also gains an entry pointing at the new location while the old entry still stands.
Reading never blocks writing and writing never blocks reading, which is what lets the analytics dashboard aggregate orders while checkout hammers the same table. Rollback is close to free: an aborted transaction abandons the versions it wrote instead of unwinding them one at a time. Both are paid for in debris, and Chapter 7 puts a number on it. Every expired version stays exactly where it was written until a separate process comes past and reclaims the space.
Extensible by Design
Extensibility in Postgres is not a plugin API bolted onto a finished server. It is a property of how the server stores its own definition. The system catalogues hold far more than tables and columns: data types, functions, operators and access methods are catalogue rows too, those rows can be written by users, and the server will load a shared library at run time to supply the code behind them.
That is why the ecosystem is a set of extensions rather than a set of forks. PostGIS adds geometry types and spatial operator classes. TimescaleDB adds time-oriented partitioning and compression. pgvector adds a vector type with index methods of its own. pg_cron adds a scheduler. None of them patch the server, and all of them install with one statement.
-- run as cartwheel_admin, connected to the cartwheel database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
\dx
Each statement writes the extension's types, functions and views into the catalogue of the database the session is connected to — not the cluster, which is a distinction the next-but-one topic makes precise. The \dx listing then shows exactly what a given database has installed and at which version. The convenience carries two obligations, and Chapter 14 collects on both: the binaries have to be present on the host before any of this works, and every extension the application depends on becomes a deployment requirement that a managed provider's fixed list may or may not satisfy.
Standards, and the Places It Goes Further
Postgres tracks the SQL standard closely and unfashionably. Window functions, common table expressions, GROUPING SETS and MERGE are all present and all behave the way the standard describes; Chapter 4 spends five topics on the parts of that surface a beginner course never reaches. The standard is the baseline here, not the ceiling.
Above the baseline sit the features the standard does not have: arrays, ranges, jsonb, exclusion constraints, and transactional DDL. That last one is not a curiosity — it changes how a migration is written, because schema changes obey the same BEGIN and ROLLBACK as any other statement.
BEGIN; ALTER TABLE orders ADD COLUMN promised_at timestamptz; CREATE INDEX orders_promised_at_idx ON orders (promised_at); ROLLBACK;
The rollback undoes the column and the index together, and orders is left byte-identical to how it started: no half-applied schema, no cleanup script, no manual note in the runbook. Several major engines still commit each DDL statement the moment it runs. Chapter 3 builds a migration discipline on that difference, with two exceptions worth memorizing early: CREATE INDEX CONCURRENTLY and CREATE DATABASE cannot run inside a transaction block at all, and the first of those is precisely the one you want on a live 40-million-row table.
Nobody Owns It
The PostgreSQL License is permissive and short. There is no dual-licensed edition, no enterprise fork holding back the features that matter, and no per-core invoice waiting at the end of a capacity plan. Development is run by a global group working on public mailing lists, which means the roadmap is readable by anyone and the reason for a change is usually still on the record years later.
The release calendar is a planning input rather than trivia. One major version ships each autumn, minor releases arrive at least every three months, and each major is supported for five years. Cartwheel runs 18 for the end date rather than for the version number, and Chapter 14 walks the upgrade from 17 that got it there. PostgreSQL 18 was released on 25 September 2025, and its final minor release is scheduled for 14 November 2030.
What Postgres Is Not For
Postgres is a single-primary, disk-based, row-oriented OLTP engine, and it is honest about all three. It does not shard itself: one cluster is one machine's worth of write throughput, and spreading a single table's writes across nodes means Citus or a competing engine rather than a configuration change. Chapter 11 is where that line gets drawn with numbers instead of adjectives.
Replicas are read-only. Building pg-replica-a moved the analytics dashboard off the primary; it added exactly zero writes per second, and treating replication as write scaling hides the real answers (pooling, indexing, partitioning) behind a hardware invoice. Postgres is also not an analytics column store. delivery_events grows by 4 million rows a day, and Chapter 11 makes that survivable with partitioning and a BRIN index; the rows stay in row-major order on disk throughout.
InnoDB — keeps one current row in a clustered index and pushes previous versions into an undo log, where a purge thread collects them, so the table itself does not accumulate dead rows the way a Postgres heap does. Choose it when the operations habits around you are already MySQL habits and the schema asks nothing unusual of the type system.
PostgreSQL — keeps every version in the table's own heap and cleans up with vacuum. The cost is bloat and autovacuum tuning; the return is nearly free rollback, freedom from clustered-index layout decisions, and the type system and extensions the rest of this book is built on.
Either one runs Cartwheel. The difference is where old row versions live, and that decides which operational problems you inherit. MySQL's operational habits do not transfer, and assuming they do is the single most common source of surprise in this book.
- Reading
UPDATEas an edit in place — the model that follows from it ("a table is as big as its live rows") is wrong, and every bloat, vacuum and long-transaction problem in Chapters 6 and 7 stays invisible until that one sentence is corrected. - Assuming a rolled-back statement writes nothing — a bulk
UPDATEover 2 million rows inorderswrites 2 million new versions before it aborts, and the table grows by the full size of the change that did not happen. - Adding a read replica to make writes faster —
pg-replica-aaccepts no writes at all, so the write path is exactly as fast as it was, and the real fix has been postponed by a quarter plus a server bill. - Installing an extension for something the core already does —
jsonb, arrays and ranges cover most of what a second datastore gets installed for, and each extension is one more object that has to exist on whatever host the database moves to next. - Choosing a major version by novelty rather than by its support window — a version picked without checking the end-of-life date is an unscheduled upgrade that will surface in an audit rather than in a plan.
- Running a migration as a bare sequence of statements outside a transaction — Postgres will roll back
ALTER TABLEandCREATE INDEXtogether if you let it, and a script that declines the offer leaves half a schema behind when statement four fails.
- Say "row version" out loud when reading any
UPDATE— the vocabulary is the model, and it turns vacuum, isolation and index maintenance from mysteries into predictions. - Reach for a core type before an extension:
jsonbfor documents, arrays for short lists, ranges for intervals, andEXCLUDEconstraints for "these two must not overlap". - Record every extension the application depends on as a versioned deployment requirement alongside the schema, so a rebuild or a move to a managed provider fails at review time instead of at cutover.
- Wrap each migration in an explicit
BEGIN…COMMIT, and give the statements that cannot run in a transaction block,CREATE INDEX CONCURRENTLYfirst among them, a separate step of their own. - Pick the major version by its five-year support window and schedule the next upgrade before that window enters its final year.
- Treat advice written for MySQL or Oracle as a hypothesis to test against
EXPLAINand thepg_stat_*views, never as a rule that transfers.
Knowledge Check
A single-row UPDATE commits on Cartwheel's inventory table. What has physically happened to the previous version of that row?
- It remains in the table as an expired version until vacuum reclaims the space
- It was copied into a separate undo log, so the table itself holds one row
- It was overwritten in place, and the previous bytes are gone once the transaction commits
- It was removed at commit and its space handed straight back to the file system
Why can an extension add a new data type with its own index access method without anyone patching or recompiling the server?
- Types, operators and access methods are catalogue rows the server reads at run time
- The extension is already compiled into every official PostgreSQL binary package that ships
- PostgreSQL rewrites the query into equivalent core SQL before it plans anything
- Each extension runs inside a sandboxed process outside the database backend
A migration runs ALTER TABLE and CREATE INDEX inside one explicit transaction, and the index build raises an error. What is the state of the schema afterwards?
- Unchanged, because both statements are rolled back together
- The column exists and the index is missing, so the script needs editing
- Both are gone, but an invalid index definition stays behind in the catalogue
- Both survive, and the index is marked invalid until it is reindexed
Cartwheel's write throughput is at its ceiling on Saturday mornings. Why does building a second streaming replica not raise it?
- Replicas do accept writes, but merging them back to the primary is too slow to count
- Replicas take read traffic only, so every write still lands on the primary
- Each extra replica doubles the volume of WAL the primary has to write
- The replica has to be promoted before it can share any of the workload
A bulk UPDATE touching 2 million rows in orders is rolled back after twenty minutes. What did that cost the table?
- Postgres reverses each row change, so the rollback takes roughly as long as the update did
- Two million new row versions were already written and are now dead rows
- Nothing at all, since an aborted statement never writes anything into the heap
- The table stays locked until a vacuum reclaims the space the update reserved
You got correct