Relational Database Internals

Data Engineering & Modern Data Platforms · Lesson 08 · Data Engineering Foundations

Lesson Overview

The progress-funnel query from Advanced SQL and Window Functions was correct and still late. On a Monday morning it read tens of millions of event rows, spilled a sort, and ran on the same PostgreSQL primary that enrolls learners. The plan said Seq Scan, Rows Removed by Filter in the tens of millions, and shared read far above shared hit. A second complaint arrived the same week: updating enrollments.status had slowed down, autovacuum had not visited the table since Friday, and the table was larger on disk than its live row count could explain. Neither incident was a SQL-syntax problem. Both were the storage engine showing through the query.

This lesson explains the PostgreSQL mechanisms a data engineer has to reason about before tuning, replicating, or extracting change. Pages move through a shared buffer cache. Commits become durable because the write-ahead log reaches stable storage before the client is told success. Readers and writers pass each other because MVCC keeps old row versions in the heap instead of blocking reads. Indexes point at those versions and still need a visibility check. Vacuum removes dead versions, freezes old transaction ids, and maintains the map that makes index-only scans possible. The query planner uses statistics to choose among scans and joins, and EXPLAIN (ANALYZE, BUFFERS) is how you check its guess against the pages it actually touched.

The engine detail lives in the PostgreSQL course: architecture, MVCC, VACUUM, ANALYZE, indexes, EXPLAIN ANALYZE, isolation, and streaming replication. This lesson uses those mechanisms on TechLearningPro's workload and stops where those courses go deeper on each command. It also draws the line Module 2 will need: logical replication and change data capture read the same WAL this lesson treats as the durability log. You cannot design CDC, a safe analytical replica, or a vacuum policy if commit, snapshot, and bloat are still magic.

Learning Objectives

By the end of this lesson, you should be able to:

1. Describe an 8 kB page moving from disk into shared_buffers, and separate that cache from the operating-system cache and from effective_cache_size.
2. Explain why a commit waits on WAL flush, what an LSN identifies, and what synchronous_commit changes about that wait.
3. Trace a checkpoint: dirty buffers, full_page_writes, WAL volume, and why a checkpoint is not a backup.
4. Explain MVCC with xmin and xmax, a snapshot, and the reason readers do not block writers on PostgreSQL.
5. Relate READ COMMITTED, REPEATABLE READ, and SERIALIZABLE to the snapshot a statement or transaction sees.
6. Explain heap-only tuple updates, fillfactor, and why an update of an indexed column cannot stay HOT.
7. Choose a B-tree, a BRIN index, a partial index, or a covering index for a stated predicate, and explain why an index-only scan needs the visibility map.
8. Explain what VACUUM and VACUUM FREEZE accomplish, why VACUUM FULL takes a lock you do not want in the enrollment path, and how transaction-id wraparound forces freezing.
9. Read EXPLAIN (ANALYZE, BUFFERS) for a bad estimate, a late filter, a sort spill, and a buffer-read storm.
10. Use pg_stat_user_tables and pg_stat_statements to connect a slow plan to dead tuples, missing analyzes, or a query that should not run on the primary.
11. State what streaming replication ships (WAL) and what logical replication publishes (row changes), at the level a CDC design needs.
12. Review an index and vacuum change for write amplification, replica lag, and lock risk before it reaches production.
13. Hand Module 2 a clear rule: the primary serves product transactions; analytical window queries and extracts run from a replica or a later warehouse load.

Prerequisites

• Advanced SQL and Window Functions, because the slow query in this lesson is that funnel's scan and sort, not an abstract SELECT 1.
• Transactions, ACID, and COMMIT. This lesson explains what COMMIT waits for. It does not re-teach BEGIN.
• Comfort reading a query plan at the level of "scan" and "join." EXPLAIN and query planning are the command references.
• No kernel programming. When the lesson says fsync, it means the write is asked to reach durable storage before success is returned.

Detailed Core Concepts

Pages and the buffer cache

PostgreSQL stores tables and indexes in fixed-size pages. A default build uses 8 kB. A row is a tuple inside a heap page. A query never asks the disk for "the row" as an independent object. A backend asks the buffer manager for the page that contains the tuple.

shared_buffers is the shared-memory region that holds those pages for every backend. A page in that region is a buffer. The first backend to need a page not already cached allocates a buffer, reads the page through the operating system, and pins it. Later backends hit the same buffer. A dirty buffer has been changed in memory and does not yet match the copy in the data file. Dirty does not mean "not durable." Durability is the WAL's job, described below. The data file can lag memory until a checkpoint or the background writer flushes the page.

The operating system keeps its own page cache. A buffer PostgreSQL evicts can still be in RAM, so the next read is a kernel hit rather than a device read. That second cache is why setting shared_buffers to most of the machine's memory is a poor default: you shrink the cache the kernel uses, and you shrink memory available for sorts, hashes, and connections. effective_cache_size allocates nothing. It tells the planner how much RAM it may assume is available for caching when it compares an index scan with a sequential scan. Set it near the memory you really expect to act as cache. It will not create that memory.

Bulk readers do not get to evict the entire cache. Sequential scans, COPY, and vacuum use a small ring of buffers so a one-pass read of a large events table does not flush the enrollment pages the product is updating. That is why a large analytical scan on the primary can still be rude — it burns I/O and CPU — without completely emptying shared_buffers.

The clock-sweep eviction increments a usage count when a buffer is used and decrements as the sweep looks for a victim. Hot enrollment pages stay. Cold historical event pages are the ones a funnel query should be reading from a replica, not promoting over the working set of the checkout path.

Write-ahead log, LSN, and commit

Before a data page is considered durable, the log records that describe its change are written to the write-ahead log. The rule is the whole reliability story: after a crash, replay the log and the page can be reconstructed, including changes that had not reached the data file. PostgreSQL can therefore delay writing heap pages and still acknowledge a commit.

Each position in the WAL is a log sequence number, an LSN. You will see LSNs in replication lag, in pg_current_wal_lsn(), and in backup tooling. WAL is stored in segment files. On current default builds a segment is 16 MB. Segments are recycled or removed according to checkpoints and replication slots. A forgotten replication slot retains segments until the disk fills. That operational fact belongs in the same mental model as "WAL is the durability log."

On commit, with synchronous_commit at its default on, the backend waits until the WAL record of the commit has been flushed to durable storage locally. It does not wait for the heap page to be written. synchronous_commit = off returns to the client before that local flush. A crash can then lose transactions the client was told succeeded, within a short window. The cluster does not come up corrupted; it comes up missing the latest acknowledged work. TechLearningPro does not use off for payments or enrollments. It can be a conscious choice for high-volume telemetry that is also retained in an upstream log, and someone has to write down the loss window.

When synchronous streaming replicas are configured, the wait can extend beyond the local flush. local waits only locally. remote_write waits until the replica has written the WAL to its operating system. remote_apply waits until the replica has replayed it. on with synchronous replicas waits for the configured flush on those standbys. Each step buys a stronger statement about the standby and costs latency on the primary. Streaming Replication is the command-level lesson. The design choice is here: enrollment commits wait for one synchronous standby in the same region; the analytical replica is asynchronous and is allowed to lag.

full_page_writes, on by default, writes a full image of a page into WAL the first time that page is changed after a checkpoint. The image repairs a torn page if the operating system crashes in the middle of an 8 kB write. Turning it off to save WAL volume is a durability decision, not a performance tweak you make on a payments database because a graph looked high.

Checkpoints flush dirty buffers so crash recovery has a bounded amount of WAL to replay. checkpoint_timeout and max_wal_size both influence when a checkpoint starts. A checkpoint that is too infrequent makes recovery and replica catch-up replay a long WAL range. A checkpoint storm writes a large amount of data at once and can stall the product. checkpoint_completion_target spreads the writes. The background writer trickles dirty pages between checkpoints so the checkpoint has less to do. None of this is a backup. A checkpoint does not give you a point-in-time copy you can restore to another region. Base backups plus archived WAL do. Backup & Restore covers the tools.

wal_level controls how much information the WAL contains. replica supports physical replication and is the usual production setting. logical adds the information logical decoding needs to emit row changes. Module 2's change-data-capture lesson will require logical on the database you decode from, plus a replication slot. Raising wal_level increases WAL volume and is a planned change, not a session setting you flip during an incident without telling the operator who manages disk.

MVCC, snapshots, and isolation

PostgreSQL does not overwrite a heap tuple in place when you UPDATE. It inserts a new tuple and marks the old one as expired for transactions that should not see it. INSERT creates a tuple. DELETE marks a tuple expired. The expired tuple occupies space until vacuum reclaims it. That is the origin of bloat on enrollments, which is updated every time a status changes, and the reason an append-only events table bloats far less from updates it does not perform.

Each tuple header carries xmin, the transaction id that created it, and xmax, the transaction id that expired it, or zero if it is still current. A snapshot is the set of transaction ids a statement considers in progress, plus the horizon of ids that are visible. A tuple is visible to a snapshot when its creator committed and is not in progress from the snapshot's point of view, and its expirer is either absent, not committed, or still in progress relative to that snapshot. The exact predicate is in the source and in MVCC. The engineering consequence is enough to design with: a reader sees a committed version without waiting for a writer to finish, and a writer does not wait for a reader to finish, because the reader is using an older tuple. Writers still block writers on the same row. Locks have not disappeared. They have stopped being the mechanism of ordinary read consistency.

Hint bits on the tuple record that xmin or xmax committed, so later reads can skip a lookup in the commit-status log. The first reader after a commit often dirties the page by setting hint bits. A SELECT can therefore write. That surprises people who replicate a "read-only" analytical query onto the primary and watch WAL appear.

Isolation levels choose the snapshot lifetime:

READ COMMITTED is not "dirty read allowed." PostgreSQL's weakest level already forbids dirty reads. Choosing SERIALIZABLE for the event ingest path will turn retries into an incident. Choosing READ COMMITTED for a two-statement balance transfer without an explicit row lock or a single statement will allow anomalies the accountant can demonstrate. Match the level to the anomaly you can name. Isolation Levels lists the anomalies in the engine's terms.

Transaction ids are 32-bit values with modular comparison. A very old tuple whose xmin is not frozen eventually looks like it is in the future, which is wraparound, and PostgreSQL will shut down writes to protect the data rather than serve wrong rows. Freezing marks tuples as belonging to a frozen past. Autovacuum does this. The parameter autovacuum_freeze_max_age (default 200 million) is the backstop, not a target you want to hit. Monitoring the age of datfrozenxid and relfrozenxid is part of running the database, not an optional DBA hobby.

HOT updates and indexes

If an UPDATE does not change any indexed column and the new tuple fits on the same page, PostgreSQL can perform a heap-only tuple update. The indexes still point at the older tuple; a chain on the page leads to the new one. That avoids index maintenance and a large amount of WAL. Status changes on a row whose primary key and foreign keys did not change are the textbook case, if the page has free space. fillfactor below 100 reserves that space. A table that is only inserted, such as events, wants the default full pages. A table that is updated in place, such as enrollment status, often wants a lower fillfactor so HOT has room. Measure bloat and WAL before and after. A fillfactor of 70 on a 200 GB append-only log wastes disk and helps nothing.

An index entry does not contain the full MVCC visibility story. An index scan finds candidate tuple ids, then the heap page confirms visibility, unless the visibility map says the page is all-visible. Then an index-only scan can answer from the index alone, which is what you want for COUNT(*) on a narrow index that covers the predicate. The visibility map is maintained by vacuum. A table that is never vacuumed, or whose pages keep being modified, will not offer index-only scans and every index probe pays a heap fetch. Covering Indexes documents the INCLUDE clause, which adds payload columns to a B-tree leaf so more queries avoid the heap. INCLUDE columns do not participate in search order. Put the predicate and order columns in the key, and the extra selected columns in INCLUDE.

Index types TechLearningPro actually chooses among:

A B-tree on (learner_id, event_time, event_id) serves the window order from lesson 7. A BRIN index on event_time is a few pages that summarize min and max time per block range. It is attractive when the heap is physically ordered by insert time and queries ask for "the last two days." It is useless when updates scatter those rows. BRIN Indexes and B-Tree Indexes are the structures. This lesson's job is the choice.

Every secondary index slows INSERT and the updates that change indexed columns. The morning funnel's perfect index is a tax on the ingest path. Add the index the EXPLAIN proves, not the index a naming convention suggests.

Vacuum, analyze, and statistics

VACUUM reclaims tuples that no running transaction can still see, updates the free-space map and the visibility map, and freezes old transaction ids. It does not, in its normal form, return the file to the operating system as a smaller file. It marks space reusable inside the file. VACUUM FULL rewrites the table and does return space, and it takes an ACCESS EXCLUSIVE lock that blocks reads and writes. TechLearningPro does not run VACUUM FULL on enrollments or events during the teaching day. If a table is truly bloated, a rewrite is planned: a new table, copy, swap, with a lock window you scheduled, or a tool the operator already trusts. The unplanned VACUUM FULL is how a maintenance script becomes an outage.

Autovacuum runs the same family of work when thresholds are crossed. autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor together mean "this many dead tuples, plus a fraction of the table." The default scale factor of 0.2 is late for a large table: a 100 million row event table can accumulate a large absolute number of dead tuples before a 20 percent trigger, and an update-heavy smaller table can still bloat if autovacuum workers are busy. Overrides belong in ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...) for the table you measured, not in a global change copied from a blog.

ANALYZE samples the table and writes statistics the planner uses: most-common values, histograms, null fraction, distinct counts. Autovacuum analyzes as well. A funnel query planned with stale stats estimates 1,000 rows, chooses a nested loop, and then processes 8 million. EXPLAIN shows the estimate. EXPLAIN ANALYZE shows the actual. The gap is the clue. After a large load, run ANALYZE on the loaded tables rather than waiting for the scale factor. ANALYZE is the command. It takes a read lock that conflicts with VACUUM FULL and schema changes, and it does not block ordinary SELECT and INSERT.

pg_stat_user_tables exposes n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, and last_autoanalyze. pg_stat_statements, once the extension is created, ranks queries by total time. Those two views are the start of every "the database is slow" review in this course. They are not a monitoring product. They are the facts the plan has to agree with.

What the planner is choosing

For a single table the useful scan types are sequential scan, index scan, bitmap index scan, and index-only scan. A sequential scan of events for "one learner" is a mistake when an index on learner_id exists and the learner is selective. A sequential scan for "80 percent of last month" can be cheaper than random index probes. Bitmap scans sit in between: they find many tuple ids, sort them into page order, and heap-fetch with less random I/O.

Joins are nested loop, hash, or merge. A nested loop that the planner thought was 100 rows and that actually runs 2 million times is the classic bad estimate. Hash joins need work_mem. If the hash spills, the plan is still correct and the runtime collapses. Window sorts from lesson 7 use the same memory budget, per sort node.

random_page_cost and seq_page_cost are planner opinions about I/O. On SSD-backed managed PostgreSQL they are often lowered so the planner is less afraid of index scans. Change them with a before-and-after plan on the queries you serve, not as a global act of optimism.

Architecture and Design Diagrams

The write path and the read path share the buffer cache and diverge at durability and visibility.

mermaidCopy
flowchart TD
  APP[Product backend] -->|INSERT UPDATE DELETE| BE[Backend process]
  BE --> BUF[shared_buffers 8 kB pages]
  BE --> WAL[WAL insert]
  WAL -->|commit flush| DUR[Durable WAL segment]
  BUF -->|dirty pages| BG[Background writer]
  BUF -->|checkpoint| CP[Checkpointer writes data files]
  DUR -->|replay| REC[Crash recovery or streaming replica]
  READ[Analytics SQL on replica] --> RBUF[Replica shared_buffers]
  REC --> RBUF
  RBUF --> SNAP[MVCC snapshot visibility]
  SNAP --> PLAN[Planner: stats, indexes, costs]
  PLAN --> EXEC[Scan, join, window sort]
  VAC[Autovacuum] --> BUF
  VAC --> VM[Visibility map and freeze]
  VM --> EXEC

A single enrollment update that can stay HOT looks like this:

mermaidCopy
sequenceDiagram
  participant API as Enrollment API
  participant BE as PostgreSQL backend
  participant WAL as WAL
  participant HEAP as Heap page
  participant IDX as B-tree indexes

  API->>BE: UPDATE status WHERE enrollment_id = ?
  BE->>BE: Snapshot and row lock on the live tuple
  BE->>HEAP: New tuple on the same page, old tuple xmax set
  Note over HEAP,IDX: Indexed columns unchanged and page has room: HOT, indexes not updated
  BE->>WAL: Heap change and commit record
  WAL-->>BE: Flush LSN durable
  BE-->>API: COMMIT success
  Note over HEAP: Old tuple remains until vacuum and no snapshot needs it

Physical replication ships WAL bytes. Logical replication ships decoded row changes. CDC in Module 2 sits on the logical path and inherits wal_level, slot retention, and the primary's write load.

mermaidCopy
flowchart LR
  PRI[Primary WAL] -->|streaming replication| PHYS[Physical replica for analytics SQL]
  PRI -->|logical decoding| SLOT[Replication slot]
  SLOT --> PUB[Publication of selected tables]
  PUB --> SUB[Subscriber or CDC consumer]
  SUB --> STAGE[Module 2 staging tables]
  PHYS --> FUNNEL[Funnel and window jobs]

Technical Explanation

A commit, named precisely

An enrollment status change in READ COMMITTED does the following. The backend takes a snapshot for the statement, finds the visible tuple, locks the row against concurrent updates, writes a new tuple, appends WAL for the heap change, and on COMMIT appends a commit record and waits until that record is flushed when synchronous_commit requires it. The client may proceed. The data file page might still be dirty in shared_buffers. If the process crashes before the checkpoint, recovery replays WAL from the last checkpoint's redo location and redoes the heap change. The learner does not see a committed status without a WAL record that survived.

If the same transaction also inserts a payment row, both the enrollment tuple and the payment tuple commit or neither does, because there is one commit record for the transaction. That is the local ACID promise. It does not extend to a call to an external payment provider made before COMMIT. The provider lives outside this WAL. The application pattern for that boundary is an idempotency key and a reconciliation job, which later architecture lessons treat as a distributed problem. This lesson's promise stops at the PostgreSQL transaction.

Visibility with numbers

Suppose transaction 100 inserts an enrollment and commits. Transaction 101 updates the status and is still open. A READ COMMITTED statement that starts after 100 committed and while 101 is open sees the tuple created by 100, because 101's new tuple has an xmin that is in progress and the old tuple's xmax is that same in-progress id. When 101 commits, a new statement in the same session sees the new status. A REPEATABLE READ transaction that took its snapshot before 101 committed keeps seeing the old status until it ends. Two product bugs fall out of mixing those expectations: a UI that reads twice and thinks the database "lost" an update, and a report transaction that expected to see its own earlier committed sibling transaction and cannot, because its snapshot predates that commit. Name the level in the code review.

Why the events table and the enrollment table age differently

learning.events is insert-mostly. Dead tuples come from failed transactions and rare corrections. The heap grows with live data. A BRIN index on event_time can pay off if copy order matches time. Vacuum is still required for freeze and for the visibility map, and a long-running transaction that started on Monday and is idle until Thursday holds back the freeze and vacuum horizon for the whole cluster. pg_stat_activity showing idle in transaction is an incident, not a session style.

learning.enrollments is updated. Each status change leaves a dead tuple unless the update is HOT and even HOT leaves the old tuple until vacuum. A table of 2 million enrollments updated several times each can carry more dead space than live space if autovacuum is losing the race. Queries that used to be index scans start fetching bloated pages. The fix is a table-level autovacuum setting you measured, a fillfactor that allows HOT, and a stop to indexing columns that change every update if the index is not earning its keep. Dropping an unused index is a write optimization.

Reading the plan of the slow funnel extract

The Monday query restricted events with a function on the column:

sqlCopy
SELECT course_id, COUNT(DISTINCT learner_id) AS viewers
FROM learning.events
WHERE date_trunc('day', event_time AT TIME ZONE 'UTC') = DATE '2026-11-09'
  AND event_name = 'course_view'
GROUP BY course_id;

A B-tree on event_time cannot satisfy date_trunc(...) = ... unless the index expression matches. The plan shape that followed on a 50 million row table looked like this, with costs and times representative of that incident rather than a promise of your laptop:

textCopy
HashAggregate
  Group Key: course_id
  -> Seq Scan on events
       Filter: (date_trunc('day', event_time AT TIME ZONE 'UTC') = '2026-11-09'
                AND event_name = 'course_view')
       Rows Removed by Filter: 49880000
       Buffers: shared hit=120000 read=760000

Rows Removed by Filter means the scan read the pages and threw the tuples away. shared read means those pages were not in shared_buffers and PostgreSQL asked the kernel. Some of those may still have been OS-cache hits. The plan does not by itself prove device I/O. It does prove the working set of this query was far larger than the shared cache and that the predicate did not use the time index.

The repaired predicate is a range, which a B-tree can use:

sqlCopy
WHERE event_time >= TIMESTAMPTZ '2026-11-09 00:00:00+00'
  AND event_time <  TIMESTAMPTZ '2026-11-10 00:00:00+00'
  AND event_name = 'course_view'

A composite index on (event_name, event_time) or a partial index WHERE event_name = 'course_view' matches this query. The partial index is smaller and is invisible to queries that forget the same predicate. After the change, the plan you want is an index or bitmap scan whose actual rows is the viewers' events, not the whole history, and whose shared read is proportional to that slice. Then EXPLAIN the window query from lesson 7. Removing the sequential scan and still spilling a sort means the next knob is the date bound or work_mem for that session, not another index on a column nobody filters.

EXPLAIN ANALYZE executes the query. Run it on a replica, or inside a transaction you roll back when the statement is a write. Do not paste ANALYZE on a destructive statement against production to "see if it is fast."

Statistics that lie by omission

A load of historical events with COPY leaves the planner holding yesterday's picture until analyze runs. The symptom is an estimate off by orders of magnitude and a nested loop that was planned for a handful of outer rows. ANALYZE learning.events after the load is part of the load, not a follow-up ticket. For a column with a skewed event_name, the default statistics target can store too few most-common values. Raising the statistics target on that column, then analyzing, is a precise fix. Raising default_statistics_target globally makes every analyze more expensive. Prefer the column.

Replica lag is an LSN gap

An asynchronous replica applies WAL it has received. pg_current_wal_lsn() on the primary and pg_last_wal_replay_lsn() on the replica are comparable positions. Time lag from pg_last_xact_replay_timestamp() is what the funnel job in lesson 7 should check before publishing. A lag of seconds is normal for an async replica. A lag that grows without bound means the replica cannot replay as fast as the primary writes, or WAL cannot be sent. The funnel job's refusal to publish is the product protection. The operator's protection is disk on the primary: if the replica stops and a replication slot holds WAL, segments accumulate. Monitor slot retained bytes. CDC consumers in Module 2 create the same hazard. A slot is a contract to keep WAL, not a buffer you can forget.

Logical replication publishes changes for listed tables through a publication and a subscription. The subscriber is a separate primary with its own indexes. That is how you can keep analytical indexes off the enrollment primary: replicate learning.events to an analytics database, add the funnel indexes there, and accept that the subscriber is not an automatic failover of the whole cluster. Logical Replication is the mechanics. The decision this lesson records is: product failover uses physical replication; the extra index set for windows uses a logical subscriber or an async physical replica that is allowed to carry those indexes only if you accept the write cost on that replica's replay. A physical replica has the same indexes as the primary. You cannot add a "replica-only" B-tree to a physical standby. If the index must not tax the primary, the target is logical replication or an export into the warehouse.

Locks that still matter under MVCC

MVCC does not remove lock waits. UPDATE locks the row. Two sessions updating the same enrollment serialize. ALTER TABLE and CREATE INDEX without CONCURRENTLY take locks that block writes. CREATE INDEX CONCURRENTLY builds the index without blocking ordinary writes and can fail and leave an invalid index if the session dies. The invalid index does not get used; it still needs a drop. Autovacuum needs to truncate or freeze with a lock that conflicts with some operations, and a session in idle in transaction can block it. The diagnostic is pg_locks joined to pg_stat_activity, looking for the granted lock and the waiter. Locking Strategies and Deadlocks are the catalogs of modes. The design rule is shorter: keep product transactions short, build indexes concurrently, and do not hold a transaction open across a user think-time or an HTTP call to a payment provider.

Practical Implementation or Design Walkthrough

Problem

The Monday funnel extract scans learning.events on the primary, the enrollment update latency rises with dead tuples, and the team wants the morning job off the primary without losing the ability to audit how far the replica is behind.

Targets

• The daily extract's predicate is a sargable range on event_time.
• A partial or composite index serves event_name = 'course_view' plus the time range, created concurrently, and EXPLAIN (ANALYZE, BUFFERS) shows the filter no longer removes tens of millions of rows after a sequential scan.
• enrollments has a measured autovacuum scale factor and a fillfactor that makes status updates HOT, confirmed by fewer index tuple inserts relative to heap updates in pg_stat_user_tables and pg_stat_user_indexes.
• The extract connects to the physical replica, checks replay time, and refuses to publish beyond a five-minute lag.
• No VACUUM FULL in the change.
• The primary's wal_level stays replica until a CDC design explicitly requires logical.

Steps

1. Capture the statement from pg_stat_statements by total time, not by a single slow-log anecdote. Confirm it is the funnel extract.
2. EXPLAIN (ANALYZE, BUFFERS) on the replica. Record estimated rows, actual rows, buffers, and the scan type. Cancel if you accidentally started it on the primary during peak and the plan is a sequential scan of the live table; do not "let it finish so we have the numbers" while checkout is waiting on I/O.
3. Rewrite date_trunc equality into a half-open timestamptz range. Repeat the plan.
4. If the scan is still sequential because the range is a large fraction of the table, stop adding indexes for that shape and move the query to the replica first. If the range is a day and the scan is still sequential, add:

sqlCopy
CREATE INDEX CONCURRENTLY events_course_view_time_idx
  ON learning.events (event_time)
  WHERE event_name = 'course_view';

5. ANALYZE learning.events. Confirm the new plan uses the index and the buffer read count dropped in proportion to the selectivity.
6. On enrollments, read n_dead_tup, n_live_tup, last_autovacuum. If dead tuples dominate and autovacuum is hours behind, set a lower scale factor for that table only and confirm workers are not stuck behind an idle transaction.
7. For HOT, check whether status updates modify indexed columns. If an index exists on status alone and nothing queries status without a learner or course id, drop it and let the update stay HOT. Set fillfactor when pages are full and HOT is failing for lack of space. New pages pick up the fillfactor; existing bloated pages need a later rewrite you schedule.
8. Point the job's connection string at the replica. Before the publish INSERT, run the lag check and write a row to the job log either way.
9. Leave a note in the runbook: a replication slot for any future CDC consumer must have an alarm on retained WAL. This change does not create a slot.

Validation

• Fixture: the day-bounded query returns the same count as the date_trunc query for a UTC day. They differ if the session time zone changes the date_trunc version. The range version is the one that matches the contract "UTC day."
• The partial index is unused when event_name is omitted. That is correct. The job must include the predicate.
• An open BEGIN that updates one enrollment and sleeps holds a snapshot. Vacuum does not remove the tuple that session can still see. The test is a warning in review: no connection pool session may stay idle in transaction.
• Replay lag injected by pausing the replica makes the job skip publish. The dashboard shows yesterday's good run, not a partial today.

Operational boundary

This walkthrough tunes a PostgreSQL system of record. It does not turn PostgreSQL into the long-term analytical store. When the retained event history no longer fits the replica's I/O budget, the same SQL moves to a warehouse table loaded by the Module 2 pipeline. The internals still matter on the primary, because the extract, the logical slot, and the product transactions share WAL and checkpoints. ETL and ELT Architecture chooses the destination. This lesson chooses what must stay healthy at the source.

Enterprise Real-World Scenarios

TechLearningPro: the funnel job and the enrollment table on one primary

Checkout latency rose on Monday at 06:10 UTC, the same minute the funnel job started. The job's plan was the sequential scan above. It did not block on locks. It saturated read I/O and CPU, and the buffer ring did not save the device. Enrollment updates, which fsync WAL on commit, waited behind device latency. Moving the job to the async replica removed the contention the same day. The index and the sargable predicate made the replica job finish in the window the dashboard expects. A later review found n_dead_tup on enrollments at roughly twice n_live_tup, with last_autovacuum a day old because one reporting session had been idle in transaction since Sunday. Killing that session and setting a tighter autovacuum scale factor on enrollments let vacuum catch up. No table rewrite was required. The team wrote the idle-transaction alarm before they wrote any new index.

Security and governance stayed ordinary and sufficient: the replica role used by the job can SELECT events and INSERT into the analytics schema on a different database reached by logical publication, or, while the job still writes aggregates back, it writes through a controlled primary role that cannot update enrollments. A shared superuser connection string in the job config was the first review comment, and it was removed. Internals work that opens a superuser session to "make vacuum faster" is how a metric job becomes a credential incident.

TechLearningPro: WAL growth after a logical slot was left behind

A proof of concept decoded learning.events with a replication slot and wal_level = logical. The consumer process was stopped on a Friday. The slot remained. WAL segments could not be recycled past the slot's restart LSN. The primary's disk reached the threshold that refuses new connections before anyone connected the alert to CDC. The data was not corrupted. Commits slowed and then failed for lack of space. Recovery was: start the consumer or drop the slot, confirm segments recycled, and add the retained-WAL alarm. The lesson the platform kept is the one Module 2 has to inherit. A slot is part of the pipeline's production budget. A decoder that reads WAL is a consumer with a freshness SLO, not an experiment that can be idle. Physical replicas that disconnect without a slot do not pin WAL the same way; they risk falling too far behind to catch up without a new base backup. Both failure modes are WAL retention problems with different owners.

What a large product company is doing when it says "we just added a read replica"

The pattern that transfers is physical replication for failover and read scaling, analytical SQL pointed at a replica whose lag is measured in LSN or replay time, and a refusal to put the heavy scan on the primary that fsyncs payments. The numbers differ. The mistake that does not transfer well is copying a shared_buffers fraction or a fillfactor from a conference slide onto a database whose working set you have not measured. TechLearningPro's change was driven by EXPLAIN (ANALYZE, BUFFERS) and pg_stat_user_tables. Those views exist so the slide is unnecessary.

Best Practices

• Run analytical scans and lesson-7 window jobs on a replica, and check replay lag before publishing.
• Write time predicates as ranges on the stored timestamptz. Keep date_trunc in the select list for labels, not in the filter, unless you indexed the same expression.
• Add indexes with CREATE INDEX CONCURRENTLY, confirm they are valid, and drop indexes pg_stat_user_indexes shows are unused after a full business cycle.
• Put autovacuum and fillfactor overrides on the tables whose update pattern you measured. Leave append-only event tables at the default fillfactor.
• Treat idle in transaction as a defect in the application or the job. It holds snapshots, blocks vacuum, and pins bloat.
• ANALYZE after bulk loads. Look at estimated versus actual rows before you believe a plan.
• Keep product transactions short. Do not include an external HTTP call inside a database transaction.
• Alarm on replication-slot WAL retention and on replica replay lag as separate signals.
• Use synchronous_commit and synchronous standbys for enrollment and payment transactions whose acknowledgement must survive a local crash and a primary loss. Do not set the cluster default to off because ingest benchmarks improved.
• Leave full_page_writes on unless a durability review says otherwise and the storage stack's torn-page story is written down.
• Prefer a planned table swap over VACUUM FULL on a live product table.
• When an index exists only to serve analytics, put it on a logical subscriber or accept its write cost on the primary consciously. A physical standby cannot carry an extra index the primary lacks.
• Read maintenance and index maintenance before automating vacuum schedules that fight autovacuum.

Common Mistakes and Anti-Patterns

Analytical SQL on the primary "because the replica might be stale." The replica's lag is measurable. The primary's checkout path is the thing the scan puts at risk. Consequence: I/O coupling between a report and payments. Fix: lag check plus replica, or a warehouse extract once the scan no longer fits a replica.

Wrapping indexed columns in functions. date_trunc on event_time in WHERE hides the B-tree. Consequence: sequential scans and a belief that "indexes do not work on this table." Fix: a range predicate, or an expression index that matches the call exactly, including the time zone.

VACUUM FULL as a weekly cron. It rewrites the table under an exclusive lock. Consequence: an outage window you did not size. Fix: normal vacuum and autovacuum for reusable space; a planned rewrite only when the file must shrink.

Raising shared_buffers to most of RAM after a cache-hit dashboard turned yellow. The parameter is not the OS cache, and effective_cache_size is not memory you allocate. Consequence: less room for sorts and a planner that was misinformed if you changed the wrong setting. Fix: measure the working set, leave room for the kernel and work_mem, and set effective_cache_size to a realistic estimate.

Indexing every foreign key and every status "for safety." Each index is write amplification and a barrier to HOT when the indexed column changes. Consequence: slower enrollments and more WAL. Fix: index the predicates and orders EXPLAIN shows, and review unused indexes.

A logical replication slot without an owner. WAL is retained from the slot's restart LSN. Consequence: the primary disk fills while the proof of concept is stopped. Fix: an owner, an alarm on retained bytes, and a documented drop procedure.

EXPLAIN ANALYZE of an unbounded scan on the primary during class hours. ANALYZE runs the query. Consequence: you reproduce the incident to learn that you reproduced it. Fix: replica, a statement_timeout, and a date predicate.

Ignoring wraparound until PostgreSQL stops accepting writes. Freezing is vacuum's job. Consequence: a forced outage when the xid horizon is exhausted. Fix: monitor datfrozenxid age and do not disable autovacuum on busy databases to "save I/O."

Holding a transaction open across user time or a payment HTTP call. MVCC will not hide the row lock or the snapshot. Consequence: blocked updates, blocked vacuum, and a lock graph in pg_locks. Fix: commit before the external call, and use an idempotency key when the call's result must be recorded later.

Troubleshooting and Design Review

A design review for a database change asks:

1. Does this query run on the primary, and what does its EXPLAIN (ANALYZE, BUFFERS) do to shared reads during peak?
2. Which index, if any, matches the predicate without a function wrapper, and what does it cost on INSERT?
3. What is the dead-tuple and freeze picture of the tables it writes, and can autovacuum see them?
4. Does the change require wal_level = logical or a slot? Who drops the slot?
5. What lock does the migration take, and is the index build concurrent?
6. What is the allowed replica lag for consumers of this data?

Bring the plan output to the review. A paragraph that says "we will add an index" without the predicate and the plan is not a review.

Interview Questions and Answers

Q1. Why can PostgreSQL acknowledge a commit before the heap page is written to the data file?

The commit record is flushed to the WAL first. Crash recovery replays WAL from the last checkpoint and redoes heap changes that never reached the data file. The heap write can wait for the background writer or the checkpointer. This is safe only for transactions whose commit record actually reached durable storage. With synchronous_commit = off, the client can be told success before that flush, and a crash can lose those transactions. The database structure remains recoverable. The lost work is the recent acknowledged transactions, which is unacceptable for enrollments and payments.

Q2. What is the difference between shared_buffers, the operating-system page cache, and effective_cache_size?

shared_buffers is PostgreSQL's shared memory holding 8 kB pages, including dirty pages. The operating system caches file data as well, so a page evicted from shared_buffers may still be in RAM. effective_cache_size allocates nothing. It is a planner estimate of how much cache to assume when costing an index scan against a sequential scan. Setting shared_buffers to nearly all of RAM squeezes the OS cache and query memory. Setting effective_cache_size does not speed up a query by itself. It changes plans, for better or worse, depending on whether the estimate is honest.

Q3. How does MVCC let a reader avoid waiting on a writer, and what does that cost?

An update inserts a new tuple and sets xmax on the old one. A snapshot decides which of those versions is visible by comparing xmin and xmax with committed and in-progress transaction ids. The reader uses the version its snapshot can see and does not take the writer's row lock. Writers updating the same row still serialize. The cost is dead tuples, vacuum, wraparound freezing, and index entries that can point at tuples the snapshot cannot see. Until vacuum runs, the table and indexes are larger than the live data, and index-only scans fail when the visibility map is not all-visible.

Q4. What is a HOT update, and when does an enrollment status change fail to be one?

A heap-only tuple update keeps the new tuple on the same page and avoids updating indexes, when no indexed column changes and the page has room. A status change is HOT if status is not indexed and fillfactor or natural free space leaves room. It is not HOT if any indexed column changes, if the new tuple does not fit, or if the update moves the row to another page. Indexing status "just in case" can disable HOT for the hottest update on the table. Confirm with statistics on index tuple activity versus heap updates, not with hope.

Q5. Why does WHERE date_trunc('day', event_time) = DATE '2026-11-09' ignore a B-tree on event_time?

The index orders raw timestamps. The predicate applies a function, so the planner cannot walk a contiguous range of the index unless an expression index was built on that exact function call. The result is often a sequential scan that discards almost every row. Write a half-open range on event_time in UTC, which matches the index order. If the product truly must filter on a truncated local day, index that expression and understand the time zone baked into the index.

Q6. What does ordinary VACUUM reclaim, and why is VACUUM FULL a different operation?

Ordinary VACUUM marks dead tuples' space reusable in the file, updates the free-space and visibility maps, and freezes old transaction ids. It does not shrink the file on disk in the common case. VACUUM FULL rewrites the table into a new file and returns space to the operating system. It takes an ACCESS EXCLUSIVE lock that blocks reads and writes. Production response to bloat is autovacuum, a table-specific threshold, and removal of the long transaction holding the horizon. A rewrite is a scheduled migration, not a cron.

Q7. What is transaction-id wraparound, and what prevents it?

Transaction ids are 32-bit and compared modulo that space. Tuples that are not frozen eventually fall outside the safe horizon and would appear to be in the future. PostgreSQL freezes old committed tuples so they no longer depend on a normal xid, and it will stop accepting new transactions rather than return wrong visibility. Autovacuum's freeze work, driven by age limits such as autovacuum_freeze_max_age, is the prevention. Disabling autovacuum, or blocking it with idle transactions and unanswered lock requests, is how wraparound becomes an outage. Monitor the age of the database and the busy tables.

Q8. When is a BRIN index a better fit than a B-tree for learning.events?

BRIN stores summaries, typically min and max values, for a range of heap pages. It is tiny and it helps queries that restrict event_time when the heap is physically correlated with time, which an append-only insert stream often is. A B-tree on (learner_id, event_time) is the right tool for one learner's timeline and for the window order in lesson 7. BRIN will not efficiently find one learner scattered through the heap. Use B-tree for selective equality and ordered windows, BRIN for large time-slice scans of an insert-ordered table, and confirm both with EXPLAIN after analyze.

Q9. What has to be true for an index-only scan, and why does vacuum affect it?

The index must contain every column the query needs, either as key columns or as INCLUDE columns, and the visibility map must mark the heap pages all-visible so the executor can skip the heap. Vacuum sets those bits. A table with constant updates, or a table vacuum cannot process because a snapshot is held open, will show index scans with heap fetches instead. The plan is the proof. Adding INCLUDE columns without vacuum only makes a wider index.

Q10. Streaming replication and logical replication both involve WAL. What does each one send to a consumer, and which one can carry an analytics-only index?

Streaming physical replication ships WAL records and produces a byte-level copy of the cluster, including the same indexes. It is the failover and read-replica path. You cannot add an index that exists only on a physical standby. Logical replication decodes WAL into row changes for published tables and applies them to a subscriber that is its own primary, with its own indexes. The subscriber can carry the funnel indexes the product primary should not pay for. It is not a substitute for physical failover of the whole cluster. Both can retain WAL through slots if you configure them that way, and an abandoned slot fills the primary disk.

Q11. EXPLAIN says an index scan; the query is still too slow the morning after a big COPY. What do you look at next?

Compare estimated and actual rows with EXPLAIN (ANALYZE, BUFFERS). A fresh load often has stale statistics, so the planner picks the index for the wrong reason or nests a loop that explodes. Run ANALYZE, then look at buffers. A large shared read with a selective predicate can mean the index is not as selective as the name suggests, or the visibility map is forcing heap fetches across a bloated table. Check pg_stat_statements for the real statement text, including a function wrapped around the column. Finish by confirming the job is not running this plan on the primary.

Q12. A teammate proposes synchronous_commit = off for the events ingest and for enrollments, because a benchmark got faster. What do you accept?

You can discuss off for telemetry whose loss of a short recently acknowledged window is already tolerated because the producer retains the batch and can resend. You do not accept it for enrollments or payments, where the client treats commit as a promise the status survived a crash. Even for events, the loss window has to be written next to the producer's retry policy. The faster benchmark is the absence of a durability wait, not a free optimization. If the pain is the analytical scan, move the scan. Do not pay for it by weakening commit on the product tables.

Hands-On Exercises

Exercise 1. Plan, predicate, and index

Problem. Using a local PostgreSQL, load enough learning.events rows that a sequential scan is visible in the plan. Implement the non-sargable day filter and the half-open range. Compare plans.

Tasks.

1. Create the table and a B-tree on event_time. Insert rows across several days.
2. EXPLAIN (ANALYZE, BUFFERS) the date_trunc predicate and the range predicate.
3. Add the partial index from the walkthrough with CONCURRENTLY if your session is not inside a transaction block. CONCURRENTLY cannot run inside a transaction block. State that limitation in your notes.
4. Show a plan that uses the partial index, and a plan that does not, because event_name was omitted.
5. Write the lag check you would run before publishing: the function names pg_last_xact_replay_timestamp and what you would do if it returned null on a primary by mistake.

Acceptance. The range plan touches fewer pages than the truncated predicate on the same data. The write-up says where this job is allowed to run.

Exercise 2. Dead tuples and a blocked horizon

Problem. Demonstrate that an open transaction changes what vacuum can reclaim.

Tasks.

1. Create a small enrollment-like table. Insert rows. Update them.
2. In session A, BEGIN and SELECT the rows, then leave the transaction open.
3. In session B, update or delete rows and run VACUUM VERBOSE. Record what it says about tuples it cannot remove.
4. Commit session A and vacuum again. Record the difference.
5. Explain, in one paragraph, why an application connection in idle in transaction is a vacuum incident and not a cosmetic session state.
6. Do not use VACUUM FULL as the fix in the write-up. State the lock it would take and the alternative.

Rubric.

Lesson Summary

PostgreSQL keeps 8 kB pages in shared_buffers, makes commits durable by flushing WAL, and lets readers see a snapshot while writers create new tuple versions. That MVCC choice is why the product stays responsive under read load and why tables bloat, freeze, and depend on vacuum. Indexes find tuples and still consult visibility unless vacuum has marked the page all-visible. The planner turns statistics into a choice of scan and join, and EXPLAIN (ANALYZE, BUFFERS) is how you catch a function-wrapped predicate, a stale estimate, or a scan that belongs on a replica.

The Monday incident was fixed by a sargable time range, an index the predicate can use, autovacuum that is allowed to finish, and a job that reads a replica only when replay lag is inside its contract. VACUUM FULL, a giant shared_buffers, and synchronous_commit = off on payments were not the fix.

Module 2 starts from this source. Data Integration Patterns will move rows. ETL and ELT Architecture will decide which transforms stay in SQL on the replica and which land in a warehouse. Change data capture will read WAL through logical decoding and will inherit slot retention, wal_level, and the rule that an idle consumer must not pin the primary's disk. The next lesson, Data Modeling Fundamentals, designs the tables these pages store: conceptual, logical, and physical models, and the normalization choices that decide how many of those pages an update rewrites.

Further Reading

• PostgreSQL, Resource Consumption for shared_buffers, work_mem, and effective_cache_size, and WAL for synchronous_commit, wal_level, and full_page_writes.
• PostgreSQL, Internal Introduction to WAL, MVCC, Routine Vacuuming, and EXPLAIN.
• PostgreSQL, Index Types and Index-Only Scans.
• TechLearningPro: PostgreSQL Architecture, MVCC, VACUUM, ANALYZE, Index Fundamentals, B-Tree Indexes, BRIN Indexes, Covering Indexes, Partial Indexes, EXPLAIN ANALYZE, Isolation Levels, Streaming Replication, Logical Replication, Locking Strategies.