Skip to content

Re-sync, Sync now, and a table's sync mode

This page covers three things you can ask of a pipeline whose source is PostgreSQL: copy a table again (a re-sync of some tables, or of every table), write what is waiting now (Sync now), and how deletes reach a destination table (its sync mode). Each is a request: it is recorded at once and applied by the pipeline, within about a minute when it is running, or when you next start it.

Re-sync in a table’s drawer on the Schema tab, or several ticked tables at once (or POST /v1/pipelines/{id}/resync with "tables"), copies those tables again from your source, while the pipeline keeps its place in your source’s change log. Each table is copied at the pipeline’s next start, one after another, and catches up from the log after its copy. While a table copies, the pipeline’s other tables wait and then catch up.

What happens to the rows your source no longer has depends on the table’s sync mode, its destination, and whether it has a primary key (a table with no primary key kept live is copied replacing what your destination holds, at either destination). The rows a new row filter leaves out are told under Setting or changing a filter:

  • Soft delete: the copied rows are written over the ones there, and once the copy has finished, rows your source no longer has are marked deleted (_REYANSH_DELETED_AT on Snowflake, _reyansh_deleted_at on PostgreSQL). None of them is deleted.
  • Live on Snowflake: the same, then rows your source no longer has are deleted.
  • Live on a PostgreSQL destination: a table with a primary key keeps them, counted, unless you ask it to delete the rows your source no longer has ("delete_rows_not_in_source": true); a table with no primary key is copied replacing what your destination holds, so those rows are removed. A table you write to yourself is yours: say so before you ask.

A copy is metered as copy work and shows on your usage. A copy that is interrupted continues from its last saved batch when it can; the pipeline’s page says whether the running copy can.

Re-sync every table (on the pipeline’s page under Settings › Re-sync, or POST /v1/pipelines/{id}/resync without "tables") copies every table that is not removed, and forgets where the pipeline had got to in your source’s change log: it starts again from where its replication slot stands now. It is the way back after a lost or recreated replication slot or a source restored from a backup, where “delete this pipeline and create a new one” used to be the only answer.

  • One at a time. A second full re-sync is refused while the copies the first asked for are still waiting or running (409 resync_in_progress), and no table request is taken while a full re-sync waits. A table waiting for you (its waiting.reason says why: the publication, the source’s version, the key) is not one of those copies, so it does not hold a second one off.
  • A table read to its end is delivered first. If a table had already been read to the end of what your source sent, the re-sync waits until that is written, so nothing read is lost.
  • New source names (optional). You can move the pipeline to a new replication slot and publication with the same request ("slot_name", "publication_name"). Draw the pair first: tick Use a new change bookmark (replication slot) and publication under Settings › Re-sync, then Draw a new pair, or GET /v1/pipelines/{id}/new-source-names, which answers the names and what creates them, and stores nothing. Create them on your source before the re-sync is applied, because a running pipeline restarts onto it within a minute: the publication (statements) before you ask, the slot (start_statements) right before, or for a stopped pipeline right before you start it, because a slot keeps your source’s change log from the moment it exists. The pipeline never creates or drops them. The re-sync’s answer repeats the creation (setup.statements for the publication, setup.start_statements for the slot) and adds the old ones’ drop statements, for once it has settled. The draw is refused where the re-sync would be (a request already waiting, copies still running, an old slot still there), so you are never told to create a slot for nothing.
  • The old slot keeps your source’s log until you drop it. We never drop a slot on your database. While the old slot is there, the pipeline raises the warning old_slot_holds_log, naming the slot and the statement that drops it, and emails it by default; it ends when the pipeline finds the slot gone. A further rename is refused while it is there (409 previous_slot_still_present).
  • On PostgreSQL 17 before 17.6, new source names are what let the copies run. Those releases can lose the changes of a table added to a publication while a replication slot reads it, so a table asked to be copied there waits (waiting.reason version). A re-sync with new source names copies every table at its first start, because its new slot began after its publication. A restart part way through those copies (a deploy, a crash) leaves the tables not copied yet waiting for the version: upgrade the source to 17.6 or later, or drop the old replication slot that re-sync left (the old_slot_holds_log warning names it), then re-sync every table with new source names again. A full re-sync that keeps its names is judged like any other copy, and its tables wait there too.
  • While it copies, no table receives new changes until the last table is copied; then every table catches up. Your source keeps its change log from the re-sync until the last copy, which is more for a large pipeline.

Sync now (POST /v1/pipelines/{id}/sync) writes everything the pipeline has received from your source up to the press, without waiting for its next batch. It never restarts the pipeline. Its answer says what it does:

effect What happens
writing_held_changes What it holds is written now; it reaches your destination as fast as the destination takes it.
nothing_held This pipeline writes every change as it arrives, so nothing is waiting.
after_the_copy It is copying a table; what it holds is written as soon as the copy finishes.
started A pipeline at rest after its last sync (one that syncs on a schedule or by hand) starts, and syncs now.
next_run This pipeline reads your source in runs, on its schedule, and holds nothing between them: its next run reads every change made up to it.

A stopped pipeline refuses it (409 pipeline_stopped): start it, and it writes what changed while it was stopped. A pipeline that stopped and needs you refuses it too (409 pipeline_gave_up).

A full re-sync can switch a pipeline’s update method (update_method on POST /v1/pipelines/{id}/resync): every table is copied again into the same tables and schema, so your models keep working, billed like the first copy.

  • To query-based (query_based): every table must be kept live or soft_delete (history and append need the source’s log). The answer’s setup holds the statements that drop the replication slot and publication the pipeline leaves; run them once the re-sync has settled. Until you do, the pipeline keeps naming that slot (it holds your source’s log), and deleting the pipeline names it too.
  • To logical replication (logical_replication): draw a new slot and publication with GET /v1/pipelines/{id}/new-source-names, run the statements its answer’s setup holds on your source, and send the two names with the re-sync.
Mode A row deleted at your source (PostgreSQL, MySQL, read from the change log) Offered on
Soft delete (soft_delete) stays, marked deleted with when. A truncate at your source marks every row of a table with no primary key, whatever the pipeline’s truncate rule says, and does what the rule says to any other table; when the table is copied again, every row your source no longer has is marked. A Stripe source sends no deletes: a row deleted there stays here as it was, unmarked, until the table is truncated at your source or copied again Snowflake (the default), PostgreSQL
Live (live) is deleted here. A truncate at your source removes every row of a table with no primary key, whatever the pipeline’s truncate rule says, and does what the rule says to any other table; when the table is copied again, a row your source no longer has, marked deleted or not, is removed, unless the table has a primary key in a PostgreSQL destination, which keeps it until you purge or delete it. A Stripe source sends no deletes: a row deleted there stays here as it was until the table is truncated at your source or copied again Snowflake, PostgreSQL (the default)

A query-based pipeline (update_method: query_based) reads your tables’ rows, never a change log: its source sends no delete and no truncate, and nothing finds a row deleted there (Query-based capture), so it stays here as it was, unmarked, in either mode, and so does every row of a table emptied or truncated at your source. A re-sync of the table brings it in line: its copy marks the rows your source no longer has (soft delete) or removes them (live; into PostgreSQL only when you ask the re-sync to delete rows your source no longer has, since a table with a primary key kept live there keeps them otherwise, as above). Its copies, purges and mode changes are the table above’s.

GET /v1/connectors names each destination’s default and its modes. A table takes its mode when it is added: the pipeline’s default (sync_mode_default when you create it, or PUT /v1/pipelines/{id}/sync-mode), else the destination’s; another mode for one table is asked for once it is added. It changes only when you ask (PUT .../tables/{table_id}/sync-mode); a change between soft delete and live is applied in place, copies nothing, and waits until the table is syncing. From soft delete to live, the rows already marked deleted of a table with a primary key stay until you purge them or a copy removes them; a table with no primary key removes them the next time the pipeline writes to it, because a table with none kept live removes every row marked deleted after each write. A waiting change can be cancelled (DELETE .../tables/{table_id}/sync-mode-request).

History (history) and append-only (append) are offered at a Snowflake and a PostgreSQL destination for a PostgreSQL source read from its change log, to every account. Nothing about a table changes until you pick its mode: a table keeps the mode it has. A deployment’s operator can switch the two off; then a request for either answers 422 not_yet_available, a table already kept so goes on being written so, and GET /v1/connectors says which modes your account is offered.

  • A change of a table’s shape needs confirm: true (422 confirm_required otherwise, saying what happens): out of history or append, into append, and into history at a PostgreSQL destination. The old table is kept, with every row it has, under a name fixed when you ask (<table>__<old mode>_<UTC yyyymmddhhmmss>, also in the request’s history entry as aside_table, and on the table’s row as pending_mode.aside until the change is applied). It is yours: the pipeline never writes it or drops it. A change out of history or append on a table stopped because it has no primary key (keyless_mode_not_offered), with nothing written in that mode, is made in place at the pipeline’s next start instead, while the table is stopped: nothing is copied, and no table is kept aside. A table merely waiting for its first copy is not: its destination table may already be in the old mode’s shape, so its change waits for it to sync. The copy into the new shape is metered as copy work. A change to history at Snowflake, from soft delete or live, is made in place and needs no confirm.
  • At Snowflake the new table is built beside the old one, copied, and then takes its name with its grants. A view that selects * from the table, or names a column the new table does not have, stops working at that swap until you recreate it; a Snowflake stream on the table stays on the old one, which is now the kept table. A change whose grants cannot be carried is refused before anything is built: a grant on the table to a share or an application, or a schema with managed access that the pipeline’s role does not own. The table syncs on as it was, and the pipeline’s history says what blocks it (A change of a table’s sync mode was refused, and the table goes on as it was, table_mode_change_refused); ask again once that has changed.
  • At PostgreSQL the pipeline stops the table with the statements that build its new shape, to run as its owner; resume it once they have run, and it is copied into the new table.
  • A history table’s first copy is read from your source’s primary server, never a standby: on a standby it waits (history_standby) until the pipeline reads the primary, which its next start then copies.
  • A history table’s copy waits for another session’s lock on it (history_copy_lock), as a table with no primary key’s does: the pipeline tries it again on its own about once a minute, and the third time in a row parks it (history_copy_lock_held), naming the sessions holding the lock. End them, or let them finish, then resume it.
  • A table with no primary key cannot be kept in history or append-only yet (Fivetran keeps one in history, by a hash of its row; this release does not yet). Asking for either on a table the pipeline has read with no primary key answers 422 mode_not_offered. A table that reaches one anyway (the pipeline’s default for new tables is history or append-only, or its key was dropped after you asked) is stopped on its own when the pipeline next starts (keyless_mode_not_offered), and the pipeline syncs its other tables; nothing more of it is written or added to its table. If nothing of it was written yet, give it a primary key at your source, or ask for it to be kept in soft delete or live instead (that is applied while it is stopped), then resume it. If rows of it are kept in history or append-only already (its key was dropped after it synced), a request for soft delete or live waits until the table syncs again, which it does not while it has no primary key: give it a primary key at your source, then resume it, and it is copied again into the table it is kept in, as a re-sync of its mode copies (below).

A history table keeps every version of every row: one version per key for each transaction at your source that changed it, ordered by where that transaction is in your source’s log. Besides your columns it holds _reyansh_start and _reyansh_end (when the version was current), _reyansh_active (it is the current one), _reyansh_committed_at (your source’s own commit time, as it sent it), _reyansh_position (the change that made it: with the key, the version’s identity), _reyansh_deleted_at and _reyansh_synced_at; on Snowflake the same names in capitals, and _REYANSH_META.

  • The current version has _reyansh_active true and ends at 9999-12-31 23:59:59.999, as in Fivetran’s history tables, so a query written for those finds it the same way.
  • The times are microseconds, your source’s own. A key’s versions never overlap and leave no gap: WHERE <instant> BETWEEN _reyansh_start AND _reyansh_end finds exactly one version of each key for any instant in microseconds or coarser, and none after its delete. Two commits in one microsecond, or a clock that stepped back, are told apart by moving the later start forward a microsecond; _reyansh_committed_at keeps the time your source sent.
  • Changes inside one transaction are one version: nobody outside it could see the steps between. A row inserted and deleted in one transaction leaves no version at all (Fivetran keeps a version that lasts no time).
  • A row deleted at your source closes its current version, which keeps the delete’s instant in _reyansh_deleted_at; every earlier version stays, and nothing is written for the delete itself. If the key comes back, it opens a new version. So a deleted row and one a later version replaced are told apart by _reyansh_deleted_at, without reading the next version.
  • A change of a row’s primary key closes the old key’s version as deleted and opens one for the new key; a value your source did not send (an unchanged large value) is taken from the old key’s version.
  • A value your source did not send in an update is taken from the version before, which holds it.
  • A truncate at your source, under Mark its rows deleted or Delete its rows, closes every current version as deleted at that time and removes none: a history table never has a version deleted. Keep its rows leaves it as it was, and Ask me waits for your answer.
  • A version being written has no _reyansh_start yet and is not active, so a query for the current row or for an instant never matches it.
  • A re-sync writes no version for a row that has not changed (it only moves _reyansh_synced_at), writes a version for each one that has, and closes, as deleted, the current version of every row your source no longer has. A change your source made while the copy ran is written after the copy’s version: if the copy’s snapshot already saw it, that version holds the same values and lasts one microsecond; if it was older than what the snapshot saw, the history’s first microseconds after the copy hold that older value. From the moment the pipeline has caught up past the snapshot, the current versions are your source’s rows.
  • A column added later is NULL in the older versions, which is true of them; a fill of a new column writes the current version only, because the older versions’ values in it are not known.
  • Your warehouse runs more statements for a history table than for one kept by key (each run closes and opens versions), and the table grows with every change: that work and that storage are your warehouse’s. Measured on Snowflake (an X-Small warehouse, a table of about a million versions, two batches of 1,000 changes each), history’s statements took about the same execution time as soft delete’s (1.0 to 1.1 times), 1.3 to 1.7 times its elapsed time, and read 5 to 6 times the bytes, because each batch reads the history of every key it touches. Snowflake bills the time your warehouse runs, so expect a history table’s writes to cost about what soft delete’s do, a little more, and more as each key’s history grows long.

An append table keeps a row for every change, in order, and never updates or removes one. Besides your columns (every one nullable: a delete row carries only the key, a truncate row none) it holds _reyansh_op (insert, update, delete, read or truncate), _reyansh_change_id (the row’s identity), _reyansh_sequence (its order), _reyansh_committed_at (your source’s commit time; NULL on a read row), _reyansh_meta and _reyansh_synced_at.

  • Every change is written exactly once, by its identity, however often the pipeline replays it.
  • A change of a row’s primary key is two rows: a delete of the old key and an insert of the new one, so a “latest row per key” query sees the old key end.
  • A value your source did not send (an unchanged large value) is NULL in that row and named in _reyansh_meta’s absent as “unchanged at the source, not sent”, so it is never read as a NULL value. The pipeline never reads your source or your table to fill it.
  • A delete is a row with the key and _reyansh_op = delete; a truncate is one row with every column of yours NULL, unless the pipeline’s truncate rule is Keep its rows; under Ask me the table waits for your answer.
  • A copy, or a re-sync, appends one read row for each row your source has; nothing already in the table is changed. A copy that resumes after the pipeline was stopped can append a second, complete set of read rows after a partial one: a “latest row per key” view that orders by _reyansh_sequence takes the newer.
  • A column added later is NULL in older rows, and is never filled in them: they record what was sent.
  • Your warehouse’s work stays flat as the table grows: each write reads only the table’s newest part (on 10^8 rows, the write read 2 of 12 micro-partitions); the storage is your warehouse’s.

Soft delete into PostgreSQL is offered to every account. A deployment that does not offer it answers a request for it with 422 not_yet_available (on a table’s mode, the pipeline’s default and a create’s default), and GET /v1/connectors does not list it.

A MySQL or Stripe pipeline into PostgreSQL writes every table live and runs no purge, so its tables are offered live alone: a change of a table’s mode, the pipeline’s default (PUT .../sync-mode) and a create’s sync_mode_default are refused there with 422 mode_fixed_by_source, and a purge with 422 purge_not_offered. GET /v1/connectors names each destination’s modes by source kind (sync_modes: {"<source kind>": {default, offered, purge}}), for the account asking.

On a PostgreSQL destination, soft delete adds two columns to your table (_reyansh_deleted_at, _reyansh_synced_at). If the pipeline’s role cannot add them, the table is parked, and nothing of it is written until they are there: its park names the statement to run as its owner, and you then resume it. A truncate at your source, under the Mark its rows deleted rule, marks every row of a soft-delete table not yet marked (_reyansh_deleted_at and _reyansh_synced_at set to that time; a row marked already keeps its first mark) and removes none; under Delete its rows its rows are deleted, as on Snowflake (schema changes). A table with no primary key is emptied whatever the rule says: every row is removed when it is kept live, and marked when it is kept in soft delete.

Delete the rows marked deleted in the table’s drawer on the Schema tab, confirmed with Yes, delete them (POST .../tables/{table_id}/purge-deleted with "confirm": true; when an API key asks for it, every owner of the account is told) deletes the rows marked deleted from a destination table, at the pipeline’s next start. It cannot be undone, and a change replayed from your source’s log can mark a row deleted again.

  • A waiting purge can be cancelled (DELETE .../tables/{table_id}/purge-request; Cancel the delete in the table’s drawer on the Schema tab): the rows stay. When an API key cancels one, every owner is told, as for the purge. A purge the pipeline has already begun runs to its end, and the table’s rows_purged then says what it removed; 404 no_pending_purge when none waits.
  • A parked table is not purged (422 purge_needs_resume): a purge runs only for a table the pipeline writes, so resume it first. A purge asked before the table parked waits for it (pending_purge.waits_for_resume), and so does the table’s resume: cancel the purge to resume it first, as for a waiting change of its mode.
  • A purge your PostgreSQL destination refuses stops its table (a trigger, rule, constraint or permission of yours refused it, or a BEFORE DELETE trigger kept one of its rows): nothing is removed, the table stops, saying what refused or kept the purge, and the purge stays pending, so the table cannot be resumed while it waits. Cancel the purge, then resume the table; once the cause is fixed and the table is syncing, ask for the purge again.
  • Nor is a table waiting for you (422 purge_table_waiting): one whose waiting.reason is set and is not destination_busy (its publication, the source’s PostgreSQL version, a partition’s identity, the source’s room, or its key). The pipeline skips such a table until what it waits for is done, so the refusal names that, and you ask for the purge once the table is copied. A table waiting for its first copy, or on a busy destination table, is written at a start, so its purge is accepted. A purge asked before the table was found to wait stays pending with it until the table is copied; cancel it if you would rather not wait.
  • A cancel’s takes_effect says a purge the pipeline has already begun runs to its end, for every table that is not parked, even on a stopped pipeline: its worker may still be finishing one.

Changing the schedule, the discarded-rows answer, the change policy, a table, its mode, or the destination restarts a running pipeline within a minute. If a copy is running then, the pipeline’s page says so before you save: a copy that keeps its place pauses and continues from its last saved batch; one that cannot starts again, and the answer names it in copy_restarts.

If a defect of ours needs a table copied again, we ask for the re-sync ourselves. Its copy is recorded as repair work and never charged; the pipeline’s history says it was ours.