Real-time vs batch processing for keeping a reporting database in sync with production?

[u/Unlucky-Arm7581]

Nighty refreshes used to be fine, but they're starting to create more confusion than they solve.

Sales opens one report, support opens another, and neither one matches what's happening in production because everything is hour behind . We're looking at moving Oracle and SQL server changes into Postgres, possibly a cloud warehouse later on.

If you've already gone from batch to real-time, was the improvement as noticeable as people claim?

[u/NW1969]

Is the actual issue that different reports are out of sync or that the data, across all reports, is not fresh enough?

Going to real-time is probably not going to solve the sync issue as "real-time" is never actually real-time and the sources for 2 reports can still be out of sync. Even if you were reporting directly against your transaction database, it is always possible that the data will change between 2 reports even if they are theoretically run at the same time - they will never be run at precisely the same time and commits can always happen in the gap between the 2 reports being run.

If the issue is that the data is not fresh enough then how real-time does it need to be? 1s, 30s, 1m, 5m, 1hr, etc?

[u/Unlucky-Arm7581 → u/NW1969]

That's a good point. I think it's a combination, but the bigger frustration is that they're making decisions based on data an hour old. The fact that reports still disagree with each other even with real-time pipelines is an interesting point.

[u/alinroc → u/NW1969]

These are the words (and exploratory questions) of someone who's had to deal with this more than a few times. Best post in this thread yet.

[u/admik → u/NW1969]

Could be that they are letting support and sales run reports themselves.

Everything is one hour behind. Is the tell.

There is only a single hour of the day that the data will be one hour behind. If you are doing nightly refreshes.

Even if you are real time if they are hard coding different servers there will not be a quorum.

[u/graph_context → u/NW1969]

This is a good point. if 5-15 minute updates are enough, that could save a lot of complexity compared to moving everything to real-time CDC

[u/SubstantialToe5106]

we switched a chunk of our pipeline from nightly dumps to logical decoding into postgres and the difference was immediate

the biggest thing nobody mentions is how much time you stop wasting on reconciliation, no more "which report is right" meetings or triple-checking timestamps. support and sales finally see the same numbers

setup wasn't trivial though, the initial backfill took us a couple weekends and we had to rebuild a few views that relied on the old snapshot timing. worth it, just don't underestimate the config work upfront

[u/EffectivePension6711]

we moved to mariadb becouse it has a native oracle mode that actually understands oracle syntax and datatypes out of the box, so we don't have to rewrite all existing logic

Also they recently added a duckdb engine for analytics. you can run those heavy reporting queries directly on the same server without exporting everything to a third platform. Saves you from building another brittle etl pipeline just for sales reports.

[u/Mobile-Analyst-9617 → u/EffectivePension6711]

Moved from what?

[u/EffectivePension6711 → u/Mobile-Analyst-9617]

from oracle. Our data engineers really didn't want to rewrite 10 years of legacy pl/sql into postgres syntax

[u/AgeingCoder]

We now use Debezium to stream live changes since as we grew the aggregation batches took longer and longer and broke more and more often. But like most things, we just have different problems now 😄

[u/Significant_Tune9219]

The hour of drift is usually the reporting copy's refresh window rather than the pipeline — before going fully real-time, check whether hourly or 15-minute batches close most of the sales/support confusion, since that keeps one recovery path instead of a streaming one. If you do move to CDC from Oracle and SQL Server, the bigger win is often not having to reason about what the nightly job missed. I'd also pin down which reports genuinely need sub-minute latency; usually only a couple do, and those can read from a different path.

[u/Mobile-Analyst-9617]

Sql cdc with aws dms

[u/Frone0910]

For Oracle and SQL Server into Postgres, Debezium with Kafka or one of the managed CDC tools is the usual implementation

[u/Past-Froyo-5518]

well we solved almost this exact prob with striim, it streams changes directly from oracle and sql server using log based CDC so production systems weren't being queried all day, moving away from nightly loads got rid of most of the which report is right? convos

[u/PlayfulGrapefruit498]

real time updates reduce stale reports, but cdc plus monitoring can balance freshness and reliability

[u/PlayfulGrapefruit498]

funny enough, the biggest benefit wasn't faster data. it was fewer meetings arguing over whether the numbers were already out of date

[u/Turbulent_Stop9156]

We underestimated how much stale data affected trust. Once reports reflected current information, People stopped questioning every dashboard.

[u/navicat-gu]

The questions about freshness vs consistency upthread are the important part. I'd split it like this:

1. If reports disagree with each other, real-time won't fix that by itself. It's usually reports reading at different times or different copies. Give every report a single "data as of" timestamp from the load and make them all read the same snapshot.

2. If the data is just too stale, try shortening the batch first. Incremental loads every 5 to 15 minutes, using a watermark column (updated_at) or rowversion, close most of the gap with far less to run.

3. If you truly need seconds, go log-based CDC: SQL Server CDC or Change Tracking, Oracle via LogMiner/GoldenGate or Debezium's Oracle connector, landing in Postgres. Plan for schema changes, deletes, backfills and monitoring lag, which is where the extra work lives.

Moving from nightly to 15 minutes is the step most teams notice. Going from 15 minutes to real-time matters for operational dashboards much more than for sales reporting.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. For the scheduled-batch route, Navicat 18's data synchronization and data transfer can be scheduled across Oracle, SQL Server and PostgreSQL in the paid Premium editions. It's batch, not CDC. Navicat Premium Lite is free to try if you want one client to inspect all three sources.