Change Streams vs Triggers vs Polling: Three Ways to Detect Changes

Polling, triggers, and change streams are the three ways a system learns that a row changed, and they differ on who does the work and when. Polling asks the table what changed since the last run, triggers write a change record inside the write transaction, and change streams read the database log after commit. As of October 2026 every major database ships at least one of them natively.

The Debezium project calls polling query-based capture, and Postgres calls its change stream logical decoding.

Which change detection mechanism does each database ship?

Postgres and MySQL ship both triggers and a log reader, SQL Server adds two engine-side tracking features, and the document stores expose a change feed with no trigger layer at all.

How does polling for changes work?

A poller stores the highest cursor value it has seen, usually an updated_at timestamp, an auto-increment id, or the Postgres xmin column, and on each run selects rows above it. The dlt docs show the whole contract in one line.

updated_at = dlt.sources.incremental("updated_at", initial_value="1970-01-01T00:00:00Z")

updated_at = dlt.sources.incremental("updated_at", initial_value="1970-01-01T00:00:00Z")

updated_at = dlt.sources.incremental("updated_at", initial_value="1970-01-01T00:00:00Z")

Polling is cheap to build and blind to three kinds of change. The first is deletes. The Debezium blog is direct. "Polling will not allow you to identify any records that have been deleted since the last poll."

The second is anything that happens between two polls. Fivetran's Postgres docs say its Query-Based method "does not capture data that exists for an amount of time smaller than the sync interval."

The third is the late commit. In Postgres, now() returns the start time of the transaction, not the commit time. A row written by a long transaction therefore carries a timestamp older than rows the poller already read past. The Confluent JDBC source docs admit that on busy tables incrementing mode "always results in some data loss."

The patch is a trailing window, which Confluent exposes as timestamp.delay.interval.ms and dlt as a lag setting.

Trigger-based capture inside the write transaction

A trigger-based capture puts an AFTER trigger on each table that copies the changed row into a change table, so the record of the change commits with the change itself. The Postgres manual states that a trigger "is executed as part of the same transaction as the statement that triggered it." MySQL says the same. "Failure of a trigger causes the statement to fail, so trigger failure also causes rollback."

CREATE TRIGGER orders_changes
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_order_change();

CREATE TRIGGER orders_changes
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_order_change();

CREATE TRIGGER orders_changes
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_order_change();

That shape is adapted from the CREATE TRIGGER reference, and it is the design behind SymmetricDS, Slony-I, and Bucardo.

The cost lands on every write, and the gaps are structural. A Cybertec benchmark from 2018, a vendor blog rather than the manual, measured up to 12 percent extra write latency. Microsoft says its synchronous Change Tracking overhead "will frequently be less than" that of triggers.

The gaps are the operations that never fire a row trigger. Per the TRUNCATE reference, "TRUNCATE will not fire any ON DELETE triggers that might exist for the tables." MySQL documents the same and adds that "cascaded foreign key actions do not activate triggers." Slony-I's limitations page is blunt that "neither schema changes nor large object operations are captured by triggers."

How do change streams read the database log?

A change stream is a cursor over the database's own commit log, so the reader receives every committed insert, update, and delete in commit order without touching the tables. The Postgres output plugin docs carry the guarantee that matters most. "Concurrent transactions are decoded in commit order," and rolled back transactions never get decoded.

pg_recvlogical -d shop --slot=orders_stream --create-slot
pg_recvlogical -d shop --slot=orders_stream --start -f

pg_recvlogical -d shop --slot=orders_stream --create-slot
pg_recvlogical -d shop --slot=orders_stream --start -f

pg_recvlogical -d shop --slot=orders_stream --create-slot
pg_recvlogical -d shop --slot=orders_stream --start -f

Those two commands, from the logical decoding example, need wal_level set to logical and one replication slot. The slot is the stream's bookmark. It persists across crashes, and slots "will prevent removal of required resources even when there is no connection using them," which is both the feature and the hazard.

The restrictions page adds that "the database schema and DDL commands are not replicated," so a Postgres consumer watches the catalog itself.

MySQL's equivalent is the binary log, on by default in 8.4 with row-based logging as the default format. MongoDB exposes the oplog through watch(), and the change streams docs require a replica set and make every event's _id a resume token.

db.orders.watch([], { fullDocument: "updateLookup" })

db.orders.watch([], { fullDocument: "updateLookup" })

db.orders.watch([], { fullDocument: "updateLookup" })

A log reader still needs a snapshot to start from. Logs are pruned, so the Debezium Postgres connector first takes "an initial consistent snapshot of the database." Postgres closes that gap cleanly, because creating a slot exports a snapshot showing "exactly the state of the database after which all changes will be included in the change stream."

Where each mechanism goes wrong

Every mechanism has one failure that recurs in production, and most show up as a silent gap rather than an error. These three earn a runbook entry.

Polling misses the row that committed late

A long transaction stamps its rows with its start time and commits after the poller has moved past that time, so the rows are never read. Confluent's fix is a delay window larger than "the duration of the longest write transaction on the source tables," which assumes you know that duration.

A replication slot nobody reads fills the disk

A Postgres slot holds WAL until its consumer confirms it, forever by default. In extreme cases, the manual warns, "this could cause the database to shut down to prevent transaction ID wraparound." Airbyte tells users who stop a CDC sync to delete the slot or "it may fill up your disk space." Set max_slot_wal_keep_size before the first slot exists.

The log expires before the reader catches up

The opposite failure is a log pruned while the reader was down. MongoDB raises error 286, ChangeStreamHistoryLost, because "the resume point may no longer be in the oplog." Airbyte restarts a Postgres sync from scratch when the needed WAL "has been removed."

How Filament detects changes

Filament ships two of the three mechanisms, a watermark poll and a log reader, and no trigger mode. Its replication modes page puts the choice plainly. An incremental route on updated_at "is lighter than rereading the table, but it cannot see a hard delete," so "use CDC when deletes or source transaction order must propagate." Filament is built by Galaxy, which also publishes this site.

The poll mode is built around the late-commit problem. The Postgres source, which is beta, pages a compound (cursor, pk...) watermark inside a repeatable-read transaction, and "a default 300-second lookback rewinds the low bound to absorb late commits and clock skew," with the duplicates absorbed by upsert. The first run captures the high watermark and backfills by primary key, so changes committed during the backfill are replayed rather than lost.

# Postgres source keys from the connector page, connection and pipeline scope
replication: cdc          # standard for full or incremental reads
publication: filament     # created and kept current by Filament
snapshot_mode: initial    # none streams from the current position only

# Postgres source keys from the connector page, connection and pipeline scope
replication: cdc          # standard for full or incremental reads
publication: filament     # created and kept current by Filament
snapshot_mode: initial    # none streams from the current position only

# Postgres source keys from the connector page, connection and pipeline scope
replication: cdc          # standard for full or incremental reads
publication: filament     # created and kept current by Filament
snapshot_mode: initial    # none streams from the current position only

The log mode streams pgoutput over a replication slot Filament creates and names, and it needs wal_level=logical and a primary key on every selected table. The bootstrap uses the exported snapshot from the Postgres manual, so "the baseline and the change stream share one consistent point."

On MySQL the source connects as a binlog replica, prefers GTID positions, and holds read locks "for the instant between capturing the position and opening the snapshot." Steady state on both is a bounded catch-up cycle, so continuous CDC can run on a schedule.

The limits are on the page too. CDC rejects keyless tables, incremental needs a NOT NULL timestamp cursor, the MySQL incremental lookback defaults to zero, and there is no MongoDB source, so change streams are not covered at all.

Which tools support change data capture and which still poll?

As of October 2026, every mainstream replication tool reads the log on Postgres and MySQL, most read MongoDB change streams, and polling survives only as an opt-in mode in Filament, Airbyte, Fivetran, dlt, Sling, and the Singer taps.

Which mechanism fits which workload

Read the log when deletes matter or the source is busy, use triggers only when you cannot get log access, and poll only append-only tables with an indexed cursor.

Polling is a default inherited from a time before every engine exposed its log, and it stays wrong for anything with a DELETE in its workload.

Related guides

• What is change data capture, the complete guide

• Every Postgres change data capture method compared

• MySQL binlog replication explained

• Postgres logical replication, the complete guide

• Full load vs incremental vs change data capture, which to use

• Checkpointing in data pipelines, pause, resume, recover

Frequently asked questions

What is the difference between polling and change data capture?

Polling queries a table for rows above a stored cursor, so it only sees the state that exists at poll time. Change data capture reads the database log or a trigger-written change table, so it sees every committed insert, update, and delete, including rows rewritten between two polls.

Can polling detect deleted rows?

No. A hard delete removes the row, so a query for rows above a cursor never returns it. Polling only tracks deletes when the application soft deletes with a flag column, or when a tool scans every row id against the previous run, which is how Fivetran's Capture Deletes option works.

Are database triggers a reliable way to capture changes?

Triggers capture every row the statement touches and commit inside the same transaction, so nothing is missed or duplicated. The gaps are structural. TRUNCATE skips delete triggers in Postgres and MySQL, cascaded foreign key actions skip triggers in MySQL, and triggers never see schema changes or large objects.

How much do triggers slow down writes?

The trigger runs inside the write transaction, so every captured change costs an extra insert before the statement commits. A Cybertec benchmark on Postgres measured up to 12 percent extra latency depending on what the trigger logged, and Microsoft documents its synchronous Change Tracking as cheaper than triggers.

What is a MongoDB change stream and does it need a replica set?

A change stream is a cursor over the MongoDB oplog that delivers insert, update, replace, and delete events as they commit, with a resume token on every event. It is only available on replica sets and sharded clusters running WiredTiger, so a standalone mongod has to become a single-node replica set first.

What happens when the oplog or binlog expires before the reader catches up?

The reader loses its place and has to start over with a fresh snapshot. MongoDB raises error 286, ChangeStreamHistoryLost, when the resume token is no longer in the oplog, and Airbyte restarts a Postgres sync from scratch when the WAL its offset points at is gone. Size retention to the longest outage you expect.

Does Postgres logical decoding capture schema changes?

No. The Postgres docs state that the database schema and DDL commands are not replicated, and the same restriction covers sequences and large objects. A column added at the source shows up only as new values in later row events, so the consumer detects it from the data or watches the catalog.

Which ELT tools support change data capture?

Filament, Debezium, Airbyte, Fivetran, Estuary Flow, Sling, PeerDB, and the Meltano taps all read the log on Postgres and MySQL, and all but Filament also read MongoDB change streams. dlt reads the Postgres log through a separate pg_replication source and otherwise polls a cursor column. SymmetricDS and Bucardo capture with triggers.

How does Filament detect changes in Postgres?

Filament reads Postgres either by a watermark poll or by logical decoding. The poll pages a compound cursor and primary key watermark with a 300-second lookback for late commits, and the log mode streams pgoutput from a slot Filament manages, bootstrapped from an exported snapshot. Both need a primary key, and neither covers MongoDB.