Your poller can't see what dies young

Say you work on a chat assistant. Each conversation is saved in a database as a series of turns (messages). Copies of real conversations go into an evaluation set, or eval set, which graders, human and automated, score for things like harmful answers.

The copying is done hourly by a poller (a job that re-queries the database on a timer). A chat has no end button, so “finished” means ten minutes with no new turn (the cool-down). The run at 15:10 copies every conversation whose last message landed between 14:00 and 15:00, its window. Queueing and startup make its query reach the database a few minutes late, at 15:18 say: the read delay.

Three made-up conversations:

• A lasts from 14:05 to 14:20. Nobody deletes it.
• B is one question at 14:30 and one answer at 14:40. At 14:45 the user deletes it.
• C lasts from 14:50 to 14:58. The user deletes it the next morning.

All three belong to the 15:10 run. At 15:18 it copies A and C; B has been gone for 33 minutes. The scheduler marks the run green.

Nothing errored. B was hard-deleted (removed outright, no marker left), so the query correctly returned nothing, and a job can’t report what it never saw. Any job that copies “finished” rows on a schedule has this blind spot.

The loss isn’t random: the sooner a conversation is deleted after its last message, the less likely the job read it. Plausibly the ones people delete within minutes skew toward what the eval set exists to find, such as a jailbreak attempt (a prompt written to talk the model out of its rules). That skew is a hypothesis, and a poller makes it unmeasurable, because the evidence is what it lost. The eval set is conditioned on survival (survivorship bias): its numbers, such as the share of harmful answers, describe only conversations that lived long enough to be read.

The lag is the time from a conversation’s last message to the read: cool-down, plus the wait for the next run, plus the read delay. In the toy hour A’s lag is 58 minutes, B’s 38 and C’s 20; B was deleted after five. In the figure, conversations ride a belt from their last message, a delete is a trapdoor, and the poller is a camera one lag downstream (drag it, or use the arrow keys). The hopper’s tally counter, counting every write, is the next section’s change log.

The model

States, not transitions

An insert, update or delete is a transition. The table’s state is whichever rows exist at one instant. A poller sees only states, at the instants it reads. So anything inserted and deleted between two reads never appears, and a delete is never an event, because it removes the thing the poller reads. A poller can notice a row vanished only by rescanning everything it saw before; one that asks “what finished since 16:00?” can’t do even that.

The replay plays ten changes to four rows, a to d, to two observers: a poller that snapshots the whole table every 4 minutes, and CDC (change data capture), which reads the database’s change log, the ordered record of every committed change that most databases already keep for crash recovery and replication.

states vs transitions · one log, two observersreplay

replay time

12.0m

At 12 minutes the poller has seen two of the four rows and three of the seven row versions; CDC has all ten changes, including the three deletes.

In general terms, with entity for the thing being copied:

• A run fires every P minutes, at times T.
• The run at T selects entities whose last write e falls in [T − C − P, T − C) (start included, end excluded), where C is the cool-down.
• The run reaches the database q minutes after it fires: the read delay.

The toy hour used P = 60, C = 10 and q = 8, the same settings as the field notes.

For B, T(e) + q is 15:18 and d is 14:45, so B is lost.

The lag is L = T(e) + q − e: a fixed C + q plus the wait for the next run. With writes spread evenly against the schedule, L = C + q + U·P with U uniform on (0, 1]. With the toy settings the lag runs from 18 to 78 minutes.

Let F(x) be the fraction of deleted entities already gone x minutes after their last write (a cumulative distribution function, CDF). An entity with lag L is lost when it dies before L, with probability F(L). Averaging over every lag the schedule produces gives the loss rate among deleted entities:

P(lost | deleted)
  = E[F(L)]
  = (1/P) ∫ F(x) dx
    over x in [C+q, C+q+P]

lognormal F, μ = ln(median):
∫ F(x) dx = x·F(x)
  − e^(μ+σ²/2) Φ((ln x − μ − σ²)/σ)

The second half is the closed form for a lognormal F (log-lifetime is a bell curve: most lifetimes short, a few very long), with μ the log of the median, σ the spread and Φ the bell curve’s CDF. It is p_lost_formula in capture_sim.py; a seeded simulation agrees with it to within 0.0014 at every point checked.

Two distributions decide your loss: how long your entities live after they become eligible for capture, and how long your pipeline takes to look. You can measure both today.

Reproduce it

The shutter

Each row is a conversation that will be deleted: its bar runs from last write to delete, the dot marks its run’s read, green was captured and red was already gone. The big numbers are capture rates over the whole lifetime distribution rather than the rows drawn. At the field-note settings, 1 of the 28 drawn conversations is still there when the read arrives.

Drag poll every down to 1 minute: capture rises only to 13.6%, because the cool-down and read delay alone add up to 18 minutes, longer than most of these conversations live. Set cool-down and read delay to 0: the baseline cohort (lifetimes fitted to the field data) reaches 93.9%, and the adversarial cohort still loses 28.0%. That cohort is hypothetical: people probing the model who delete much faster, with an illustrative (not measured) median lifetime of one minute. Press capture with CDC: both reach 100%, because the change log records the insert and the delete as they commit, and P, C and q never enter into it.

The math

What a lag costs

The lifetimes come from a field sample of 55 conversations created and deleted on the same day, median 4.2 minutes from creation to delete. Pinning a lognormal’s median at 4.2 and fitting σ by binned maximum likelihood (the spread that makes the observed bin counts most likely) gives σ = 1.35. The fit is close below 30 minutes, where most of the loss sits; above that, the sample has only three conversations:

The sample counts from creation, but exposure starts at the last write, which is later, so every loss rate below is a lower bound.

At a fixed lag the loss is F(L): 14.4% at one minute, 55.1% at five, 74.0% at ten, 85.9% at eighteen, 92.7% at thirty and 97.6% at an hour. Averaged over the default lags of 18 to 78 minutes, the poller loses 95.3% of same-day deletes (simulation: 95.3%).

The measured 80.8% is below the model’s 95.3%, plausibly because the lifetime sample holds only same-day deletes, while the 672 include conversations created days earlier, which any poller catches. The two agree if about 15% of the deletes were of such older conversations. That is a hypothesis; I did not measure the split.

The obvious fix is to poll more often. Share of deleted conversations lost, from capture_sim.py:

Polling sixty times more often moves baseline loss from 95.3% to 86.4%. The floor is F(C + q): even continuous polling captures at most 14.1%.

Why this is bias, not noise

Loss at random only shrinks the sample; loss that depends on what you measure moves the percentages. Suppose (illustratively: these are not measured rates) 2% of conversations are unsafe. Safe ones are deleted at the field-note rate of 1.7%; unsafe ones more often and sooner, with a median lifetime of one minute instead of 4.2. survivorship.py computes the unsafe rate the default poller’s eval set would report:

Nothing in the eval set tells you which row you are on, because the evidence was deleted. This is Abraham Wald’s 1943 problem, estimating aircraft vulnerability from the damage on the planes that came back, turned into a pipeline. Human review sampled from the same set inherits the same skew.

Windows

Gaps or duplicates: pick one

The cool-down exists because a user can always add another turn. Without it, a windowed extractor (one that copies rows in time slices) would grab many conversations half-finished, then again later. The cool-down guesses that ten quiet minutes means done. Stream processing calls such a guess a watermark, and the Dataflow paper notes that when one is too fast “there may be late data that arrives behind the watermark.”

So the knob trades one error for another. window_gap.py measures both at a 5-minute poll with no read delay. Partial captures (copied before finished, so copied twice) use illustrative conversation shapes; deletes use the field-note lifetimes:

No setting gets both columns to zero. “Done” is a transition, the moment a conversation stops changing, and an extractor that reads states can only guess at it.

The one-second hole

Windowed extraction has a second, smaller leak. The logged window 14:00:00 to 14:59:59 is the fingerprint of an end bound computed as end − 1 second for SQL’s BETWEEN, which includes both ends. That is right for whole-second columns, but Postgres timestamps resolve to 1 microsecond. Against a microsecond column, created_at BETWEEN '14:00:00' AND '14:59:59' leaves out everything from 14:59:59.000001 to 14:59:59.999999, and the next window starts at 15:00:00.

Whether the bound reached SQL in that form in production is unverified; treat this as a hypothesis about that system and a lesson about yours.

The widget zooms in on one hour boundary: the hour ending there (W) in teal, the one starting (W+1) in violet. Pick a precision and press fuzz to throw 10,000 random timestamps at three window forms. Red dots were selected by no window, orange dots twice.

the boundary gap · hh:59:58 to hh+1:00:01property test

column precision second millisecond microsecond

fuzz 10,000 timestamps reset

window_gap.py runs the same check with a million uniformly random timestamps per precision (each must be selected by exactly one hourly window). A is BETWEEN s AND s + 1h − 1s, B is BETWEEN s AND s + 1h and C is >= s AND < s + 1h:

The exact rate for A at microsecond precision is 0.0278%, one second in every hour. The cool-down turns a lost row into a lost conversation: W skips any conversation with a write in the ten minutes after its end bound, which a write in the hole is, and W+1 skips it because it has no rows from 15:00 on. In the simulation, A loses 0.0325% of conversations’ final states and C loses none. If the A form did reach production, and last writes are spread evenly across the hour, the field-note volume of 1,496 threads per hourly window would drop about 10 final states a day: small, but never zero, and nothing reports it.

B fails the other way. It double-counts rows exactly on the boundary: almost never at microsecond precision, one time in 3,600 at second precision, or constantly if something writes on the hour.

Prior art

What’s known, and what’s under-said

None of the mechanism is new. Gunnar Morling’s five advantages of log-based change data capture states both halves: with polling “you might miss intermediary data changes that happen between two runs of the poll loop”, and “polling will not allow you to identify any records that have been deleted since the last poll.” Martin Kleppmann’s turning the database inside-out and Designing Data-Intensive Applications (first edition, chapters 11 and 12) give the fix in general form: treat the change log as the primary record, since “a materialized view is just a cached subset of the log.” An eval set built from production data is such a view.

What gets less attention: the CDC literature frames missed deletes as a sync-correctness problem, a copy that keeps a row it should have dropped. When the consumer is an eval or analytics set, the missed inserts matter more, and they are missed selectively by lifetime, so the loss is a bias you can compute in advance.

Fixes

Fixes, ranked

1. Capture transitions

Read the database’s change log instead of its state. Postgres writes every change to its write-ahead log (WAL) before applying it, and logical decoding turns the WAL into a stream of row changes: the output plugin callback fires for “every individual row modification … INSERT, UPDATE, or DELETE” (docs). Debezium, an open-source CDC tool, packages this as a connector. Underneath, a publication names the tables to stream, and a replication slot is the database’s bookmark for one consumer:

-- the slot pins WAL until its consumer confirms it
CREATE PUBLICATION eval_capture FOR TABLE turns;
SELECT pg_create_logical_replication_slot(
  'eval_capture', 'pgoutput');

The trade-offs:

• Deletes carry only the key by default: under REPLICA IDENTITY DEFAULT, Postgres records “the old values of the columns of the primary key” (docs). That is fine for an eval pipeline, since the content arrived with the inserts; use FULL only if a consumer needs the deleted row’s content.
• Slots retain WAL “even when there is no connection using them” (docs), so a stalled consumer fills the disk. Cap it with max_slot_wal_keep_size and the slot is marked lost instead, forcing a backfill from the table (Netflix’s DBLog interleaves log events with table reads for this). Alarm on slot lag.
• Delivery is at least once, so the consumer must be idempotent. Key it on (row key, log position).

If you can’t touch replication, use an outbox: the application writes an event row to an outbox table in the same transaction as the change, and a relay publishes those rows in order. The delete path is the one people forget:

with conn.transaction():
    conn.execute(
        "DELETE FROM turns WHERE conversation_id = %s", (cid,))
    conn.execute(
        "INSERT INTO outbox (aggregate_id, event_type, payload)"
        " VALUES (%s, %s, %s)",
        (cid, "conversation.deleted",
         json.dumps({"deleted_at": now.isoformat()})))
# a relay, or CDC on the outbox table alone, publishes in order

2. Capture at write time

Have the application emit conversation.turn_completed, with the turn’s content, through the same outbox. The evaluator then holds every conversation from the moment it exists and decides on its own clock when one is finished: still a guess, but over data it already has, so a late turn produces an update instead of a gap. The cost is coupling: the serving path now has a contract with the eval pipeline, and the event schema needs versioning. The evaluator also now holds content users may later delete, which makes the next fix mandatory.

3. Make delete a protocol, not a statement

Once data has been copied, a hard delete removes one copy. Make it the first step of a protocol that reaches every derived store. The first field note fails both ways: evaluation lost the conversations before any policy decided it should, and two stores outside the delete path kept them for 30 days or more.

• Soft delete first, purge later. Set deleted_at instead of removing the row, so pollers and consumers see the delete as a state change. Purge on a schedule once every derived store has acknowledged it.
• Propagate tombstones. Debezium follows each delete with a tombstone (the key, no value) so Kafka log compaction, which keeps only the latest message per key, can drop the key. A consumer can still “miss delete markers if it lags by more than delete.retention.ms” (24 hours by default), and an insert-only consumer drops tombstones by construction.
• Write the policy down. Should a deleted conversation be evaluated at all? Possibly yes as a safety label or aggregate, possibly no for content. That is a product and legal decision (GDPR Article 17, the right to erasure, and Article 19 on telling “each recipient to whom the personal data have been disclosed”). A poller makes it by race between the delete and the read, inconsistently, and nobody signs off.

4. Shrink the lag

This helps and never fixes it: the sweep table shows the floor, and a shorter cool-down raises partial captures. Do it if the transition fixes are months away, and measure the curve for your own lifetimes first.

Detect it

The check that catches it on day one

Keep a ledger: a count of conversations that reaches you by a different path than the extractor. In the field notes one existed by accident (the metrics stream); make one on purpose. Then check conservation by hour and by fate: every conversation the ledger saw must be captured, explained by a delete, or still open.

SELECT date_trunc('hour', l.last_turn_at) AS hour,
       count(*)                 AS ledger,
       count(c.conversation_id) AS captured,
       count(*) FILTER (WHERE c.conversation_id IS NULL
                          AND l.deleted_at IS NOT NULL)
                                AS lost_to_delete,
       count(*) FILTER (WHERE c.conversation_id IS NULL
                          AND l.deleted_at IS NULL)
                                AS unexplained
FROM ledger l
LEFT JOIN (SELECT DISTINCT conversation_id FROM captured) c
  USING (conversation_id)
-- skip the newest 2 hours: younger than the worst-case lag
WHERE l.last_turn_at >= now() - interval '2 days'
  AND l.last_turn_at <  now() - interval '2 hours'
GROUP BY 1
ORDER BY 1;

Apply the extractor’s own filters to the ledger side first, then page on unexplained > 0: that is a bug. Chart lost_to_delete / ledger. It is the poller’s survivorship rate, and it belongs on the eval set’s dashboard next to the scores it distorts.

Treat an empty run as a number to watch, not a success: emit the row count per window, alarm when it drops below the same hour last week, and make sure zero rows can’t crash the run (ThreadPoolExecutor(max_workers=0) raises ValueError).

Property-test the windows. Mirror the predicate your SQL actually uses, and generate timestamps at the column’s real precision:

import random

HOUR_US = 3_600 * 1_000_000

def in_window(t, j):  # mirror the SQL exactly
    return j * HOUR_US <= t < (j + 1) * HOUR_US

def test_every_row_in_exactly_one_window():
    r = random.Random(1)
    for _ in range(1_000_000):
        t = r.randrange(48 * HOUR_US)  # microseconds
        k = t // HOUR_US
        hits = sum(in_window(t, j) for j in (k - 1, k, k + 1))
        assert hits == 1, t

Run conservation and a positive control every hour, not in a postmortem. And before you debug the filter, the cool-down or the window math, ask whether the row existed when the reader looked. It is the cheapest hypothesis to test and the one people check last.

Try it

Run the experiments

Stdlib Python 3.9, seeded like the widgets, so the shutter’s drawn rows and the boundary widget’s fuzz counts match the scripts exactly.

cd experiments/the-poller-cant-see-what-dies-young
python3 -I capture_sim.py   # fit, E[F(L)] vs simulation, sweep
python3 -I window_gap.py    # boundary test, cool-down trade-off
python3 -I survivorship.py  # the biased unsafe rate

For your own system, replace two inputs in capture_sim.py: your lifetime histogram (time from eligibility to delete) and your scheduler’s P, C and q. If the result is not close to zero, the fix is capturing transitions, not editing the cron schedule.