Databases in Trading Systems

Before you start — this chapter leans on a handful of primer ideas:

• WAL and MVCC — how Postgres actually writes and why updated rows leave bloat behind: ch00f
• Streaming vs logical replication — the two ways a replica can follow a primary: ch00f
• Lock queues and the ALTER TABLE trap — how a “fast” migration freezes production without doing any work: ch00f
• pgbouncer pooling modes — session vs transaction vs statement, and what transaction pooling breaks: ch00f
• Tick data and kdb+ — what a tick store is and the query shape it exists for: ch00f

Read those first — 20 minutes there saves an hour here.

This is your declared weak spot, so this chapter goes deepest. The good news: trading systems use databases in a strongly opinionated, tiered way, and once you can articulate the tiering, every interview question about “how do you store X” has a slot to fall into. The second half is Postgres operational depth — replication, failover, pooling, and above all online schema migration — because that’s what “we need someone who can also touch the platform” interviews actually test. You run pgbouncer and a multi-tenant Postgres 17 on your own GCP host, and Redis as an ephemeral tier at work; use both as story hooks — it’s rare and it lands.

The tiering: hot, warm, cold

Rule zero: the hot path never touches a database. No exceptions, and interviewers will probe until you say it. A matching or strategy decision path budgeted in microseconds cannot tolerate a round trip to anything with a query planner, a lock manager, or a network hop to a storage tier. Sub-rule: it doesn’t touch a remote cache either — Redis at ~100µs+ RTT is just as disqualified as Postgres.

So where does state live?

Hot tier: memory + the event log

The engine’s working state — books, orders, positions, risk counters — is in-process memory, laid out for the access pattern (Part II material). Durability comes from the event log (ch13), not from a DB: append to a memory-mapped or O_DIRECT (write straight to disk, skipping the kernel’s page cache) sequential log, replicate to the standby, and that is the database of record. The phrase to use: “the log is the system of record; every database downstream is a derived, eventually-consistent view.” This inverts the enterprise mental model where the DB is truth and logs are exhaust — say the inversion explicitly, it’s the key idea of the whole Part.

Downstream consumers tail the log and project it into whatever store suits their query pattern. Which brings us to:

Warm tier: tick stores / time-series

The quant and ops query load: “give me every trade and top-of-book for BTC-perp across venues between 09:30 and 10:00,” “compute realized spread per venue per hour for the last quarter.” Billions of rows, append-mostly, time-ordered, scanned in ranges, aggregated by column. That shape is why columnar time-series stores own this tier:

• kdb+ (ch00f for the gentle intro) — the tradfi incumbent, and you should be able to say why it won rather than just name-drop it. (1) Columnar on-disk layout: a date-partitioned table is a directory per date, a file per column; “average spread over 3 months for one symbol” reads only the columns touched, at sequential-scan speed. (2) The same language, q, runs against in-memory real-time tables and on-disk historical ones — the canonical deployment is a ticker plant: a real-time database (RDB) holding today in RAM, appended from the feed, written down at end-of-day to the historical database (HDB), with q queries spanning both. (3) It’s a full programming environment, so the analytics run inside the store instead of hauling billions of rows out. (4) Decades of trust and installed base in banks. Costs: eye-watering per-core licensing, a famously terse language, key-person risk. Being conversant — columns, splayed/partitioned tables (splayed = one file per column on disk), RDB/HDB, why xasc (sort ascending) and aj (as-of join) matter — signals tradfi literacy even if you’ve never run it. As-of joins deserve one sentence in any answer here: “join each trade to the most recent quote at or before its timestamp” is the canonical tick-store query, and native as-of support is half the reason these engines exist.
• ClickHouse — the open-source columnar workhorse; crypto-native shops overwhelmingly land here. MergeTree tables ordered by (symbol, ts), aggressive compression (delta + zstd on timestamps and prices compresses hard), materialized views for rollups, ASOF JOIN built in. Operationally heavier than it looks — in plain terms: constant background compaction (merges), whole-part rewrites for updates/deletes (mutations), and its own coordination service (Keeper) to run replication — but the query performance per dollar is the draw.
• QuestDB / TimescaleDB — QuestDB: purpose-built TSDB, SQL with ASOF joins, strong ingest, simpler ops story than ClickHouse at smaller scale. Timescale: Postgres extension — you keep the Postgres operational model (next section applies verbatim) and get hypertables (one virtual table auto-partitioned into time chunks) plus compression; the right choice when your tick volumes are modest and you value one database technology to operate.
• Arctic / ArcticDB — Man Group’s open-source approach: versioned dataframe storage over object storage or LMDB (an embedded key-value store), Python-native. Less “database,” more “columnar dataframe store for research”; the research-cluster complement rather than the production tick store.
• The crypto-world equivalent: raw capture files (compressed JSONL or your normalized binary log) in object storage as the immutable record, loaded/projected into ClickHouse-or-similar for querying. Many crypto desks run exactly that and it’s a perfectly respectable answer — the log-plus-projection pattern again.

Cold tier: Postgres for reference data, accounts, compliance

Everything low-rate, high-value, relational, and audit-sensitive: instrument reference data (symbols, tick sizes, multipliers, venue mappings), accounts and permissions, credentials metadata, fee schedules, end-of-day positions and P&L snapshots, reconciliation results, compliance/audit records, config history. Tens to thousands of writes per second at most, but correctness and queryability matter, transactions matter, and this data feeds humans and regulators. Postgres is the default and nobody gets fired for it. This tier is also where your operational experience lives: you run a shared Postgres 17 behind pgbouncer for your own multi-tenant platform — say so.

Redis sits beside the tiers, not in them: ephemeral coordination state — sessions, queues, cursors, distributed-ish locks you don’t bet money on. The discipline is that nothing in Redis is the record of anything; lose it and you re-derive. You run this exact split at work; one sentence about “Redis is allowed to lose data by policy” shows tier thinking.

Postgres operational depth

Now the part you’re weakest on and interviews for platform-adjacent trading roles genuinely test. Four topics: WAL and replication, failover, pooling, and online migration.

WAL mechanics in one page

Every change in Postgres is written twice: to the write-ahead log (WAL) (ch00f walks it slowly) first, then to the actual table/index pages (“heap”) in shared buffers, which reach disk lazily at checkpoints. Commit = WAL flushed to disk (fsync), nothing more; crash recovery = replay WAL from the last checkpoint. If that sounds familiar, it should: Postgres is internally an event-sourced system — WAL is the event log, the heap is the snapshot, recovery is snapshot+tail replay. Making that connection out loud in an interview (“Postgres does internally what my engine does architecturally”) is a genuinely strong move because it’s true and it shows transfer.

Three consequences worth knowing, one at a time.

The durability knob. synchronous_commit decides whether COMMIT waits for the WAL fsync (durable, slower) or returns as soon as the record is in the WAL buffer (fast, but a crash can lose the last few hundred milliseconds of “committed” transactions) — a per-transaction trade of durability for latency. Concrete scenario: an internal metrics table can run synchronous_commit = off and take the risk for the throughput; the fills table a regulator will ask about cannot.

Checkpoint storms. A checkpoint is the moment Postgres flushes all its dirty table/index pages to disk so old WAL can be recycled. Mistune it and you get I/O storms: let max_wal_size grow too large and each checkpoint arrives with an enormous backlog to flush at once, spiking every query’s latency when it hits. checkpoint_completion_target is the smoothing knob — it spreads the flushing across the checkpoint interval instead of one burst, turning a periodic I/O cliff into a steady hum.

Torn pages. A Postgres page is 8 kB but a disk only guarantees atomic writes of a smaller unit, so a crash mid-write can leave a page half-old, half-new — “torn,” and unrepairable from ordinary WAL records alone. full_page_writes is the defense: the first change to each page after a checkpoint writes the entire page into the WAL, giving recovery a known-good copy to restore before replaying changes — hence the write-amplification spike right after every checkpoint.

Streaming vs. logical replication

Two different machines that ship the same WAL:

• Streaming (physical) replication ships WAL bytes; the replica is a block-for-block copy, replaying continuously. Same major version, same architecture, whole cluster or nothing. Replicas serve read-only queries, with visibility lag and one built-in conflict: WAL replay sometimes needs to remove a row version (the primary’s vacuum already cleaned it up) that a replica query is still reading, and the replica must then either cancel that query or pause replay and fall further behind. hot_standby_feedback is the escape hatch — the replica tells the primary “don’t vacuum row versions my queries can still see,” so queries stop dying, but the dead rows now pile up on the primary (bloat) for as long as replica queries run. That’s the trade: query cancellation on the replica vs. bloat on the primary. Sync vs. async: synchronous_standby_names makes commits wait for replica flush — durability across host loss, at latency cost. This is your HA mechanism.
• Logical replication decodes WAL back into row-change events and publishes them per-table: CREATE PUBLICATION / CREATE SUBSCRIPTION. The plumbing is a replication slot — the primary’s durable promise to retain WAL until this subscriber confirms it received it (think: a webhook queue that never truncates until the consumer acks) — plus an output plugin (e.g. pgoutput) that does the decoding. Replica is a live, writable database applying changes — so it can be a different major version (this is how near-zero-downtime major upgrades are done), a subset of tables, or a differently-indexed copy. Limitations to name: DDL is not replicated (schema changes must be applied on both sides — coordinate with the migration discipline below), sequences don’t replicate, and an abandoned replication slot pins WAL on the primary until the disk fills — the classic 3am logical-replication incident.
• CDC: the same logical decoding mechanism feeds Debezium-style change-data-capture into Kafka/ClickHouse — “DB as event producer,” the mirror image of your engine’s log-projection pattern.

Rule of thumb to say: streaming for HA/failover, logical for upgrades, migrations, selective copies, and CDC.

Failover: the Patroni pattern

Manual failover of Postgres is a pager-driven ritual; Patroni (an open-source agent that automates Postgres leader election and failover) is the standard automation. Shape: each Postgres node runs a Patroni agent; agents coordinate through a consensus store (etcd/Consul/ZooKeeper) holding a leader lease; the leader holds/renews the lease, replicas watch. Leader dies or loses the lease → healthiest sufficiently-caught-up replica (by WAL position) wins an election, promotes, others re-point. Client routing via HAProxy/vip-manager or Patroni’s REST health endpoints (/primary, /replica).

        ┌───────────────────────────────┐
        │  etcd: lease "leader = pg1"   │◄── pg1 renews every few seconds
        └───────────────────────────────┘
   pg1 (primary) ──WAL──► pg2 (replica)
        └────────WAL────► pg3 (replica)

   pg1 dies → lease expires → least-lagged replica (pg2) promotes
            → proxy re-points clients at pg2

Underneath the name, three concepts an interviewer actually probes. Fencing: the old primary must be prevented from accepting writes when it comes back — lease expiry plus demote-on-start; without fencing you get split-brain and divergent timelines. Data-loss window: async replication means promoting a lagged replica loses the tail — maximum_lag_on_failover bounds it; only sync replication makes it zero. Timeline forks: the demoted primary’s un-replicated WAL must be discarded — pg_rewind reconciles it back into the cluster as a replica:

 shared history ──1──2──3──┬── old primary keeps writing: A4──A5   (never replicated)
                           └── new primary writes:        B4──B5   (the surviving timeline)
 pg_rewind = cut A4–A5 off the old primary, rejoin it as a replica on the B timeline

Notice these are exactly your hot-standby failover problems — fencing, gap-free handover, split-brain — wearing DB clothes. Say that; it converts your engine experience into DB credibility.

Connection pooling: pgbouncer

You literally run one, so own this. Why pooling exists: each Postgres connection is a backend process with real memory cost, and connection storms (a fleet of app instances × their pools) collapse a server that’s happy at 50 active backends. pgbouncer multiplexes thousands of client connections onto tens of server connections.

The modes, because this is the standard probe:

• Session pooling — server conn held for the client’s whole session; safe, least sharing.
• Transaction pooling — server conn borrowed per transaction; the production default and the big multiplexing win.
• Statement pooling — per-statement; forbids multi-statement transactions; rare.

Transaction pooling buys its multiplexing by breaking session state: no session-level PREPARE/prepared-statement caching by name (pgbouncer 1.21+ added protocol-level support), no SET that must persist, no session advisory locks, no LISTEN. Your concrete hook: your platform runs host-native pgbouncer on :5432 routing by database name to a loopback Postgres, ~25 server conns per tenant DB against a 500-client ceiling — that’s transaction-pooling economics in one sentence, from your own infra, plus a real caveat you documented yourself (your ReadySet cache tier bypasses per-tenant auth — an isolation trade you made consciously). Interviewers remember candidates who volunteer the caveat they own.

Online schema migration — the flagship skill

The scenario every platform-flavored interview reaches: “the orders table has 500M rows and the system trades 24/7. Add a column / change a type / add an index. Go.” The framework: know your locks, expand-migrate-contract, backfill in batches.

Lock analysis first. DDL takes locks; the killer is ACCESS EXCLUSIVE (the strongest table lock — conflicts with everything including plain SELECTs; lock levels are decoded in ch00f). Worse trap: lock queuing — your ALTER waits behind one long-running query, and every subsequent query (even reads) queues behind your waiting ALTER. A “fast” migration can freeze production for minutes without doing any work:

 time ──►
 [long-running SELECT]════════════════════╗  holds ACCESS SHARE on orders
        ALTER TABLE orders ...  ──waits──►║  wants ACCESS EXCLUSIVE — queued
              SELECT ...        ──waits──►║  queued BEHIND the waiting ALTER
              SELECT ...        ──waits──►║  queued
              UPDATE ...        ──waits──►║  queued
                                          ╚═ table effectively frozen: nothing new
                                             runs until the long query ends AND the
                                             ALTER finishes (or gives up)

 Defense: SET lock_timeout = '2s' — the ALTER aborts after 2s of waiting,
 the queue drains, you retry later. The freeze is bounded by the timeout.

Defenses: SET lock_timeout = '2s' in every migration session and retry, run migrations when long transactions aren’t running, and know the lock cost of each operation:

Expand–migrate–contract, step by step. The worked example interviewers love: orders.qty is int4 and you need int8. In-place ALTER TYPE rewrites 500M rows under an exclusive lock — hours of downtime. Instead:

-- EXPAND: add the new column (instant, metadata-only)
ALTER TABLE orders ADD COLUMN qty_v2 bigint;

-- Dual-write: deploy app code writing BOTH columns (or a trigger while old code drains):
CREATE OR REPLACE FUNCTION orders_qty_sync() RETURNS trigger AS $$
BEGIN NEW.qty_v2 := NEW.qty; RETURN NEW; END $$ LANGUAGE plpgsql;
CREATE TRIGGER t_qty_sync BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_qty_sync();

-- MIGRATE: backfill in keyed batches — small transactions, no long locks,
-- throttled, resumable from a checkpoint of last_id:
UPDATE orders SET qty_v2 = qty
 WHERE id > $last_id AND id <= $last_id + 10000 AND qty_v2 IS DISTINCT FROM qty;
-- loop, sleeping between batches; watch replication lag and bloat as you go

-- Verify: count mismatches, spot-check; then enforce NOT NULL the online way:
ALTER TABLE orders ADD CONSTRAINT qty_v2_nn CHECK (qty_v2 IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT qty_v2_nn;      -- scan, but only SHARE UPDATE EXCLUSIVE
ALTER TABLE orders ALTER COLUMN qty_v2 SET NOT NULL;   -- instant on PG12+: proven by the CHECK

-- CONTRACT (a separate deploy, days later, after reads are switched):
--   deploy code reading qty_v2 only → drop trigger → drop old column
DROP TRIGGER t_qty_sync ON orders;
ALTER TABLE orders DROP COLUMN qty;                     -- instant; optionally rename v2→qty

Narrate the deploy interleaving — that’s what the question is really testing: every schema state must work with both the previous and next app version (N/N+1 again — the schema-evolution guarantee of ch14, applied to DDL). Expand ships before any code depends on it; contract ships only after no running code touches the old column; each step is independently rollback-safe.

Batched backfill details that show production scars: batch by primary-key range, not LIMIT/OFFSET (offset re-scans); keep batches small enough that each transaction is milliseconds (row locks held briefly, replicas keep up); make it resumable and idempotent (IS DISTINCT FROM guard).

Then expect bloat, and plan the cleanup. Every updated row is a new row version under MVCC (multi-version concurrency control — Postgres never overwrites a row in place, it writes a new version and leaves the old for vacuum; ch00f), so a 500M-row backfill leaves up to 500M dead versions behind. That’s the job for pg_repack: an extension that rebuilds a bloated table/index online by copying it to a new table while a trigger captures deltas, then swaps under a brief exclusive lock — the online alternative to VACUUM FULL (which takes ACCESS EXCLUSIVE for its whole run).

Finally, name-drop the linting layer: tools like squawk / strong_migrations exist to catch unsafe DDL in review, and lock_timeout-plus-retry belongs in the migration runner, not in tribal memory.

Zero-downtime index builds. CREATE INDEX CONCURRENTLY: builds without blocking writes — it scans the table once to build the index, scans a second time to catch rows written during the first pass, then waits out every transaction older than itself, so no in-use snapshot predates the index. Failure modes to recite: (1) it’s slower and can wait indefinitely behind long-running transactions (including idle-in-transaction — pgbouncer clients misbehaving, or a forgotten psql); (2) if it fails or is cancelled it leaves an INVALID index — still maintained on writes (pure cost), unusable for reads — you must DROP INDEX CONCURRENTLY and retry; (3) can’t run inside a transaction block, so your migration tool needs a non-transactional mode; (4) unique-index builds can fail late on duplicates. Same story for REINDEX CONCURRENTLY (PG 12+). “INVALID index left behind” is the detail that proves you’ve done this.

Reconciliation: engine state vs. DB drift

Your engine’s truth is the log-derived in-memory state; the DBs downstream are projections; venues hold their version of your orders and balances. These will drift — a projection consumer crashes mid-batch and double-applies, a venue fill never reaches you, a manual DB fix bypasses the log. Reconciliation is the immune system:

• Engine vs. warehouse/DB: periodically (end-of-day at minimum, hourly better) compare authoritative aggregates — position per instrument, open-order count/qty per venue, cash — between engine snapshot at sequence N and the projection’s view as of N. Sequence numbers make “as of the same point” well-defined; without them recon chases its own tail. Row-level diff on mismatch; alert with materiality thresholds (a 1-lot drift pages differently than a 10k-lot drift).
• Engine vs. venue: the one that costs money. Venue drop-copy / execution reports / REST “open orders” and balance endpoints diffed against engine state; on restart this is a mandatory gate before trading resumes (ch16). Crypto reality you know firsthand: REST snapshots are rate-limited and eventually consistent with their own WS stream, so recon logic needs tolerance windows, not equality asserts.
• Design stance to state: recon detects, humans-or-runbooks decide, and every correction is applied as a new logged event (ManualAdjustment { reason, ticket }), never by mutating state or DB rows in place — otherwise you’ve just created drift the next recon can’t explain and broken replay besides.

Plain-English recap

• Rule zero is “the card-authorization decision never waits on the warehouse.” A microsecond decision path can’t afford a network hop to anything with a query planner — not Postgres, not even Redis. Decisions run on in-process state; the databases are downstream.
• Log-as-truth is CDC turned into the whole architecture. You know the pattern from change-data-capture: events stream out, projections consume them. Here the event log is the system of record and every DB — tick store, Postgres, dashboards — is a derived read model. Postgres itself works this way internally: WAL is the event log, tables are the projection, recovery is replay.
• Tick stores are your analytics warehouse, specialized for time. Columnar, append-only, range-scanned — ClickHouse-shaped. The one exotic bit is the as-of join: “match each trade to the quote in force at that moment,” which is the same query as “match each payment to the FX rate in force when it settled.”
• The lock-queue trap is the senior half of migrations you already run. You’ve done expand–contract column changes; the trap is that even an instant ALTER can freeze production for minutes by queuing behind one long query while everything else queues behind it. lock_timeout + retry bounds the freeze — that detail is what interviewers listen for.
• pgbouncer you literally run. Transaction pooling’s broken session state (prepared statements, SET, advisory locks, LISTEN) is the standard probe; your own host — 500 clients multiplexed onto ~25 backend conns per DB — is the worked example, and the ReadySet auth-bypass caveat you documented is the volunteered trade-off that lands.
• Reconciliation is payments recon, verbatim. Engine vs DB is ledger vs read-model at a common sequence number (the statement date); engine vs venue is ledger vs PSP settlement report, tolerance windows included; and every correction is a new journal entry, never an UPDATE on history.

Interviewer will ask

Q1: “Where’s the database in your hot path?” Nowhere — the card-authorization rule: the decision never waits on the warehouse. The arithmetic is the argument: the budget is microseconds, and anything with a query planner, a lock manager, or a network hop costs orders of magnitude more — even Redis at ~100µs RTT blows the whole budget, never mind Postgres. So working state lives in process memory, and durability comes from the append-only event log, replicated to the standby — the log is the record. Every database is a downstream projection of that log: columnar tick store for time-series analytics, Postgres for reference/accounts/compliance, Redis for ephemeral-by-policy coordination. Land the inversion: log-as-truth, DB-as-view — the opposite of the enterprise model where the DB is truth and logs are exhaust. The discipline: nothing in Redis is the record of anything; lose it and you re-derive.

Q2: “Why does kdb+ dominate tradfi tick data?” Because the tick workload has a specific shape, and kdb+ matches it three ways. Query shape: billions of time-ordered rows, range-scanned and aggregated by column — and the canonical query is the as-of join, each trade matched to the quote in force at its timestamp, which kdb+ supports natively. Storage shape: date-partitioned, one file per column, so “average spread over three months” reads only the columns it touches, at sequential-scan speed. Language shape: q runs identically over today-in-RAM (RDB) and history-on-disk (HDB), so analytics execute inside the store instead of hauling billions of rows out. Add two decades of installed trust in banks, and it won. The costs: license cost, terse language, key-person risk — which is why crypto shops run ClickHouse or QuestDB, same shapes, open source.

Q3: “Add a NOT NULL column with a default to a 500M-row table that’s live. Go.” State the governing rule first: every step exists to avoid holding ACCESS EXCLUSIVE — the lock that blocks even reads — for longer than a metadata flip. And name the trap that has nothing to do with work: your ALTER queues behind one long query, everything else queues behind your ALTER, so lock_timeout + retry bounds the freeze. Then the plan. On PG 11+, ADD COLUMN DEFAULT constant is instant — the default lives in the catalog, no rows rewritten — so the literal question is already solved. If the default must be computed, each arrow has its reason: nullable add (metadata-only, instant) → dual-write (new rows arrive complete) → batched keyed backfill (each transaction holds row locks for milliseconds) → CHECK NOT NULL NOT VALID (instant, no scan yet) → VALIDATE (the scan, but under a lock that lets reads and writes continue) → SET NOT NULL (instant, because the validated CHECK already proved it). Mentioning the lock-queue trap is what marks the answer senior.

Q4: “int4 → int8 on a live orders table.” One invariant generates the whole procedure: every intermediate schema works with app versions N and N+1, and every step rolls back independently. In-place ALTER TYPE violates it immediately — a 500M-row rewrite under ACCESS EXCLUSIVE, hours of downtime. So each step is the invariant applied: add qty_v2 (old code ignores it); dual-write via trigger or app code (both columns stay true whichever version runs); backfill in keyed, idempotent batches (either version reads correctly mid-backfill, and it resumes after interruption); verify, then constraint-then-NOT-NULL the online way; switch reads; contract — drop the old column — in a later deploy, once nothing running touches it. Land: expand–migrate–contract is the N/N+1 guarantee of the schema-evolution chapter (ch14) applied to DDL.

Q5: “CREATE INDEX CONCURRENTLY — what can go wrong?” Derive the failures from the mechanism: it avoids blocking writes by scanning twice, then waiting out every transaction older than itself, so no in-use snapshot predates the index. That wait is failure one: it hangs indefinitely behind a long-running or idle-in-transaction session — a forgotten psql, a misbehaving pooled client — because the wait has no timeout. Failure two comes from the index being registered before it’s valid: cancellation or crash leaves an INVALID index, maintained on every write (pure cost) but unusable for reads — DROP INDEX CONCURRENTLY and retry. Two more from the same machinery: it can’t run inside a transaction block, so your migration tool needs a non-transactional mode; and unique builds can fail late, on a duplicate the second scan finds. “INVALID index left behind” is the giveaway that you’ve run this in anger. Bonus: same CONCURRENTLY machinery for REINDEX, and pg_repack for bloat.

Q6: “Streaming vs. logical replication — when each?” One contrast carries everything: streaming ships raw WAL block bytes, which only mean something to a bit-identical cluster; logical decodes WAL into row events, which can land anywhere. Everything follows. Streaming: an exact block-for-block replica, same major version, whole cluster or nothing — which is precisely the HA/failover job, sync or async per durability need. Logical: a live, writable subscriber applying row changes — so a different major version (near-zero-downtime upgrades), a subset of tables, or CDC into Kafka/ClickHouse. Rule of thumb: streaming for HA, logical for upgrades, migrations, selective copies, and CDC. Volunteer the two operational teeth: DDL doesn’t replicate logically, and an abandoned slot pins WAL until the primary’s disk fills.

Q7: “How does Postgres failover actually work in production?” Patroni: agents + leader lease in etcd, election of the least-lagged replica, promotion, client re-routing via proxy/health endpoints. Then the three concepts under the tooling, each with its why: fencing — the old primary must be unable to take writes when it returns, or split-brain gives you two divergent histories; the data-loss window — async means a promoted replica may lack the tail, so maximum_lag_on_failover bounds the loss and only sync replication zeroes it; and pg_rewind — the demoted primary’s un-replicated WAL is a fork that must be cut off before it rejoins as a replica. Then the bridge: identical problem shape to your engine’s hot-standby failover — different layer, same fencing/gap/split-brain checklist.

Q8: “How do you know your engine and your DB agree?” You don’t — you check: scheduled reconciliation of aggregates as-of a common sequence number — the statement date; without a common cutoff, recon chases its own tail — row-drill on mismatch, materiality-tiered alerts; venue-side recon against drop-copy/REST as a hard gate on restart; and all corrections applied as logged adjustment events so the audit trail and replay stay intact. “Recon detects, events correct, nothing mutates in place.”

Further reading

• Kleppmann, DDIA — ch. 3 (Storage and Retrieval: B-trees vs LSM, columnar storage — the tick-store theory), ch. 5 (Replication: leader-based, sync/async, failover pitfalls — maps 1:1 onto the Patroni section), ch. 11 (derived data / “turning the database inside out”).
• Postgres official docs — chapters on WAL configuration and reliability, high-availability & replication (streaming), Logical Replication (restrictions section especially), ALTER TABLE notes (lock levels per subform), and CREATE INDEX (“Building Indexes Concurrently”).
• Patroni documentation — architecture and failover/switchover semantics; skim pg_rewind docs alongside.
• pgbouncer docs — the features/pooling-modes page; the transaction-pooling caveat list is interview gold.
• kdb+ / KX whitepapers (code.kx.com) — “Building Real-time Tick Subscribers,” the ticker-plant architecture papers, and the columnar/splayed-table storage docs; skim one so your kdb+ paragraph is grounded.
• ClickHouse docs — MergeTree internals and ASOF JOIN; the Braintree/GitLab-style public write-ups on zero-downtime Postgres migrations (GitLab’s migration style guide is public and excellent) for battle-tested expand-contract discipline.

Where this goes next: databases can be migrated online — but your engine holds state no load balancer can flip. Chapter 16 is how you deploy a stateful trading engine with zero downtime: hot-standby cutover, session takeover, shadow deploys, and rollback discipline.