Ingestion Patterns: Advanced
Four ways a reader hurts its source
| Mechanism | What the extract does | What the application feels |
|---|---|---|
| Held snapshot | Reads a 400M-row table inside 1 transaction for 6 hours | Old row versions pile up, so every query reads more pages |
| Cache eviction | Pulls the cold pages of a full scan into memory | Hot rows get pushed out and are read from disk again |
| Disk and CPU contention | Reads sequentially at full speed | Every query sharing those disks waits longer |
| Locks and connections | Takes a table lock to dump, or opens 32 parallel connections | Writes queue behind the lock, or the app's connection pool runs dry |
Read from a replica, in short transactions
Keyset chunks instead of OFFSET
Paging with OFFSET makes the database read and discard every row before the page it returns, so each page costs more than the last. Keyset paging starts each chunk after the last key it read: every chunk is an index range read of 10,000 rows in its own short transaction, and the reader can stop or resume between chunks. On a 400M-row orders table read 10,000 rows at a time, the totals compare like this:
Throttle on the application's own signals
A consistent snapshot without a table lock
| Step | Reader action | Emitted downstream |
|---|---|---|
| 1 | Insert low watermark L | Nothing |
| 2 | SELECT order_id 5001 to 5003; all 3 read as placed | Nothing yet |
| 3 | Insert high watermark H | Nothing |
| 4 | Read the log from L to H: order 5002 changed to shipped | 5002 shipped, from the log |
| 5 | Reach H in the log and drop 5002 from the chunk | 5001 placed, 5003 placed |
A nightly extract on a replica pages through orders with OFFSET in 1 transaction lasting 5 hours. The replica kept canceling it, so the team turned on snapshot reporting to the primary. It now finishes, but checkout latency on the primary creeps up every night. What fixes the cause?
The transactional outbox moves the second write into the first system. Checkout inserts the event as a row in an outbox table inside the order's own transaction, so both rows commit or neither does. A separate publisher reads committed outbox rows and sends them to the broker, retrying until it accepts. The animation runs 3 orders through both designs during a 2-second broker outage.
Designing the outbox table
| Column | Why it is there |
|---|---|
| id | The dedupe key consumers store, and the polling publisher's read order. Never reused. |
| aggregate_id | The broker partition key, so each order's events land in 1 partition, in order. |
| event_type, schema_version | Consumers route and parse by declared type and version; versions change by expand-contract. |
| payload | The event as its data contract defines it, built inside the transaction. Internal columns stay out. |
| published_at | Polling publishers only: NULL until the broker acknowledges the row. |
Polling publisher or CDC publisher
- Queries the outbox every few seconds; delay is about the poll interval
- Adds query load on the primary at every poll, even an empty one
- Writes published_at per acknowledgement: 1 extra write per event
- Needs only SQL access
- Reads outbox inserts from the log; delay under a second
- Adds no query load: the log is written anyway
- Lets outbox rows be deleted soon after insert
- Needs a running log reader whose retention and failover you own
At peak, a payments service writes 3,000 outbox rows per second on a primary near 70% CPU. Fraud scoring needs each event within 1 second, and log-based CDC already runs for 2 other services. Which publisher fits?
The gap a bookmark poller falls into
Sequence ids are assigned at insert, but rows become visible at commit. Transaction A takes id 207, transaction B takes 208, and B commits first. A poller that tracks id > last_id sends 208, moves its bookmark to 208, and never sees 207 when A commits a moment later. Polling on published_at IS NULL keeps a late-committing row visible until it is sent:
This poller runs every 2 seconds, then stores the highest id it sent as the next bookmark. Every order has an outbox row, yet a few orders a month never reach consumers. Why?
- BufferAbsorb now, drain laterHold the surge in durable storage. Costs freshness and disk, and fails when the surge outlasts the buffer.
- Signal the producerAsk it to slow downReturn 429 with Retry-After. For pull-based sources, stop pulling; for credit-based ones, stop granting credits. Works only if the producer holds data while it waits.
- Drop selectivelyDiscard by ruleSample, shed low-priority event types, or keep only the latest value per key. Works only where consumers agreed to the loss.
Classify the source before choosing
| Source | Can it wait? | Can it replay? | Response |
|---|---|---|---|
| Partner order webhooks | Yes, retries for a limited window | Within that window | Buffer; return 429 near full |
| Mobile clickstream | No, the app discards what it can't send | No | Buffer, then sample under pressure |
| Internal payment events | Yes, rows wait in its outbox | Yes, from the outbox | Signal; the outbox is the buffer |
| Vehicle location pings | No | No | Keep the latest per vehicle, drop the rest |
| Log-based CDC | Yes, but the database holds its log meanwhile | Within log retention | Drain fast into your own log; slow down after it |
Sizing the buffer you depend on
When backpressure reaches the database
Webhooks and clickstream pass the admission rule. The CDC reader writes straight to the durable log, so the database never waits on a slow drain.
An admission rule that uses all 3
A fleet of 50,000 scooters sends GPS positions every 2 seconds with no retries. The only consumer is a live map of each scooter's current position. The shared buffer nears full during a surge. Which response fits?
One resync, 300 tenants waiting
| Scheduler | A small tenant's 09:05 batch waits | acme's resync finishes after |
|---|---|---|
| First in, first out | 250 minutes or more | About 250 minutes |
| Fair across tenants | 3 minutes at most | About 313 minutes |
Deficit round robin across tenant queues
Weights express plan tiers: a weight-4 tenant gets 4 times a weight-1 tenant's share while both have work. Resetting deficit for empty queues stops an idle tenant from banking credit and bursting later. A batch larger than its credit waits extra rounds, so large loads should arrive as keyset chunks.
When sharing itself is the risk
| Level | How it works | Protects against | Cost |
|---|---|---|---|
| Fair scheduling | Per-tenant queues sharing a worker pool by weight | Queue starvation | Lowest; a bad payload can still crash shared workers |
| Per-tenant quotas | A token bucket per tenant on rows or bytes per second at admission | A single tenant flooding the buffer | Tenants over quota wait or get 429 |
| Bulkheads | Separate worker pools per tier or for the largest tenants | Crashes and memory blowups spreading | Idle capacity in every pool |
| Dedicated cell | A full ingestion stack for a single tenant | Every shared resource | Highest; for tenants whose load would dominate any pool |
Per-tenant observability
Carry tenant_id in every record's structure
Ingestion stamps tenant_id at admission and rejects records without it. From there it is a required envelope field, part of every table's primary key and a partition column in storage. Recovering the tenant later by joining to an accounts table fails when it matters most: when 2 tenants each have an order 1001, when an account moves between tenants, or when a departing tenant's rows must all be found and deleted.
Tenant boreal uploads 40 GB JSON files that exhaust worker memory. Each crash fails every batch on that worker, whichever tenants they belong to. Fair scheduling is already in place. What's the smallest change that protects the other tenants?
Measure before you move
The reconciliation is a left join from orders to events, counted per hour so failures line up with incidents. count(DISTINCT ...) keeps duplicate deliveries from hiding a gap. The mirror query, from events to orders, counts phantoms.
The migration in 5 phases
| Phase | Change | Exit check |
|---|---|---|
| 1. Expand | Add the outbox: checkout writes an order_placed row in the order's transaction, still publishing directly | Every new order has exactly 1 outbox row |
| 2. Shadow | A CDC publisher fills a new topic, order_events_v2, that no consumer reads yet | Reconciliation on v2 shows zero missing and zero phantom for 7 days |
| 3. Cut over | Move consumers to v2 in turn: search, warehouse, fraud, email, invoicing, shipping | Each consumer's output matches its v1 output across the overlap |
| 4. Contract | Delete the direct publish from checkout | v1 receives no new events |
| 5. Clean up | Retire the v1 topic once its retention passes; keep outbox rows 7 days | Nothing reads v1 |
The overlap at cutover
Events on v1 carry no outbox id, so a consumer switching topics can't dedupe across the 2 streams by id. It switches on a business key instead: it notes the time of its last v1 event, reads v2 from 10 minutes before that, and upserts on (order_id, event_type), unique because an order is placed once. Duplicates in the overlap collapse, and any event v1 lost in those 10 minutes arrives from v2.
Shipping switches to v2. It starts reading v2 from 10 minutes before its last v1 event and dedupes on outbox id. What goes wrong?
Target state, 3 of the 6 consumers shown: a single transaction writes the order and its outbox row, the log reader publishes outbox inserts keyed by order_id, and consumers dedupe on outbox id.
What the new path costs to run
- ▸Log retention: a stalled reader pins log on the primary, so set a retained-bytes ceiling. A reader that crosses it gets dropped and re-snapshotted.
- ▸Outbox growth: around 9.2M rows a month, deleted after 7 days in off-peak keyset chunks.
- ▸Failover: a new primary may lack the reader's log position. Replay outbox rows by id range within the 7-day retention.
- ▸Payload ownership: checkout builds the event, so its schema falls under the data contract.
- ▸Duplicates: delivery is at least once, so every consumer stores processed outbox ids and skips repeats.
Phase 2 has run 7 days. Reconciliation on v2 shows 12 orders missing, all committed within 2 minutes before a database failover. What should the team do?
> Back at Shopify at noon on Black Friday, the sneaker drop tests every part of this lesson at once. The CDC reader that seeded the orders stream weeks earlier did it in watermark chunks on a replica, so the sale starts with a complete stream and no bloated tables. Each order commits with its outbox row, so a broker restart mid-drop costs a few seconds of delay instead of lost shipments. The surge fills the ingestion buffer, clickstream gets sampled, order events keep their reserved headroom, and the CDC reader keeps draining the log so the shared database never fills its disk. Deficit round robin keeps every other merchant's orders minutes fresh while the hot merchant's backlog drains, and the worst-tenant lag query is the number the on-duty engineer watches.
Read live databases gently, publish events reliably, and keep a single tenant's surge from starving the rest.
- Category
- Pipeline Architecture
- Difficulty
- advanced
- Duration
- 38 minutes
- Challenges
- 11 hands-on challenges
Topics covered: Reading Live Databases Safely, The Transactional Outbox Pattern, Backpressure at the Ingestion Layer, Multi-Tenant Ingestion Fairness, Dual-Write to Outbox + CDC
Lesson Sections
- Reading Live Databases Safely
Every query an extract sends to a production database competes with the application the database serves. Checkout wants a few milliseconds per query and a cache full of hot rows, while a bulk extract wants every page of a big table once. Left alone, the extract wins because it asks for more, so a safe reader is built to lose. Four ways a reader hurts its source Databases that keep multiple row versions leave each updated row's old version in place until no open transaction could still read it, s
- The Transactional Outbox Pattern
When an order commits, checkout has 2 jobs: save the order and tell shipping. The database and the broker are separate systems, and no transaction spans both. If checkout commits first and then publishes, a crash or broker outage between the 2 steps loses the event. Publishing first fails the other way: a rollback leaves shipping with an event for an order that doesn't exist. In the dual-write variant, the order at second 3 arrives while the broker is down: its row commits but its publish fails,
- Backpressure at the Ingestion Layer
Backpressure travels upstream from a slow stage to a faster one until it reaches the pipeline's edge. Ingestion is that edge, and the next hop upstream is a system you do not run. When arrivals outpace what ingestion can drain, you have 3 possible responses. Which of them are open to you depends on the source. Classify the source before choosing Sizing the buffer you depend on A worked surge: traffic normally runs at 2,000 events per second and workers drain 3,000. A promotion pushes arrivals to
- Multi-Tenant Ingestion Fairness
A multi-tenant ingestion layer runs many customers' data through the same workers and queues and writes it to shared sinks. Sharing keeps hardware busy and cheap, and lets the largest tenant's worst day land on every tenant. Fairness means each tenant's freshness depends on its own load and plan, whatever its neighbors are doing. One resync, 300 tenants waiting A shared queue serves 301 tenants. At 09:00, tenant acme requests a full resync, and 50M rows enter the queue. The other 300 tenants eac
- Dual-Write to Outbox + CDC (concepts: Change Data Capture)
A marketplace's checkout service publishes order_placed to the broker inside its database transaction before commit. It logs and ignores publish errors so checkout never fails on them. Its 6 consumers are shipping, invoicing, search, fraud and email, plus warehouse ingestion. A 30-day reconciliation against 9.2M orders found both failure kinds from the outbox section: The missing events cluster around broker restarts, when the publish failed and the commit went ahead. The phantom events come fro