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

Postgres Just Cracked the Top Fastest Databases for Analytics

mooncake.dev

Post reply on HN
Print view View original

111–120 of 126 posts

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#111

Post by bhouston »

sgarland wrote: post #94
dig1 wrote: post #47

Earlier quoted context omitted.

As someone dealing with billions of records on it, BigQuery is far from cheap; G will not charge you much for storage as they will charge you for queries and data transfer. AFAIK, the cheapest Postgres server on GCP is very expensive compared to the usual Postgres installation (price/performance).

Yep. I used it once for a personal project involving GHTorrent [0], and each query was about $20. That may be peanuts for a business, but for someone playing around on their own, it's incredibly prohibitive. [0]: https://github.com/ghtorrent/ghtorrent.org/blob/master/gclou...

On billions of records my query costs were generally less than a cent. But I partitioned and clustered on the key fields.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#112

Post by chrisldgk »

sgarland wrote: post #94
dig1 wrote: post #47

Earlier quoted context omitted.

As someone dealing with billions of records on it, BigQuery is far from cheap; G will not charge you much for storage as they will charge you for queries and data transfer. AFAIK, the cheapest Postgres server on GCP is very expensive compared to the usual Postgres installation (price/performance).

Yep. I used it once for a personal project involving GHTorrent [0], and each query was about $20. That may be peanuts for a business, but for someone playing around on their own, it's incredibly prohibitive. [0]: https://github.com/ghtorrent/ghtorrent.org/blob/master/gclou...

Either the website this is referring to got hacked or you sold it. Either way, this probably shouldn’t be linked on HN.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#113

Post by sgarland »

sgarland wrote: post #94
dig1 wrote: post #47

Earlier quoted context omitted.

As someone dealing with billions of records on it, BigQuery is far from cheap; G will not charge you much for storage as they will charge you for queries and data transfer. AFAIK, the cheapest Postgres server on GCP is very expensive compared to the usual Postgres installation (price/performance).

Yep. I used it once for a personal project involving GHTorrent [0], and each query was about $20. That may be peanuts for a business, but for someone playing around on their own, it's incredibly prohibitive. [0]: https://github.com/ghtorrent/ghtorrent.org/blob/master/gclou...

EDIT: The site no longer exists, it seems. The project (which is not my creation) was a regularly-updated dump of all GitHub metadata (PR comments, Issues, etc.). Here's an archive snapshot: https://web.archive.org/web/20220126060859/https://ghtorrent...

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#114

Post by sam_goody »

mirekrusin wrote: post #33

Why timescaleDB is not mentioned anywhere? Genuine question.

Actually, it is, in a diagram right at the top of https://github.com/Mooncake-Labs/pg_mooncake.

However, the Venn diagram does not tell me anything useful, so I agree with the sentiment. Would love it if someone could explain what mooncake is good for and what its strengths and weaknesses are vs Timescale.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#115

Post by zhousun »

saisrirampur wrote: post #107
zhousun wrote: post #86

Earlier quoted context omitted.

Hi, Zhou From Mooncake labs here. Love your work on PeerDB and it's inspiring the evolvement of pg_mooncake (logical replication will be the killing feature for V2) The core idea of mooncake is to built upon open columnar format + substitutable vectorized engine, while natively integrate with Postgres: 1. For small devs, we allow the whole stack to be embedded as a Postgres extension for ease of use 2. For enterprise…

Thank you for the kind words! :) 1, makes sense. On 2, I understand your thinking around purpose-built — but you're retrofitting an analytical database into a transactional database without fully supporting all the features (both in terms of functionality and performance) of either. It's really hard to be truly purpose-built this way. As a result, users might not get the best of both worlds. PeerDB is different. We k…

Yep what I want say is the line between the two designs is indeed very blur.

Logical replication with mooncake will try to create a columnar version of a postgres heap table, that can be readable within postgres (using pg_mooncake); or outside postgres (similar to peerdb + clickhouse) with other engines like duckdb, StarRocks,Trino and possibly ClickHouse.

But since we can purposely build the columnstore storage engine to have postgres CDC in mind, we can replicate real-time updates/deletes(especially in cases traditional OLAP system won't keep up).

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#116

Post by saisrirampur »

zhousun wrote: post #115
saisrirampur wrote: post #107

Earlier quoted context omitted.

Thank you for the kind words! :) 1, makes sense. On 2, I understand your thinking around purpose-built — but you're retrofitting an analytical database into a transactional database without fully supporting all the features (both in terms of functionality and performance) of either. It's really hard to be truly purpose-built this way. As a result, users might not get the best of both worlds. PeerDB is different. We k…

Yep what I want say is the line between the two designs is indeed very blur. Logical replication with mooncake will try to create a columnar version of a postgres heap table, that can be readable within postgres (using pg_mooncake); or outside postgres (similar to peerdb + clickhouse) with other engines like duckdb, StarRocks,Trino and possibly ClickHouse. But since we can purposely build the columnstore storage engi…

I understand. In that scenario, why can't users just use these other query engines directly instead of the extension. You're heavily relying on DuckDB within your extension but may not be able to unleash its full power since you're embedding it within Postgres and operating within the constraints of the Postgres extension framework and interface.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#117

Post by zhousun »

saisrirampur wrote: post #116
zhousun wrote: post #115

Earlier quoted context omitted.

Yep what I want say is the line between the two designs is indeed very blur. Logical replication with mooncake will try to create a columnar version of a postgres heap table, that can be readable within postgres (using pg_mooncake); or outside postgres (similar to peerdb + clickhouse) with other engines like duckdb, StarRocks,Trino and possibly ClickHouse. But since we can purposely build the columnstore storage engi…

I understand. In that scenario, why can't users just use these other query engines directly instead of the extension. You're heavily relying on DuckDB within your extension but may not be able to unleash its full power since you're embedding it within Postgres and operating within the constraints of the Postgres extension framework and interface.

lol spot-on comment and stay tuned for our v2 :)

The focus of mooncake is to be a columnar storage engine, that natively integrate with pg, allowing writing from pg, replicating from pg, and reading by pg using pg_mooncake. We want people to use other engine to read from mooncake, and here they are effectively stateless engine, that's much easier to manage and avoids all data ETL problems.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#118

Post by saisrirampur »

zhousun wrote: post #117
saisrirampur wrote: post #116

Earlier quoted context omitted.

I understand. In that scenario, why can't users just use these other query engines directly instead of the extension. You're heavily relying on DuckDB within your extension but may not be able to unleash its full power since you're embedding it within Postgres and operating within the constraints of the Postgres extension framework and interface.

lol spot-on comment and stay tuned for our v2 :) The focus of mooncake is to be a columnar storage engine, that natively integrate with pg, allowing writing from pg, replicating from pg, and reading by pg using pg_mooncake. We want people to use other engine to read from mooncake, and here they are effectively stateless engine, that's much easier to manage and avoids all data ETL problems.

Sounds good. I'm still a bit confused. But will wait for your next version. :) ETL problems still aren't avoided — replicating from Postgres sources using logical replication is still ETL. One topic we didn't chat much is, be careful about what you're signing up for with logical replication — we built an entire company just to solve the logical replication/decoding problem. ;)

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#119

Post by atombender »

bhouston wrote: post #110
atombender wrote: post #73

Earlier quoted context omitted.

Your comment is confusing to me because it appears to have nothing to do with the article, which is not about using Postgres' heap tables. In fact, you're arguing for a purpose-built storage engine (BigQuery), which is what pg_mooncake also is, except the latter is integrated with Postgres. At my company we self-host ClickHouse, but if we started out today we'd definitely adopt ClickHouse Cloud. One big advantage of…

I will investigate Clickhouse. It is probably an even better solution that BigQuery when you have a lot of data and have a lot of queries.

Clickhouse is amazing. So is Postgres, but CH excels at its particular use case where Postgres does not.

While these new Postgres extensions will make Postgres more competitive, CH is built from the ground up for these kinds of analytics workloads in a way that Postgres isn't, e.g. in terms of scalable data ingestion.

CH also has a rich set of features like materialized views, aggregate function types, and dictionaries, none of which have counterparts in Postgres of these extensions.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#120

Post by mslot »

jot wrote: post #9

How is this different from Crunchy Warehouse which is also built on Postgres and DuckDB? https://www.crunchydata.com/products/warehouse

It's a similar idea, but Crunchy Data Warehouse was built by several founding engineers of Citus, which lets us speedrun through it :)

It's a generally available (very solid) product powering some large production workloads, with fully transactional Iceberg, and auto-compaction. All SQL queries and almost all Postgres features are fully supported on Iceberg tables.

We are also seeing interesting patterns emerging with the ability to load/query csv/json/parquet/shapefile/... directly from S3 in combination with pg_parquet and pg_incremental. For instance, incrementally & transactionally loading CSV files that show up in S3 into Iceberg, or periodically exporting from Postgres to Parquet and then querying with data warehouse.

Post reply on HN