5 ms·

Putting DuckDB in Postgres to Query Iceberg

97 points by philippemnoel 2y ago

chuckhend 2y ago
Congrats to the paradedb team!
nhudson2367 2y ago
congrats paradedb team!
lolive 2y ago
Can you have a single transaction write both to a pg table and an iceberg table?
- philippemnoel 2y ago
  For now, pg_lakehouse is read-only. We are planning to add write support and this is a feature we would like to support. What is your use case for it?
nitinreddy88 2y ago
I really wish they resume work on pg_analytics which is in IMO the key differentiator in current PG Market
- philippemnoel 2y ago
  What we've observed is that pg_analytics is very useful, but significantly more work to make production-ready than pg_lakehouse, and we can service ~80% of our initial users with pg_lakehouse. We do hope to resume work on pg_analytics eventually, but given the bigger scale of the project, we've decided to postpone it for a bit longer. But stay tuned! We'll be announcing some partnerships which will help us get there faster.
  - higeorge13 2y ago
    Could you elaborate on this? What work does it require or missing features to be more production-ready? Most startups start with postgres/mysql, probably build some customer-facing analytical reports, and stay with the same setup until they get bigger customers with lots of data. And then the postgres/mysql setup for analytical queries suffers from performance degradation, cannot scale much, etc. My point is that most of these companies would love a fast postgres-compatible analytical service. Obviously, there are Clickhouse and other competitors, but speaking from experience all these require some changes in the setup, ingestion, schema, etc. While the postgres-ones would require minimum changes.
    - philippemnoel 2y ago
      I 100% agree. This is going to be a bit of a longer comment: 1) You are right that startups want all-in Postgres. To do fast analytics, we need to get columnar storage and vectorized processing. In `pg_analytics`, we implemented a Table Access Method (TAM) to try to accomplish this. TAMs involve changing the storage format of Postgres. When we did this, we discovered there were A LOT of Postgres internals we needed to interface with. We needed to reimplement write-ahead logs (WALs), buffer caching, MVCC, JOINs, and more. Each of these projects is a significant undertaking. Hacking on Postgres is really hard: https://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-really-hard.html https://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-... As a result, in pg_analytics we cut a lot of corners to ship quickly. The performance was great, but when it came to using it in any real production setting, not having these core features of Postgres meant users couldn't really use pg_analytics, and it would take us months/over a year to build any of this. Even more so, the Table Access Method API in Postgres still has a few limitations. The folks being OrioleDB are probably the most state-of-the-art implementation of a TAM, and they joined Supabase and are pushing for upstream patches to the TAM API to enable their workload. 2) At the same time, while you are right that startups want all-in Postgres, we discovered that larger companies typically have so much analytical data that they're pretty much forced to store it on object storage (e.g. S3) for cost and scalability reasons. We realized that by building pg_lakehouse, a Foreign Data Wrapper (FDW), we could avoid needing to create columnar storage in Postgres and just do work at the query engine layer and still be able to service those customers. Since pg_lakehouse is not touching Postgres storage, we inherit MVCC, WALs, buffer caching, etc. We're a small startup. People are often surprised to find out that ParadeDB is only 4 people and is just turning one year old. There's so much we wish to do, but we have very limited resources and need to be strategic. We actually think pausing pg_analytics in order to focus on pg_lakehouse is accelerating our path to launching pg_analytics, because we'll be able to more quickly monetize pg_lakehouse, grow our resources, and have the capacity to take on those heavier engineering projects :) Hope this helps/is interesting to you! We're excited about your interest in pg_analytics and you can rest assured it will happen in the Postgres community, whether by us or one of the other startups working on this.
PaulWaldman 2y ago
How does this compare to the Parquet S3 Foreign Data Wrapper? https://github.com/pgspider/parquet_s3_fdw https://github.com/pgspider/parquet_s3_fdw
- boomskats 2y ago
  Does the S3 FDW support predicate pushdown?
- philippemnoel 2y ago
  We talk about it more in our first post for pg_lakehouse: https://blog.paradedb.com/pages/introducing_lakehouse https://blog.paradedb.com/pages/introducing_lakehouse This FDW does not support predicate pushdown, so performance is weak. pg_lakehouse is the same performance as DuckDB. In addition, we're supporting table formats like Apache Iceberg, more object stores, and have many other plans to improve analytics in Postgres through pg_lakehouse.
deleted 2y ago
[deleted]
mritchie712 2y ago
The `duckdb_iceberg`[0] extension doesn't support partitioning / predicate pushdown. Is that what you're using for pg_lakehouse? 0 - https://github.com/duckdb/duckdb_iceberg/issues/2 https://github.com/duckdb/duckdb_iceberg/issues/2
- philippemnoel 2y ago
  It is! They (and we) are working on fixing that so that the performance for Iceberg gets a lot better
  - mritchie712 2y ago
    amazing! are you thinking of submitting a PR to duckdb_iceberg? Or an alternative fix?
    - philippemnoel 2y ago
      It would be to duckdb_iceberg, yes. We're looking to keep things in the open-source :)
rubenfiszel 2y ago
Why not call it pg_duckdb ?
- philippemnoel 2y ago
  The first version of it was built using Apache DataFusion and we kept the name. We're planning for it to become more than "just" DuckDB in Postgres
  - chrisjc 2y ago
    Interested to hear why you switched from DataFusion?
    - wenc 2y ago
      It’s in the linked article.
    - bastawhiz 2y ago
      That's the whole point of the article, really.
    - philippemnoel 2y ago
      We'll be bringing DataFusion again in a different part of our tech stack soon... Guesses are open for where that might be!
  - guillem_lefait 2y ago
    Well done ! In the github page https://github.com/paradedb/paradedb/tree/dev/pg_lakehouse https://github.com/paradedb/paradedb/tree/dev/pg_lakehouse, DataFusion still appear and probably should be replaced by DuckDB.
    - philippemnoel 2y ago
      !! Thank you, I've put up a PR to fix this

97 points by philippemnoel 2y ago

chuckhend 2y ago

Congrats to the paradedb team!

nhudson2367 2y ago

congrats paradedb team!

lolive 2y ago

Can you have a single transaction write both to a pg table and an iceberg table?

philippemnoel 2y ago
For now, pg_lakehouse is read-only. We are planning to add write support and this is a feature we would like to support. What is your use case for it?

nitinreddy88 2y ago

I really wish they resume work on pg_analytics which is in IMO the key differentiator in current PG Market

philippemnoel 2y ago
What we've observed is that pg_analytics is very useful, but significantly more work to make production-ready than pg_lakehouse, and we can service ~80% of our initial users with pg_lakehouse. We do hope to resume work on pg_analytics eventually, but given the bigger scale of the project, we've decided to postpone it for a bit longer. But stay tuned! We'll be announcing some partnerships which will help us get there faster.
- higeorge13 2y ago
  Could you elaborate on this? What work does it require or missing features to be more production-ready? Most startups start with postgres/mysql, probably build some customer-facing analytical reports, and stay with the same setup until they get bigger customers with lots of data. And then the postgres/mysql setup for analytical queries suffers from performance degradation, cannot scale much, etc. My point is that most of these companies would love a fast postgres-compatible analytical service. Obviously, there are Clickhouse and other competitors, but speaking from experience all these require some changes in the setup, ingestion, schema, etc. While the postgres-ones would require minimum changes.
  - philippemnoel 2y ago
    I 100% agree. This is going to be a bit of a longer comment: 1) You are right that startups want all-in Postgres. To do fast analytics, we need to get columnar storage and vectorized processing. In `pg_analytics`, we implemented a Table Access Method (TAM) to try to accomplish this. TAMs involve changing the storage format of Postgres. When we did this, we discovered there were A LOT of Postgres internals we needed to interface with. We needed to reimplement write-ahead logs (WALs), buffer caching, MVCC, JOINs, and more. Each of these projects is a significant undertaking. Hacking on Postgres is really hard: https://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-really-hard.html https://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-... As a result, in pg_analytics we cut a lot of corners to ship quickly. The performance was great, but when it came to using it in any real production setting, not having these core features of Postgres meant users couldn't really use pg_analytics, and it would take us months/over a year to build any of this. Even more so, the Table Access Method API in Postgres still has a few limitations. The folks being OrioleDB are probably the most state-of-the-art implementation of a TAM, and they joined Supabase and are pushing for upstream patches to the TAM API to enable their workload. 2) At the same time, while you are right that startups want all-in Postgres, we discovered that larger companies typically have so much analytical data that they're pretty much forced to store it on object storage (e.g. S3) for cost and scalability reasons. We realized that by building pg_lakehouse, a Foreign Data Wrapper (FDW), we could avoid needing to create columnar storage in Postgres and just do work at the query engine layer and still be able to service those customers. Since pg_lakehouse is not touching Postgres storage, we inherit MVCC, WALs, buffer caching, etc. We're a small startup. People are often surprised to find out that ParadeDB is only 4 people and is just turning one year old. There's so much we wish to do, but we have very limited resources and need to be strategic. We actually think pausing pg_analytics in order to focus on pg_lakehouse is accelerating our path to launching pg_analytics, because we'll be able to more quickly monetize pg_lakehouse, grow our resources, and have the capacity to take on those heavier engineering projects :) Hope this helps/is interesting to you! We're excited about your interest in pg_analytics and you can rest assured it will happen in the Postgres community, whether by us or one of the other startups working on this.

PaulWaldman 2y ago

How does this compare to the Parquet S3 Foreign Data Wrapper? https://github.com/pgspider/parquet_s3_fdw https://github.com/pgspider/parquet_s3_fdw

boomskats 2y ago
Does the S3 FDW support predicate pushdown?
philippemnoel 2y ago
We talk about it more in our first post for pg_lakehouse: https://blog.paradedb.com/pages/introducing_lakehouse https://blog.paradedb.com/pages/introducing_lakehouse This FDW does not support predicate pushdown, so performance is weak. pg_lakehouse is the same performance as DuckDB. In addition, we're supporting table formats like Apache Iceberg, more object stores, and have many other plans to improve analytics in Postgres through pg_lakehouse.

deleted 2y ago

[deleted]

mritchie712 2y ago

The `duckdb_iceberg`[0] extension doesn't support partitioning / predicate pushdown. Is that what you're using for pg_lakehouse? 0 - https://github.com/duckdb/duckdb_iceberg/issues/2 https://github.com/duckdb/duckdb_iceberg/issues/2

philippemnoel 2y ago
It is! They (and we) are working on fixing that so that the performance for Iceberg gets a lot better
- mritchie712 2y ago
  amazing! are you thinking of submitting a PR to duckdb_iceberg? Or an alternative fix?
  - philippemnoel 2y ago
    It would be to duckdb_iceberg, yes. We're looking to keep things in the open-source :)

rubenfiszel 2y ago

Why not call it pg_duckdb ?

philippemnoel 2y ago
The first version of it was built using Apache DataFusion and we kept the name. We're planning for it to become more than "just" DuckDB in Postgres
- chrisjc 2y ago
  Interested to hear why you switched from DataFusion?
  - wenc 2y ago
    It’s in the linked article.
  - bastawhiz 2y ago
    That's the whole point of the article, really.
  - philippemnoel 2y ago
    We'll be bringing DataFusion again in a different part of our tech stack soon... Guesses are open for where that might be!
- guillem_lefait 2y ago
  Well done ! In the github page https://github.com/paradedb/paradedb/tree/dev/pg_lakehouse https://github.com/paradedb/paradedb/tree/dev/pg_lakehouse, DataFusion still appear and probably should be replaced by DuckDB.
  - philippemnoel 2y ago
    !! Thank you, I've put up a PR to fix this