Chapter 7: Relational Databases for Data Engineering

"The most dangerous query is the one that works."

Overview

You will spend a great deal of your career reading from databases that other people own, that were designed for a purpose that is not yours, and that are serving live traffic while you read them.

That is a different relationship to a database than an application developer has, and it changes what matters. An application developer asks how to model their domain and how to make their writes fast. A data engineer asks how to get a large amount of data out, repeatedly, correctly, without anyone noticing — and what happens to their extract when the thing underneath it is busy.

This chapter is the database material a data engineer actually needs, which is a specific subset. It is not a course in database internals; it is the parts of database internals that explain why your extract is slow, why it caused a page, why your incremental logic lost rows, and why a column type someone chose in 2019 is still costing you.

Four things in it are worth flagging as the ones that repay the time.

MVCC and the long-running transaction. Every serious extract-caused outage traces to this, and it is not obvious from the outside. Your read-only query cannot block writes — and it can still take a database down.

Integer cents. The most consequential column-type decision in this book, stated once here and honored in every code sample. The build validator fails on a float money column.

Reading EXPLAIN. The highest return-on-time skill in the chapter, and one that transfers directly to Chapters 8, 18, and 21.

Logical versus physical replication. The distinction that decides whether CDC in Chapter 14 is possible at all.

In this chapter, you will learn to:

• Describe how a row-oriented engine reads, and predict which queries will be expensive before running them.
• Explain MVCC and what a long-running analytical query does to a transactional database.
• Choose correct types for money, timestamps, identifiers, and semi-structured data, and price each wrong choice.
• Read an EXPLAIN ANALYZE plan and find the three lines that matter.
• Design an extract that does not degrade the source: a replica, a bounded range, and a statement timeout.
• Distinguish physical from logical replication and know which one CDC requires.
• Compare PostgreSQL and MySQL on the properties that affect you rather than the ones in benchmarks.

Who needs this chapter: everyone. It is on the Quick Start path. If you already know PostgreSQL well, read §7.3, §7.7, and §7.8 and skim the rest.

7.1 You Are a Guest

Start with the relationship, because it determines everything else.

Kestrel's kestrel_app database exists to make checkout work. It is sized for transactional load, tuned for transactional load, monitored for transactional load, and owned by a team whose objectives say nothing about analytics. Your extract runs on capacity that is there for someone else.

Six consequences follow, and they are the practical content of being a guest:

You do not get to add indexes. An index you want for your extract costs the owning team write throughput on every insert and update, forever. Asking is reasonable; assuming is not.

You do not get to change the schema. Including adding the updated_at column that would make your life dramatically easier. That is a conversation, and Chapter 17 is about making it a structured one.

Your query competes for the same resources — buffer cache, I/O, CPU, connection slots. A query that scans a large table evicts the pages the application needs, and the effect outlasts your query.

You are subject to their maintenance. Failovers, upgrades, vacuum, and index rebuilds all happen on their schedule.

Your read-only query can still cause an incident. §7.3. This is the one that surprises people.

When something goes wrong, it will be attributed to you, correctly or not, because you are the unusual workload. Having a statement timeout and a documented resource footprint is how you defend against a bad attribution.

📐 Design Decision — Read the primary, read a replica, or read a snapshot?

Three options, and this is the first architectural choice in Part II.

The primary. Freshest data, no lag, and every query you run competes directly with checkout. Acceptable only for small, bounded, indexed lookups.

A read replica. The default and the right answer for almost all extraction. Costs you replication lag (Chapter 4 §4.3, and the six-week incident in its Case Study 2) and one more instance to pay for.

A restored snapshot or logical dump. Complete isolation — you cannot possibly affect production. Costs freshness measured in hours and a restore process to operate. Genuinely correct for enormous full loads and for compliance situations where touching production is restricted.

Kestrel reads a replica, with the watermark read from the primary and a lag guard, which is the combination Chapter 4 arrived at after losing six weeks of orders.

What that gives up: an extra instance's cost, and the standing obligation to monitor lag. Teams that skip the lag monitoring get the correctness of a replica with none of the safety, which is the worst of the three positions.

7.2 How a Row Store Reads

You do not need to implement a storage engine. You do need a model accurate enough to predict cost, and the model is short.

Pages and the heap

PostgreSQL stores table data in pages of 8 KB. A page holds as many whole rows as fit. The collection of pages for a table is the heap, and it is unordered — rows sit wherever there was room when they were written.

Everything follows from two facts: the page is the unit of I/O, and rows are stored whole.

Reading one column of one row reads the entire 8 KB page containing that row, which contains all columns of that row and of every other row on the page.

For a transactional workload this is exactly right. "Fetch order 88214" wants all of order 88214's columns, and one page read delivers them.

For an analytical workload it is exactly wrong. "Sum net_revenue_cents across six million rows" reads every page of the table — including all forty columns of every row — to use one column.

That single asymmetry is the entire reason columnar storage exists, and Chapter 8 §8.2 picks it up. Here it explains something more immediate: why your analytical query against an OLTP database is slow in a way that no index fixes.

   ROW STORE (PostgreSQL heap)          8 KB page
   ┌──────────────────────────────────────────────────────┐
   │ row 1: id | cust | date | qty | price | ... 40 cols  │
   │ row 2: id | cust | date | qty | price | ... 40 cols  │
   │ row 3: id | cust | date | qty | price | ... 40 cols  │
   └──────────────────────────────────────────────────────┘
   To SUM(price) you read every page, i.e. all 40 columns.

In words: a row-oriented page interleaves all columns of each row, so reading one column requires reading all of them.

B-tree indexes

An index is a separate structure mapping column values to row locations. PostgreSQL's default is a B-tree: a balanced tree, sorted, with the row pointers in the leaves.

Finding one value costs the depth of the tree — typically three or four page reads for a table of any size, because a B-tree's fan-out is high. That is why an index turns a scan of millions of rows into a handful of reads.

The costs, which matter to you as a guest:

Every write maintains every index. An insert into a table with six indexes does seven writes. This is why the owning team resists your index request, and their resistance is legitimate.

Indexes take space — often as much as the table.

An index only helps if the planner can use it. A function applied to the indexed column defeats it, exactly as a function on a partition column defeats pruning (Chapter 4 §4.2). WHERE lower(email) = 'x' cannot use an index on email; it needs an index on lower(email).

The planner may correctly decline to use it. Reading 40% of a table through an index is slower than scanning the table, because index access is random and scanning is sequential. When you see a sequential scan and expected an index scan, the planner is often right.

Access paths

Three, and you will see all three in plans:

Sequential scan. Read every page. Fast per page, and the right choice when you need most of the table.

Index scan. Walk the index, then fetch each matching row from the heap. Fast for few rows; degrades badly as the match count rises, because each heap fetch is a random read.

Bitmap heap scan. A hybrid: collect matching row locations from the index, sort them by page, then read pages in order. This is what the planner chooses in the middle ground, and seeing it tells you the selectivity is moderate.

🧪 Try It — Make the planner change its mind

On the Kestrel database (after Chapter 7's seed), run the same query shape at three selectivities and watch the access path change:

sql EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 8841; EXPLAIN ANALYZE SELECT * FROM orders WHERE placed_at >= now() - interval '1 day'; EXPLAIN ANALYZE SELECT * FROM orders WHERE placed_at >= now() - interval '2 years';

You should see an index scan, then a bitmap heap scan, then a sequential scan. Nothing changed except how many rows match.

Then the instructive part: run SET enable_seqscan = off; and re-run the third query. Compare the actual times. The planner was right, and seeing by how much is what stops you from assuming an index scan is always better. (Set it back to on afterwards — this is a session-level debugging tool, never a production setting.)

7.3 MVCC and the Long-Running Transaction

This section is the one to read carefully. It explains a class of incident that is genuinely counter-intuitive and that data engineers cause more often than anyone else.

How MVCC works

PostgreSQL uses multi-version concurrency control. An UPDATE does not overwrite a row; it writes a new version and marks the old one as no longer current from a given transaction onward. Each transaction sees a snapshot: the set of row versions that were committed when it started.

The consequence everyone knows: readers do not block writers and writers do not block readers. Your analytical query cannot lock anyone out.

The consequence almost nobody knows follows immediately: old row versions cannot be cleaned up while any transaction might still need to see them.

The failure

 09:00  your extract begins.
        BEGIN; SELECT * FROM orders WHERE ...    -- expected: 20 minutes
        A snapshot is taken. Every row version live at 09:00 must be preserved
        until this transaction ends.
   │
 09:20  the query is still running -- the table was bigger than you thought
   │
 10:00  checkout has updated 4.1 million order rows in the last hour.
        Every superseded version is still on disk. VACUUM cannot remove them:
        your transaction might still need them.
   │
 11:30  the orders table has grown from 40 GB to 61 GB of mostly-dead rows.
        Sequential scans now read 50% more pages. Checkout latency rises.
        The application team is paging.
   │
 11:47  someone finds your query in pg_stat_activity and cancels it.

Your read-only query, which locked nothing, degraded the production database. It did so by preventing garbage collection, and the damage is proportional to how long you held the snapshot multiplied by the write rate underneath you.

This is bloat, and it is the mechanism behind most "the data team took down the database" incidents. It is worth being precise about the blame: nobody did anything obviously wrong. The query was read-only. The extract was scheduled off-peak. The table was simply bigger than the estimate, and the query ran into a busy period.

⚠️ Failure Mode — The extract that ran for four hours

Symptoms, in the order they appear:

1. pg_stat_activity shows a query with a xact_start hours in the past.
2. n_dead_tup in pg_stat_user_tables climbs steadily and does not fall.
3. Table and index sizes grow with no corresponding row-count growth.
4. Autovacuum log lines saying it found dead rows but could not remove them.
5. Application query latency rises gradually, then sharply.

The five defenses, and you want all five:

1. SET statement_timeout on every analytical connection. Kestrel uses 30 minutes for extracts. A query that exceeds it fails loudly, which is the outcome you want (Chapter 2, Case Study 1).
2. SET idle_in_transaction_session_timeout. A connection that opened a transaction and then went idle — a crashed client, a debugger paused at a breakpoint — holds a snapshot forever and is worse than a long query, because nothing is even running.
3. Read a replica, where bloat affects only the replica. Note that on PostgreSQL this needs hot_standby_feedback considered carefully: with it on, the replica's long queries do prevent cleanup on the primary; with it off, the replica may cancel your query. Both settings have a cost and you must know which one you chose.
4. Chunk large extracts. Ten short transactions instead of one long one. Each releases its snapshot.
5. Monitor the oldest transaction age and alert on it. One query: SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state <> 'idle';

Defense 3 is the one with a genuine trap in it. Read the hot_standby_feedback documentation before assuming a replica isolates you.

Isolation levels, briefly

READ COMMITTED is PostgreSQL's default: each statement sees a fresh snapshot. Two identical queries in one transaction can return different results.

REPEATABLE READ takes one snapshot for the whole transaction. For a multi-query extract that must be internally consistent — orders and order_items that agree — this is what you want, and it is what makes bloat worse, because the snapshot is held longer. That trade is unavoidable and should be made deliberately.

7.4 Types That Matter

Four type decisions produce most of the data quality problems a data engineer inherits.

Money is integer cents

Never store currency in a floating-point type. This is the most consequential column-type rule in this book and it is not a style preference.

>>> 0.1 + 0.2
0.30000000000000004
>>> 1.10 * 3
3.3000000000000003

Floating point represents values in binary; 0.1 has no exact binary representation, exactly as 1/3 has no exact decimal one. Errors are individually tiny and they accumulate across aggregation, and they accumulate asymmetrically, so they do not cancel.

At Kestrel's 6,480,000 order lines a year, summing a FLOAT revenue column produces a total that disagrees with the source. Not by much — and the acceptance criterion for the whole platform is reconciliation to the cent (Chapter 1 §1.7), which a float cannot meet by construction.

Two correct options:

Kestrel uses BIGINT cents everywhere, every column named *_cents. scripts/validate.py fails this book's build if a money column is typed as a float in any code sample.

The cost of integer cents, stated honestly: every display needs a division by 100, every calculation needs care about rounding, and multi-currency requires a separate currency column and a conversion table. Those are real and they are much smaller than the alternative.

Timestamps: always with time zone, always store UTC

TIMESTAMP WITHOUT TIME ZONE stores a wall-clock reading with no indication of where the clock was. It is the wrong choice for almost everything and it is the default in more schemas than it should be.

TIMESTAMP WITH TIME ZONE (timestamptz) stores an absolute point in time. Despite the name, it does not store a time zone — it normalizes to UTC on write and converts to the session's zone on read.

Store timestamptz, store UTC, and convert at the presentation boundary only.

The failure this prevents is worth naming because it is seasonal and therefore hard to catch: a daily aggregation using local wall-clock boundaries gets a 23-hour day and a 25-hour day at daylight saving transitions. Two days a year the numbers are wrong, and the discrepancy is small enough to be dismissed as noise.

Kestrel's 6am SLA is stated in America/New_York, and the conversion is written out every time it appears rather than assumed — 06:00 America/New_York is 10:00 UTC in winter and 11:00 UTC in summer.

Identifiers

The v4 problem is worth understanding because it is a real performance cliff: because v4 values are random, each insert goes to a random point in the B-tree, so the working set is the whole index rather than its right edge. On a large table this turns a cached write into a disk read.

Semi-structured data

PostgreSQL's JSONB is genuinely good, and it is genuinely a trap.

When it is right: attributes that are sparse, per-tenant, or genuinely schemaless, and event payloads whose shape you do not control.

When it is wrong: as a way to avoid deciding on a schema. A JSONB column accumulating fields nobody documented is where data quality goes to die — nothing validates it, nothing lists what keys exist, and every consumer discovers the shape by sampling.

What it costs a data engineer: extracting a field is payload->>'field', which needs an expression index to be fast; there is no schema to read; and every downstream consumer must handle missing keys.

The practical rule: if you find yourself extracting the same JSON key in more than about three places, it should be a column. Chapter 17 makes this a contract question.

7.5 Indexes From the Reader's Side

You mostly cannot create indexes. You can understand them well enough to write queries that use the ones that exist, and to ask for a new one with a case rather than a request.

Composite index column order matters, and the rule is left-to-right. An index on (customer_id, placed_at) serves WHERE customer_id = ?, and WHERE customer_id = ? AND placed_at > ?, and does not serve WHERE placed_at > ? alone.

A covering index avoids the heap entirely. If every column a query needs is in the index, PostgreSQL can answer from the index alone — an index-only scan. INCLUDE adds payload columns without making them part of the key.

Partial indexes cover a subset: CREATE INDEX ... WHERE status = 'paid'. Small, cheap, and ideal when your extract always filters the same way.

BRIN indexes are the one worth asking for by name. A BRIN index stores the min and max of each block range rather than an entry per row, which makes it minute compared to a B-tree — and it works only when the column correlates with physical row order, which is exactly true of an append-only created_at. For a large append-only table extracted by time range, a BRIN index on the timestamp is a small ask with a large payoff, and it is the request most likely to be granted because it costs the write path almost nothing.

🎓 Interview Angle — "How would you extract 500 million rows from a production database?"

The answer they want has four moves, and candidates typically give one.

"First I'd ask what the freshness requirement is, because if daily is fine I'd rather read a replica or a restored snapshot than the primary at all.

Then I'd chunk it — bounded ranges on an indexed column, ideally the primary key or a time column, committing between chunks so I'm not holding one snapshot for hours. On PostgreSQL a long-running transaction prevents vacuum from cleaning up dead tuples, so a four-hour read-only query can bloat the table and degrade the application even though it locks nothing.

I'd set a statement timeout and an idle-in-transaction timeout so a stuck extract fails loudly rather than silently holding a snapshot.

And I'd ask whether there's an index or a BRIN index on the column I'm ranging over — BRIN is cheap to add on an append-only timestamp and it's the easiest ask to get approved because it barely touches the write path."

The MVCC point is what distinguishes the answer. Most candidates know to chunk; far fewer can explain why a read-only query is dangerous.

7.6 Reading a Query Plan

The highest return-on-time skill in this chapter. Three lines matter more than everything else.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price_cents) AS revenue_cents
  FROM orders o
  JOIN order_items oi USING (order_id)
 WHERE o.placed_at >= DATE '2025-11-01'
   AND o.placed_at <  DATE '2025-12-01'
 GROUP BY o.customer_id;

HashAggregate  (cost=... rows=214883) (actual time=8422.1..8511.7 rows=218441 loops=1)
  Group Key: o.customer_id
  Buffers: shared hit=41203 read=1882914                      ← (3)
  ->  Hash Join  (cost=...) (actual time=1204.3..7115.2 rows=781380 loops=1)
        Hash Cond: (oi.order_id = o.order_id)
        ->  Seq Scan on order_items oi                        ← (1)
              (cost=... rows=6480000) (actual time=0.02..2841.7 rows=6480000 loops=1)
        ->  Hash  (actual time=1198.4..1198.4 rows=289400 loops=1)
              ->  Index Scan using idx_orders_placed_at on orders o
                    (cost=... rows=291002) (actual time=0.08..902.1 rows=289400 loops=1)
                    Index Cond: ((placed_at >= '2025-11-01') AND (placed_at < '2025-12-01'))
Planning Time: 0.42 ms
Execution Time: 8544.9 ms

(1) Where the time goes. Read actual time bottom-up. The Seq Scan on order_items took 2.8 seconds and produced 6.48 million rows — the whole table — because there is no index on order_items.order_id, or the planner judged the join not selective enough to use one. That is the first thing to attack.

(2) Estimated versus actual rows. rows=291002 estimated against rows=289400 actual on the orders scan: excellent, within 0.6%. When these diverge by an order of magnitude, the planner is working from bad statistics and every decision above that node is suspect. Fix with ANALYZE tablename; before you fix anything else — a plan built on stale statistics is not evidence about your query, it is evidence about your statistics.

(3) Buffers. shared hit=41203 read=1882914 — 41 thousand pages from cache, 1.88 million from disk. This is the line that tells you the real cost, and it is the one people skip because it only appears with BUFFERS. At 8 KB per page, that is roughly 15 GB read from disk. Always run EXPLAIN (ANALYZE, BUFFERS), never bare EXPLAIN ANALYZE.

🔎 Read the Plan — The four-line checklist

For any plan, in this order:

1. Execution Time at the bottom. Is it what you expected?
2. Largest actual time node, reading bottom-up. That is where the time is. Everything else is a detail.
3. rows= estimated vs. actual on that node. Off by 10× or more means run ANALYZE and start over — you are debugging statistics, not a query.
4. Buffers: read=. Pages fetched from disk. Multiply by 8 KB for the I/O.

Two warnings.

EXPLAIN without ANALYZE does not run the query and shows only estimates. Useful when you dare not execute; useless for finding where the time went.

EXPLAIN ANALYZE on a DELETE or UPDATE executes it. Wrap it in a transaction you roll back. People learn this the memorable way.

7.7 Extracting Without Causing an Outage

Everything above, assembled into a pattern.

"""Bounded, chunked, timeout-guarded extract from a production replica.

The four properties that make this safe, and none is optional:
  * a REPLICA, not the primary
  * a BOUNDED range with an upper bound from the PRIMARY  (Chapter 4 CS 2)
  * CHUNKED into short transactions, so no snapshot is held for long
  * a STATEMENT TIMEOUT, so a stuck query fails loudly
"""
from __future__ import annotations

import os
from datetime import datetime, timedelta, timezone

import psycopg

CHUNK = timedelta(hours=1)
OVERLAP = timedelta(minutes=5)     # deliberate re-read; the write is idempotent
STATEMENT_TIMEOUT_MS = 30 * 60 * 1000


def safe_upper_bound(primary_dsn: str) -> datetime:
    """The end of the range, read from the PRIMARY.

    Never from the replica: the replica's clock and the replica's applied
    position are different things, and recording a position from a lagging
    replica silently skips whatever arrives in the lag window. That cost six
    weeks of orders in Chapter 4's second case study.
    """
    with psycopg.connect(primary_dsn, connect_timeout=5) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT now() - interval '30 seconds'")
            return cur.fetchone()[0]


def extract(table: str, since: datetime, replica_dsn: str, primary_dsn: str):
    upper = safe_upper_bound(primary_dsn)
    lower = since - OVERLAP
    total = 0

    while lower < upper:
        chunk_end = min(lower + CHUNK, upper)
        # A NEW connection per chunk. The snapshot is released when it closes,
        # which is the whole point of chunking -- one four-hour transaction
        # prevents vacuum from cleaning up for four hours.
        with psycopg.connect(replica_dsn, connect_timeout=5) as conn:
            with conn.cursor() as cur:
                cur.execute("SET statement_timeout = %s", (STATEMENT_TIMEOUT_MS,))
                cur.execute("SET idle_in_transaction_session_timeout = 60000")
                cur.execute(
                    # Half-open interval [lower, chunk_end). Closed on both
                    # sides double-counts a row landing exactly on a boundary;
                    # open on both sides drops it.
                    f"SELECT * FROM {table} "
                    " WHERE updated_at >= %s AND updated_at < %s "
                    " ORDER BY updated_at",
                    (lower, chunk_end),
                )
                rows = cur.fetchall()
        land_to_bronze(table, rows, chunk_end)      # idempotent upsert
        total += len(rows)
        lower = chunk_end

    # The watermark is the range's upper bound, NOT max(updated_at) of what was
    # read. An empty chunk must still advance it, and it must advance to a
    # value the primary vouched for.
    write_watermark(table, upper)
    return total

Six things in that code are load-bearing, and each maps to an earlier chapter:

A new connection per chunk, so each snapshot is short (§7.3). A statement timeout and an idle-in-transaction timeout, so a stuck extract fails loudly (§7.3). An upper bound from the primary, so replication lag cannot corrupt the watermark (Chapter 4 §4.3). A half-open interval, so boundary rows are neither dropped nor duplicated (Chapter 4 §4.6). A deliberate five-minute overlap, trading duplicates for guaranteed coverage, paid for by an idempotent write (Chapter 4 §4.5). The watermark set to the range's upper bound rather than to max() of what was read, so an empty chunk still advances and the else now() bug from Chapter 4's Case Study 2 cannot exist.

💸 Cost Check — What a bad extract costs, in the source system's currency

A four-hour unchunked extract on Kestrel's orders table during a period when checkout is updating roughly 4 million rows an hour.

Dead tuples accumulated and unreclaimable: ~16 million rows. At an average row width of about 180 bytes, that is roughly 2.9 GB of dead space in one table plus its index bloat.

The costs are not on any invoice:

• Sequential scans read the dead pages too. A table 40% bloated is 40% slower to scan for every query, including checkout's.
• The bloat does not disappear when your query ends. Autovacuum reclaims the space for reuse but does not return it to the operating system; the table stays large until someone runs VACUUM FULL, which takes an exclusive lock and therefore an outage window.
• Buffer cache pollution. Your scan evicted the pages checkout needs, and the cache refills at disk speed.

The chunked version accumulates dead tuples for one hour instead of four, and each chunk releases its snapshot so autovacuum can work between them. Same rows extracted, roughly a quarter of the damage — and the damage is bounded rather than proportional to how badly you estimated the runtime.

7.8 Replication, and Which Kind CDC Needs

Two mechanisms, and the distinction decides whether Chapter 14 is possible.

Physical (streaming) replication ships the write-ahead log byte for byte. The replica is an exact block-level copy of the primary — same tables, same indexes, same physical layout — and is read-only. Simple, efficient, and all-or-nothing: you replicate the whole cluster or nothing.

Logical replication decodes the write-ahead log into row-level change events — insert, update, delete, with the values — and publishes them. You can replicate selected tables, to a different PostgreSQL major version, or to something that is not PostgreSQL at all.

Change data capture needs logical replication, or specifically the logical decoding facility underneath it. That is why Chapter 5's docker-compose.yml sets wal_level=logical nine chapters before CDC appears: the setting requires a restart, and restarting a database that has data in it is something you want to have practiced first.

⚠️ Failure Mode — The replication slot that filled the disk

Logical replication uses a replication slot to track how far a consumer has read. The slot guarantees the primary retains WAL until the consumer confirms it — which is exactly what makes CDC reliable, and exactly what makes it dangerous.

If the consumer stops and the slot remains, WAL accumulates without bound. Debezium crashes on a Friday evening. The slot stays. The primary retains every WAL segment since. By Sunday the disk is full, and a PostgreSQL primary with a full WAL disk stops accepting writes — which means checkout stops.

A data pipeline component taking down the storefront is the worst outcome in this book, and it is entirely preventable:

1. Monitor slot lag, and alert well before the disk is at risk: sql SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained FROM pg_replication_slots;
2. Set max_slot_wal_keep_size (PostgreSQL 13+). The slot is invalidated rather than allowed to fill the disk. You lose the CDC stream and must re-snapshot — bad, and much better than an outage.
3. Alert on inactive slots. active = false on a slot that should be consumed is a five-minute problem that becomes a weekend outage.
4. Drop slots you are not using. An abandoned slot from an experiment is a landmine.

Chapter 14 §14.6 covers the operational side properly. The setting to put in place now is max_slot_wal_keep_size.

7.9 PostgreSQL, MySQL, and the Rest

You will extract from whatever exists. The differences that affect you:

The MySQL clustering difference matters for extraction. Because InnoDB stores the table in primary key order, a range scan on the PK is sequential and fast, and a range scan on a secondary index requires a lookup back into the clustered index for every row. Range your extract on the primary key where you can.

The utf8 trap is real and still catches people. MySQL's utf8 charset is three bytes and cannot represent emoji or many CJK characters; utf8mb4 is actual UTF-8. A table declared utf8 will silently truncate or reject on an emoji in a product review, and you will meet it as mysteriously missing rows.

Others you may meet: SQL Server (CDC built in and good, DATETIME2 not DATETIME), Oracle (LogMiner or GoldenGate for CDC, and licensing that shapes architecture), SQLite (no concurrent writers; excellent for tests and fixtures).

Connections, pools, and the limit you will hit first

A PostgreSQL connection is a process, not a thread and not a lightweight handle. It costs several megabytes before it does anything, and max_connections is a hard ceiling on how many can exist.

This matters to an extractor for a reason that is not obvious: you are competing for a small, fixed resource that the application needs in order to serve customers.

max_connections = 200      the server's hard limit
  the application's pool        160
  replication + monitoring        8
  the DBA's headroom             20
  ──────────────────────────────────
  available to you               12   -- and nobody told you the number

Three consequences, and the third is the one that causes incidents.

Parallelism has a much lower ceiling than you expect. An extractor opening sixteen connections to read sixteen chunks concurrently has consumed more than the whole budget — and the failure may not be yours. The application's next connection is the one that fails.

A leaked connection is worse than a slow query. A query holds a connection for its duration; a leak holds one indefinitely, and the pool drains one leak at a time. The symptom is an application outage attributed to whoever deployed most recently, which is you.

And a connection pooler changes the arithmetic rather than removing the limit. PgBouncer in transaction mode multiplexes many clients onto few server connections, which works well for short transactions and not at all for §7.7's long chunked reads — those hold a server connection for their duration, which is the thing the pooler exists to avoid.

What to do:

pool = ConnectionPool(dsn, min_size=1, max_size=4)      # explicit, small, agreed

cur.execute("SET statement_timeout = '600s'")
cur.execute("SET idle_in_transaction_session_timeout = '60s'")

with pool.connection() as conn:                          # never leak past an exception
    ...

idle_in_transaction_session_timeout is the one people omit, and it converts a crashed extractor from a bloat incident (§7.3) into a logged error. A session that opened a transaction and then died holds its snapshot until the connection closes, and TCP will not notice for two hours.

And ask for the number. "How many connections may we use?" is a one-line question with a one-line answer, and asking it is §7.1 in practice: a guest asks about the room they are using.

🏭 From the Pipeline — the query that was blamed, and the query that was guilty

A nightly extract had run without incident for eleven months. On a Tuesday the application's p99 latency tripled between 02:10 and 02:40. The extract ran at 02:00. It was disabled the next morning, by someone else, without discussion.

It was not the extract, and establishing that took four days, because the extract had no instrumentation and could not say what it had done.

What actually happened: a deploy at 01:50 shipped a query with a missing index — a new feature, correctly built, tested against a development database with 4,000 rows. Against 340 GB it did a sequential scan per request. The scan evicted the buffer cache, which is what made everything else slow. The extract's reads were a small fraction of the I/O.

Two things made the misattribution stick.

The extract was the unusual workload — §7.1's sixth consequence, exactly. Everyone else's queries are the ones that always run.

And it could not defend itself. It logged that it started and that it finished. Nothing about rows read, chunk durations, connections used, or its footprint in pg_stat_statements. There was nothing to point at.

What changed was not the extract's behaviour. It was its record-keeping:

text per run, into a table the data team owns: chunk count · rows read · bytes read wall clock per chunk, and the maximum peak connections used n_dead_tup on the read tables, before and after pg_stat_statements total_exec_time attributable to our queries

The last line ended the argument permanently. It is a number, on the source's own terms, saying what fraction of the database's time was spent on you — 0.4%, at Kestrel.

The transferable point is not "keep logs." It is that a guest workload needs a documented resource footprint, for the same reason a contractor keeps an invoice: not because anyone currently disputes it, but because the dispute will happen and the record cannot be created afterwards.

📏 Scale Note — what changes as the source grows

The extract that works at 10 GB and the extract that works at 2 TB are different programs, and the transitions are sharper than the growth curve suggests.

```text source size full load chunked? CDC? the binding constraint ──────────────────────────────────────────────────────────────────────── < 10 GB minutes no no nothing. Do full loads. 10-100 GB ~an hour YES maybe the snapshot duration (§7.3) 100-500 GB hours yes YES bloat, and the maintenance window you do not have

500 GB overnight n/a YES there is no window ```

Kestrel's kestrel_app is 340 GB, which puts it firmly in the third row and explains the whole shape of Part III: full loads for the seven small tables, CDC for the mutable five (§14.2's callout).

The transition people miss is the first one, at around 10 GB, and it is not about duration. It is the point at which your read starts holding a snapshot long enough to matter to somebody else — and the symptom is not a slow extract, it is an application incident attributed to you.

Three numbers to know about your own source, none of which is its size:

```sql -- 1. how long can a transaction run before it hurts? SELECT max(now() - xact_start) FROM pg_stat_activity WHERE xact_start IS NOT NULL;

-- 2. how fast does the table churn? SELECT relname, n_tup_upd + n_tup_del AS churn, n_dead_tup FROM pg_stat_user_tables ORDER BY churn DESC LIMIT 10;

-- 3. what is the autovacuum's actual cadence on the tables you read? SELECT relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables; ```

The third is the one nobody looks at, and it is the one that says whether your snapshot is competing with maintenance or merely with queries.

🔐 Privacy & Governance — the extract is a copy, and the copy has fewer controls

The source database has a decade of access control on it. Row-level security, column grants, an audit log, a DBA who reviews requests. Your extract reads through all of it with one service account and writes the result somewhere with none of it.

text in kestrel_app after the extract ──────────────────────────────────────────────────────────────── column grants on customers.email a Parquet file in bronze row-level security by region one file, all regions an audit log of who read what an object anyone with the bucket prefix can read a DBA who reviews access a bucket policy nobody reviews

This is not an argument against extracting. It is the reason the first thing to decide about an extract is what it may read, and the answer is almost never "everything."

Three questions at extract-design time, all cheap now and expensive later:

Which columns does the pipeline actually need? §13.7 says bronze should keep SELECT *, and §13.8 allows a projection for a column you can recover. A column you are forbidden to store is the other exception (Chapter 9's ADR-004) — and deciding it here means the raw copy never exists.

Does the service account have more than it needs? An extract running as a superuser because that is what worked on the first day is the most common finding in a first access review (Chapter 30). Read-only, on named tables, is a five-minute change on day one.

And does anything downstream inherit the source's restrictions? Usually not, and that is the honest answer: the restrictions have to be re-implemented in the warehouse (Chapter 8's 🔐), and nothing carries them across automatically. Writing that sentence into the extract's README is worth more than it sounds, because the next person will assume otherwise.

7.10 Seeding Kestrel

The Kestrel source database, from Chapter 5's compose stack, with generated data.

kestrel_app  (PostgreSQL 16)
  customers ──┬── addresses
              └── orders ──┬── order_items ── products ── categories
                           ├── payments
                           ├── shipments ── warehouses
                           └── returns
                  promotions          inventory

The seed generator produces realistic data, and "realistic" is doing specific work here — it is what makes the rest of the book's exercises meaningful rather than decorative:

• Order volume follows a seasonal curve with the Black Friday spike, so the 6.28× peak ratio is present in the data and capacity exercises have something to measure.
• Order status transitions over time, so mutable-source extraction (Chapter 13) has something to catch.
• Some rows are hard-deleted, so the deletes problem is real and CDC has a reason to exist.
• Roughly 1 in 15 lines is returned, so returns modeling is not hypothetical.
• One wholesale customer places 8% of order lines, so Chapter 4's skew detector fires on real data.
• A small number of rows are deliberately dirty — a null where one should not be, a duplicated natural key, a timestamp in the future — so Chapter 23's data quality tests catch something.

🧱 Kestrel Platform — Increment 7: the source database

bash python platform/seed/seed_kestrel.py --scale small # ~100k orders, under a minute python platform/seed/seed_kestrel.py --scale full # a full year; take a break

--scale small is what every exercise in this book assumes. Use --scale full exactly twice, in Chapter 21 and Chapter 33, and the book tells you when.

Then verify, and verify against the frozen figures rather than against a feeling:

```sql -- structure SELECT table_name, (SELECT COUNT(*) FROM information_schema.columns c WHERE c.table_name = t.table_name) AS cols FROM information_schema.tables t WHERE table_schema = 'public' ORDER BY 1; -- expect 12 tables

-- the seasonal curve is present SELECT date_trunc('month', placed_at) AS m, COUNT(*) FROM orders GROUP BY 1 ORDER BY 1;

-- the skew is present (Chapter 4 Case Study 1) SELECT customer_id, COUNT(*) AS lines FROM order_items oi JOIN orders o USING (order_id) GROUP BY 1 ORDER BY 2 DESC LIMIT 5; -- expect one customer far above the rest

-- money is integer cents everywhere SELECT table_name, column_name, data_type FROM information_schema.columns WHERE column_name LIKE '%_cents' AND data_type NOT IN ('bigint', 'integer'); -- expect zero rows ```

That last query is the one to keep. Run it against any database you inherit — a _cents column typed as a float, or a money column with no unit in its name, is a finding.

7.11 Summary

You are a guest in someone else's database, and six things follow: no adding indexes, no changing schemas, competing for the same buffer cache and I/O, subject to their maintenance windows, capable of causing an incident with a read-only query, and likely to be blamed when anything goes wrong because you are the unusual workload.

A row store reads whole pages. Rows are stored complete in 8 KB pages, so reading one column of six million rows reads all forty columns of all six million. That asymmetry is why analytical queries against an OLTP database are slow in a way no index fixes, and it is the entire reason columnar storage exists.

MVCC is the mechanism behind most "the data team took down the database" incidents. Your read-only query blocks nothing and still prevents vacuum from reclaiming superseded row versions for as long as it holds a snapshot. A four-hour extract against a table taking 4 million updates an hour leaves roughly 16 million unreclaimable dead rows and a table 40% bloated — which makes every query slower, does not shrink when your query ends, and needs an exclusive lock to fix. Five defenses: statement timeout, idle-in-transaction timeout, read a replica (knowing what hot_standby_feedback does), chunk into short transactions, and alert on oldest transaction age.

Money is integer cents. Floating-point currency cannot meet a reconcile-to-the-cent acceptance criterion, by construction. BIGINT cents or NUMERIC(19,4); never a float. Timestamps are timestamptz, stored UTC, converted only at presentation — the failure it prevents is the 23-hour and 25-hour days at daylight saving transitions, which are wrong twice a year and small enough to dismiss as noise. Identifiers: prefer BIGINT or UUID v7; UUID v4's randomness destroys index locality on insert. JSONB is excellent for genuinely schemaless data and a trap when used to avoid deciding — if you extract the same key in more than about three places, it should be a column.

Ask for a BRIN index by name. On a large append-only table extracted by time range, it is tiny, it barely touches the write path, and it is therefore the index request most likely to be approved.

Read plans with EXPLAIN (ANALYZE, BUFFERS) and four checks in order: execution time, the largest actual time node reading bottom-up, estimated-versus-actual rows on that node (off by 10× means you are debugging statistics, not a query), and Buffers: read= for the real I/O. Bare EXPLAIN shows estimates only, and EXPLAIN ANALYZE on a DELETE executes it.

A safe extract has six properties: a replica rather than the primary, a bounded range with the upper bound read from the primary, chunking into short transactions with a fresh connection each, a statement timeout, a half-open interval, and a watermark set to the range's upper bound rather than to max() of what was read.

CDC needs logical replication, which is why wal_level=logical was set in Chapter 5. And a logical replication slot with a stopped consumer retains WAL without bound until the disk fills, at which point the primary stops accepting writes and checkout stops — set max_slot_wal_keep_size now, monitor slot lag, and alert on inactive slots.

PostgreSQL and MySQL differ in ways that affect extraction: MySQL clusters the table on the primary key, so range your extract on the PK; PostgreSQL bloats under long transactions where MySQL grows its undo log; and MySQL's utf8 is not UTF-8 — you want utf8mb4, and the symptom of getting it wrong is mysteriously missing rows.

What's next

Chapter 8 is the data warehouse: Snowflake, BigQuery, Redshift, and the columnar storage model that makes the asymmetry in §7.2 disappear. It is also where the separation of storage from compute — the economic fact that reorganized this entire field — gets explained properly, along with an honest account of what DuckDB does not show you about it.