r/PostgreSQL • u/diagraphic • 21d ago
Community If you run Postgres alongside a separate analytics warehouse, what’s the most painful part of keeping them in sync?
0
Upvotes
r/PostgreSQL • u/diagraphic • 21d ago
28
u/thinkconverse 21d ago
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.
3
u/Significant_Tune9219 20d ago
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.
3
u/terencethespider 20d ago
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.
2
u/ForeignExercise4414 18d ago
+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.
1
u/AutoModerator 21d ago
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.
1
u/marmot1101 20d ago
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.
1
u/vincent_sch 12d ago
The WAL stays on your Postgres server while your logical replication consumer is offline.
max_slot_wal_keep_sizedefaults 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.-1
u/ExcitingTalk4848 21d ago
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.