Every data sync starts the same way. Someone needs system B to know when a row changes in system A's database, and the fastest thing to build is a scheduled job that runs every few minutes, queries for anything new, and pushes it downstream. It works. It ships fast. And it keeps working right up until the table it's polling stops being small.
The failure isn't sudden. It's a slow accumulation of small compromises that each seemed reasonable on their own, until the sync job is costing more engineering attention than the feature it was supporting.
The Query Gets Slower as the Data It's Meant to Skip Gets Bigger
A typical polling query looks something like WHERE updated_at > last_run_time. That's fine on a table with ten thousand rows and a solid index on updated_at. It's still fine on a table with a million rows, mostly. It stops being fine once the table has tens of millions of rows and the index has to scan through significantly more data to find the sliver that's actually new, especially if updated_at values cluster unevenly across time, which real production data almost always does.
The query doesn't fail outright. It just gets a little slower every quarter, in a way that's easy to miss until someone notices the sync job that used to finish in ten seconds now takes four minutes, and nobody remembers exactly when that started.
Hard Deletes Don't Show Up At All
Polling on a timestamp column only catches inserts and updates that touched that column. A hard delete, a row that's simply gone, leaves nothing behind for a WHERE updated_at > query to find. The downstream system never learns the row was removed, and it keeps serving stale data that no longer exists on the source side, sometimes indefinitely, until someone notices a record downstream that shouldn't exist anymore.
Teams work around this with soft deletes, a deleted_at column instead of an actual DELETE, which solves the visibility problem but adds its own tax: every query against that table now needs to filter out soft-deleted rows, forever, and eventually someone forgets to add that filter somewhere and ships a bug.
The Race Condition Nobody Notices Until It Matters
Polling queries have an inherent timing gap. If a row is updated at the exact moment a poll is running, depending on transaction isolation and exactly when the write commits relative to the query's snapshot, that change might get picked up this run, next run, or in an unlucky ordering, get missed entirely if a later update to the same row resets the timestamp window in a way the query doesn't account for.
This kind of bug is genuinely hard to catch in testing because it only shows up under real concurrent write load, and by the time it's visible in production, the missing update could have happened days or weeks ago. Debugging "why is this one specific row wrong" when the root cause is a timing edge case in a polling query is a miserable way to spend an afternoon.
Locking Becomes Tempting, and Locking Is Where It Gets Genuinely Painful
To make polling more accurate, teams often reach for a transaction that holds a consistent read, sometimes with an explicit lock, to guarantee nothing changes mid-query. That's a reasonable instinct for correctness. It's also how a sync job starts contending with the same table your application is writing to for checkout, signups, or whatever your product actually depends on in real time.
A lock that holds for a few hundred milliseconds on a quiet table is invisible. The same lock on a table under real production write load queues up every competing write behind it, and what used to be an invisible background job becomes the reason checkout felt slow for ninety seconds during a routine sync.
The Engineering Tax Nobody Budgeted For
None of the problems above show up as a single dramatic failure that forces a redesign. They show up as a steady accumulation of workarounds, a retry wrapper here, a manual reconciliation script there, a "just re-run it for that customer" fix that becomes a monthly ritual. Each individual patch is small and easy to justify in the moment. Collectively, they turn a sync job that was supposed to be a background utility into something that eats real engineering time every sprint.
The tell is usually in how the team talks about the sync job. Once people start saying "don't touch that, it's fragile" or scheduling changes around it out of caution rather than necessity, the job has quietly become a liability the team is managing rather than a tool that's just working. That's usually the point where the cost of migrating to something more resilient stops being a hard sell, because everyone's already feeling the tax firsthand.
What Actually Fixes This
The underlying problem with polling isn't the interval, running more often doesn't fix any of the issues above, it just makes them happen more frequently. The actual fix is changing what the sync job is reading from. Instead of querying the table and inferring what changed, change data capture reads the database's transaction log directly, the same log PostgreSQL and other databases already maintain for their own replication.
The transaction log records every insert, update, and delete as a side effect of normal writes, in order, with no query needed against the live table at all. Deletes show up as delete events instead of vanishing silently. Updates carry both old and new values instead of just a changed timestamp. And because reading a log is fundamentally different from querying a table, there's no lock contention with your application's normal traffic, the read path and the write path never compete for the same resource.
Tools like Debezium implement this pattern for Postgres, MySQL, and several other databases, streaming changes into Kafka or a similar durable log that multiple downstream consumers can read from independently, at their own pace, without any of them touching the source table directly.
When Polling Is Still Fine
None of this means polling is always wrong. A low-write table, an internal admin tool, a sync that genuinely only needs to run once a day against a table nobody else is hammering, polling is simpler to build and operate, and simplicity has real value when the downsides above don't apply yet. The mistake isn't choosing polling, it's not noticing when the table's growth or write volume has quietly crossed the point where those downsides start being felt.
We've walked several teams through exactly this migration, from a polling job that used to work fine to a log-based pipeline that stopped competing with production traffic. The full writeup on building a change data capture pipeline covers the architecture end to end, including schema drift handling and lag monitoring, and it's a useful read once your polling job starts showing any of the symptoms above. For more on how we build these systems generally, https://137foundry.com has further background on the kinds of integrations this pattern shows up in.
The honest signal that it's time to move off polling isn't a specific row count or a specific interval, it's the moment the sync job starts requiring its own incident response plan. That's usually a good indicator the architecture, not the schedule, needs to change.
Top comments (0)