[postgres][future] WAL / logical-decoding ingestion for a high-throughput server backbone

[joeldsouzax]

Future-work / watch-list, spun out of the #213 design discussion. Not needed for the freeze.

Context

The first postgres adapter (#213) closes the cross-stream global-ordering hazard on the reader side (the "Way 2 / watermark" approach — see #213 for the decision): keep insert-time BIGSERIAL, but the $all read only advances past events whose writing transaction is fully committed and older than any still-in-flight write (pg_snapshot_xmin-style oldest-active-transaction watermark), ordering/resuming by (transaction_id, global_seq). Writers stay fully parallel; no event is silently skipped. That fits the freeze goal (it exercises our generic read_all / WakeSource / subscription machinery) and the embedded/mobile-first targets.

The future option

For a high-throughput, server-side, multi-consumer event backbone, PostgreSQL logical decoding (WAL / CDC) is the strongest mechanism: concurrent transactions are decoded in commit order (PG docs — logical decoding), with zero write-side cost and no reader-side watermark. The position is the slot's LSN, naturally monotonic and in commit order, so a plain "read after my bookmark" is correct again.

Why it's deferred (not freeze work)

• It bypasses the contract we're validating. It replaces read_all + WakeSource + the generic catch-up/live loop with "tail a replication stream" — so it doesn't stress the abstraction [freeze][validation] Write a 2nd store adapter (postgres) to validate the contract before freeze #213 exists to prove.
• Heavy infrastructure: requires wal_level = logical (server restart), a replication slot, and the slot disk-fill footgun (an un-consumed/slow slot pins WAL and can fill the disk / take down the DB).
• Splits history from live: deep catch-up still comes from the table (SELECT); only the live tail comes from the WAL (slots don't retain arbitrarily-old WAL). Two mechanisms, two position notions.
• Heavy for nexus's embedded/mobile-first targets — this is more Agency-runtime / server-backbone territory than nexus-kernel territory.

When to revisit

If/when there's a need for a server-side, high-throughput, multi-consumer postgres backbone (likely an Agency concern). At that point evaluate WAL/logical-decoding (or Debezium-style CDC) vs. the Way-2 watermark adapter.

Relates to #213 and milestone "Phase 4: Cutting Edge & New Adapters".