Putting DuckDB in Postgres to Query Iceberg | Hacker News Reader

Putting DuckDB in Postgres to Query Iceberg

blog.paradedb.com

97 pointsphilippemnoel27 comments

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

It is! They (and we) are working on fixing that so that the performance for Iceberg gets a lot better

amazing! are you thinking of submitting a PR to duckdb_iceberg? Or an alternative fix?

It would be to duckdb_iceberg, yes. We're looking to keep things in the open-source :)

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

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.

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.

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

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.

I appreciate your response. I am actually surprised about the need of pg_lakehouse by large companies. I expected that they would choose one of the prominent data warehouses (snowflake, bq, etc.) or query engines on top of s3 (athena, trino, etc.) and that this fdw approach would be more relevant for smaller ones (which want to avoid those extra costs). Do they have some special requirements or other use cases to go that route?

I follow your work in paradedb, and i look forward to seeing more development in the pg_analytics extension. :)

The use case for pg_lakehouse is the following:

You have historical data which you want to surface as a user-facing experience. Companies build their frontends against Postgres, and needing to also build frontends against Snowflake/DuckDB/etc. is a lot harder to maintain than just keeping your frontend built against Postgres and use pg_lakehouse to query it.

pg_lakehouse is not trying to replace data warehouses. Data warehouses are the best tool for internal analytics (i.e. a data analyst crunching large historical data to inform some business strategy decision). pg_lakehouse is designed for exposing historical data in user-facing analytics features (like a dashboard) with a good price-performance tradeoff.

Thanks for your support :)

> People are often surprised to find out that ParadeDB is only 4 people and is just turning one year old. Woooooooooooo my Buddha! Only 4 people? I plan to replace our database (a modified mysql5.7) recently. And I'm hesitating between timescaledb, questdb, and pg_analytics. pg_analytics seems much more performant than timescaledb but discontinued... QuestDB could only do compress with ZFS's builtin compression...

What is your use case? If you're considering pg_analytics, I would suggest you check out pg_lakehouse. It's even more performant than pg_analytics, see here: https://github.com/ClickHouse/ClickBench/pull/208

How does this compare to the Parquet S3 Foreign Data Wrapper?

https://github.com/pgspider/parquet_s3_fdw

Does the S3 FDW support predicate pushdown?

We talk about it more in our first post for pg_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.

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

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?

Congrats to the paradedb team!

Why not call it pg_duckdb ?

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

Interested to hear why you switched from DataFusion?

It’s in the linked article.

That's the whole point of the article, really.

We'll be bringing DataFusion again in a different part of our tech stack soon... Guesses are open for where that might be!

Well done !

In the github page https://github.com/paradedb/paradedb/tree/dev/pg_lakehouse, DataFusion still appear and probably should be replaced by DuckDB.

!! Thank you, I've put up a PR to fix this

congrats paradedb team!

Reply on news.ycombinator.com