Chapter 7 — Key Takeaways (Relational Databases for Data Engineering)

The page for reading from someone else's production database without becoming an incident.

You are a guest

No adding indexes · no changing schemas · competing for the same cache and I/O · subject to their maintenance · a read-only query can still cause an outage · you will be blamed because you are the unusual workload.

How a row store reads

8 KB page = the unit of I/O. Rows stored whole. Reading one column of 6M rows reads all 40 columns of all 6M rows. That asymmetry is the entire reason columnar storage exists.

MVCC — the counter-intuitive outage

Readers don't block writers. And old row versions cannot be reclaimed while any transaction might still need them.

09:00  BEGIN; SELECT ...            snapshot taken
11:30  4.1M rows updated since. Every superseded version still on disk.
       VACUUM can remove none of them. Table 40 GB → 61 GB.
       Sequential scans read 50% more pages. Checkout latency rises.

⚠️ The hot_standby_feedback trap

SHOW hot_standby_feedback; — ten seconds, and most people have never run it on a replica they extract from daily. If on, your statement timeout is protecting the primary.

The five defenses — use all five

1. statement_timeout in the connection string, not in application code
2. idle_in_transaction_session_timeout — an idle open transaction is worse than a running query
3. Read a replica (knowing which hot_standby_feedback you have)
4. Chunk — a new connection per chunk, so each snapshot is short
5. Alert on oldest transaction age and on n_dead_tup / n_live_tup

Off-peak scheduling protects against contention, not duration. A 02:00 job running six hours runs at peak.

Types

Money is integer cents

>>> 0.1 + 0.2
0.30000000000000004

BIGINT cents (this book) or NUMERIC(19,4) (when you need fractional cents). Never a float — a reconcile-to-the-cent criterion is unmeetable by construction.

⚠️ The float enters through the percentage, not the money column. discount_percent DOUBLE PRECISION × unit_price_cents → float. An exact system is only exact if every operand is exact. Store rates as basis points; make the rounding rule explicit and single.

The rest

Indexes from the reader's side

• Composite indexes work left to right. (customer_id, placed_at) serves customer_id = ? and the pair; it does not serve placed_at > ? alone.
• A function on the column defeats the index — same failure as a function on a partition column.
• Ask for a BRIN index by name on a large append-only timestamp: tiny, barely touches the write path, and therefore the request most likely to be approved.

Reading a plan — EXPLAIN (ANALYZE, BUFFERS)

Four checks, in order:

1. Execution Time at the bottom — is it what you expected?
2. Largest actual time node, reading bottom-up — that's where the time is
3. rows= estimated vs. actual on that node — off by 10× means run ANALYZE; you are debugging statistics, not a query
4. Buffers: read= — pages from disk × 8 KB = the real I/O

⚠️ Bare EXPLAIN shows estimates only. EXPLAIN ANALYZE on a DELETE executes it — wrap it in a rolled-back transaction.

The safe extract — six load-bearing properties

upper = primary.execute("SELECT now() - interval '30 seconds'")   # from the PRIMARY
lower = watermark - timedelta(minutes=5)                          # deliberate overlap
while lower < upper:
    end = min(lower + CHUNK, upper)
    with psycopg.connect(REPLICA_DSN) as conn:        # new connection per chunk
        cur.execute("SET statement_timeout = 1800000")
        cur.execute("SET idle_in_transaction_session_timeout = 60000")
        cur.execute("... WHERE updated_at >= %s AND updated_at < %s", (lower, end))
    land_idempotently(rows)
    lower = end
write_watermark(upper)          # the RANGE's bound, not max() of what was read

1. Replica, not primary · 2. Upper bound from the primary · 3. Chunked, fresh connection each ·
2. Statement + idle timeouts · 5. Half-open interval [lo, hi) · 6. Watermark = the range bound, so an empty chunk still advances

Replication

⚠️ A logical replication slot with a stopped consumer retains WAL without bound. Disk fills → the primary stops accepting writes → checkout stops. Set max_slot_wal_keep_size · monitor slot lag · alert on active = false · drop unused slots.

PostgreSQL vs. MySQL — what affects you

The query to run against any database you inherit

SELECT table_name, column_name, data_type
  FROM information_schema.columns
 WHERE column_name LIKE '%_cents' AND data_type NOT IN ('bigint','integer');
-- and then: money-ish names with no unit at all
 -- price / amount / total / revenue / cost NOT LIKE '%_cents'

Zero rows, or you have a finding.