Transactional vs Analytical Workloads

A checkout service issues 2,000 transactions per second, each touching a handful of rows by primary key in under 5 ms. An analyst runs one query: revenue by country by week over the last 12 months. Both hit 'the orders table' — and they want opposite physical layouts. OLTP is many small reads and writes of individual records; OLAP is few, enormous scans that aggregate millions of records. A storage engine optimized for the first is pathologically bad at the second, and no amount of indexing fully hides it.

Why the layouts diverge, mechanically:

The column-store numbers are worth internalizing: reading three columns out of fifty from a billion-row table touches ~6% of the data, and compression ratios of 5–10x are routine because equal values sit together (dictionary + RLE + delta encoding). That is why one analyst query on a columnar engine can run in seconds where the same predicate on a row-store replica takes minutes and a CPU core. Conversely, that columnar engine is miserable at UPDATE orders SET status='shipped' WHERE id=? — it must rewrite segment files — which is why warehouses favor append-plus-merge (or pure append) load patterns. Row stores, meanwhile, answer a primary-key read with one index seek plus one heap fetch: single-digit milliseconds regardless of table size, which is exactly what a checkout path needs and exactly what a scan destroys.

The middle ground exists and is increasingly the default: HTAP engines (SQL Server with columnstore, Postgres with columnar extensions, SingleStore, TiFlash replicas) keep a row store for serving and a columnar copy for analytics in one system, refreshed continuously. That collapses the pipeline but does not erase the trade: heavy analytical queries still contend for the same node's resources unless the engine isolates them (separate replicas, workload groups, or resource governors — which you should confirm exists before betting latency-sensitive serving on it).

Interview framing: name the workload by its shape — request-per-key versus scan-over-range — then derive layout from that, rather than reciting 'SQL for transactions, NoSQL for analytics' (which is wrong anyway; the axis is row-vs-columnar, not relational-vs-not). Also be ready for the cost side: OLTP rows are expensive to write (every index must be maintained, every constraint checked, every WAL record flushed) while OLAP queries are expensive to serve — which is why mixing them on one node fails in both directions. The most common way teams discover this: reporting queries aimed at a serving replica, the next chapter.

The dashboard's 'export all orders from last quarter with customer details' is 'just a read — the DBA said read replicas are free'. At 14:00 it runs against a replica; the query scans 40M rows, spills to disk, and saturates the replica's I/O. Checkout's read routing sends 30% of traffic to that replica; p99 climbs from 40 ms to 900 ms; the alert fires — and the warehouse team's response is that they were told to read from replicas. The mistake is treating replicas as free read capacity rather than as serving capacity with a defined job.

The degradation paths, each measurable:

1. I/O saturation:      big scans evict the buffer pool → subsequent point reads go to disk
                        (µs → ms), p99 explodes even though CPU looks idle
2. CPU saturation:      complex joins/aggregations pin cores; the replica's capacity
                        intended for user reads is consumed by one query
3. WAL/sync pressure:   long-running transactions hold snapshots; replication lag grows;
                        failover promotes a node that is minutes behind
4. Lock/queue effects:  on MySQL replicas, long SELECTs can block; on both engines,
                        statement queues add latency variance to every other query

The fixes, in the order you should propose them:

1. Isolate analytically-shaped reads onto infrastructure whose job they are. A dedicated analytics replica (or the warehouse) absorbs the scans; user-facing reads keep a replica pool sized for point lookups. This is capacity planning by workload shape — the same split the last chapter derived.
2. Impose limits. statement_timeout (Postgres, e.g. 5 s for the serving pool), resource governors/workload groups (SQL Server, MySQL 8 resource groups), query queues, and per-tenant concurrency caps. A killed query is a support ticket; an unbounded one is an outage.
3. Materialize what dashboards actually need. Nightly or hourly rollups (revenue_by_day_country) turn a 40M-row scan into a 90-row read — precomputation is the oldest performance technique and still the best first move for aggregate dashboards.
4. Route by intent, not by 'it is a SELECT'. Read routers that classify by query shape (or, better, by endpoint: /api/orders/:id vs /analytics/export) prevent the next 'harmless read'. Connection-level separation — separate pools for app and BI tools — is the minimum viable version of this.
5. Scale out the serving tier read-side: replicas for point reads, pgbouncer for connection storms, and cache layers for hot keys — none of which helps if scans still share the pool.

Detection deserves a note for interviews: the signature is replica p99 with normal primary load, plus pg_stat_activity/processlist showing one or two long scans, high shared_blks_read (disk reads) per statement, and growing replication lag. Have a first-response script: identify the statement, cancel it (pg_cancel_backend), cap the user, and only then discuss prevention.

An interview answer that lands states the principle — replicas are for reads with the same SLA as the serving path; analytics is a different SLA and gets different infrastructure — then lists limits and materialization as the guardrails. That raises the natural next question: how does the warehouse get fresh data in the first place? The answer — change data capture from the database's own log — is the final chapter of this topic.

The nightly job runs SELECT * FROM orders WHERE updated_at >= yesterday at 02:00. Two problems surface immediately: rows updated without touching updated_at are missed forever; and when a backfill updates 3M rows at 15:00, the warehouse is wrong until tomorrow. Change data capture fixes both by reading the database's own log of changes — the write-ahead log already records every physical mutation, so CDC taps it instead of re-querying tables.

app writes row ─► WAL (every insert/update/delete, in commit order)
                     │
                     ├─ Debezium / pglogical / MySQL binlog reader / native connector
                     ▼
              change stream (ordered per partition/tx, with LSN/gtid + before/after images)
                     │  exactly the committed changes — including ones no updated_at touched
                     ▼
              connector → Kafka / Kinesis / Pub/Sub → sinks:
                warehouse (upsert by PK) · search index · cache · another service

What CDC gives you, stated as engineering properties:

• Completeness: every committed change appears, including deletes (the pattern-query approach structurally misses deletes and out-of-band updates — application-layer writes, migrations, DBA fixes).
• Ordering: changes stream in commit order per transaction, so a downstream projection can apply them without reordering hazards — this is what makes INSERT a, INSERT b in one transaction safe for consumers.
• Low freshness: seconds rather than a day; streaming sinks keep analytics near-real-time without hammering the source with queries.
• Decoupled source load: one sequential log read instead of repeated indexed scans over growing tables — the OLTP cluster keeps serving while the ecosystem consumes its tail.

The sharp edges, because interviews ask for them: snapshot bootstrap (a new connector must first take a consistent snapshot of existing rows, then switch to streaming — done correctly, without a gap or duplicate window); schema evolution (column adds/renames flow through the stream and must be handled downstream — see Schema Evolution and Compatibility); idempotent sinks (redelivery happens; warehouse sinks upsert on primary key or use merge semantics keyed on the log position); replica-slot growth (Postgres wal_level=logical replication slots grow unboundedly if the consumer stalls — monitor slot lag or you fill the disk and block checkpoints, taking the primary down); and PII propagation (CDC copies everything, including columns you would never export — masking policies must live at the connector).

Choosing the transport for the changes: direct connector-to-sink (simplest, Debezium to a warehouse connector) when there is exactly one consumer; connector to a log (Kafka) when there are several consumers with independent progress, replay needs, or different sinks — the log becomes the shared distribution point and gives backfills for free (Message Queues and Brokers covers the replay mechanics); and query-based replication only when the source cannot expose a log (some SaaS systems), accepting its polling and completeness costs.

Delivery-guarantee summary to say out loud: at-least-once from the log to the sink, so make sink application idempotent (upsert by PK + position), and treat full refreshes as the reconciliation net. This outbox-and-projection machinery — where the write model feeds independently built read models — is exactly the boundary the next topic formalizes: CQRS Pattern.