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

81–90 of 126 posts

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#81

Post by bhouston »

Galanwe wrote: post #44
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…

Real time means a user can load a web page and see a query results without significant wait. So less than a few seconds.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#82

Post by tesch1 »

owenthejumper wrote: post #38

What's the business model? It's a extension that's MIT licensed, yet it has a company and a VC behind it. Sounds like a rug pull waiting to happen

Counterpoint: It's actually very astute of them to start like this.

In order to have a viable business model they need to create value for users. Users are intelligent and will not even consider trying something that has no exit path, should the company disappear.

What every company hopes to have is customers who are thrilled with the value they deliver and tell everyone what a great deal it is so the company can grow.

What no company hopes for is to end up like hashicorp, where they end up spending more investment $ than the value they provide, never achieve profitability, and eventually just piss everyone off, and everyone is trying to make the best of a leveraged situation, they end up having to pull the rug. The user's leverage in that situation is something like opentofu, made possible by the license, same as what's being offered here.

The price of the big columnar dbs is very high, so there's a lot of ground to capture / value to arbitrage/offer in this space - as evidenced by other comments in this thread, how fast the benchmarks are changing, the likelihood of memory prices coming down, etc.

Aside from that, you have to wonder big picture if the AI space will put significant downward pressure on memory and compute prices with everyone wanting to run local LLMs, might change some fundamental tradeoffs made in db systems. If in 10 years I can get a phone with a 1 TB of fast access RAM to ask siri how to sauté mushrooms, what will 99% of us need columnar store for?

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#83

Post by bhouston »

Simon_O_Rourke wrote: post #65
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…

> it was basically untouchable because it was so slow. I've worked with a 5B row table on Snowflake with (maybe) no indexes, and while somewhat slow you could still run reasonable queries on it in a minute or two.

I guess I liked the convenience with Bigquery of being able to do significant queries in a second or two on large data sets. It means I can do the query live for the user as they navigate the analytics dashboard and this simplifies design and implementation complexity significantly.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#84

Post by crazygringo »

Galanwe wrote: post #44
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…

These are two totally different use cases.

Analytics generally means a full table scan or similar for each query, because you're e.g. taking an average of every value.

You're talking about indexes which are to optimize retrieving a handful (or few thousand) of rows.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#85

Post by furstenheim »

DrFalkyn wrote: post #54
bhouston wrote: post #42

Earlier quoted context omitted.

> 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 simpl…

Indices are one the first things you learn in any decent DB course, so you don’t need a PhD But if BigTable just solves the problem it seems the way to go PostGres is popular for a reason, it’s ACID unlike BigTable You may run into these problems later on, you may not

ACID is good and it has an implementation cost (vacuum for example). When doing analytics you do not care for ACID

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#86

Post by zhousun »

saisrirampur wrote: post #23

Sai from PeerDB/ClickHouse here. Nice to see the progress on this project! I wanted to leave a few notes: For analytics on transactional data, it looks like you'd still need to use logical replication ( https://github.com/Mooncake-Labs/pg_mooncake/issues/90 ). Logical replication is somewhat similar to an ETL/CDC experience, though it's more Postgres-native. Managing logical replication at a production grade isn't tr…

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, our stack is also purpose-built stack similar to PeerDB + ClickHouse, not a more generalized approach

We allow a gradual transition from 1 to 2.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#87

Post by tomnipotent »

Eikon wrote: post #51
Galanwe wrote: post #44

Earlier quoted context omitted.

> 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…

Same for https://www.merklemap.com/ The biggest table contains 30B records. A query that uses a B-tree index completes in a few microseconds. EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms

Index-backed point look-ups are not the problem for analytical queries, but rather minimizing disk I/O for large scans with high column or predicate selectivity.

Once you've optimized for the more obvious filters like timestamp and primary key, like using partitions to avoid using indexes in the first place, you're left with the situation where you need to aggregate over many gigabytes of data and an index doesn't help since your query is probably going to touch every page within the filtered partitions.

You can solve some of these problems in Postgres, like partitioning, but now you're stuck with random I/O within each page to perform non-SIMD aggregations in a loop. This approach has a ceiling that other implementations like ClickHouse do not.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#88

Post by zhousun »

owenthejumper wrote: post #38

What's the business model? It's a extension that's MIT licensed, yet it has a company and a VC behind it. Sounds like a rug pull waiting to happen

Zhou from mooncake labs here.

Mooncake is built upon open-table formats and substitutable query engines. So it don't need to be just a postgres extension.

PG_mooncake will stay open-source under MIT, for small devs where everything fits in their postgres, we hope everyone to enjoy the !

And we would love to help companies outgrown postgres to transition into the modern stack with postgres + mooncake potentially outside pg + iceberg/delta.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#89

Post by zhousun »

theLiminator wrote: post #31
saisrirampur wrote: post #23

Sai from PeerDB/ClickHouse here. Nice to see the progress on this project! I wanted to leave a few notes: For analytics on transactional data, it looks like you'd still need to use logical replication ( https://github.com/Mooncake-Labs/pg_mooncake/issues/90 ). Logical replication is somewhat similar to an ETL/CDC experience, though it's more Postgres-native. Managing logical replication at a production grade isn't tr…

Do you believe that things like cedardb might one day remove the distinction between OLAP and OLTP dbs?

Interesting enough, the mooncake team was building SingleStore before, and so far it is the best production-ready HTAP system. One lesson I really learned is, people don't want to switch their system-of-record OLTP system.

Spoiler-alert: Mooncake will be supporting HTAP use-cases soon, and it comes with better trade-offs: keep your OLTP postgres tables as is, and mooncake adds analytics capacity to those table on up-to-date data.

Re: Postgres Just Cracked the Top Fastest Databases for Analytics

#90

Post by zhousun »

bach4ants wrote: post #72
dsiegel2275 wrote: post #67

A question that I have had for a while that I can't seem to find an answer: for teams that are using various columnar store extensions to turn Postgres into a viable OLAP solution - are they doing so in the same instance of their Postgres that they are using for OLTP? Or are they standing up a separate Postgres instance? I'm trying to understand if there is any potential performance impact on the OLTP workload by inc…

And further, with this pg_mooncake extension allowing you to store the data in S3, is Postgres simply providing compute to run DuckDB? I suppose it's also providing a standardized interface and "data catalog."

transactions are also managed by postgres as if they are native table, so that you don't need to worry about coordinating commits between postgres and the S3 data.
Post reply on HN