Postgres Just Cracked the Top Fastest Databases for Analytics • Hacker News Board

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#41

Post by gavinray » Sat, Mar 08, 2025, 12:05 PM UTC

moonikakiss wrote: post #15

dleeftink wrote: post #4

> To enhance query execution speed, we embedded DuckDB as the execution engine for columnstore queries So is it Postgres or DuckDB that cracked the analytics top ?

well, pg_mooncake is a Postgres extension, and Postgres + pg_mooncake is still just Postgres. Users deploy pg_mooncake as a Postgres extension and write and query all tables through psql. Fast analytic databases need two key things: columnar storage and a vectorized execution engine. We introduce a columnstore table access method in Postgres with data stored in Parquet) and execute queries on those tables using DuckD…

Can I suggest you put this sentence somewhere in the README near the beginning?

> Fast analytic databases need two key things: columnar storage and a vectorized execution engine. We introduce a columnstore table access method in Postgres with data stored in Parquet) and execute queries on those tables using DuckDB.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#42

Post by bhouston » Sat, Mar 08, 2025, 12:10 PM UTC

rapfaria wrote: post #39

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

> 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time - it was basically untouchable because it was so slow. This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding…

> This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk.

Or just use BigQuery and it is works, it is cheaper to run (by 10x to 100x) and can be done by a junior dev rather than a PhD in Database configuration.

I prefer simple solutions though - I also hate Kubernetes: https://benhouston3d.com/blog/why-i-left-kubernetes-for-goog...

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#43

Post by Isn0gud » Sat, Mar 08, 2025, 12:10 PM UTC

rapfaria wrote: post #39

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

> 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time - it was basically untouchable because it was so slow. This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding…

Doesn't sound like solved problem to me if you have to employ more than four different mitigation strategies.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#44

Post by Galanwe » Sat, Mar 08, 2025, 12:13 PM UTC

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

> a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time

I mean, I don't know what you call "close to real time", and what kind of query you did, but I have Postgres serving requests from a 20B rows table just fine, with some light tweaking of indexes and partitions (I'm by no means a DBA).

Unless we are digging much deeper than that in the specifics of the data and queries, I don't think any conclusion can be taken from either experience.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#45

Post by TuringNYC » Sat, Mar 08, 2025, 12:14 PM UTC

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

>> Just to be clear, standard SQL databases are not great for large-scale analytics.

What do you mean by "standard" SQL? Were you using an OLTP or OLAP db when you faced these difficulties? Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#46

Post by atombender » Sat, Mar 08, 2025, 12:15 PM UTC

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

Did you use plain Postgres tables or a columnar extension like Timescale, Citus, or pg_mooncake?

Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. However, it looks like these extensions can be quite decent, at least at queries (you might still run into write performance due to Postgres' OLTP architecture, but I've never benchmarked it). In OLAP terms the size of your data is tiny and this would probably work just fine.

Personally I'm a huge fan of dedicated databases like ClickHouse. BigQuery gets very expensive as your data and query volume grows.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#47

Post by dig1 » Sat, Mar 08, 2025, 12:18 PM UTC

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

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

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#48

Post by bhouston » Sat, Mar 08, 2025, 12:23 PM UTC

atombender wrote: post #46

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

Did you use plain Postgres tables or a columnar extension like Timescale, Citus, or pg_mooncake? Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. However, it looks like these extensions can be quite decent, at least at queries (you might still run into write performance due to Postgres' OLTP architecture, but I've never benchmarked it). In OLAP terms the size of you…

> Did you use plain Postgres tables or a columnar extension like Timescale or pg_mooncake?

Plain Postgres tables hosted on Google Cloud SQL on a decent machine.

> Nobody in their right mind would argue that Postgres without columnar storage is good for analytics.

Which is what I am saying. Standard means without extensions.

> Personally I'm a huge fan of dedicated databases like ClickHouse.

Where do you run it? What type of machine? Can it be server less?

> BigQuery gets very expensive as your data and query volume grows.

I didn't experience this, it has been hard for me to make BigQuery cost a lot. Storage costs were basically nothing and we just set the clustering/partition on DB creation to be users/sessions/events and dates respectively. It aligned really well with general query patterns.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#49

Post by braiamp » Sat, Mar 08, 2025, 12:25 PM UTC

TuringNYC wrote: post #45

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

>> Just to be clear, standard SQL databases are not great for large-scale analytics. What do you mean by "standard" SQL? Were you using an OLTP or OLAP db when you faced these difficulties? Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants.

According to their other comment standard means what it comes in the box by default.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#50

Post by bhouston » Sat, Mar 08, 2025, 12:27 PM UTC

TuringNYC wrote: post #45

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 5…

>> Just to be clear, standard SQL databases are not great for large-scale analytics. What do you mean by "standard" SQL? Were you using an OLTP or OLAP db when you faced these difficulties? Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants.

> What do you mean by "standard" SQL?

No extensions, just the default Postgres Cloud SQL offering from Google Cloud.

> Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants.

I view BigQuery as not a standard DB because it really doesn't do transactions, rollbacks, foreign key constraints, indices, stored procedures, etc. I view it as a non-SQL database pretending to be SQL for query convenience.