Query-based capture: reading PostgreSQL by queries
A PostgreSQL pipeline normally reads your database’s change log through a replication slot and a
publication: every insert, update and delete arrives as it happens. Query-based capture
("update_method": "query_based" when you create the pipeline) reads your tables by queries instead, on the
pipeline’s schedule. Choose it when you cannot give the pipeline a replication slot: a managed service or a read
replica that does not offer logical decoding, a role your team will not grant REPLICATION, or a database whose
change log you do not want held for a pipeline.
What it needs is a role that may SELECT the tables (and read the catalogue, which every role may). It creates
nothing on your database: no slot, no publication, no trigger, no table.
Creating one
Section titled “Creating one”Query-based capture is offered to every account. Reading the log (logical replication) stays the default and
the recommended method: a pipeline you do not create or re-sync as query-based keeps reading the log, and nothing about
an existing pipeline changes unless you ask. A deployment’s operator can still switch the choice off; then the app
does not ask how to read your database, and POST /v1/pipelines (or a re-sync) naming query_based answers 422
not_yet_available, creating and recording nothing. GET /v1/me says which with query_capture_enabled.
In the app, New pipeline asks How we read your database on step 1 once your source is a PostgreSQL database: choose By queries (query-based). Then:
- Test connection (step 2 tests the source alone, unless it is a saved source whose saving reported a passing test, which skips step 2; step 4 tests it with the destination) signs in as your connection string’s user (through your SSH tunnel first, when the source has one: An SSH tunnel) and lists the tables it may read. The change bookmark (replication slot) and published tables (publication) steps say they are not used, and there are no statements to run on your database. Pick your tables on step 5 and press Continue: the test then checks each one you picked (that the user may read it, row security, and how much each sync reads), keeps them picked, and keeps the columns you chose for them.
- Schedule (step 6, on the step itself): a query-based pipeline syncs on its schedule into any destination (every six hours by default).
- Rows that are already there: nothing to choose. The first sync copies every row each table holds, because that is how a query reads them.
With the API, send "update_method": "query_based" to POST /v1/pipelines. POST /v1/connections/test takes the
same update_method and the tables to check.
How a sync reads your tables
Section titled “How a sync reads your tables”- The first sync copies every table, as a log pipeline’s first copy does. A query-based pipeline always copies its tables’ existing rows: that is how it reads them.
- Each sync after reads only the rows changed since the last one, table by table, using PostgreSQL’s own record of which transaction last wrote each row. Nothing in your tables has to carry an “updated at” column.
- Each sync ends with one commit: the place every table read to is saved together, after your destination
holds what was read. A sync that stops part way (you stopped the pipeline, the database went away) delivers again
from the last commit next time, and is not charged. A sync asked to stop (a pause, an edit, our service
restarting) is shown Stopped before it finished — not charged in the run history (
outcome: stopped), and itssync.endedsaysstatus: stopped. - A table keeps syncing while another waits for you. A change of a table’s shape follows the pipeline’s change rules, as on a log pipeline.
- A table is known by which table it is, not only by its name, as on a log pipeline. The first sync records which
table each name is, and every sync after checks it before it reads anything. A table dropped and created again, or
a rebuilt copy renamed into its place (what online schema-change tools do), is never read as the table the
pipeline was syncing: the table is stopped (
table_parked, causetable_replaced), the others keep syncing, and resuming it copies the table that has the name now. If your change rules re-copy a replaced table and you confirmed that rule, it is copied again instead, at the next sync. A table swapped while a sync reads it ends that sync’s read of it unread, the same way. - A first copy that a later sync resumes is checked first. When the first sync’s copy is interrupted (a deploy, a stop), the next sync carries on where it left off, after checking the database against what the first sync read: another database at the address stops the pipeline, a database behind it (a restore) stops it, and a replica behind it is waited for, as at every other sync.
Because each sync reads what changed since the last, a row changed twice between two syncs is delivered once,
as it is at the second sync. The history and append modes keep every change as it happened, which only the change
log can see: a query-based pipeline offers live and soft_delete.
Rows deleted in your database are not found
Section titled “Rows deleted in your database are not found”A query reads the rows that are there; it cannot read a row that is gone. A query-based pipeline does not find rows
deleted in your database: such a row stays in your destination as it was, not marked and not removed, whether the
table is kept soft_delete or live, into PostgreSQL and into Snowflake. Nothing tells you about it. The same holds
for every row of a table emptied or truncated in your database, and for the old row of a row whose key changed there
(the row under its new key arrives as a new row).
To bring a table in line with your database, re-sync it: the copy marks the rows your database no longer has
(soft_delete) or removes them (live; on PostgreSQL only when you ask the re-sync to delete rows your database no
longer has). See Re-sync, Sync now, and a table’s sync mode. A row your destination lost (a
retention rule, a restore of your destination) is not read again either until it changes in your database: re-sync
the table to bring it back.
If deletes must reach your destination as they happen, read the change log instead (logical replication), which delivers every delete and truncate. Finding deleted rows by comparing each table’s keys with your destination’s (a delete check) is not in this release.
What it checks about your database’s history
Section titled “What it checks about your database’s history”Reading by queries trusts that your database moves forward. Before each sync reads a row, it checks what it finds against what the last sync committed:
- The same server. Another server behind the same address, or this server upgraded with
pg_upgrade(which gives it a new identity), stops the pipeline before it reads (source_identity_changed). If it reaches the wrong server, point the pipeline back; otherwise re-sync it. Continuing after an upgrade without copying again is not offered yet. - Nothing went back. A transaction counter or a log position behind what the last sync read is what a restore
from a backup, or from a disk or VM snapshot, looks like: the pipeline stops (
source_counter_behind); re-sync it. - The same timeline. A failover or a switchover moves your database to a new timeline:
- When the pipeline’s user has the
REPLICATIONattribute, it reads where the new timeline began (TIMELINE_HISTORY, on a replication connection of its own; nothing is created). A move after everything it had read continues on its own, told assource_failed_over. A move before it lost transactions it had read, and stops for a re-sync. - When it does not (the usual case for this method), a move of one timeline with the transaction counter and
the log position past what it read is a question (
source_timeline_unverified), on the pipeline’s page and throughPOST /v1/pipelines/{id}/source-history/acceptwith the facts the stop shows. Accept this history, then Accept and start the pipeline, after a planned switchover or your provider’s maintenance: the pipeline carries on from where it last read and skips nothing. Re-sync every table (Settings › Re-sync) if your database lost recent transactions or was restored to a point in time: continuing after such a move keeps the rows those transactions changed or added as we delivered them until they change again, and misses rows written since the move under re-used transaction numbers until they change again. Read from a replica, whose own reading of its timeline lags, a move that everything we can check continues past is followed without a question; one that lost transactions we had read and then caught up can go unnoticed there, so grant the userREPLICATIONto have it judged exactly. - A new primary behind what we read lost transactions we had read. The pipeline stops for a re-sync, and stays stopped for that move even once the server has caught up.
- The new timeline’s start is read from your server’s timeline history files: keep them (and keep one WAL archive across failovers). When one cannot be read, the move is the question above.
- When the pipeline’s user has the
- A restart is told. When a server of your database (the same system identifier) has started since the one the
last sync read (
source_restarted), the pipeline says so before it reads: at the same address it is a restart, at another one your database moved, was restored onto another host behind the same name, or another of its servers now answers there. Each of those is what a restore looks like, so the sentence asks for a re-sync if you restored. A Neon compute’s own restarts when idle are not told.
A restore that has already caught up can pass those checks, and then it goes unnoticed: rows your database wrote soon after it can be missing from your destination or keep older values, for good on a table that is only inserted into. After you restore your database to an earlier point, re-sync the pipeline.
The user it reads as
Section titled “The user it reads as”SELECTon each table itself. Every read first takesLOCK … IN ACCESS SHARE MODE, which PostgreSQL grants only on a privilege on the whole table, so a user granted columns one by one cannot read in windows, whatever columns it has. To keep a column out of your destination, block it in the table’s drawer on the pipeline’s Schema tab: a blocked column is never selected, so its values never leave your server. When the user can no longer read a table, the pipeline stops and its sentence gives theGRANTto run (table_not_readable).- A partitioned table needs
SELECTon each partition, because each is read by its own name and PostgreSQL checks the partition’s own grants. The setup test probes every partition and names each refused one. For partitions created later, runALTER DEFAULT PRIVILEGES FOR ROLE <the role that creates them> IN SCHEMA <schema> GRANT SELECT ON TABLES TO <user>; with pg_partman, setinherit_privilegeson the parent (it is off by default). - Row-level security: a table whose policies apply to the user is refused, by name, because every read would be a subset nothing reports. Read as a user the policies do not apply to.
pg_current_wal_flush_lsn(): every sync asks it (below); a provider that does not let the user call it fails the setup test (durability_unreadable).- Aurora: connect to the cluster’s writer endpoint. A reader is refused by the setup test
(
aurora_reader_untested) until we have recorded what one answers; an Aurora writer and a Neon compute are read with a warning that the provider is not yet verified (provider_untested). Every sync repeats the writer test before it reads anything, because after a failover an instance endpoint can reach a reader: a sync that cannot prove it reached the writer ends without reading (aurora_not_writer), is not charged, and tries again. - Names are looked up in
pg_catalogonly. Every session the pipeline reads your tables through is opened withsearch_pathset topg_catalog, pg_temp, whatever the user’s or the database’s own setting, and checks it before it runs anything, so a function of the same name created in another schema never runs with the pipeline’s rights. A connection pooler or proxy that drops the connection’s startup options is refused with a sentence that says so: connect directly, or let the pooler pass theoptionsstartup parameter through.
Reading a replica
Section titled “Reading a replica”A query-based pipeline can read a streaming replica (a standby). Each sync’s start waits for the replica to reach
what the last sync read, taking its reading again every 5 seconds for up to 5 minutes; after that the sync ends,
nothing lost, and the pipeline tries again shortly (replica_behind). A replica that cancels our reads to replay its
primary’s changes costs a window, retried three times, then the same (standby_reads_cancelled); the setup test
warns when that can happen (standby_cancels_reads): turn on hot_standby_feedback, or raise
max_standby_streaming_delay to 15 minutes or more. An unlogged table cannot be read on a replica.
Locks and long transactions
Section titled “Locks and long transactions”Each window of a read is one short transaction that takes ACCESS SHARE on the table, the lock every SELECT
takes. It waits at most 60 seconds behind an ALTER TABLE, a VACUUM FULL or a TRUNCATE (the locks that exclude
reads); past that the sync ends without saving and the pipeline tries again shortly.
A transaction left open anywhere on your server holds back where each sync can read from (the oldest transaction
still running), so every sync reads again everything changed since it began. Nothing is lost; each sync costs more.
End it: find it in pg_stat_activity (the oldest backend_xid) or in pg_prepared_xacts.
synchronous_commit = off
Section titled “synchronous_commit = off”With it off, your database reports a transaction committed before it is on disk, and a crash can lose it. The setup
test warns when it is off (synchronous_commit_off). Each sync’s start waits until what it is about to trust is on
disk (source_wal_not_flushing after 30 seconds), so a sync never saves a position past a commit a crash could lose;
a change it read during the sync and the crash then lost stays in your destination as we read it, until the row
changes again (a row the lost transaction inserted stays there until you re-sync the table).
Changes no capture method sees
Section titled “Changes no capture method sees”Some changes alter how a row reads without writing the row, so neither a change log nor a query reads it again:
renaming an enum label (ALTER TYPE … RENAME VALUE), adding an attribute to a composite type a column uses, and
renaming a table a regclass column names. Re-sync the table after such a change. Generated columns, stored or
virtual, are not read by a query-based pipeline.
What it costs
Section titled “What it costs”Each sync reads every page of each table, in windows of about 256 MB (one statement on PostgreSQL 13), and sends
only the rows changed since the last sync. Rows read again are counted as the work they are and told
(rows_reread, with why: a column filled, a partition attached). A sync that stops before its commit is not charged.
A view you pick costs all of it, every sync. Each sync runs the view twice in one snapshot (once to check its key, once to read it), holds a share lock on every table it reads for as long as the read lasts (your ALTER TABLE, TRUNCATE, VACUUM FULL or REINDEX on them waits for it), and copies it whole, before other tables’ changes are read; its rows are metered as copy work at every sync, however few changed. Pick the tables
it reads instead where the view is large and changes little.
Changing a pipeline’s method
Section titled “Changing a pipeline’s method”The method is changed by a re-sync of every table (POST /v1/pipelines/{id}/resync with "update_method"),
which copies every table again into the same tables. See Re-sync, Sync now, and a table’s sync mode.
- The new method’s setup test runs first, with the pipeline’s own tables and stored credential; a step that fails
refuses the switch (
409 method_switch_untested, naming the step) and nothing is recorded. - A switch to queries is refused while a table is kept in
historyorappendmode (409 method_switch_blocked_by_mode, naming them): only the change log can keep every change. - After a switch to queries, the replication slot and publication the pipeline used stay on your server until you
drop them: each sync says so (
old_slot_still_present, with the statement), and stops saying it once they are gone. A switch to the change log needs slot and publication names drawn for it first.
A source column named like one of ours
Section titled “A source column named like one of ours”A pipeline keeps a few columns of its own in every destination table: in PostgreSQL, _reyansh_deleted_at (when a
row was marked deleted, which a purge removes) and _reyansh_synced_at (when the pipeline last wrote the row); in
Snowflake, _REYANSH_DELETED_AT, _REYANSH_SYNCED_AT, _REYANSH_META and the tags its staging tables add, in any
spelling that folds to them. A source table with a column of one of those names is stopped at the start of the
sync, before anything of it is read or written and before the destination table is changed, and the rest of the
pipeline syncs on. The table’s drawer on the Schema tab says which column it is. Block that column there so it is
never read, or rename it at the source (unless the source table is another pipeline’s destination: there the column
is that pipeline’s own), then resume the table: it is copied again.
Views, materialized views and other relations that are not tables
Section titled “Views, materialized views and other relations that are not tables”A pipeline syncs tables, as Fivetran’s does, and a query-based pipeline also syncs a view or a materialized view you pick with its key columns, which Fivetran does not.
Syncing a view you pick
Section titled “Syncing a view you pick”Name the view as a table, with key_columns: the columns whose values identify one of its rows, in order, as the
view names them (a view has no primary key of its own to tell us).
POST /v1/pipelines{ "update_method": "query_based", "tables": [ { "schema": "public", "name": "order_summary", "key_columns": ["order_id"] } ], ... }- Each sync reads the view whole, in one snapshot (the key check first, then the read: the view runs twice):
every row it has then is written to
your destination by its key, and the rows that left it since the last sync (deleted, filtered out, their group
gone) are found by that read, as a re-sync finds a table’s: marked deleted at Snowflake and in a PostgreSQL table
kept
soft_delete, and counted and kept in a PostgreSQL table keptlive, which may hold rows of your own (ask for a re-sync that deletes rows not in the source to remove them). Its status says how many. A view is never read byxmin, which it does not have; a column of your view namedxminis your value, nothing more. - What it costs: all of it, every sync. Each sync runs the view twice in one snapshot (once to check its key, once to read it), holds a share lock on every table it reads for as long as the read lasts (your
ALTER TABLE,TRUNCATE,VACUUM FULLorREINDEXon them waits for it), and copies it whole, before other tables’ changes are read; its rows are metered as copy work at every sync, however few changed. Pick the tables it reads instead where the view is large and changes little. - A view whose rows can differ between two reads of the same data is refused (
view_not_syncable): one that reads a sample (TABLESAMPLE), usesLIMIT,OFFSETorFETCH FIRSTanywhere (a lookup subquery withLIMIT 1too: write it as a join), keeps one row of each group (DISTINCT ON), or uses a window function (OVER: the latest row of each group byrow_number(),lag,first_value): each sync could mark rows deleted that are not. Group withGROUP BYand aggregates, limit it in your destination, or sync the table itself. A view that filters on the clock (WHERE created_at > now() - interval '7 days') is synced: the rows that age out of it between two syncs are gone from it, and are found gone. An aggregate whose result depends on its input’s order (string_agg,array_agg,json_aggwith noORDER BY) can differ between syncs: as a value it is written as an update, but as a KEY its rows are marked deleted and come back under another key at every sync, so key such a view by its group (or give the aggregate anORDER BY). The same holds for one of two equal but distinct values thatUNION,GROUP BYorDISTINCTkeeps (1.0and1.00,citext’sAanda): a value only, unless it is a key of a case-insensitive type. - Its key is checked at every read, in the read’s own snapshot, before anything is written. A row with no value
(NULL) in a key column parks the view
view_key_null, and two rows sharing a key park itview_key_not_unique: nothing of the view is written in that sync, the words count the rows and name the columns, never a value, and the rest of the pipeline syncs. Change the view, then resume it. A key column the view no longer has parks itview_key_missing; one of a type with no equality (json) parks itview_key_not_comparable(cast it in the view:::textor::jsonb). - A view is locked for its read, which locks every table of its definition, so no table it reads can be
rewritten under the read. So a view is refused (
view_not_syncable, named, nothing read) when its definition reads something that lock cannot reach or that could change data: a materialized view or a foreign table, a volatile function (nextval,random()), or a function defined in your database (its body may read any table; an extension’s function declaredIMMUTABLE, such ascitext’s, is accepted). A materialized view you pick itself takes no lock (PostgreSQL refuses one) and needs none: aREFRESHrebuilds it whole, and a sync reads its contents as they stand. - Its key is fixed once named. A removed view asked back keeps its key; a view picked with none (which parks
not_a_table, saying how to name one) is given the key the add names when you remove it and add it again. To sync a view by another key, pick it in another pipeline. - The user the pipeline reads as needs
SELECTon the view itself (GRANT SELECT ON public.order_summary TO ...). - The table list in the app picks tables; a view is picked through the API for now.
When a name holds something else
Section titled “When a name holds something else”When a pipeline checks a name that holds a view it was not asked to sync, a materialized view, a foreign table or a
sequence (at the start of every sync, and in the check a pipeline reading the change log makes of its tables while
it runs), you are told what it is and what to sync instead. A view keeps no rows of its own, so neither a change log
nor a row’s version can say which of its rows changed or were deleted; a materialized view is rebuilt whole by each
REFRESH; a foreign table’s rows live on another server.
- Picked with no key, or for a pipeline that reads the change log (a view is never in a publication): the test’s
tables step fails with
not_a_table, and its sentence names the tables the view reads, through any nested view, so that you can add those and build the view again in your destination from them, and says that a query-based pipeline syncs it with key columns. If the pipeline is created anyway, the view stops at the start with its own cause,not_a_table, in the same words, and waits for you to remove it; the rest of the pipeline syncs (unless the pipeline is set to stop every table when any one stops). It is never alerted as a table dropped or renamed. A view that calls a function of your own or names a foreign table is said to, never “reads no table”: we cannot follow what those read. - Left under a synced table’s name — the commonest case: a table renamed (
orderstoorders_v2) and a view created under the old name so that old queries keep working. The table stops at the next sync, its place let go (table_replaced), in words that say a view took its name and name the table it reads (orders_v2): add that table to the pipeline. Nothing of the view is read or written, a waiting table never follows it, and atable_replacedrule set to copy again never copies it: under that rule a table that replaced yours is copied by itself, but a swap that passes through a view (renamed, a view under the name, then a new table renamed in) waits for you to resume the table once a table holds the name again. A pipeline that reads the change log reads the table to its end first where what it recorded lets it, so every change made before the rename reaches the destination; its words say whether that happened, and a copy you asked for meanwhile waits for that, saying a view holds the name. A few narrower moments still say “another table” of a view: a copy that finds the name changed while it opens, and a key change or an added column met while the table is read to its end. The next sync’s start says what it is. - A view you picked with its key, dropped and created again (a tool that rebuilds views does this): it is read, whole as at every sync, from the view the name holds now.
Events
Section titled “Events”| Event | What it means |
|---|---|
query_read |
a table’s rows read this sync: how many changed (no count of deleted rows: a query finds none) |
rows_reread |
rows read again this sync, and why (a column filled, a partition attached) |
source_failed_over |
your database moved to a new timeline after everything we had read, so the pipeline continued |
source_restarted |
the server we read last time has started again since |
replica_behind |
the replica stayed behind what the last sync read, so this sync ended; it tries again shortly |
source_wal_not_flushing |
the server did not write its log to disk within 30 seconds, so this sync ended; it tries again shortly |
standby_reads_cancelled |
the replica kept cancelling our reads to replay changes, so this sync ended; it tries again shortly |
aurora_not_writer |
the sync reached an Aurora instance that could not show it is the writer, so it ended without reading; it tries again shortly |
table_parked (cause table_replaced) |
a table’s name now names another table; it is stopped, the others keep syncing, and resuming it copies the new one |
table_rewritten_during_read |
a table was rewritten three times while it was read; it is read again at the next sync |
table_shape_changed_during_read |
a table’s columns changed while it was read; the next sync follows your change rules |
old_slot_still_present |
after a switch to queries, the replication slot the pipeline used is still on your server (drop it with the statement shown) |
run_stopped_uncommitted |
the sync was asked to stop before it finished: the next sync reads its tables again from where it began, and it is not charged |
A stop for a changed or rewound history is the pipeline’s own stop, shown on its page with what to do, and notified as every stop is.