28 ms·

How We Pushed CDC into Postgres

152 points by craigkerstiens 2mo ago

whateveracct 2mo ago
Snowflake is a really amazing product. It's been a delight using it the last few years.
- arvyy 2mo ago
  as much praise as some people give to it, I feel deeply uncomfortable with an idea of SaaS-only DB tech that you don't have an option to self host
  - cheema33 2mo ago
    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.
- p_l 2mo ago
  Seeing Postgres articles from Snowflake surprises me a lot though given how there's zero relation between Snowflake the product and Postgres itself EDIT: I now see it's mainly to do with pushing data out of customer's postgres systems into snowflake
  - jbonatakis 2mo ago
    They acquired Crunchy Data and now have some of the most prominent Postgres developers working there.
gopalv 2mo ago
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]. [1] - https://aws.amazon.com/rds/aurora/zero-etl/ https://aws.amazon.com/rds/aurora/zero-etl/
- bastawhiz 2mo ago
  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.
  - fock 2mo ago
    And this is, when it doesn't delete data randomly: https://github.com/trinodb/trino/issues/28885 https://github.com/trinodb/trino/issues/28885 (some enterprise open source was slop before AI even it seems)
  - abdullahk0634 2mo ago
    [dead]
bastawhiz 2mo ago
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).
deleted 2mo ago
[deleted]
jauntywundrkind 2mo ago
Although pg_lake is open source, worth noting that it heavily refers to but is missing CDC capabilities. There's a bunch of comments/links to a closed https://github.com/snowflake-eng/sfpg-extension-pg_lake_replication https://github.com/snowflake-eng/sfpg-extension-pg_lake_repl...
- evertheylen 2mo ago
  I'm interested in pg_lake so I wanted to check out your link, but it seems to be internal to snowflake?
  - plaur782 2mo ago
    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] [1] https://github.com/Snowflake-Labs/pg_lake https://github.com/Snowflake-Labs/pg_lake [2] https://www.snowflake.com/en/blog/engineering/pg-lake-postgres-lakehouse-integration/ https://www.snowflake.com/en/blog/engineering/pg-lake-postgr...
    - evertheylen 2mo ago
      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.
  - jauntywundrkind 2mo ago
    Apologies, I could have introduced that better. The article links https://github.com/Snowflake-Labs/pg_lake 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_replication https://github.com/snowflake-eng/sfpg-extension-pg_lake_repl...
holydementor 2mo ago
[flagged]
hasyimibhar 2mo ago
It's interesting to watch how different companies that offer both Postgres and warehousing solution under 1 roof approach the same problem: - 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.
- jbonatakis 2mo ago
  > 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.
  - hasyimibhar 2mo ago
    I’m waiting for Cloudflare R2 to eventually support mirroring Postgres into R2 catalog. It seems like a nice fit because they already have R2 SQL.
  - kiwicopple 2mo ago
    > mirror data directly to Iceberg > I’d bet you’re likely to see this from a Postgres-specific provider first, like Supabase. we deprecated this feature in our ETL tool[0]. The functionality is still in there but we can't support some of the production features we'd need for data/schema guarantees Iceberg is still nascent - only supporting single-table transactions (at least when we tried). A lot of important CDC/transactional semantics were "a work in progress" upstream. We shifted our focus to ducklake, which stores the catalog in Postgres [0] https://github.com/supabase/etl https://github.com/supabase/etl
  - wasifaleem 2mo ago
datadrivenangel 2mo ago
Now fivetran has more competition. Good.
wlindley 2mo ago
Excellent that Control Data is contributing! Oh wait, I'm a few decades out of sync
- kps 2mo ago
  I'm afraid nobody (else) here remembers Control Data Corporation. But I'm happy to see the Centers for Disease Control using Postgres.
quotrend 2mo ago
[flagged]
aboardRat4 2mo ago
What did Center for Disease Control use before? Mysql?
- bjt 2mo ago
  In this case CDC = Changed Data Capture.
deleted 2mo ago
[deleted]
clovisge 2mo ago
[flagged]

152 points by craigkerstiens 2mo ago

whateveracct 2mo ago

Snowflake is a really amazing product. It's been a delight using it the last few years.

arvyy 2mo ago
as much praise as some people give to it, I feel deeply uncomfortable with an idea of SaaS-only DB tech that you don't have an option to self host
- cheema33 2mo ago
  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.
p_l 2mo ago
Seeing Postgres articles from Snowflake surprises me a lot though given how there's zero relation between Snowflake the product and Postgres itself EDIT: I now see it's mainly to do with pushing data out of customer's postgres systems into snowflake
- jbonatakis 2mo ago
  They acquired Crunchy Data and now have some of the most prominent Postgres developers working there.

gopalv 2mo ago

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]. [1] - https://aws.amazon.com/rds/aurora/zero-etl/ https://aws.amazon.com/rds/aurora/zero-etl/

bastawhiz 2mo ago
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.
- fock 2mo ago
  And this is, when it doesn't delete data randomly: https://github.com/trinodb/trino/issues/28885 https://github.com/trinodb/trino/issues/28885 (some enterprise open source was slop before AI even it seems)
- abdullahk0634 2mo ago
  [dead]

bastawhiz 2mo ago

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).

deleted 2mo ago

[deleted]

jauntywundrkind 2mo ago

Although pg_lake is open source, worth noting that it heavily refers to but is missing CDC capabilities. There's a bunch of comments/links to a closed https://github.com/snowflake-eng/sfpg-extension-pg_lake_replication https://github.com/snowflake-eng/sfpg-extension-pg_lake_repl...

evertheylen 2mo ago
I'm interested in pg_lake so I wanted to check out your link, but it seems to be internal to snowflake?
- plaur782 2mo ago
  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] [1] https://github.com/Snowflake-Labs/pg_lake https://github.com/Snowflake-Labs/pg_lake [2] https://www.snowflake.com/en/blog/engineering/pg-lake-postgres-lakehouse-integration/ https://www.snowflake.com/en/blog/engineering/pg-lake-postgr...
  - evertheylen 2mo ago
    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.
- jauntywundrkind 2mo ago
  Apologies, I could have introduced that better. The article links https://github.com/Snowflake-Labs/pg_lake 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_replication https://github.com/snowflake-eng/sfpg-extension-pg_lake_repl...

holydementor 2mo ago

[flagged]

hasyimibhar 2mo ago

It's interesting to watch how different companies that offer both Postgres and warehousing solution under 1 roof approach the same problem: - 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.

jbonatakis 2mo ago
> 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.
- hasyimibhar 2mo ago
  I’m waiting for Cloudflare R2 to eventually support mirroring Postgres into R2 catalog. It seems like a nice fit because they already have R2 SQL.
- kiwicopple 2mo ago
  > mirror data directly to Iceberg > I’d bet you’re likely to see this from a Postgres-specific provider first, like Supabase. we deprecated this feature in our ETL tool[0]. The functionality is still in there but we can't support some of the production features we'd need for data/schema guarantees Iceberg is still nascent - only supporting single-table transactions (at least when we tried). A lot of important CDC/transactional semantics were "a work in progress" upstream. We shifted our focus to ducklake, which stores the catalog in Postgres [0] https://github.com/supabase/etl https://github.com/supabase/etl
- wasifaleem 2mo ago

datadrivenangel 2mo ago

Now fivetran has more competition. Good.

wlindley 2mo ago

Excellent that Control Data is contributing! Oh wait, I'm a few decades out of sync

kps 2mo ago
I'm afraid nobody (else) here remembers Control Data Corporation. But I'm happy to see the Centers for Disease Control using Postgres.

quotrend 2mo ago

[flagged]

aboardRat4 2mo ago

What did Center for Disease Control use before? Mysql?

bjt 2mo ago
In this case CDC = Changed Data Capture.

deleted 2mo ago

[deleted]

clovisge 2mo ago

[flagged]