Building an Incremental Sync from PostgreSQL to DuckDB

The job

You have a transactional table in PostgreSQL that grows constantly, and an analytical copy in DuckDB that dashboards read. Something has to move new rows from one to the other, continuously, and survive being killed halfway through.

That "survive being killed" clause is where most naive implementations fall apart.

The timestamp cursor and why it leaks rows

The obvious approach:

SELECT * FROM events WHERE created_at > $1 ORDER BY created_at;

Two failure modes, both silent:

Ties at the boundary. If 400 rows share the timestamp 12:00:00.000 and your batch cuts through them, the next run asks for > 12:00:00.000 and skips the remainder. You lose rows and nothing errors.

Clock and commit-order skew. created_at is set when the row is built, but the row becomes visible when the transaction commits. A long transaction can commit a row with an older timestamp after your cursor has moved past it. Gone.

Keyset pagination on a monotonic id

Page on the primary key instead:

SELECT id, site_id, timestamp, path, country, browser
  FROM events
 WHERE id > $1
 ORDER BY id
 LIMIT $2;

A BIGSERIAL id is unique and totally ordered, so there are no ties to straddle. Each batch's last id becomes the next batch's cursor. It is also index-friendly — no OFFSET scan that degrades as the table grows.

async function syncBatch(pg, duck, state) {
    const { rows } = await pg.query(
        `SELECT id, site_id, timestamp, path, country, browser
           FROM events WHERE id > $1 ORDER BY id LIMIT $2`,
        [state.lastId, BATCH_SIZE]
    );
    if (rows.length === 0) return 0;

    await duck.run('BEGIN');
    try {
        await insertBatch(duck, rows);
        await duck.run(
            'UPDATE sync_state SET last_id = ?, updated_at = now() WHERE source = ?',
            [rows[rows.length - 1].id, 'events']
        );
        await duck.run('COMMIT');
    } catch (err) {
        await duck.run('ROLLBACK');
        throw err;
    }
    return rows.length;
}

Checkpoint inside the same transaction

This is the detail that makes restarts safe. The cursor update and the row insert commit together, in DuckDB. Either both happened or neither did.

Update the cursor only at the end of a full sync run and a crash re-copies everything since the last successful run — duplicates, and every metric inflated. Update it before inserting and a crash loses the batch entirely.

Because the checkpoint lives in the destination, the sync is idempotent under restart: it always resumes from the last row DuckDB actually has.

Handling updates and deletes

The keyset cursor only sees inserts. If rows can change after insert, you need a second pass.

For an append-mostly analytics table, the pragmatic answer is a bounded reconciliation window: rows older than, say, 48 hours are treated as immutable; anything newer is re-checked on a slower cycle.

-- reconcile recent mutable rows
SELECT id, … FROM events
 WHERE updated_at > now() - INTERVAL '48 hours';

Then upsert into DuckDB by deleting the id range and re-inserting, inside one transaction. For hard deletes, either soft-delete in Postgres (a deleted_at column the sync can see) or periodically reconcile id sets for the recent window. A hard DELETE in Postgres is invisible to any cursor-based sync — there is no row left to observe.

Backfilling without blocking live sync

Initial load of an existing table should not run through the same small-batch path. Export and bulk-load instead:

-- in Postgres
COPY (SELECT * FROM events ORDER BY id) TO '/tmp/events.csv' CSV HEADER;

-- in DuckDB
INSERT INTO events SELECT * FROM read_csv_auto('/tmp/events.csv');

Then set last_id to the max id in the export and start the incremental loop. DuckDB can also read from Postgres directly via its postgres extension, which skips the file entirely for moderate volumes.

Operational details that matter

• Batch size. 1,000–10,000 rows. Larger batches amortize overhead; too large and a failure re-does more work and holds memory.
• Adaptive polling. Sleep longer when a batch comes back empty, shorter when it comes back full. Constant 1-second polling on an idle table is pure waste.
• Single writer. DuckDB allows one writer. Guard the sync with a lock so two instances cannot run concurrently.
• Lag as a health metric. Track max(id) in Postgres minus last_id in DuckDB. Growing lag is the earliest signal that sync is failing, and it is far more useful than "the process is running."
• Log rows-per-second per batch. When sync slows down, you want to know whether it is the read side or the write side before you start guessing.

Frequently asked questions

Why is a timestamp cursor unsafe for incremental sync?

Because rows can commit out of timestamp order. A transaction that started before your cursor but commits after it has a timestamp you have already passed, so the next run skips it — silent, permanent row loss.

What should the cursor be instead?

A monotonic id with keyset pagination — WHERE id > $last ORDER BY id. Ids are assigned at insert and visible only on commit, so nothing appears behind the cursor after you have moved past it.

How do I make restarts safe?

Write the checkpoint in the same transaction as the batch it describes. If the process dies mid-batch, both roll back together and the next run replays that batch cleanly, giving idempotent restarts.

How do updates and deletes propagate?

They do not, with an append-only cursor — it only sees new ids. Mutable rows need either a separate change feed keyed on an updated-at column, or periodic re-materialization of the affected partition.

Can I backfill without stopping live sync?

Yes. Run the backfill downward from the starting id while live sync runs upward from it. The two never overlap, so neither blocks the other and the ranges meet in the middle.

What good looks like

A correct sync has three properties, and you should be able to state why each holds:

1. No gaps — the cursor is on a unique, monotonic key, so nothing falls between batches.

2. No duplicates — the cursor advances in the same transaction as the insert.

3. Resumable — killing the process at any instant and restarting produces the same final state.

InsightsTrack's sync engine is built on exactly this: keyset cursor on the event id, per-batch checkpointing in DuckDB, bulk multi-row inserts, and a lag metric on the dashboard. See the architecture overview for how it fits together, and the Node.js guide for the driver details.