1.2.1 Postgres is not MySQL with more types

MySQL teams reach for Postgres expecting a familiar engine with a richer column catalog. They get something else. MySQL’s InnoDB stores rows by clustering them around the primary key, which makes primary-key reads fast and secondary-index reads a hop slower. Postgres has no clustered index. Every table is a heap. The primary key is a B-tree pointing into the heap, identical in shape to any other index. A heap is a pile of rows in no particular order on disk. Think of it as a warehouse where every box has a coordinate (ctid), and indexes are forklifts that know which coordinate to drive to.

That single difference cascades. In MySQL, a covering query on a secondary index still has to follow pointers if the index isn’t covering, but it has the primary key clustered nearby. In Postgres, every index lookup may force a heap visit unless the visibility map says it can skip one. The visibility map is a small bitmap per relation that records which pages contain only tuples visible to all transactions. Index-only scans in Postgres are conditional, not free.

The bigger gap is concurrency. Postgres uses snapshot-based MVCC at every isolation level, including READ COMMITTED and REPEATABLE READ; readers see a consistent version of the world without taking range locks. MySQL’s InnoDB at its default REPEATABLE READ reaches for gap locks on index ranges to prevent phantoms, which changes how concurrent inserts and updates contend. Postgres still lets lost updates happen under READ COMMITTED, the same way Oracle and InnoDB do; you reach for SERIALIZABLE or explicit row locking when you need to rule them out. The MySQL playbook for “how to handle concurrent updates” does not transfer.

1.2.2 Postgres is not Oracle with worse tooling

Oracle veterans show up looking for the equivalents of materialized view logs, autonomous transactions, and MERGE with WHEN NOT MATCHED BY SOURCE. Some of those map. Some don’t. Postgres has materialized views but they don’t refresh incrementally on their own. It has MERGE (since 15) but historically you reached for INSERT ... ON CONFLICT, the upsert, which has better semantics for many cases anyway.

The deeper Oracle expectation that breaks is the undo log. Oracle keeps old versions in a separate tablespace and lets vacuum-like cleanup happen invisibly. Postgres keeps old versions in the table itself, marked dead, and waits for a process called autovacuum to walk through and reclaim them. Autovacuum is a background worker that scans tables, marks dead tuples reusable, and updates statistics. The reader who has tuned Oracle for twenty years will, on Postgres, feel the operational tax of MVCC sooner than expected.

1.2.3 Postgres is not SQLite with a network port

SQLite is a beautiful single-process database where the whole file is the database. It uses a write-ahead log and a single writer at a time, and it scales to a remarkable amount of work for a process-embedded engine. People use it as a mental model for “small Postgres,” and that model leads them to write code that doesn’t pool connections, doesn’t think about concurrency, and runs INSERT in a loop without a transaction.

Postgres is multi-process and multi-connection. Every connection is its own OS process. Every transaction sees a consistent snapshot of the data, isolated from other in-flight transactions. You can have a hundred writers in flight if you can afford the connections, and the answer to “can I afford the connections” is almost always “fewer than you think.”

1.2.4 The “Postgres has X” pattern

Ask a Postgres veteran whether the database supports something. The answer is almost always yes, possibly through an extension. JSON queries with indexes? jsonb plus GIN. Time-series? pg_partman or TimescaleDB. Geographic data? PostGIS. Vector search? pgvector. Full-text search? Built in. Custom types, operators, and index methods? All first-class.

This is not an accident. Postgres ships with an extension API that lets new types, operators, index access methods, and procedural languages plug in as first-class citizens. When someone says “Postgres can do that,” they’re describing the database’s culture, which is to absorb features the way a sponge absorbs water.

1.2.5 What Postgres does not do well

The honest version of “Postgres has X” comes with a list of things Postgres does badly, and pretending otherwise will get you in trouble:

• High-throughput OLTP at extreme write rates with single-node writes is hard. The WAL is one log. The primary is one process tree. There is no native multi-master, despite a decade of half-attempts. If your sustained write rate is in the tens of thousands of write transactions per second on a single primary, depending on hardware and tuning, you’re at the edge of what one Postgres node does, and you should know that going in.
• Cross-shard transactions are not a thing in core Postgres. Citus exists, FDW-based sharding exists, and both make trade-offs.
• Connection scaling is a known weakness. Each connection is a process; thousands of connections is thousands of processes. pgbouncer is mandatory in any production deployment of meaningful scale, and that requirement tells you something about the architecture.
• Schema-on-read flexibility the way some NoSQL stores do it is not idiomatic. JSONB gets you most of the way there, but the planner still works best on typed columns.

A book that pretends Postgres is the right answer to every problem is a marketing pamphlet. This isn’t. We’ll call out the cliffs as we walk past them.

1.3.1 What MVCC actually is

Picture a row in a Postgres table the way you’d picture a parking spot. When you UPDATE the row, Postgres doesn’t repaint the spot. It draws a new spot next to it, parks the new version there, and puts a sign on the old spot saying “expired as of transaction 12345.” The old spot stays there until every transaction that started before 12345 has either committed or died. Only then can vacuum come along, paint over the sign, and reuse the asphalt.

In Postgres terms: an UPDATE is an INSERT of a new tuple plus a flag on the old one saying “deleted by transaction X.” The old tuple is dead but visible to anyone whose snapshot started before X committed. The new tuple is alive but invisible to anyone whose snapshot started before X committed. Each tuple carries a small header with two transaction IDs: xmin (the transaction that created it) and xmax (the transaction that deleted or updated-out-of-existence it). The combination of those IDs and a snapshot tells the database which version any given reader sees.

You can see this directly. Every Postgres table exposes the system columns to a query that knows to ask:

SELECT xmin, xmax, ctid, id, body
FROM reviews
LIMIT 5;

xmin is the inserting transaction. xmax is usually zero on a row that has never been touched by a writer or locker; SELECT FOR UPDATE and SELECT FOR SHARE can stamp xmax on a still-live row as a lock marker. When multiple sessions hold a lock on the same row at once, xmax actually stores a MultiXact ID, a small structure that lists all the locking transactions. ctid is the physical location of this version: a (page, offset) pair that points into the heap.

1.3.2 Why this is the bet that defines everything

MVCC is the foundation. Once you accept that every row can have multiple physical versions, the rest of Postgres becomes inevitable:

• Indexes have to point at versions, not rows. Each index entry references a ctid. If a row gets a new version, that’s a new heap entry, and every relevant index has to learn about it. This is why heavy update workloads bloat indexes as much as they bloat tables.
• Vacuum is mandatory. Old versions don’t clean themselves up. A process has to scan the heap, identify dead tuples that no transaction can still see, and mark their space reusable. Without vacuum, the database fills with corpses. Autovacuum exists to keep this from being your job.
• Replication is built on the WAL of versions, not the WAL of statements. Because each modification creates a physical tuple version, the write-ahead log captures physical changes, and replicas replay them as physical changes. This is why Postgres replication is fast and identical-byte-for-byte, and why logical replication needs a separate decoder to translate physical changes back into row-level events.
• Long transactions are toxic. A transaction that started two hours ago is still holding a snapshot. Vacuum cannot reclaim any tuple version that transaction might still need to see. A single long-running query on a busy table can prevent autovacuum from making progress, with bloat scaling with the duration of the open snapshot.
• Locking changes shape. Readers don’t take row locks for normal reads. Writers don’t block readers. The contention surface narrows to writer-writer conflicts and to the explicit locks you ask for.

Once you internalize MVCC, every “weird” Postgres behavior makes sense. Why does my table grow even though the row count is flat? Bloat. Why is my replica falling behind? WAL volume from version churn. Why did my UPDATE on a table with twelve indexes take 30ms instead of 3ms? Every index had to learn about the new version. Why is count(*) slower than you’d expect on a churning table? Even with an index-only scan, the visibility map has to certify every page as all-visible to skip the heap, and a write-heavy table keeps invalidating those bits.

1.3.3 The cost of the bet, stated honestly

MVCC isn’t free. The cost shows up in three places:

1. Space. Old versions occupy disk until vacuum reclaims them. A table with a steady update rate runs perpetually larger than the live row count suggests.
2. CPU. Vacuum is real work. Visibility checks during reads are real work. Multi-version row headers are bigger than single-version row headers would be.
3. Operational complexity. Autovacuum tuning is a real skill. Long-running transactions are a real risk. The transaction-ID counter wrapping around is a real thing that has taken real production databases offline.

1.3.4 Other databases bet differently

Oracle uses MVCC too, but stores old versions in undo segments rather than inline in the table. SQL Server has a switchable model. MySQL InnoDB uses MVCC but the implementation differs in important ways that make MySQL feel different at the seams. The point isn’t that Postgres is unique in using MVCC; it’s that Postgres’s flavor of MVCC, with old versions inline in the heap and a vacuum process to clean them, is the architectural decision that drives the rest of the design.

Postgres treats row versions as first-class objects on disk and lets background work clean up later, in exchange for keeping reads and writes out of each other’s way at runtime. Every chapter that follows is downstream of that choice.

1.4.1 The cost of a connection

Spinning up a connection is not free. A new backend process needs:

• An OS process and its kernel data structures (file descriptor table, signal mask, page tables)
• Roughly 5 to 10 megabytes of resident memory (RSS) before it does any real work, much of which is shared via copy-on-write with the postmaster; unique private memory per backend is typically a few MB
• A piece of shared-memory state to participate in the buffer cache and lock manager
• A PGPROC slot in shared memory’s ProcArray, whose size is fixed at server start by max_connections plus auxiliary slots

Multiply that by your connection count. A thousand idle Postgres connections is on the order of 10 GB of process footprint, plus a thousand entries in the OS run queue. The kernel’s context-switch overhead between thousands of mostly-idle processes is real and measurable. I’ve personally watched a Postgres server with 4,000 idle connections spend 40% of its CPU on context switches before any query ran.

Compare this to MySQL or SQL Server, where connections are threads inside a single process and idle connections cost a kilobyte and a stack page. Or to a stateless service behind a connection pool, where 10,000 client connections fan into 50 server connections. The Postgres model assumes you do not have 10,000 backends. It cannot handle 10,000 backends. It is built for tens to low hundreds of active backends, and the gap between client connections and server backends is filled by something external.

1.4.2 Why this matters before you write a single query

Most teams discover the connection problem during a traffic spike. The application’s connection pool was sized assuming connections are cheap. Postgres is happily serving 200 backends, then 400, then 800, and somewhere around 1,500 the box runs out of memory or max_connections and the application starts seeing “remaining connection slots are reserved” errors.

The fix is not to raise max_connections. The fix is connection pooling. pgbouncer is the de facto solution: a tiny, fast process that sits between your application and Postgres, multiplexing many client connections onto a small number of database backends.

A few numbers worth keeping in your head:

• A working Postgres backend sits around 5 to 10 MB of resident memory before it does anything useful.
• Active backends should fit under your CPU count times some small factor.
• Client connections can be in the thousands. Server backends should be in the dozens.

The mismatch is what pgbouncer fixes. Pretending the mismatch doesn’t exist is what page-3 incidents are made of.

1.4.3 Per-backend memory adds up

The backend’s baseline cost is the floor. Each backend also reserves memory for its own work:

• work_mem for sorts and hash operations within a query
• temp_buffers for temporary tables
• maintenance_work_mem for index builds and vacuum

A work_mem of 64 MB sounds modest. Multiply by 200 active backends running multi-stage queries, where each query node can use work_mem independently, and you can theoretically commit gigabytes of memory beyond what shared_buffers reserves. The point now is that “I’ll just bump work_mem” is one of those settings that looks innocent and explodes under concurrency.

-- A live snapshot of every backend the server is currently running.
-- One row per process. The `state` column tells you whether the backend
-- is doing work or holding open an idle connection.
SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;

Run that on any production database. You’ll see the shape of your real connection load: how many backends are active, how many are idle in transaction (the worst kind, because they hold snapshots and prevent vacuum), and how many are idle.

1.4.4 What this enables

The process model is not all cost. Process isolation is one of the reasons Postgres is famously hard to crash. A backend that hits a bug, runs out of memory, or trips an assertion dies on its own. The postmaster notices, terminates the affected children to clean shared state, and brings the system back. Other databases that share more state across worker threads can fail in louder, more correlated ways.

The model also makes background workers a clean concept. Autovacuum workers, the WAL writer, the checkpointer, and logical replication apply workers are all separate processes with well-defined lifetimes. You can see them in ps aux | grep postgres on any running server, and that maps cleanly to the pg_stat_activity view.

The process model is a deliberate trade. Postgres bought isolation and crash safety; it sold cheap connections. The book treats this as a fact about the world, not a defect to apologize for. We design around it.

1.5.1 The four extension surfaces

Extensibility in Postgres has a specific technical meaning. There are four places where an extension can plug into the database without forking it:

1. Types. CREATE TYPE lets you define a new column type with its own storage layout, input/output functions, and operators. PostGIS adds geometry. pgvector adds vector. JSON started life as a type before becoming a default.
2. Operators and functions. CREATE OPERATOR and CREATE FUNCTION extend SQL itself. The && overlap operator on ranges. The <-> distance operator on vectors. None of those are core SQL; they’re all defined operators that the planner treats as first-class.
3. Index access methods. This is the one that surprises people. Postgres lets extensions register entirely new index implementations through the index access method API. Bloom filters, GiST for spatial data, GIN for inverted indexes. The planner uses them through the same machinery as the built-in B-tree.
4. Procedural languages. PL/pgSQL is the default language for stored procedures, but PL/Python, PL/Perl, PL/V8 (JavaScript), and others slot in through the procedural language API. Functions in any of them are first-class to the planner.

Together those four surfaces explain why someone can build PostGIS or TimescaleDB or pgvector without changing one line of Postgres core. The core ships with hooks. The community fills them.

-- Loading an extension is one statement.
-- Most extensions are header-and-shared-object packages installed via the OS,
-- then enabled per-database with CREATE EXTENSION.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

pg_stat_statements records query latency by query template. pg_trgm adds trigram-based similarity matching. btree_gin lets you build GIN indexes on scalar types. Three lines. Three superpowers.

1.5.2 The trade-off nobody talks about

Extensibility is not free, and the book will not pretend otherwise.

The first cost is operational surface area. Every extension is a shared library loaded into the Postgres backend process. A buggy extension can crash the database. A poorly-written extension can leak memory or hold locks longer than expected. RDS and Cloud SQL deliberately restrict which extensions you can install, because the cloud provider doesn’t want a third-party shared object running in their fleet. If you’re targeting RDS, check the supported extensions list before you architect around one.

The second cost is upgrade friction. Most major Postgres upgrades require a corresponding update to every extension. If your stack depends on PostGIS, your upgrade path is gated by PostGIS releasing a compatible version. The cinetrack stack in this book uses only extensions that ship with the core distribution and a small set of well-maintained ones.

The third cost is mental load. The same problem can be solved three ways, and “what’s idiomatic” is harder to answer than in databases that ship with one solution. Full-text search is a great example: built-in tsvector plus GIN, the pg_trgm extension, or an external Elasticsearch. All three work. Two of them are in the book.

1.5.3 What you should install on every cinetrack database

These three earn their keep on almost any production Postgres:

• pg_stat_statements: aggregated query latency and call counts per query template. The single most useful extension.
• pg_trgm: trigram similarity for fuzzy text search and LIKE '%foo%' acceleration.
• btree_gin or btree_gist: lets you build composite indexes that mix scalar columns with arrays, ranges, or JSON. Helpful for surprising queries.

If you’re targeting a self-hosted server, also install pg_repack for online table reorgs. On managed services, check what’s available before you depend on it.

SELECT
  e.extname,
  e.extversion AS installed,
  a.default_version AS available
FROM pg_extension e
JOIN pg_available_extensions a ON a.name = e.extname;

1.5.4 Extensibility shapes how the book is structured

The book treats core Postgres and the most-installed extensions as one stack. JSONB and GIN are inseparable. Trigram search is covered. Monitoring without pg_stat_statements is monitoring with one eye closed. The book never asks you to install something obscure. It assumes you’ll install the boring, battle-tested set.

The flip side: the book does not cover every cool extension. pgvector is out of scope. PostGIS gets mentioned but isn’t a dependency. The line is drawn at “extensions every working Postgres engineer should know.”

1.6.1 What each part assumes

Part I: Foundations. Chapters 1 through 6. MVCC, storage, the WAL, processes and memory, indexes from first principles. This is the model the rest of the book runs on. Skip Part I and the chapters on vacuum, replication, and concurrency stop making sense. Read it in order.

Part II: Querying like an expert. Chapters 7 through 14. The planner and statistics, reading EXPLAIN, B-tree strategy, specialty indexes (GIN, GiST, BRIN, hash), joins and rewrites, JSONB at scale, full-text search, and schema design with partitioning. This part assumes Part I.

Part III: Concurrency and production patterns. Chapters 15 through 21. Isolation, locking, the patterns expert teams reach for instinctively (SKIP LOCKED, advisory locks, optimistic concurrency, idempotency), Postgres-as-a-queue and the outbox, vacuum and bloat, connection pooling, monitoring, and zero-downtime migrations. The book stops being about “how Postgres works” and starts being about “how do I keep this thing running.”

Part IV: Replication, HA, and operations. Chapters 22 through 28. Streaming replication, logical replication, Patroni for HA, pgBackRest for backups, tuning, capacity planning, and the runbook collection that closes the book. Part IV reuses MVCC vocabulary directly.

The book ends on a runbook chapter, because at that point you have a working operational vocabulary and a model that lets you reason about the failures you haven’t met yet.

1.6.2 How the chapters are structured

Every chapter is the same shape. Hero image, overview, substantive subchapters that each end on a hook into the next, a common-mistakes section, and a summary whose last bullet previews the next chapter. Code is interleaved with prose, never separate. Every code snippet is pure SQL. No Spring, no JDBC, no JPA, no Hibernate. If your application uses an ORM, the book is the database side of that conversation.

1.6.3 The cinetrack sandbox

The whole book runs against one example schema. Cinetrack is a movie-review and watchlist application: users rate movies, write reviews, build watchlists, and trigger view events. It’s the same domain used in the author’s other books, so the queries here can sit alongside the application-level chapters in the Spring Boot books.

Every chapter has a self-contained code project in the db-book-example repository under chapter-NN/. Each project includes a docker-compose.yml for Postgres (and, in later chapters, pgbouncer, replicas, Patroni, or pgBackRest), an init.sql, a seed.sql, and a queries.sql of annotated examples.

The chapter-1 sandbox is the smallest version: about a hundred movies, fifty users, five hundred ratings, fifty reviews. Subsequent chapters extend it. Chapter 10 needs a hundred million view_events rows for BRIN demos; Chapter 14 needs partitioned tables; Chapter 26 needs a WAL archive. Each chapter’s README.md says what’s new.

git clone https://github.com/umur/db-book-example
cd db-book-example/chapter-01
docker compose up -d
psql -h localhost -U cinetrack -d cinetrack -f init.sql
psql -h localhost -U cinetrack -d cinetrack -f seed.sql

Three commands, under a minute on a fresh Docker image. You’re now sitting at a psql prompt in front of a working cinetrack database, and every query in this chapter runs from here.

1.6.4 Your first MVCC peek

Run this:

-- Look at the system columns Postgres exposes on every row.
-- xmin: transaction that inserted this version.
-- xmax: transaction that deleted/updated this version (0 if alive).
-- ctid: physical location, formatted as (page, offset).
SELECT xmin, xmax, ctid, id, user_id, movie_id
FROM reviews
LIMIT 5;

You’ll see xmax = 0 on every row, because no review has been deleted or updated. Now update one:

UPDATE reviews SET body = body || ' (edited)' WHERE id = 1;

SELECT xmin, xmax, ctid, id
FROM reviews
WHERE id = 1;

The xmin is now your update transaction. The ctid changes too: a new tuple is always written at a new physical location. Postgres has an optimization called HOT (Heap-Only Tuple) that, on a small update with free space on the same page, skips writing new index entries; the index keeps pointing at the old line-pointer slot, which redirects to the new tuple. Either way, the row was not edited in place. The old version is still there, marked with the deleting xmax, and vacuum will reclaim it later.

1.6.5 How to use the code companion

A few habits make the companion repo earn its keep.

Rule one: actually run the queries. A book on Postgres that you read without running queries is a book on something else. Every snippet in the chapter prose is in queries.sql, copy-paste-runnable, with comments that match the chapter’s narration.

Rule two: read the EXPLAIN output. Most production Postgres mistakes are made by people who don’t read query plans. Run EXPLAIN on every query the book asks you to run, even if we haven’t formally introduced it yet. You’ll start absorbing the shape.

Rule three: break things. The cinetrack sandbox is yours. Drop indexes. Insert ten million rows. Hold a transaction open and watch vacuum stop working. The book will ask you to do all of these eventually. The faster you start treating the database as something to experiment on, the faster expert Postgres becomes a reflex.

1.7.1 Summary

• Postgres is not MySQL with more types or Oracle with worse tooling. Different lineage, different concurrency model, different physical layout. The skills you brought from another database transfer about half the time and bite you the other half.
• MVCC is the bet that defines everything. Every row can have multiple versions on disk. Readers and writers don’t block each other. The price is dead-tuple cleanup, the operational tax of vacuum, and a planner that has to think about visibility on every read.
• Process per connection is the other defining bet. Each connection is an OS process. Connections are expensive. Production Postgres uses pgbouncer.
• Extensibility is a culture, not a feature. Custom types, operators, index methods, and procedural languages plug in through documented APIs. “Postgres has X” is almost always true; the cost is operational surface area and upgrade gravity.
• The book is one path, not a reference. Read Part I in order. The cinetrack sandbox is yours to break. Run every query. Read every plan.