MotherDuck and PostgreSQL: Why ATTACH Is Not a Sync

What ATTACH actually gives you

DuckDB ships a postgres extension, usually called the Postgres scanner. It lets a DuckDB or MotherDuck session attach a running PostgreSQL database and read its tables through the normal SQL surface. Filters and projections are pushed down where they can be, scans run in parallel, and the attached tables appear in the catalog next to everything else.

INSTALL postgres;
LOAD postgres;

-- attach a live PostgreSQL database, read only
ATTACH 'dbname=app host=db.internal user=analytics' AS pg
  (TYPE postgres, READ_ONLY);

-- query it as if the tables were local
SELECT status, count(*)
FROM pg.public.orders
WHERE created_at > now() - INTERVAL 7 DAY
GROUP BY 1;

-- or pull a one-off snapshot into the warehouse
CREATE TABLE orders_snapshot AS SELECT * FROM pg.public.orders;

That is genuinely useful. Joining a warehouse fact table against a live application table without building a pipeline first is a good afternoon. Prototyping a model against real production shapes beats guessing at them. And for a small reference table that barely changes, reading it live is simpler than copying it. The reason federation does not grow into a sync is not that federation is bad. It is that federation answers a question, and a sync maintains a state.

• There is no incremental copy. Every query re-reads Postgres over the wire. CREATE TABLE AS gives you a snapshot that starts going stale the moment it lands, and refreshing it means reading the whole table again, because nothing tracked what changed.
• There is no history. Federation shows the row as it is now. If someone asks what a customer's plan was in March, the answer had to be stored at the time, and a live read never stored anything.
• Read performance is tied to the live connection. Pushdown helps, but a wide analytical scan still crosses the network into a database sized for short transactional work, competing with the application for connections and cache.
• Nothing decides who wins. Federation has no notion of two editable copies, so it carries no conflict policy, no origin tracking and no retry. There is nothing to be in conflict with, which is exactly the point.
• It only speaks database. The scanner family covers Postgres, MySQL and SQLite. A SaaS API is not an attachable database, so anything past your own databases needs a different mechanism anyway.

The write question deserves a straight answer, because it is the one people ask first. The postgres extension can execute DML against an attached database, so an INSERT into pg.public.orders from a DuckDB session will run. What you do not get is anything that makes it a write-back: no record of what changed since the last run, no idempotency key, no retry when a statement fails halfway, no suppression of the change your own write just caused, and no rule for the case where the row moved on both sides. That is a manual write, not a sync.

Federation, replication and two-way sync are three different things

It helps to name the three shapes, because teams routinely evaluate one while describing another. Query federation is ATTACH: no copy, no schedule, no state. One-way replication is a pipeline, where Postgres change data lands in MotherDuck on a cadence, the warehouse copy is materialised and queryable without touching production, and nothing travels back. Two-way sync is a single engine that reads and writes both sides, tracks the origin of every write, and holds a policy for collisions.

Most teams need the middle column and believe they need the right one. The test is short: does a value that only exists in the warehouse ever have to change something in the application? If the answer is no, one-way replication is correct and a two-way sync is complexity you will maintain for nothing.

What a real two-way sync between MotherDuck and PostgreSQL has to solve

Once the answer to that test is yes, five problems arrive together. None of them is exotic, and all five have to be owned by whatever sits between the two systems.

Change capture on the Postgres side. Postgres has the good half of this problem solved already. Logical replication turns committed row changes into an ordered, durable stream a consumer can read, with no polling and no triggers. It needs wal_level = logical, a publication naming the tables, and a role with the replication attribute.

-- postgresql.conf (a restart is required)
--   wal_level = logical

CREATE PUBLICATION stacksync_pub FOR TABLE public.orders, public.customers;
CREATE ROLE sync_user WITH REPLICATION LOGIN PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sync_user;

-- the number to watch: an idle slot pins the WAL on disk
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

That last query belongs on a dashboard. A replication slot holds the write-ahead log until its consumer confirms it has read past that point. If the consumer stops and the slot stays, the WAL keeps accumulating on the database's disk, and a full disk stops writes to production. The outage does not begin in Postgres. It begins in whatever stopped reading from it. The mechanics are covered in more depth in the guide to PostgreSQL logical decoding plugins.

Watermarks on the MotherDuck side. The warehouse has no equivalent. MotherDuck does not publish a row-level change feed, so "what changed here since the last run?" is not a question you can subscribe to. It has to be modelled. The usual answers are an updated_at column maintained by whatever writes to the table, or an explicit change table that every write appends to.

Watermarks are easy to get subtly wrong. One based on wall-clock time misses rows written by a transaction that started before the last run and committed after it, so a safe implementation re-reads a small overlap window and leans on idempotent application rather than on the boundary being exact. A monotonic sequence beats a timestamp wherever you can have one. The same trap on the other side of the wire is covered in watermarks for parallel CDC backfills.

Batched writes instead of row-by-row updates. DuckDB stores data in columns. Updating one row means rewriting the column segments that hold it, which is inexpensive once and painful ten thousand times in a loop. The pattern the engine is built for is to land the batch in a staging table and then apply it in a single statement with MERGE or INSERT ... ON CONFLICT, keyed on a stable business key. Ten thousand row updates become one statement. It also keeps a sync job from monopolising the compute instance an analyst is querying through, since MotherDuck allocates compute per user and per session.

Loop prevention. Write a row into Postgres and it appears in the write-ahead log a moment later, indistinguishable from a user edit. Without origin tracking the engine reads its own write, decides MotherDuck is stale, writes it there, and the record starts bouncing. Three things stop it: origin metadata on every write so the engine recognises its own echoes, a short suppression window keyed on the record and the field, and a value comparison so a write that did not actually change anything never emits an event.

Conflict resolution, and who wins. When the same field moves on both sides inside the same window, something has to decide. Last writer wins is the common default and it is a poor one, because it makes the outcome depend on which job happened to run last. Field-level precedence is better, and it is mostly a conversation rather than an engineering problem: Postgres owns the fields the application writes, MotherDuck owns the fields a model computes, and once every field has one owner most collisions stop happening. The ones that remain resolve the same way every time and get logged. The worked examples in bi-directional sync explained apply the same rules to other pairs.

What happens to a single row

Those behaviours are easier to hold together as the states one row passes through than as a set of jobs and schedules.

Three of those states carry the whole argument. Echo dropped is loop prevention doing its job. Conflict is the policy question, answered per field rather than per record. Retrying is the acknowledgement that a batch write fails for ordinary reasons and has to be reapplied without duplicating anything, which is why the batch needs a stable key. A design that only draws the straight line down the middle will meet all three in its first week.

Choosing, and what to check before production

Start from latency, because it rules options out quickly. If the MotherDuck copy feeds dashboards and nothing writes back, a scheduled load is fine, and real-time sync versus batch ETL is the decision worth reading first. The moment a value has to travel back into Postgres and be acted on, batch stops working, because a number that arrives tomorrow morning is not a number anyone can use today.

Then write down every field that will sync and put one owner's name next to it. That list is your conflict policy, and writing it is usually the point where a ten-table plan turns into a three-table plan. If you cannot name an owner for a field, it probably should not be bidirectional yet.

Whatever you build or buy, four checks are worth running before it reaches production. Confirm the consumer advances the Postgres replication slot and alerts on retained WAL, so a stalled sync cannot fill the disk. Confirm writes into MotherDuck are batched and keyed, so a retry updates rather than duplicates. Confirm the engine recognises its own writes on both sides. And confirm every synced field has a named owner and a precedence rule recorded somewhere other than the engine's configuration screen.

To see PostgreSQL and MotherDuck kept in step in both directions, look at the MotherDuck and PostgreSQL integration or book a demo. The same engine connects DuckDB and pairs like MotherDuck and Snowflake, and the MotherDuck connector announcement covers what shipped. If you would rather have the backstory, the DuckDB origin story explains where the name came from.