Clickhouse really nailed this with the acquisition of peerdb. I used it with many terabyte databases and I essentially never thought about it. The only thing we really had to watch for was trying to replicate too much at once (because of the physical compute/io capacity of the postgres or clickhouse clusters).
This was basically Vertica's party trick for quite a long time to have a WOS and ROS formats for the same row and anti-caching between those two.
You could've built a similar system with dezebium and delta lake for quite some time but it would fail compactions, if you run it fast enough. I've seen Oracle GoldenGate 12c do this trick in 2014 or so, using Mysql as the cheap replica. But they are all fragile to schema updates in some direction.
The closest batteries-included equivalent to this is the Aurora -> Redshift bridge[1].
Aurora zero etl was a nightmare for us. Almost any schema changes require a VACUUM FULL for it to continue functioning. On a few occasions, it just stopped running without an obvious explanation, requiring slow and lengthy back and forth threads with AWS support. If it worked as advertised, it would be great, but I can't recommend it for any serious production system.
The challenge is converting primary key updates/deletes to row offsets in a columnar table. That requires maintaining an expensive mapping or doing expensive scans, and is not something you want Postgres itself to do. It's also this bursty, memory-intensive workload that you'd rather not have a lot of dedicated infrastructure for.
At Snowflake we use Snowflake to do the apply work. Hence end-to-end mirroring has more pieces than just Postgres, but the capturing of changes is cheap enough to do in Postgres directly.
Yes I’m not suggesting to do this inside Postgres. I’m hoping that a Postgres provider can provide this mirroring capability out of the box, similar to how they provide a connection-pooled endpoint out of the box so I don’t have to self-host pgBouncer. I just want to be able to check a box somewhere and have a table in Postgres automatically mirrored to Iceberg, with guarantee that no data is lost. They can charge more for it, I will gladly pay.
> I'm still waiting for a Postgres provider to just let me mirror data directly to Iceberg, so I can plug in my own stateless query engine.
The issue that each of those providers above has recently adopted Postgres as a secondary product aimed at supporting their main product, an OLAP database or engine, so they don’t want you plugging in your own query engine.
I’d bet you’re likely to see this from a Postgres-specific provider first, like Supabase.
Snowflake/Crunchydata comes close to doing that with the pglake extension.
There’s not a mirror function like what Snowflake offers directly but you can come close with a pgcron to upsert changes to the iceberg tables every so often.
You can also purge the table put to the iceberg version every so often too depending on your data needs. Then you can create a query unions the results of both.
Agreed. I personally am very uncomfortable with the idea of SaaS-only DB tech. Databases I deal with are very large. And we are a small company. Self-hosted works for us. We cannot afford hosted database solutions as they charge by storage and some also by data transfer. From my point of view, 3rd party hosting of databases solves problems we don't have. Particularly with AI tools managing our services using ansible/terraform, I think we'd be worse off if we switched to a SaaS product.
pg_lake is an open source Postgres extension based on work done at Crunchy Data prior to the acquisition by Snowflake - you can find the repo here [1] and a blog post with more context on the project here [2]
I see you are a co-founder! Thanks for pg_lake. I am actually already heavily using it, was just interested to read about CDC in the context of pg_lake.
You could've built a similar system with dezebium and delta lake for quite some time but it would fail compactions, if you run it fast enough. I've seen Oracle GoldenGate 12c do this trick in 2014 or so, using Mysql as the cheap replica. But they are all fragile to schema updates in some direction.
The closest batteries-included equivalent to this is the Aurora -> Redshift bridge[1].
[1] - https://aws.amazon.com/rds/aurora/zero-etl/
- ClickHouse focuses on traditional CDC (ClickPipes) and just make it blazingly fast
- Databricks leans on their unified storage architecture (LTAP) to avoid copying data (though you can argue there is still a copy in the cache)
- Snowflake uses a data mirroring CDC as extension so it runs directly on Postgres
I'm still waiting for a Postgres provider to just let me mirror data directly to Iceberg, so I can plug in my own stateless query engine.
At Snowflake we use Snowflake to do the apply work. Hence end-to-end mirroring has more pieces than just Postgres, but the capturing of changes is cheap enough to do in Postgres directly.
(Author)
The issue that each of those providers above has recently adopted Postgres as a secondary product aimed at supporting their main product, an OLAP database or engine, so they don’t want you plugging in your own query engine.
I’d bet you’re likely to see this from a Postgres-specific provider first, like Supabase.
If you count AWS as Postgres provider, DMS into Kinesis into Firehose can do that.
There was preview of just Firehose doing it directly, but AWS have pulled it because it was too unreliable. Maybe they rebuilt it since?
There’s not a mirror function like what Snowflake offers directly but you can come close with a pgcron to upsert changes to the iceberg tables every so often.
You can also purge the table put to the iceberg version every so often too depending on your data needs. Then you can create a query unions the results of both.
EDIT: I now see it's mainly to do with pushing data out of customer's postgres systems into snowflake
There's a bunch of comments/links to a closed https://github.com/snowflake-eng/sfpg-extension-pg_lake_repl...
[1] https://github.com/Snowflake-Labs/pg_lake
[2] https://www.snowflake.com/en/blog/engineering/pg-lake-postgr...
The article links https://github.com/Snowflake-Labs/pg_lake but if you go looking for CDC, it's not there, and all you end up with is links to the private/closed repo that I just linked, in various corners and spots. The point is that all the CDC stuff is in the repo we don't get access to, that isn't open source: https://github.com/snowflake-eng/sfpg-extension-pg_lake_repl...