The initial copy is the boring part of streaming Postgres into a lakehouse: slow, well understood, and it finishes on its own. Nobody gets paged over a snapshot.
Streaming Postgres into a lakehouse fails at the replication slot, not the initial copy. A slot pins write-ahead log on the source until its consumer confirms it. At 3 MB/s of WAL and 420 GB free, a stopped consumer fills the volume in 39.8 hours — and the volume belongs to the production database.
What a replication slot retains, and why the risk sits on the source database
A replication slot retains write-ahead log on the source database until the consumer’s confirmed position moves past it, so a stalled consumer bills its disk to production rather than to the warehouse. Everything below runs against Oxbow, the invented legacy source this library uses when a change feed needs a home. Oxbow keeps its WAL on a 500 GB volume with 420 GB free and generates 3 MB/s of write-ahead log. I chose both so you can run the division yourself. Neither came off anyone’s server.
The slot is stupid about its consumer. Section 47.2.2 of the PostgreSQL 18 documentation, Replication Slots, says that “A logical replication slot knows nothing about the state of the receiver(s)”, and Table 53.20 defines restart_lsn as “The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won’t be automatically removed during checkpoints unless this LSN gets behind more than max_slot_wal_keep_size from the current LSN.” Retention tracks what the consumer has confirmed and nothing it intends to confirm, so a connector that is up and quietly failing to commit offsets retains as much as one dead since Friday. And max_slot_wal_keep_size is the only thing in that definition standing between a stalled consumer and the disk.
How much disk space a replication slot uses per hour
At Oxbow’s 3 MB/s, a stalled slot holds 10,800 MB an hour. Three megabytes a second times 3,600 seconds is 10,800 MB; divide by 1,024 for 10.55 GB an hour; multiply by 24 and the slot sits on 253 GB after a day of nobody noticing.
Megabytes here are binary, the way pg_size_pretty prints them. Section 9.28.7 of the PostgreSQL 18 documentation, database object management functions, says of pg_size_pretty that “the units are powers of 2 rather than powers of 10, so 1kB is 1024 bytes, 1MB is 1024^2 = 1048576 bytes”, so 420 GB free is 420 times 1,024, or 430,080 MB. That settles the convention, not your figure. A console quoting decimal gigabytes means 420,000,000,000 bytes, or 400,543 MB once divided by 1,048,576, and every runway below shortens by seven per cent. (WAL is allocated in whole files rather than as a smooth stream, 16 MB each by default — the initdb reference page puts --wal-segsize at “The default size is 16 megabytes.” Hold on to that number; it comes back.)
Slot runway: how long the source survives a dead consumer
Slot runway is free bytes on the WAL volume over the WAL generation rate, in hours of consumer downtime the source survives. Oxbow’s is 430,080 divided by 10,800, which is 39.8 hours, or 39 hours and 49 minutes.
Derive it again from the other end, because a number that arrives only one way is a number nobody checks. Oxbow writes 1,200 row changes a second at roughly 300 bytes each, both chosen, and a checkpoint cycle is 300 seconds, since the checkpoint settings put checkpoint_timeout at “five minutes (5min)” by default. A cycle therefore carries 360,000 row changes and 108,000,000 bytes of payload, which is 103 MB once divided by 1,048,576. Now the page images. The write-ahead log settings page says full_page_writes “writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint”, and ends on the flat sentence “The default is on.” A page is “usually 8 kB” per section 66.6 on database page layout. Third chosen number: 100,000 distinct pages touched in a cycle, so 100,000 times 8,192 is 819,200,000 bytes, or 781 MB. Add them. 781 plus 103 is 884 MB a cycle, 884 times 12 cycles is 10,608 MB an hour, and 430,080 divided by 10,608 is 40.5 hours.
Forty and a half against thirty-nine point eight. The two routes share the free-space figure and nothing else — a rate wrong by a factor of ten would put them hours apart rather than forty-two minutes apart.
Size the volume off the row traffic alone and you will be out by nearly an order of magnitude. Payload is 103 MB a cycle against 884 MB of WAL, so 884 divided by 103 is an amplification of 8.6, and the top-down figures agree: 1,200 times 300 bytes is 360,000 bytes a second, or 0.34 MB/s once divided by 1,048,576, and 3 divided by 0.34 is 8.8.
I don’t know what fraction of a real OLTP stream is full-page images. I picked 100,000 pages a cycle because it lands near 3 MB/s, not the other way round, and I have seen the amplification argued anywhere from 2x to 10x. I wouldn’t size a volume on my memory of an argument. Divide wal_bytes in pg_stat_wal by your own estimate of logical change bytes over the same window and use that in place of my 8.6. Not wal_fpi over wal_records: that ratio is two counts, it lands near 0.3, and a volume sized on it comes out ten times too small.
When should max_slot_wal_keep_size be set instead of left at -1?
Below roughly 72 hours of slot runway we set max_slot_wal_keep_size; above it we leave it at the -1 default and alert on retained bytes instead. The 72 is derived, not chosen. Start from the tolerance: the longest unplanned stretch in which nobody will look at the pipeline. Then the distribution. Overnight is roughly 8 hours, most nights. The weekly one is the Saturday of a Sunday-to-Friday working week, Friday 18:00 to Sunday 06:00, which is 36 hours. Design to the largest unplanned mode, then double it, because whoever gets paged has to find the consumer and restart it rather than acknowledge it. Thirty-six times two is 72. A Monday-to-Friday team runs the same reasoning to a different line — Friday 18:00 to Monday 06:00 is 60 hours, which doubles to 120. The working week sets the number.
One correction first. I had max_slot_wal_keep_size filed in my head as a PostgreSQL 12 setting, and it is not: it arrived in 13, which I checked in section E.24.3.2 of the PostgreSQL 13 release notes. On 12 the alert is all you have.
Setting the value needs a decision and a trap. Spend 60% of free space on slot retention and keep 40% for checkpoints and archive traffic: 420 times 0.6 is 252 GB, or 258,048 MB, which divided by 10,800 kills the slot at 23.9 hours; what’s left is 168 GB, or 172,032 MB, and that buys 15.9 further hours before the volume fills. Those add back to 39.8, the runway again. Set 23.9 against the 36-hour weekend that produced the threshold, because they do not reconcile and the gap is the decision. A Saturday stall under this setting invalidates the slot and buys a fresh snapshot, deliberately. The snapshot is the cheaper loss.
The trap is the unit. max_slot_wal_keep_size = 252 is megabytes, not gigabytes — fifteen and three-quarters of the 16 MB segment files from two sections ago, and at 3 MB/s it buys 252 divided by 3, or 84 seconds. Write 252GB.
Slot runway for Oxbow’s 430,080 MB of free WAL space at four generation rates.
| WAL rate | MB per hour | Slot runway |
|---|---|---|
| 1 MB/s | 3,600 | 119.5 h |
| 1.5 MB/s | 5,400 | 79.6 h |
| 3 MB/s | 10,800 | 39.8 h |
| 6 MB/s | 21,600 | 19.9 h |
Derate the rate three times over and the decision flips: at 1 MB/s the table’s 3,600 MB an hour divides into 430,080 to give 119.5 hours, above the line, so leave the parameter alone and alert instead. The crossover is 430,080 divided by 72, or 5,973 MB an hour, which is 1.66 MB/s. And the option we chose is not free. The PostgreSQL 18 replication configuration page says that “if restart_lsn of a replication slot falls behind the current LSN by more than the given size, the standby using the slot may no longer be able to continue replication due to removal of required WAL files.” A lost slot means a fresh snapshot, the full-table read I costed in the full-reload article, which raised this trade in a sentence and left the arithmetic for here. Picking the number takes WAL rate and on-call rota together, which is what a data engineering review is for.
Check wal_status in pg_replication_slots before trusting a lag dashboard
wal_status tells you which of four states a slot is in, and a lag-in-seconds dashboard tells you none of it. Table 53.20 names them reserved, extended, unreserved and lost, and the two in the middle carry the argument. extended “means that max_wal_size is exceeded but the files are still retained, either by the replication slot or by wal_keep_size”. unreserved “means that the slot no longer retains the required WAL files and some of them are to be removed at the next checkpoint. This typically occurs when max_slot_wal_keep_size is set to a non-negative value. This state can return to reserved or extended.”
Read that middle sentence again, because it decides what the column is worth to you. On the -1 default there is no cap for restart_lsn to fall behind, so a stalled slot reaches extended and stays there while the volume fills. safe_wal_size agrees: the same table says it “is NULL for lost slots, as well as if max_slot_wal_keep_size is -1”. Half the states and one column are dead text until you set the cap — which is the second argument for setting it. Until then, retained bytes against free disk is the only pair of numbers that moves.
One more column belongs there. inactive_since is “The time when the slot became inactive. NULL if the slot is currently being streamed.”
The inactive_since definition is why idle_replication_slot_timeout, new in PostgreSQL 18, is a parameter I would leave at 0. The case for it is real: a slot abandoned by a decommissioned consumer retains WAL forever, and this reaps it at the next checkpoint. But a connector that is up and streaming while nothing downstream commits holds the replication connection open and sends status updates every status.update.interval.ms, which Debezium’s property table puts at 10,000 ms, well inside the 60 seconds PostgreSQL gives wal_sender_timeout. The slot is streaming, inactive_since is NULL, and the idle mechanism has nothing to measure. Whether that holds once the connector’s own queue blocks is a question the documentation doesn’t answer and I haven’t tested. Turn it on where abandoned slots pile up faster than anyone reads a dashboard; leave it off everywhere else. It can’t see the failure that matters, and it will happily kill a healthy slot during a planned upgrade. The name says idle; the mechanism means disconnected.
No one view holds the whole measurement, so the division is yours to do:
sql
-- your own WAL rate, in MB/s, since the stats were last reset
SELECT wal_bytes / 1048576.0
/ extract(epoch FROM now() - stats_reset) AS wal_mb_per_s,
wal_fpi, wal_records
FROM pg_stat_wal;
-- retained bytes and state, per slot
SELECT slot_name, wal_status, invalidation_reason, inactive_since,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;Free space on the WAL volume is in neither query. That comes from df on the host, so the alert joins three tools for one division.
Restarting the connector does not free the WAL disk space
Restarting the connector gives the slot a live consumer again, but restart_lsn only advances as that consumer confirms what it has read, so the volume drains at the difference between the rates rather than at the consumer’s rate. A 20-hour outage at Oxbow’s 3 MB/s pins 3 times 3,600 times 20, or 216,000 MB, which is 210.9 GB once divided by 1,024. The consumer comes back and drains at 4.5 MB/s, also chosen, while the source still generates 3 MB/s, so the net drain is 1.5 MB/s. Divide 216,000 by 1.5 and you get 144,000 seconds, which is 40 hours.
Twenty hours of outage is therefore 60 hours of exposure, three times the number anybody writes in the incident report.
The near-miss is worse than the miss. If the consumer drains at 3.3 MB/s rather than 4.5, ten per cent faster than the source, the net drain is 0.3 MB/s, and 216,000 divided by 0.3 is 720,000 seconds, or 200 hours. Over eight days. “The consumer is faster than the source” and “the backlog clears” are different claims, and the ratio between the rates separates them — which is the other half of what real time actually costs.
Debezium’s own documentation is calm about this, and the calm is in the wrong chapter. The “Connector is stopped for a duration” section, the last on the Debezium 3.7 PostgreSQL connector page, says “If the connector is gracefully stopped, the database can continue to be used. Any changes are recorded in the PostgreSQL WAL. When the connector restarts, it resumes streaming changes where it left off.” Every word of that is true. The section names the WAL as the thing holding those changes and stops there; what retaining them costs sits elsewhere on the page, under setup. So the reader deciding whether an overnight outage is acceptable reads the one section written for that decision, and it never mentions the price.
Catalog bloat from a replication slot ends in transaction ID wraparound
Catalog bloat ends at transaction ID wraparound, which is where a forgotten slot stops costing disk and starts costing the whole database. Table 53.20 defines catalog_xmin as “The oldest transaction affecting the system catalogs that this slot needs the database to retain. VACUUM cannot remove catalog tuples deleted by any later transaction.” A slot with a dead consumer holds that line as firmly as it holds WAL, and the volume gives you nothing to look at.
Where that line ends is written down in the Caution box closing section 47.2.2 of the PostgreSQL 18 documentation — past the hot-standby paragraphs, past pg_log_standby_snapshot, at the bottom of the subsection where nobody scrolls:
Replication slots persist across crashes and know nothing about the state of their consumer(s). They will prevent removal of required resources even when there is no connection using them. This consumes storage because neither required WAL nor required rows from the system catalogs can be removed by VACUUM as long as they are required by a replication slot. In extreme cases this could cause the database to shut down to prevent transaction ID wraparound (see Section 24.1.5). So if a slot is no longer required it should be dropped.
Section 24.1.5 supplies the numbers behind “extreme cases”. Every table has to be vacuumed “at least once every two billion transactions”; warnings begin when the oldest XIDs reach “forty million transactions from the wraparound point”; and the server “will refuse to assign new XIDs once there are fewer than three million transactions left until wraparound”. Between the 200 million of autovacuum_freeze_max_age, mentioned only in a parenthetical, and those three million there is room to not notice. No disk alert fires in that range.
What Debezium’s heartbeat.action.query actually fixes
Debezium’s heartbeat.action.query fixes two shapes of stall, and they turn out to be one shape twice. The Debezium 3.7 PostgreSQL connector documentation describes the first under WAL disk space consumption: “Debezium then cannot confirm the LSN as replication slots work per-database and Debezium is not invoked. As WAL is shared by all databases, the amount used tends to grow until an event is emitted by the database for which Debezium is capturing changes.” The remedy is a table somebody writes to on a timer, so that “PostgreSQL then invokes Debezium, which confirms the latest LSN and allows the database to reclaim the WAL space.” Four paragraphs down, the same section names the second: on AWS RDS “a situation similar to the high traffic/low traffic scenario can occur in an idle environment”, because RDS writes to its own system tables invisibly, on a five-minute cycle. Different silent writer, same silence.
Check the default before you assume you have it. heartbeat.interval.ms is 0, and “The default behavior is that the connector does not send heartbeat messages.” A heartbeat advances the slot only while the connector is running, so it buys nothing in the failure this piece is about. It’s a fix for silence, not for death.