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.
Comparison guide Published 8 min read
- 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.
| Point | Log-based | Query-based | Trigger-based |
|---|---|---|---|
| How it works | Reads the transaction log, where the database already writes every committed change | Queries each table on a schedule for rows changed since the last run | A trigger on each table writes every insert, update and delete to a change table, which is then read |
| Sees deletes | Yes: a delete is in the log like any other change | Not usually: a deleted row is no longer there to find | Yes: the delete trigger records the row |
| Sees every update | Yes, each one, in commit order | No: a row changed several times between two runs is seen once, in its last state | Yes: every write fires the trigger |
| Load on the source | Low: no extra queries on the tables and no extra work on each write | Grows with the size of the tables and with how often the queries run | Added to every write, inside the same transaction |
| Permissions needed | Access to the log, and log settings agreed with the database administrator | Read access to the tables, and a "last changed" column on each one | Rights to create triggers and change tables in the schema |
| Usual delay | Close behind the source, because the log is read as it is written | At least the gap between two runs | Close behind the source, plus the time to read the change table |
| When it fits | The source allows log reading and its administrator agrees the settings | Tables that only receive new rows, or where deletes are rare and the "last changed" column is reliable | Log 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.
| Engine | Where the changes are | What is usually set up | What to watch |
|---|---|---|---|
| PostgreSQL | The write-ahead log (WAL) | Logical decoding: the log level set to logical, a replication slot, and an account with replication rights | A slot keeps the log on disk until the reader confirms it, so a stopped reader can fill the disk |
| MySQL | The binary log | Row-based logging, enough log retention, and an account with replication rights | Logs expire after the set period; a reader that falls behind it loses changes |
| SQL Server | The transaction log | The engine's own change data capture, turned on for the database and for each table, with its capture job running | The log cannot be cleared past changes not yet captured, so a stopped job makes the log grow |
| IBM Db2 | The recovery log | Tables altered to capture full row changes in the log, and logs archived rather than overwritten | Logs 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.
Relatediblync
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
Read next
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.
What happens next
- We read your request
- An engineer contacts you
- 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