13 ms·

Postgres Just Cracked the Top Fastest Databases for Analytics

378 points by moonikakiss 2y ago

nikita 2y ago
This is an exciting project. Few highlights: - Query processor is DuckDB - as long as it translates PG type system to DuckDB typesystem well - it will be very fast. - Data is stored on S3 in Parquet with Delta or Iceberg metadata. This is really cool. You don't need to push analytical data through WAL - only metadata goes into WAL. This mean fast loading at least in theory, and compatibility with all the Delta/Iceberg ecosystem. - Once they build real-time ingest, you can just push timeseries into this system and you don't need a second system like Clickhouse
- tarun_anand 2y ago
  Data is also stored in the local filesystem. How does that compare with vanilla PG and/or S3 based storage?
pier25 2y ago
They're already working on 0.2 https://www.mooncake.dev/blog/pgmooncake-v02discussion https://www.mooncake.dev/blog/pgmooncake-v02discussion
- moonikakiss 2y ago
  oh yeah. We're baking 0.2. Extremely excited about the small-writes. Today, each insert will create a new parquet file. This will be fixed in v0.2. Stay tuned, we will have some announcements here coming soon.
dleeftink 2y ago
> 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 ?
- tonyhart7 2y ago
  both??? I mean seeing their homepage this is clearly their business model no?
- rubenvanwyk 2y ago
  DuckDB
- moonikakiss 2y ago
  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 DuckDB. By leveraging DuckDB's execution engine, we avoid reinventing vectorized query execution while keeping everything managed through Postgres. More on our architecture: https://www.mooncake.dev/blog/how-we-built-pgmooncake https://www.mooncake.dev/blog/how-we-built-pgmooncake
  - moonikakiss 2y ago
    One thing I forgot to mention. We're actually faster than DuckDB on parquet. We implemented segment elimination based on parquet metadata. We blogged about this: https://www.mooncake.dev/blog/duckdb-parquet https://www.mooncake.dev/blog/duckdb-parquet
    - porker 2y ago
      Have you upstreamed the change to DuckDB?
      - fmbb 2y ago
        Reading the linked blog it seems the speed up comes from offloading some lookups to Postgres tables. That doesn’t seem like something that makes sense to in DuckDB. > pg_mooncake leverages Postgres to store table metadata, eliminating the need to query an external catalog (as DuckDB would require when scanning external Iceberg or Delta Lake tables). Instead, it directly queries the Postgres table to identify which Parquet files to scan, reducing overhead. > Additionally, pg_mooncake stores detailed Parquet metadata for each file in Postgres, including column statistics for every row group. These statistics, such as MIN/MAX values for each column, are aggregated to optimize scans. When querying a columnstore table, pg_mooncake uses these statistics to filter out data files and row groups that cannot produce matches.
tarun_anand 2y ago
Will this work alongside Citus? If yes, what benefits/disadvantages would it have? I presume at some point of time workloads do need a cluster.
- moonikakiss 2y ago
  In v0.1, we have a dependency on pg_duckdb which does not support citus (https://github.com/duckdb/pg_duckdb/issues/444 https://github.com/duckdb/pg_duckdb/issues/444). With what we're working in v0.2, we re-architecture some of our dependencies there, and will be able to scale out and support Citus.
bigtones 2y ago
Looks like they're currently No. 12 on their cited Clickbench benchmark - so not quite in the top 10 three weeks later.
- moonikakiss 2y ago
  I know! It's crazy how quickly everyone is moving in this space. There are some still low-hanging fruit optimizations we're working on (https://github.com/Mooncake-Labs/pg_mooncake/issues/82 https://github.com/Mooncake-Labs/pg_mooncake/issues/82), and we'd love to get back to the Top 10.
antonmks 2y ago
It is not really Postgres, the queries are run on DuckDB. Yeah, DuckDB is really fast for analytical queries.
- moonikakiss 2y ago
  Well, pg_mooncake is a Postgres extension. So it's really just Postgres :) We embed DuckDB in our extension as the vectorized execution engine on columnstore tables. Why rebuild a vectorized execution, when DuckDB has a lot of GREAT properties for this workload (similar syntax to Postgres, embedability). Here's our architecture: https://www.mooncake.dev/blog/how-we-built-pgmooncake https://www.mooncake.dev/blog/how-we-built-pgmooncake
- moonikakiss 2y ago
  oh and I totally forgot. pg_mooncake is actually faster than DuckDB on parquet. We implement segment-elimination to do so. We even blogged about it: https://www.mooncake.dev/blog/duckdb-parquet https://www.mooncake.dev/blog/duckdb-parquet
- dcreater 2y ago
  Yes but this is an extension that enables abstracting that away for an end user and allows them to just use psql
jot 2y ago
How is this different from Crunchy Warehouse which is also built on Postgres and DuckDB? https://www.crunchydata.com/products/warehouse https://www.crunchydata.com/products/warehouse
- rubenvanwyk 2y ago
  Think that use pure parquet, not iceberg tables.
  - jot 2y ago
    They list "Managed Iceberg tables" top of list of features on that page.
  - mslot 2y ago
    We first launched it as "Crunchy Bridge for Analytics" in April last year. At the time, it could mostly query/import/export Parquet/CSV/JSON. Our goal was to build a data warehouse, but getting early feedback was helpful and we did not want to misrepresent the offering. In November last year we added Iceberg support and managed storage, so it became a full data warehouse experience with writes and transactions and we renamed it to Crunchy Data Warehouse.
- moonikakiss 2y ago
  Architecturally, it's very similar. We have the same vision! And they've done a great job, especially around writing to Iceberg. Some differences: 1. Our extension is fully open-source. I believe they've open-sourced bits of their stack. 2. We are unopionated about open table formats: Iceberg and Delta. 3. In v0.2, we will support small write workloads. This will open up time-series and HTAP workloads.
- mslot 2y ago
  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.
rubenvanwyk 2y ago
Looking at the effort being put in it seems there’s a massive market for proper Postgres analytics / columnstore, which is very telling of the pain involved in moving data between systems.
- mosselman 2y ago
  Yes this has been my experience too. It isn’t just the costs and complexities involved in the moving of the data, but needing the expertise in your team to do so or at least investing the time of someone in your team to figure it out and maintain it. All the while they can’t do anything that is actually useful.
dcreater 2y ago
All you need is postgres: part 73
- menaerus 2y ago
  Or all you need is a database that allows anyone to skip the hard part and simply write a plugin for.
- cpursley 2y ago
  https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#postgresql-is-enough https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
saisrirampur 2y ago
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 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 trivial — it's quite common for customers to use PeerDB for homogeneous replication due to performance, manageability and observability issues with logical replication. One potential value of extensions is Postgres compatibility for your queries. However, I'm curious about the SQL coverage on columnar tables — for example, advanced constructs like partitioned tables, prepared statements, correlated subqueries, RCTEs, triggers, and more. While it seems there’s ongoing work to improve compatibility, achieving full Postgres compatibility is challenging. In some cases, you might need to fall back to Postgres (instead of DuckDB), sacrificing performance for certain queries. The biggest challenge we faced at Citus was the constant trade-off between Postgres compatibility and performance — both are complex and constantly moving targets. This was the key reason why Citus couldn't compete with purpose-built databases like Snowflake, ClickHouse, SingleStore. While DuckDB didn’t exist when we built Citus, it's still fundamentally different from Postgres. Even though the DuckDB dialect is somewhat similar to Postgres (as is Snowflake’s), retrofitting two distinct databases — each evolving independently for world's best performance — isn't trivial. In short, relying on ETL (logical replication) without providing full Postgres compatibility raises the question: is it better to lean on a purpose-built database with a laser focus on performance, or adopt a more generalized approach? Anyway, I come from a different school of thought — using the right tool for the right job. That said, I love seeing all the progress and evolution in the Postgres community — Postgres will always be my first love!
- theLiminator 2y ago
  Do you believe that things like cedardb might one day remove the distinction between OLAP and OLTP dbs?
  - zhousun 2y ago
    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.
timsuchanek 2y ago
Congrats on the launch! Does it support ordinary db migrations with the normal Postgres interface?
spapas82 2y ago
As others have mentioned the results are from an extension, not postgres. Unfortunately using an extension that's not "part" of postgres (like pg_stat_statements) is not trivial for most people since there are both technical and legal issues.
- kyrra 2y ago
  What are th legal issues?
  - spapas82 2y ago
    Licencing. Postgres has a well known, open source licence making it easy to use. Some extensions do not follow that practice making it more difficult to use. Even if the extension has a liberal licence eg Apache or MIT nothing guarantees that the licence won't change in the future.
  - kbolino 2y ago
    Some extensions, like TimescaleDB, have licenses explicitly forbidding their use in hosted products which compete with the companies backing them. In concrete terms, this means they can't be used in any of the major cloud providers' managed database solutions (e.g AWS RDS). Others, like Citus, are licensed under the AGPL, which is basically an even stronger version of the GPL that requires anyone hosting a service to provide their modifications to the source code. While this doesn't strictly rule out use in e.g. RDS it does basically say that Amazon would be required to open source any changes they make to get it working in RDS, which is a burden they don't seem interested in taking on, and which might reveal details about how their services work under the hood which they are generally fairly cagey about doing. The AGPL is also viewed even more suspiciously than the GPL by corporate lawyers who fear it may lead to virally forcing the company to reveal all of its source code. This fear is probably unfounded, but all copyleft licenses are primarily about expanding end-user freedom and so are treated as undesirable in most businesses that make money from selling software.
    - eska 2y ago
      I asked about this in their chat 2 years ago, and the CEO responded directly. His response I believe was that you are allowed to use Timescale on managed servers, but you're not supposed to become a Timescale hoster for others.
xkgt 2y ago
I read the title a couple of times and I'm still not sure it isn't misleading. The benchmarks are not just for Postgres but for Postgres with the Mooncake extension. There are also other results for Postgres with different extensions. While it does rank among the top fastest databases, it is not the fastest and not even within the top 10.
mirekrusin 2y ago
Why timescaleDB is not mentioned anywhere? Genuine question.
- sam_goody 2y ago
  Actually, it is, in a diagram right at the top of https://github.com/Mooncake-Labs/pg_mooncake 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.
- k_bx 2y ago
  Its ability to store data on S3 is in paid cloud version only, last time I've checked.
polskibus 2y ago
How does mooncake work with std oltp workloads? Can I use Postgres with OLTP , add mooncake and expect duckdb-level of performance for OLAP queries? I know that SAP HANA has some sort of several layers of storage and automatic movement of data between them to allow for such performant OLTP/OLAP hybrid, and I think this is the holy grail for cheap/open source db. Users need OLTP first but don’t want to add Kafka + clickhouse or similar pipelines when OLAP is needed.
- moonikakiss 2y ago
  In v0.2, this is exactly what we're building. Keep your OLTP as it. Deploy Mooncake with logical replication into columnstore tables, and get DuckDB like perf. A big part of this is not replacing your OLTP.
stoicfungi 2y ago
what are the differences between pg_mooncake and pg_duck?
- moonikakiss 2y ago
  Both add the DuckDB extension. But the use-case is slightly different: 1. pg_duckdb is great for querying existing columnar files (parquet) in Postgres. 2. Our focus is on helping you write and query your existing Postgres table into a columnar format. We spent most of our time on building the table access method for columnstore tables.
  - stoicfungi 2y ago
    Thanks a lot for the explanation. Keep up the good work :)
bhouston 2y ago
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 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 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. I know I could have used some type of daily aggregation combined with a weekly aggregation, etc to roll up the data incrementally. A dev tried this and yeah, it hide the slow queries but then it became inflexible in terms of reporting. And writing and maintaining these cronjobs is a lot of work. But then I tried using BigQuery on my https://web3dsurvey.com https://web3dsurvey.com side project and I just recorded raw data and then wrote queries to do real-time aggregation (with a caching layer) in a large variety of ways. And it was near instant and easy and also very cheap. So then I deployed this strategy over at Threekit.com and it also was easy and fast and cheap. Even more flexible queries than Web3DSurvey at basically no developer cost or infrastructure costs. No more need for aggregating cron-jobs or trying to decide ahead of time how users want to slice and dice the data. Real time, flexible queries on the fly via BigQuery is the way. Also BigQuery bill for https://web3dsurvey.com https://web3dsurvey.com is like $0.25 month and it is dealing with millions of records in its 3 month window of stored data. Where as just running the cheapest Postgres SQL server on Google Cloud is like >$25/month and it is a slow one. I would never go back to traditional SQL for analytics - it was hard, slow, expensive and inflexible. Worst of all worlds.
- rapfaria 2y ago
  > 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, caching, etc, can lower reads to a few seconds on disk.
  - 2y ago
xiphias2 2y ago
It's cool proof that it's worth to spend the effort for Postgres authors to add vectorization to the default columnstore.
owenthejumper 2y ago
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
- deleted 2y ago
  [deleted]
- tesch1 2y ago
  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?
- zhousun 2y ago
  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.
glass_door 2y ago
[dead]
osigurdson 2y ago
It would be great if Postgres had native columnar storage.
dsiegel2275 2y ago
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 including the OLAP in the same process.
- bach4ants 2y ago
  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."
  - zhousun 2y ago
    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.
- deleted 2y ago
  [deleted]
scirob 2y ago
Any differentiation vs Hydra ? They also put duckdb inside pg https://www.hydra.so/ https://www.hydra.so/
- zhousun 2y ago
  hydra/pg_duckdb embeds duckdb to query existing data on S3. So it is kind of targeting a completely different use case (someone already prepares and shared a dataset and you just want to query it). pg_mooncake (&crunchyData) is implementing columnstore tables in postgres, so you can actually use postgres as a data-warehouse (to ingest/ update and run analytics queries)
deleted 2y ago
[deleted]
rafael3 2y ago
Noob question: why is there no Hadoop cluster on Clickbench?
- zhousun 2y ago
  people tried to run spark (better hadoop) and failed lol. https://github.com/ClickHouse/ClickBench/pull/139 https://github.com/ClickHouse/ClickBench/pull/139
LIzzyytujghf 2y ago
[dead]

378 points by moonikakiss 2y ago

This is an exciting project. Few highlights: - Query processor is DuckDB - as long as it translates PG type system to DuckDB typesystem well - it will be very fast. - Data is stored on S3 in Parquet with Delta or Iceberg metadata. This is really cool. You don't need to push analytical data through WAL - only metadata goes into WAL. This mean fast loading at least in theory, and compatibility with all the Delta/Iceberg ecosystem. - Once they build real-time ingest, you can just push timeseries into this system and you don't need a second system like Clickhouse

tarun_anand 2y ago
Data is also stored in the local filesystem. How does that compare with vanilla PG and/or S3 based storage?

pier25 2y ago

They're already working on 0.2 https://www.mooncake.dev/blog/pgmooncake-v02discussion https://www.mooncake.dev/blog/pgmooncake-v02discussion

moonikakiss 2y ago
oh yeah. We're baking 0.2. Extremely excited about the small-writes. Today, each insert will create a new parquet file. This will be fixed in v0.2. Stay tuned, we will have some announcements here coming soon.

dleeftink 2y ago

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

tonyhart7 2y ago
both??? I mean seeing their homepage this is clearly their business model no?
rubenvanwyk 2y ago
DuckDB
moonikakiss 2y ago
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 DuckDB. By leveraging DuckDB's execution engine, we avoid reinventing vectorized query execution while keeping everything managed through Postgres. More on our architecture: https://www.mooncake.dev/blog/how-we-built-pgmooncake https://www.mooncake.dev/blog/how-we-built-pgmooncake
- moonikakiss 2y ago
  One thing I forgot to mention. We're actually faster than DuckDB on parquet. We implemented segment elimination based on parquet metadata. We blogged about this: https://www.mooncake.dev/blog/duckdb-parquet https://www.mooncake.dev/blog/duckdb-parquet
  - porker 2y ago
    Have you upstreamed the change to DuckDB?
    - fmbb 2y ago
      Reading the linked blog it seems the speed up comes from offloading some lookups to Postgres tables. That doesn’t seem like something that makes sense to in DuckDB. > pg_mooncake leverages Postgres to store table metadata, eliminating the need to query an external catalog (as DuckDB would require when scanning external Iceberg or Delta Lake tables). Instead, it directly queries the Postgres table to identify which Parquet files to scan, reducing overhead. > Additionally, pg_mooncake stores detailed Parquet metadata for each file in Postgres, including column statistics for every row group. These statistics, such as MIN/MAX values for each column, are aggregated to optimize scans. When querying a columnstore table, pg_mooncake uses these statistics to filter out data files and row groups that cannot produce matches.

tarun_anand 2y ago

Will this work alongside Citus? If yes, what benefits/disadvantages would it have? I presume at some point of time workloads do need a cluster.

moonikakiss 2y ago
In v0.1, we have a dependency on pg_duckdb which does not support citus (https://github.com/duckdb/pg_duckdb/issues/444 https://github.com/duckdb/pg_duckdb/issues/444). With what we're working in v0.2, we re-architecture some of our dependencies there, and will be able to scale out and support Citus.

bigtones 2y ago

Looks like they're currently No. 12 on their cited Clickbench benchmark - so not quite in the top 10 three weeks later.

moonikakiss 2y ago
I know! It's crazy how quickly everyone is moving in this space. There are some still low-hanging fruit optimizations we're working on (https://github.com/Mooncake-Labs/pg_mooncake/issues/82 https://github.com/Mooncake-Labs/pg_mooncake/issues/82), and we'd love to get back to the Top 10.

antonmks 2y ago

It is not really Postgres, the queries are run on DuckDB. Yeah, DuckDB is really fast for analytical queries.

moonikakiss 2y ago
Well, pg_mooncake is a Postgres extension. So it's really just Postgres :) We embed DuckDB in our extension as the vectorized execution engine on columnstore tables. Why rebuild a vectorized execution, when DuckDB has a lot of GREAT properties for this workload (similar syntax to Postgres, embedability). Here's our architecture: https://www.mooncake.dev/blog/how-we-built-pgmooncake https://www.mooncake.dev/blog/how-we-built-pgmooncake
moonikakiss 2y ago
oh and I totally forgot. pg_mooncake is actually faster than DuckDB on parquet. We implement segment-elimination to do so. We even blogged about it: https://www.mooncake.dev/blog/duckdb-parquet https://www.mooncake.dev/blog/duckdb-parquet
dcreater 2y ago
Yes but this is an extension that enables abstracting that away for an end user and allows them to just use psql

jot 2y ago

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

rubenvanwyk 2y ago
Think that use pure parquet, not iceberg tables.
- jot 2y ago
  They list "Managed Iceberg tables" top of list of features on that page.
- mslot 2y ago
  We first launched it as "Crunchy Bridge for Analytics" in April last year. At the time, it could mostly query/import/export Parquet/CSV/JSON. Our goal was to build a data warehouse, but getting early feedback was helpful and we did not want to misrepresent the offering. In November last year we added Iceberg support and managed storage, so it became a full data warehouse experience with writes and transactions and we renamed it to Crunchy Data Warehouse.
moonikakiss 2y ago
Architecturally, it's very similar. We have the same vision! And they've done a great job, especially around writing to Iceberg. Some differences: 1. Our extension is fully open-source. I believe they've open-sourced bits of their stack. 2. We are unopionated about open table formats: Iceberg and Delta. 3. In v0.2, we will support small write workloads. This will open up time-series and HTAP workloads.
mslot 2y ago
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.

rubenvanwyk 2y ago

Looking at the effort being put in it seems there’s a massive market for proper Postgres analytics / columnstore, which is very telling of the pain involved in moving data between systems.

mosselman 2y ago
Yes this has been my experience too. It isn’t just the costs and complexities involved in the moving of the data, but needing the expertise in your team to do so or at least investing the time of someone in your team to figure it out and maintain it. All the while they can’t do anything that is actually useful.

dcreater 2y ago

All you need is postgres: part 73

menaerus 2y ago
Or all you need is a database that allows anyone to skip the hard part and simply write a plugin for.
cpursley 2y ago
https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#postgresql-is-enough https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

saisrirampur 2y ago

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 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 trivial — it's quite common for customers to use PeerDB for homogeneous replication due to performance, manageability and observability issues with logical replication. One potential value of extensions is Postgres compatibility for your queries. However, I'm curious about the SQL coverage on columnar tables — for example, advanced constructs like partitioned tables, prepared statements, correlated subqueries, RCTEs, triggers, and more. While it seems there’s ongoing work to improve compatibility, achieving full Postgres compatibility is challenging. In some cases, you might need to fall back to Postgres (instead of DuckDB), sacrificing performance for certain queries. The biggest challenge we faced at Citus was the constant trade-off between Postgres compatibility and performance — both are complex and constantly moving targets. This was the key reason why Citus couldn't compete with purpose-built databases like Snowflake, ClickHouse, SingleStore. While DuckDB didn’t exist when we built Citus, it's still fundamentally different from Postgres. Even though the DuckDB dialect is somewhat similar to Postgres (as is Snowflake’s), retrofitting two distinct databases — each evolving independently for world's best performance — isn't trivial. In short, relying on ETL (logical replication) without providing full Postgres compatibility raises the question: is it better to lean on a purpose-built database with a laser focus on performance, or adopt a more generalized approach? Anyway, I come from a different school of thought — using the right tool for the right job. That said, I love seeing all the progress and evolution in the Postgres community — Postgres will always be my first love!

theLiminator 2y ago
Do you believe that things like cedardb might one day remove the distinction between OLAP and OLTP dbs?
- zhousun 2y ago
  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.

timsuchanek 2y ago

Congrats on the launch! Does it support ordinary db migrations with the normal Postgres interface?

spapas82 2y ago

As others have mentioned the results are from an extension, not postgres. Unfortunately using an extension that's not "part" of postgres (like pg_stat_statements) is not trivial for most people since there are both technical and legal issues.

kyrra 2y ago
What are th legal issues?
- spapas82 2y ago
  Licencing. Postgres has a well known, open source licence making it easy to use. Some extensions do not follow that practice making it more difficult to use. Even if the extension has a liberal licence eg Apache or MIT nothing guarantees that the licence won't change in the future.
- kbolino 2y ago
  Some extensions, like TimescaleDB, have licenses explicitly forbidding their use in hosted products which compete with the companies backing them. In concrete terms, this means they can't be used in any of the major cloud providers' managed database solutions (e.g AWS RDS). Others, like Citus, are licensed under the AGPL, which is basically an even stronger version of the GPL that requires anyone hosting a service to provide their modifications to the source code. While this doesn't strictly rule out use in e.g. RDS it does basically say that Amazon would be required to open source any changes they make to get it working in RDS, which is a burden they don't seem interested in taking on, and which might reveal details about how their services work under the hood which they are generally fairly cagey about doing. The AGPL is also viewed even more suspiciously than the GPL by corporate lawyers who fear it may lead to virally forcing the company to reveal all of its source code. This fear is probably unfounded, but all copyleft licenses are primarily about expanding end-user freedom and so are treated as undesirable in most businesses that make money from selling software.
  - eska 2y ago
    I asked about this in their chat 2 years ago, and the CEO responded directly. His response I believe was that you are allowed to use Timescale on managed servers, but you're not supposed to become a Timescale hoster for others.

xkgt 2y ago

I read the title a couple of times and I'm still not sure it isn't misleading. The benchmarks are not just for Postgres but for Postgres with the Mooncake extension. There are also other results for Postgres with different extensions. While it does rank among the top fastest databases, it is not the fastest and not even within the top 10.

mirekrusin 2y ago

Why timescaleDB is not mentioned anywhere? Genuine question.

sam_goody 2y ago
Actually, it is, in a diagram right at the top of https://github.com/Mooncake-Labs/pg_mooncake 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.
k_bx 2y ago
Its ability to store data on S3 is in paid cloud version only, last time I've checked.

polskibus 2y ago

How does mooncake work with std oltp workloads? Can I use Postgres with OLTP , add mooncake and expect duckdb-level of performance for OLAP queries? I know that SAP HANA has some sort of several layers of storage and automatic movement of data between them to allow for such performant OLTP/OLAP hybrid, and I think this is the holy grail for cheap/open source db. Users need OLTP first but don’t want to add Kafka + clickhouse or similar pipelines when OLAP is needed.

moonikakiss 2y ago
In v0.2, this is exactly what we're building. Keep your OLTP as it. Deploy Mooncake with logical replication into columnstore tables, and get DuckDB like perf. A big part of this is not replacing your OLTP.

stoicfungi 2y ago

what are the differences between pg_mooncake and pg_duck?

moonikakiss 2y ago
Both add the DuckDB extension. But the use-case is slightly different: 1. pg_duckdb is great for querying existing columnar files (parquet) in Postgres. 2. Our focus is on helping you write and query your existing Postgres table into a columnar format. We spent most of our time on building the table access method for columnstore tables.
- stoicfungi 2y ago
  Thanks a lot for the explanation. Keep up the good work :)

bhouston 2y ago

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 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 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. I know I could have used some type of daily aggregation combined with a weekly aggregation, etc to roll up the data incrementally. A dev tried this and yeah, it hide the slow queries but then it became inflexible in terms of reporting. And writing and maintaining these cronjobs is a lot of work. But then I tried using BigQuery on my https://web3dsurvey.com https://web3dsurvey.com side project and I just recorded raw data and then wrote queries to do real-time aggregation (with a caching layer) in a large variety of ways. And it was near instant and easy and also very cheap. So then I deployed this strategy over at Threekit.com and it also was easy and fast and cheap. Even more flexible queries than Web3DSurvey at basically no developer cost or infrastructure costs. No more need for aggregating cron-jobs or trying to decide ahead of time how users want to slice and dice the data. Real time, flexible queries on the fly via BigQuery is the way. Also BigQuery bill for https://web3dsurvey.com https://web3dsurvey.com is like $0.25 month and it is dealing with millions of records in its 3 month window of stored data. Where as just running the cheapest Postgres SQL server on Google Cloud is like >$25/month and it is a slow one. I would never go back to traditional SQL for analytics - it was hard, slow, expensive and inflexible. Worst of all worlds.

rapfaria 2y ago
> 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, caching, etc, can lower reads to a few seconds on disk.
- 2y ago

xiphias2 2y ago

It's cool proof that it's worth to spend the effort for Postgres authors to add vectorization to the default columnstore.

owenthejumper 2y ago

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

deleted 2y ago
[deleted]
tesch1 2y ago
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?
zhousun 2y ago
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.

glass_door 2y ago

[dead]

osigurdson 2y ago

It would be great if Postgres had native columnar storage.

dsiegel2275 2y ago

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 including the OLAP in the same process.

bach4ants 2y ago
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."
- zhousun 2y ago
  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.
deleted 2y ago
[deleted]

scirob 2y ago

Any differentiation vs Hydra ? They also put duckdb inside pg https://www.hydra.so/ https://www.hydra.so/

zhousun 2y ago
hydra/pg_duckdb embeds duckdb to query existing data on S3. So it is kind of targeting a completely different use case (someone already prepares and shared a dataset and you just want to query it). pg_mooncake (&crunchyData) is implementing columnstore tables in postgres, so you can actually use postgres as a data-warehouse (to ingest/ update and run analytics queries)

deleted 2y ago

[deleted]

rafael3 2y ago

Noob question: why is there no Hadoop cluster on Clickbench?

zhousun 2y ago
people tried to run spark (better hadoop) and failed lol. https://github.com/ClickHouse/ClickBench/pull/139 https://github.com/ClickHouse/ClickBench/pull/139

LIzzyytujghf 2y ago

[dead]