Data platform

Log-based vs query-based CDC: three ways to capture changes, compared

Three ways to capture database changes, what each one asks of the source system, and how to choose between them before you pick a tool.

  • Built and supported by Interkey An Interkey product, delivered and supported by Interkey
  • One platform for migration and continuous sync The move and the replication that follows it
  • Written quote after one meeting The price is discussed in a meeting about your systems

Log-based CDC reads the database's own transaction log, so it sees every change with little load on the source. Query-based CDC polls tables on a schedule and can miss deletes and intermediate updates. Trigger-based CDC records changes with database triggers and adds work to every write. The right method depends on the source database and on who administers it.

The three methods side by side

The same seven points, asked of each method.

Log-based, query-based and trigger-based change data capture compared on seven points
PointLog-basedQuery-basedTrigger-based
How it worksReads the transaction log, where the database already writes every committed changeQueries each table on a schedule for rows changed since the last runA trigger on each table writes every insert, update and delete to a change table, which is then read
Sees deletesYes: a delete is in the log like any other changeNot usually: a deleted row is no longer there to findYes: the delete trigger records the row
Sees every updateYes, each one, in commit orderNo: a row changed several times between two runs is seen once, in its last stateYes: every write fires the trigger
Load on the sourceLow: no extra queries on the tables and no extra work on each writeGrows with the size of the tables and with how often the queries runAdded to every write, inside the same transaction
Permissions neededAccess to the log, and log settings agreed with the database administratorRead access to the tables, and a "last changed" column on each oneRights to create triggers and change tables in the schema
Usual delayClose behind the source, because the log is read as it is writtenAt least the gap between two runsClose behind the source, plus the time to read the change table
When it fitsThe source allows log reading and its administrator agrees the settingsTables that only receive new rows, or where deletes are rare and the "last changed" column is reliableLog reading is not available, and the source can take extra work on each write

Each cell describes the method in general. How a given tool applies it differs by source engine and version, so ask any supplier, including us, which method they will use for each of your sources.

Log-based CDC

Every relational database keeps a transaction log: before a change is committed, it is written there, so the database can recover after a failure. Log-based capture reads that log as it grows and turns each record into a change: an insert, an update with its before and after values, or a delete. Nothing is added to the tables or the application, so the load on the source stays low and no change is missed.

Read more

The work sits with the database administrator. The capture account needs the right to read the log. The log often has to carry more detail than the database needs for itself, through a setting the administrator turns on. And the log has to be kept long enough for the reader to get to it, in step with how logs are archived and backed up.

This is also where the method stumbles. If the reader stops and the log is cleared before it catches up, the missing changes cannot be read again, and the target has to be copied afresh from a known point. Log formats differ between engines and between versions, so an upgrade of the source is a moment to test the capture again.

Query-based CDC

Query-based capture, often called polling, asks each table one question on a schedule: which rows have a "last changed" value newer than the last run? The answer is copied to the target, and the time of the run is saved for the next one. It needs read access and nothing more from the database administrator.

What it misses follows from the question. A row deleted outright is no longer there to be found, so deletes are lost unless the application marks rows as deleted instead of removing them. A row changed three times between two runs is seen once, in its last state. And the method trusts the "last changed" column: if a script updates rows without setting it, those changes never travel.

It is enough for tables that only receive new rows, and for a nightly copy where the final state is all that matters.

Trigger-based CDC

Trigger-based capture adds a trigger to each table in scope. On every insert, update and delete, the trigger writes the change, often with the old and new values, into a change table, and the capture process reads that table and empties it. It sees deletes and every update, and it works on engines where the log cannot be read.

Read more

The cost is paid on every write. Each transaction now writes twice, so busy tables slow down, and a fault in a trigger can stop the application's own write. The triggers and change tables are part of the schema: they must be deployed, versioned and tested with it, and they are easy to forget when a table changes. It fits when log reading is not an option and the write load is moderate.

What log-based capture needs on each engine

General points from each engine's documentation. Settings change between versions, so check the documentation for yours.

Where four database engines record their changes, and what log-based capture usually needs on each
EngineWhere the changes areWhat is usually set upWhat to watch
PostgreSQLThe write-ahead log (WAL)Logical decoding: the log level set to logical, a replication slot, and an account with replication rightsA slot keeps the log on disk until the reader confirms it, so a stopped reader can fill the disk
MySQLThe binary logRow-based logging, enough log retention, and an account with replication rightsLogs expire after the set period; a reader that falls behind it loses changes
SQL ServerThe transaction logThe engine's own change data capture, turned on for the database and for each table, with its capture job runningThe log cannot be cleared past changes not yet captured, so a stopped job makes the log grow
IBM Db2The recovery logTables altered to capture full row changes in the log, and logs archived rather than overwrittenLogs must stay available, on disk or in the archive, until they are read

For more on each engine, see PostgreSQL replication, MySQL replication, SQL Server replication and IBM Db2 migration. Capture from a commercial source engine is covered on our page about reading changes from a commercial database's log.

Five questions that decide the method

Answer these for each source before you look at tools. The answers usually choose the method for you.

  • Do you need deletes on the target? If yes, plain query-based capture is ruled out unless rows are marked as deleted.
  • Do you need every intermediate state of a row, or only the latest one? Auditing and event processing need every state; a reporting copy often does not.
  • Who owns the source database? Log settings and triggers both need the agreement of the team that administers it.
  • What delay is acceptable on the target? Minutes between runs may be fine for a report and not for an application that reads the target.
  • Does the supplier of the database, or of the application on it, allow its log to be read? Check the support terms before you design around it.

Where iblync fits

iblync is Interkey's platform for migration and continuous real-time sync between different systems. The capture method for each source is decided during the evaluation, once the source, its version and the rules of the team that administers it are known. Transfer speed and resource use on the source can be limited, and if the target stops, changes wait in a queue and are applied when it returns.

Scope

What this page covers

This page compares capture methods in general. It does not describe how any one tool, iblync included, captures changes from a given engine. If you are still choosing a platform, start with our criteria for comparing data replication tools.

Questions about change data capture methods

What is log-based CDC?

Log-based CDC reads the transaction log that the database already writes, to learn every committed change in the order it happened. Inserts, updates and deletes all appear there, with no extra queries on the tables and no triggers. It needs the right to read the log and log settings agreed with the database administrator.

What is query-based CDC?

Query-based CDC checks each table on a schedule for rows that changed since the last check, using a timestamp or a rising number in a "last changed" column. It needs only read access, but it sees each row in its latest state, and it misses rows deleted outright unless the application marks them as deleted.

Which CDC method is best?

Log-based capture is the most complete when the source database and the team that administers it allow it: it sees deletes and every update with little load. Query-based capture is enough for tables that only receive new rows. Trigger-based capture fills the gap when the log cannot be read and the source can take extra work on each write.

Does query-based CDC capture deletes?

Usually not. A row deleted outright is no longer in the table, so a query for changed rows cannot return it. To carry deletes, the application has to mark rows as deleted instead of removing them, or the deletes have to be captured another way, from the log or with a trigger.

Why does log retention matter for log-based CDC?

Because the reader can only read what the database still keeps. If capture stops and the log is cleared before it catches up, the missing changes are gone, and the target has to be copied again from a known point. Agree with the database administrator how long the log is kept and what happens to it during archiving.

When does trigger-based CDC make sense?

When the log cannot be read, because of the engine, the version or the rules of the database supplier, and the tables in scope are not written heavily. Triggers see deletes and every update, but each write does extra work, and the triggers have to be deployed and tested with the schema.

Next step

Ask an engineer one question

Tell us the source database, its version and who administers it. Ask one question about the capture method in front of you.

Required field

By sending this form, you agree to our Privacy Notice.

What happens next

  1. We read your request
  2. An engineer contacts you
  3. You get an estimate or a demo

Or directly

+966-11-2180999 [email protected]

Tawuniya Towers, North Tower, 7th Floor, King Fahad Highway, Olaya, P.O. Box 56835, Riyadh 11564, Saudi Arabia

or How change data capture works

Published by Interkey. Last updated . Interkey is registered in Riyadh, Saudi Arabia under commercial registration 1010156897.