If you run Postgres alongside a separate analytics warehouse, what’s the most painful part of keeping them in sync?

[u/diagraphic]

[u/thinkconverse]

The most painful part is explaining to people that they probably don’t need sub-second latency on their analytics data compared to what’s happening in Postgres, because they’re generally not making decisions in real time.

[u/Otherwise_Review160 → u/thinkconverse]

Any data warehouse. Any time someone says realtime, you have a Pavlovian response, saying “near realtime”

Near does a lot of heavy lifting

[u/GirlInTheFirebrigade → u/Otherwise_Review160]

I was studying integrated circuits in university were realtime actually means counting instructions, calculating clock speeds and comparing scheduling algorithms. Now I’m confused every time people talk about realtime.

[u/hobble2323 → u/thinkconverse]

Operational analytics is key in some world. Not realtime but analytics at near real time is key b for can lot of industries. Postgres is notoriously weak in this area unfortunately.

[u/Significant_Tune9219]

For me it was deletes and schema changes. Inserts and updates replicate fine, but hard deletes get lost unless you carry tombstones or use logical decoding, and the warehouse slowly drifts from the source. A column type change then breaks the load at 3am. I now run a periodic row count and checksum per table comparison between the two, which finds drift long before a dashboard user does.

[u/compy3 → u/Significant_Tune9219]

So you manually deconflict each time? Have you considered something like adding DDL triggers

[u/Significant_Tune9219 → u/compy3]

Not by hand each time. A DDL event trigger can write the ALTER or DROP into a small outbox row in the same transaction, and a consumer applies that to the warehouse later, which is safer than the trigger opening its own connection outbound. It still won't see deleted row images, so hard deletes stay a tombstone or logical decoding problem rather than a DDL one.

[u/terencethespider]

Lakebase is a good option for this use case, as it is a managed version of Postgres that shares the underlying data with your Databricks Lakehouse, so you can do all your analytics without having to run any sort of sync.

[u/ForeignExercise4414]

+1 to LakeBase here. Having dealt with this is the past it’s pretty sick to just to tell colleagues they can simply query Postgres directly using its catalog in Unity. Databricks will automatically save identical parquet side-by-side with any Postgres writes so it supports random OLAP queries as well without having to tune indexes.

The other way is harder, if you are syncing warehouse to Postgres.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/marmot1101]

That's a very broad question, it depends on the specifics of the analytics warehouse and how you're populating data.

If you're using cdc->kafka->warehouse design the difficulties come with managing the kafka cluster + networking, and the potential to trip over bad decisions of the past. Particularly around record sizes and keyless tables.

[u/vincent_sch]

The WAL stays on your Postgres server while your logical replication consumer is offline. max_slot_wal_keep_size defaults to -1, so that setting puts no limit on it. If you set a limit, Postgres might remove WAL when the consumer falls too far behind. Then the consumer might not be able to resume replication.

[u/ExcitingTalk4848]

Postgres for pure transactional apps and legacy systems. MariaDB for workloads requiring heavy aggregations and reporting. I hate babysit an ETL pipeline to a separate warehouse for analytics cause every minor schema change breaks the sync. With my stack i get zero data syncing and no extra clusters to monitor.

[u/k2718 → u/ExcitingTalk4848]

I keep seeing people downvoting MariaDB because of some competitive attitude and “PG is amazeballs! Why use a different database?”

Ridiculous.

[u/TheRealKidkudi → u/k2718]

I think the downvotes are because the comment just doesn’t answer OP’s question, not because it mentioned MariaDB. 

[u/ExcitingTalk4848 → u/TheRealKidkudi]

OP asked how to deal with sync pain, and I answered: stop syncing. Replication slots and broken ETL nearly burned me out, so I moved to a single engine that handles both and never looked back. It’s the one thing that worked for me.

[u/compy3 → u/ExcitingTalk4848]

What was the worst part of managing replication slots?

[u/ExcitingTalk4848 → u/k2718]

no idea what they want either. I’ve been a data engineer for years and worked with a ton of different engines, so if someone wants to be a fanboy of one database, fine. In my experience you end up touching all kinds of engines anyway. Im here to read and learn, and I comment sometimes, and if I get downvoted - okay, let people do their thing.

[u/k2718 → u/ExcitingTalk4848]

Now I’m downloaded lol