Transport verified locally. The subscriber prototype is queued after the PostgreSQL 18.6 cutover and the current application-facing compatibility batch. See the roadmap.
A read-only pg-datahike database mirrors a publication from an existing
PostgreSQL database. PostgreSQL remains the write endpoint. The mirror
loads a consistent snapshot, follows native logical replication, and
applies each upstream commit as one local transaction. Clients can query
it with SQL or Datalog and inspect history from the initial snapshot
onward with FOR SYSTEM_TIME AS OF.
| What | Verdict | |
|---|---|---|
| Option 1 | upstream PostgreSQL → pg-datahike live mirror (CDC in) | Next prototype after the compatibility batch. |
| Option 2 | a running PostgreSQL querying us through postgres_fdw / dblink | Independent interoperability work that shares catalog and test infrastructure. |
| Option 3 | pg-datahike as a logical-replication publisher (CDC out) | Defer. Needs a durable commit cursor and consumer-pinned GC roots in datahike, after the 1.0 id layout |
Use pgJDBC to connect in replication mode to a stock PostgreSQL publisher.
Start with pgoutput protocol version 1 in text mode. Create a logical
slot with an exported snapshot, import it on a regular connection, and
COPY the published tables into a staging mirror. Catch up from the slot's
consistent position before making the mirror available to readers.
Configure the upstream connection, publication, and slot explicitly.
CREATE SUBSCRIPTION can come later as a SQL management interface.
PostgreSQL replication protocol · Exported snapshots
Verified against PostgreSQL 18.6 with pgJDBC 42.7.3. The slot snapshot
showed the original rows while writes committed. The stream delivered an
update, insert, and delete across two tables in one commit and omitted
a rolled-back insert. Receiving the commit and sending status feedback
left confirmed_flush_lsn at the slot's start position. Setting the
applied and flushed LSNs explicitly advanced it to the transaction's end
position. Destination apply and crash recovery still need a prototype.
Generate parameterized SQL from decoded row changes for the first apply
implementation. Keep one write path and one commit boundary. Use the
existing transaction machinery through transact-recorded!. Consider
direct datom generation if the prototype shows a measurable SQL cost or
uncovers apply semantics that the shared path cannot express.
TRUNCATE to the published relations as part of the source
transaction. With history enabled, reads before the truncate still see
the original rows.transact-recorded!. This keeps the conflict ring
consistent. Pass transaction metadata through the same path when
needed.ring-write-eas note
below.:memory, a non-fsyncing
backend) unless explicitly allowed: "durable" is the whole argument.Independently useful, and cheap. Doing them now means nothing built in the meantime has to be reworked, and the mirror never has a standing reason to go around the one required write path.
transact-recorded! (constraint 2).25006, default_transaction_read_only reports on, and only the
replicator's internal handle writes. Useful on its own for a read-only
branch or replica, and it is what makes the sole-writer argument
behind the cursor sound.ring-write-eas density. It assumes max-tx increments by
exactly one per transaction. Under a Datomic-style shared t counter
(tx = (3<<42)|t) the difference over-counts, every window becomes
::gap, and every COMMIT falls back to a full-database conflict scan
-- about 1 s at 3M datoms. 1-2 days, no prerequisites, and required
before the 1.0 ids regardless of whether we ever build a mirror.sysviews
cluster). Discover source columns and keys through PostgreSQL's
catalogs. Load the mirror's schema through the existing DDL path so its
SQL translator, catalogs, and \d agree.postgres_fdw sessions select an AS OF through
OPTIONS (options '-c ...').scope.edn puts foreign_data and plpgsql out of scope under
:server-extension-hooks. Both are stale against our own plan:
foreign_data is the natural gate for Option 2, and Phase 7.3 commits
to plpgsql. Move each in when its feature lands rather than leaving it
excluded.
TRUNCATE, and lag and
checkpoint status. Run the focused PostgreSQL oracle during development
and the full integration suites before opening the PR.DECLARE/FETCH/CLOSE work, but each FETCH re-runs the stored
query with LIMIT n OFFSET pos (rewrite-cursor-page). At the default
fetch_size of 100 a 1M-row scan is 10k queries, each re-evaluating up
to its offset: O(n²). This is not FDW-specific -- pgjdbc with a
fetch size, psycopg named cursors and plpgsql cursors all pay it.
It looked like it might also be a correctness bug, because paging by
LIMIT/OFFSET across re-executions is only sound under a total order
and the order injected for a cursor without ORDER BY is
[1 :asc] -- non-total as soon as the first column has duplicates.
Measured, and it does not reproduce: 24 rows with 6 per
first-column value, paged at 1, 3, 5 and 7, return every row exactly
once. Each FETCH re-executes against the same frozen
:speculative-db captured at BEGIN, and the executor is
deterministic over an immutable snapshot, so successive executions
agree on the tie order. cursor-paging-does-not-repeat-or-skip-rows
in pg_cursor_protocol_test.clj pins that property, so a future
paging or planner change cannot quietly take it away.
So this is a performance item, not an S1 -- which also means the fix is free to change the strategy (hold an iterator, or materialize once per cursor) without a correctness argument to preserve.
Answered: startup options is read by two regexes only, so FDW time
travel waits on the settings store.
Measurements, not opinions: are datahike branch create/reset/merge cheap
and atomic enough to stage a multi-GB snapshot; is a multi-million-datom
retraction for TRUNCATE acceptable or do we want a datahike primitive;
does the writer preserve transact! submission order (wanted as a test
either way).
Decisions: mirror as a component in the server process or a separate
writer (affects where pgjdbc lives, and whether it is a separate
artifact); read-only forever, or "mirror plus local tables" -- local
extras break the sole-writer argument, so v1 is read-only; one upstream
schema per mirror database, or the schema folded into the attribute
namespace (:sales.orders/id); per-row valid time from upstream
valid_from/valid_to columns, where datahike has tx-level
:db.valid/from/to and not per-row.
Can you improve this documentation?Edit on GitHub
cljdoc builds & hosts documentation for Clojure/Script libraries
| Ctrl+k | Jump to recent docs |
| ← | Move to previous article |
| → | Move to next article |
| Ctrl+/ | Jump to the search field |