Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Transforming Postgres into a Fast OLAP Database

blog.paradedb.com

Post reply on HN
Print view View original

51–60 of 62 posts

Re: Transforming Postgres into a Fast OLAP Database

#51

Post by kossae »

yunyu wrote: post #32

Congrats on launching – we just spent quite a bit of time replicating/transforming our primary database into clickhouse for OLAP use cases, and it would have been way easier if there were a postgres-native solution. Hoping the managed hosting providers catch on

I’m actually in the same boat right now (primary DB -> Clickhouse). We’re currently trialing Airbyte but it appears they’re removing normalization support for Clickhouse this year. Did you land on a custom solution, or some other ETL/ELT tool?

Re: Transforming Postgres into a Fast OLAP Database

#54

Post by benjaminwootton »

kossae wrote: post #51
yunyu wrote: post #32

Congrats on launching – we just spent quite a bit of time replicating/transforming our primary database into clickhouse for OLAP use cases, and it would have been way easier if there were a postgres-native solution. Hoping the managed hosting providers catch on

I’m actually in the same boat right now (primary DB -> Clickhouse). We’re currently trialing Airbyte but it appears they’re removing normalization support for Clickhouse this year. Did you land on a custom solution, or some other ETL/ELT tool?

Have you tried the PostgreSQL table engine?

Or Debezium to Kafka for a more hand coded solution?

Re: Transforming Postgres into a Fast OLAP Database

#55

Post by pgaddict »

philippemnoel wrote: post #48
pgaddict wrote: post #45

Looks very nice. I have three random questions: 1) How does this deal with backups? Presumably the deltalake tables can't be backed up by Postgres itself, so I guess there's some special way to d backups? 2) Similarly for replication (physical or logical). Presumably that's not supported, right? I guess logical replication is more useful for OLTP databases from which the data flow to datalakes, so that's fine. But wh…

1) and 2) Backups and replication are on the roadmap. It's the next major feature we're working on. 3) We store data in Parquet files, which is a heavily compressed file format for columnar data. This ensures good compression and compatibility with Arrow for in-memory columnar processing. We also hook at the executor level and route queries on deltalake tables to DataFusion query engine, which processes the data in a…

Thanks. I wonder how you plan to replicate stuff, considering how heavily it relies on WAL (I haven't found the answer in the code, but I'm not very familiar with rust).

How large part of the plan you route to the datalake tables? Just scans or some more complex part? Can you point me to the part of the code doing that? I'm intrigued.

Re: Transforming Postgres into a Fast OLAP Database

#56

Post by chrisjc »

philippemnoel wrote: post #15
zX41ZdbW wrote: post #9

There are many Postgres forks for OLAP workloads, such as: - Greenplum; - Citus with cstore_fdw; - IMCS (Konstantin Knizhnik); - Hydra; - AlloyDB; For example, Greenplum is one of the earliest, fairly mature, but abandoned.

That's right! Greenplum is still active, but falling off. We see pg_analytics as the next-generation Citus columnar, with much better performance and integration into the wider data ecosystem via Delta Lake, and eventually Iceberg

I've been scanning through the comments looking for the opportunity to ask, while not seeming critical...

why start with Delta Lake instead of Iceberg?

I imagine it might have had something to do with timing and the state of Iceberg at the time you started this effort?

Re: Transforming Postgres into a Fast OLAP Database

#58

Post by higeorge13 »

benjaminwootton wrote: post #54
kossae wrote: post #51

Earlier quoted context omitted.

I’m actually in the same boat right now (primary DB -> Clickhouse). We’re currently trialing Airbyte but it appears they’re removing normalization support for Clickhouse this year. Did you land on a custom solution, or some other ETL/ELT tool?

Have you tried the PostgreSQL table engine? Or Debezium to Kafka for a more hand coded solution?

The materialized postgres is still experimental and has some backlog until being production ready.

Debezium -> kafka -> kafka engine -> MVs -> Replacing merge trees works like a charm for me.

Re: Transforming Postgres into a Fast OLAP Database

#59

Post by philippemnoel »

pgaddict wrote: post #55
philippemnoel wrote: post #48

Earlier quoted context omitted.

1) and 2) Backups and replication are on the roadmap. It's the next major feature we're working on. 3) We store data in Parquet files, which is a heavily compressed file format for columnar data. This ensures good compression and compatibility with Arrow for in-memory columnar processing. We also hook at the executor level and route queries on deltalake tables to DataFusion query engine, which processes the data in a…

Thanks. I wonder how you plan to replicate stuff, considering how heavily it relies on WAL (I haven't found the answer in the code, but I'm not very familiar with rust). How large part of the plan you route to the datalake tables? Just scans or some more complex part? Can you point me to the part of the code doing that? I'm intrigued.

Hey! Sorry I missed this.

1) You can find the code here: https://github.com/paradedb/paradedb/blob/996f018e3258d3989f...

For deltalake tables, we send the full plan to DataFusion.

2) Re: WALs and replication -- We are currently adding support for WALs

Re: Transforming Postgres into a Fast OLAP Database

#60

Post by philippemnoel »

chrisjc wrote: post #56
philippemnoel wrote: post #15

Earlier quoted context omitted.

That's right! Greenplum is still active, but falling off. We see pg_analytics as the next-generation Citus columnar, with much better performance and integration into the wider data ecosystem via Delta Lake, and eventually Iceberg

I've been scanning through the comments looking for the opportunity to ask, while not seeming critical... why start with Delta Lake instead of Iceberg? I imagine it might have had something to do with timing and the state of Iceberg at the time you started this effort?

You're exactly right. There is a mature project called delta-rs which we use. The Iceberg equivalent, iceberg-rs, is still very young. We are planning to contribute to it and eventually add support for Iceberg
Post reply on HN