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

41–50 of 62 posts

Re: Transforming Postgres into a Fast OLAP Database

#41

Post by philippemnoel »

chuckhend wrote: post #30
HermitX wrote: post #6

Thanks for sharing, I really like Postgres. However, I generally use Postgres for OLTP work. I would like to point out two things: 1. Based on Clickbench results, pg_analytics is still far from top-tier performance. If you're looking for a high-performance OLAP database, you should consider the top-ranked products. 2. The queries tested by Clickbench are really simple, far from real data analysis scenarios. You must…

Clickbench is really nice because it is so easy to compare and contribute benchmarks. Is there anything out there like Clickbench, but for the TPC-DS and TPC-H benchmarks?

TPC-H has some published benchmarks on their website, but it's for specific hardware systems. It's certainly not as user-friendly and modern as ClickBench. I'm not sure if it's possible to make something better without their consent, though.

Re: Transforming Postgres into a Fast OLAP Database

#42

Post by philippemnoel »

DrDroop wrote: post #27
retakeming wrote: post #25

Earlier quoted context omitted.

Datafusion's SQL dialect has some slight quirks that pertain to Datafusion-specific functionality. For instance, the ability to create an external table. With regards to ParadeDB, we rely on the Datafusion SQL parser, which can transform the Postgres SQL dialect into a Datafusion logical plan that can be executed by Datafusion. We actually have an open PR that adds support for user-defined functions...it will likely…

thx for your reply, so postgres is taking care of the parquet files in the background in some way i guess, would it be possible to combine this with something like neon?

We haven't tested. Neon has a pretty unique storage architecture, so I suspect it wouldn't work out-of-the-box, but could with some modifications on our end. We're open to this type of partnership eventually.

If you decide to try it, I believe they now have a way to load in arbitrary Postgres extensions!

Re: Transforming Postgres into a Fast OLAP Database

#43

Post by nikita »

philippemnoel wrote: post #42
DrDroop wrote: post #27

Earlier quoted context omitted.

thx for your reply, so postgres is taking care of the parquet files in the background in some way i guess, would it be possible to combine this with something like neon?

We haven't tested. Neon has a pretty unique storage architecture, so I suspect it wouldn't work out-of-the-box, but could with some modifications on our end. We're open to this type of partnership eventually. If you decide to try it, I believe they now have a way to load in arbitrary Postgres extensions!

We are happy to try for sure. Analytics workloads really benefit from separation of storage and compute!

Re: Transforming Postgres into a Fast OLAP Database

#45

Post by pgaddict »

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 what's the HA story without physical replication?

3) Presumably all the benefits are from compression at the storage level? Or are there some tweaks to the executor to do columnar stuff? I looked at the hooks in pg_analytics, but I see only stuff to handle DML. But I don't speeak rust, so maybe I missed something.

Re: Transforming Postgres into a Fast OLAP Database

#46

Post by neeleshs »

retakeming wrote: post #16
HermitX wrote: post #6

Thanks for sharing, I really like Postgres. However, I generally use Postgres for OLTP work. I would like to point out two things: 1. Based on Clickbench results, pg_analytics is still far from top-tier performance. If you're looking for a high-performance OLAP database, you should consider the top-ranked products. 2. The queries tested by Clickbench are really simple, far from real data analysis scenarios. You must…

One of the authors of pg_analytics here. 1. On Clickbench, make sure you're doing an apples-to-apples comparison by comparing scores from the same instance. We used the most commonly-used c6a.4xlarge instance. While a few databases like DuckDB rank higher, the performance of Datafusion (our underlying query engine) is constantly improving, and pg_analytics inherits those improvements. Then again, people only care abo…

+1 for this.

Most of the time, all that matter in terms of performance is user's tolerance. Once that is reached, operational complexity becomes a lot more important. We use raw Postgres for analytics, knowing that projects like these and cloud offerings like AlloyDB will make our lives easier (in terms of performance) as time goes.

pg_bm25 looks awesome too! Next up, take fdw to the level of Trino/Drill, and we dont need anything else other than postgres and its extensions!

Re: Transforming Postgres into a Fast OLAP Database

#47

Post by neeleshs »

Xcelerate wrote: post #31

This is pretty cool. It seems like there are a lot of hard database problems that still need to be solved, but finding the right database system (or extension) seems about as difficult as finding a research article and implementing the solution yourself lately. This seems like a step in the right direction by integrating with Postgres, which is widely used and supported. Case in point regarding OLAP in particular, I…

Is a sparse high dimensional boolean tensor modeled as you describe roughly the same as inverted index in a search engine like ElasticSearch/Solr?

Re: Transforming Postgres into a Fast OLAP Database

#48

Post by philippemnoel »

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 vectorized fashion for much faster execution

Re: Transforming Postgres into a Fast OLAP Database

#49

Post by philippemnoel »

DanielLapidus wrote: post #44

Nice, congrats! How does this compare to Hydra in philosophy and use cases?

I'm not fully familiar with Hydra. From my understanding, they forked Citus columnar and improved it. I think the use case for just pg_analytics vs hydra is similar, i.e. fast analytics in Postgres. However, soon they'll diverge as we interoperate it with pg_bm25, our full-text search extension. We see ParadeDB as becoming a modern alternative to Elasticsearch, while Hydra appears to be positioning itself as a Postgres warehouse

In philosophy, we believe in playing into the ecosystem. We use DataFusion to avoid needing to write a vectorized query engine, Arrow to avoid needing to build an in-memory representation, and Parquet to avoid needing to build columnar disk storage. Citus columnar/Hydra appear to be working from first principles directly within Postgres, storing data in Postgres blocks, and writing vectorized execution operator by operator

Re: Transforming Postgres into a Fast OLAP Database

#50

Post by philippemnoel »

neeleshs wrote: post #46
retakeming wrote: post #16

Earlier quoted context omitted.

One of the authors of pg_analytics here. 1. On Clickbench, make sure you're doing an apples-to-apples comparison by comparing scores from the same instance. We used the most commonly-used c6a.4xlarge instance. While a few databases like DuckDB rank higher, the performance of Datafusion (our underlying query engine) is constantly improving, and pg_analytics inherits those improvements. Then again, people only care abo…

+1 for this. Most of the time, all that matter in terms of performance is user's tolerance. Once that is reached, operational complexity becomes a lot more important. We use raw Postgres for analytics, knowing that projects like these and cloud offerings like AlloyDB will make our lives easier (in terms of performance) as time goes. pg_bm25 looks awesome too! Next up, take fdw to the level of Trino/Drill, and we dont…

This is exactly how we think over at ParadeDB, and querying data lakes is on our roadmap :)
Post reply on HN