Data pipelines and analytics
Batch, streaming, OLTP versus OLAP, and getting data out without hurting production.
OLTP and OLAP
beginnerTransactional databases are tuned for many small reads and writes of individual rows, and they store data row by row, so fetching one complete record is one contiguous read. Analytical stores are tuned for scanning a few columns across billions of rows, and store data column by column, so the columns a query does not name cost nothing at all to skip.
That layout difference is the whole performance story, and it compounds. Column pruning means a query touching three of sixty columns reads a twentieth of the data. Compression then works far better, because a column holds values of one kind with low variety, so run-length and dictionary encoding do real work where mixed row data resists them. Vectorised execution processes a batch of values per instruction rather than a row at a time.
Running heavy analytics on the production database puts the two workloads in competition for the same buffer pool, the same disk and the same connection slots. A single analyst running a wide scan can evict the working set that was serving customers, which is how a dashboard causes an outage without anything being obviously wrong.
The order of remedies is worth knowing, because most teams jump to the last one. A read replica for reporting removes the contention for a small cost and no new technology. Materialised views precompute the expensive aggregates. Only when scans genuinely need to cross billions of rows does a warehouse earn its complexity, and by then the argument makes itself.
Modelling differs too. Transactional schemas are normalised so a fact lives in one place; analytical schemas are deliberately denormalised into facts and dimensions, the star schema, because a join across billions of rows is expensive and storage is cheap. Bringing normalised thinking to a warehouse produces queries that are correct and slow.
The two ends have been converging, and it is worth knowing where. Postgres has columnar extensions, DuckDB puts an analytical engine on a laptop, and lakehouse formats such as Iceberg and Delta bring transactions to files in object storage. The distinction that stays true is the physical layout, and that is what to reason about rather than the product names.
Why are analytical schemas deliberately denormalised?
Answers are generated and can be wrong. The topic above is the reviewed version.
Batch and streaming
intermediateBatch processes a bounded chunk on a schedule. The inputs are fixed, so a run is reproducible; a failure is fixed by re-running it; and reasoning about correctness is tractable because the data stops moving while you look at it. The result is as old as the last run, which for most reporting is entirely acceptable. Streaming processes events as they arrive, which buys latency and charges in every dimension that batch made simple. The data never stops, so there is no natural moment at which a result is final, and the hard problems arrive with it: events out of order, events arriving late, and deciding what a window means when both are true.
Event time and processing time is the distinction that makes streaming difficult. An event happened at one moment and reaches you at another, and grouping by when it arrived produces figures that are wrong in ways nobody notices: a mobile app that was offline for an hour delivers its events at once, and they land in the wrong window. Windowing by event time is correct and requires deciding how long to wait for stragglers, which is what watermarks express.
So a streaming pipeline needs an explicit answer to what happens to data arriving after its window closed. Discard it, and totals quietly under-report during exactly the incidents you care about. Update the emitted result, and every downstream consumer must handle a figure that changes after publication. Hold windows open longer, and you have traded back the latency you paid for. There is no default that is right, only a decision.
The pattern that resolves most of this is to keep the log and treat processing as replayable. If events are durable and ordered, a bug in the pipeline is fixed by correcting the code and reprocessing from a position rather than by reconciling by hand, which is the operational property that makes streaming survivable. That is also why the same architecture serves both: batch is a replay over a bounded range.
Most organisations need batch and believe they need streaming. The honest test is whether a decision changes when the data is minutes old rather than hours: fraud, pricing, alerting and operational routing pass it, and a dashboard read each morning with coffee does not. Streaming for a report nobody reads before nine is complexity bought with no return.
Why group a stream by event time rather than by arrival time?
Evidence · 1 source
- 01Akidau et al., The Dataflow model (VLDB 2015)
The event time against processing time distinction, watermarks as the mechanism for deciding how long to wait, and the framing of late data as a choice between discarding, restating and waiting.
Answers are generated and can be wrong. The topic above is the reviewed version.
Change data capture
advancedChange data capture reads the database's own replication log and turns each committed change into an event. Every insert, update and delete, in commit order, with no cooperation from the application and no query load on the primary. The database is already writing that log for its replicas; CDC is a second reader of it.
Compared with polling an updated_at column it misses nothing. Polling cannot see deletes, because a deleted row simply stops appearing; it cannot see intermediate states, because two updates between polls look like one; and it adds load to the primary on a schedule forever. The log has all of it, already ordered, already written.
The structural argument is stronger than the practical one. Dual writes, where the application writes to the database and then publishes an event, have no atomicity: the process can die between the two, and nothing detects the gap. CDC has one commit, and everything downstream is derived from it, so the events cannot disagree with the data by construction. That is the same reasoning as the outbox pattern, and CDC is its lower-maintenance form.
What you take on is a coupling to the database schema. Log events carry columns, so a rename that would be invisible behind an API breaks every consumer, and a consumer now depends on a physical detail rather than a contract. The usual discipline is a translation layer that maps log events into published domain events, so the schema can move without every downstream service moving with it.
The operational details that bite are worth knowing in advance. A replication slot that no consumer is reading holds write-ahead log on the primary, and a stalled consumer over a long weekend can fill the disk of the database it was reading. Initial snapshots of a large table are heavy and need a strategy. And delivery is at least once, so consumers must be idempotent regardless of how ordered the source is.
Used well, this is how one source of truth feeds a search index, a cache, a warehouse and three downstream services without any of them being able to drift, and without the application knowing they exist. That last part is the real prize: adding a consumer requires no change to the system that owns the data.
What coupling does CDC introduce that publishing domain events does not?
Answers are generated and can be wrong. The topic above is the reviewed version.