7 ms·
Pg_lakehouse: Query Any Data Lake from Postgres
- tehlike 2y agoParadedb is doing a lot of good work with postgres. Pg_analytics, and now pg_lakehouse...
- yrashk 2y agoAs somebody who writes a lot of Postgres extensions, I can say this is quite interesting! I think I can see some parallels to Supabase's wrappers project. Keep up the good work!
- kiwicopple 2y agoNeat that you plan to support both Delta Lake and Apache Iceberg I'm curious about HN's position between these two formats? I'm having a hard time deciphering which might be the industry winner (or perhaps they both have a place, no "winner" necessary)
- retakeming 2y agoThis is anecdotal, but I feel that we (ParadeDB) have received more requests for Iceberg integration vs. Delta Lake. We were actually hesitant to launch pg_lakehouse without Iceberg support, but pulled the trigger on it because the iceberg-rust crate is still in its early days. We will probably be contributing to iceberg-rust to make it work with pg_lakehouse.
- kiwicopple 2y ago> We will probably be contributing to iceberg-rust to make it work with pg_lakehouse That's great news, thanks for your contributions to open source (here, and all the other extensions)
- lukekim 2y agoAlso anecdotal, but we (Spice AI) see more requests for Iceberg, but in practice more deployments of Delta Lake.
- FridgeSeal 2y agoMy theory is that everyone would _prefer_ to use Iceberg, but isn’t as widely supported _yet_, so they’re stuck with Delta in the interim.
- philippemnoel 2y agoI think you're spot on
- slap_shot 2y agoThere isn't a winner and there likely won't be one (at least not for a long time). Tabular will likely be acquired by Snowflake and the two industry behemoths now back their own formats, and each will treat their own as a first class citizen.
- philippemnoel 2y agoAgreed, this is why we want to support both. Maybe even Apache Hudi down the line. But I hope the industry converges to a main standard rather than Snowflake/Databricks fighting for their own formats. They can differentiate on much more meaningful features
- jaychia 2y agoThere’s a lot of interesting work happening in this area (see: XTable). We are building a Python distributed query engine, and share a lot of the same frustrations… in fact until quite recently most of the table formats only had JVM client libraries and so integrating it purely natively with Daft was really difficult. We finally managed to get read integrations across Iceberg/DeltaLake/Hudi recently as all 3 now have Python/Rust-facing APIs. Funny enough, the only non-JVM implementation of Hudi was contributed by the Hudi team and currently still lives in our repo :D (https://github.com/Eventual-Inc/Daft/tree/main/daft/hudi/pyhudi https://github.com/Eventual-Inc/Daft/tree/main/daft/hudi/pyh...) It’s still the case that these libraries still lag behind their JVM counterparts though, so it’s going to be a while before we see full support across the full featureset of each table format. But we’re definitely seeing a large appetite for working with table formats outside of the JVM ecosystem (e.g. in Python and Rust)
- philippemnoel 2y agoAre you using the iceberg-rust crate for Rust? It's a rather young project, have you found it sufficient for your needs (if using)?
- sammysidhu 2y ago
- kcirerick 2y agoI'm also building in the lakehouse space and anecdotally have seen more excitement around Iceberg over delta lake just because of its completely open source origins. Iceberg has evolved faster and has had more contributions from a more diverse set of contributors than Delta Lake. Not sure if this will change with a Snowflake <> Tabular acquisition but I'd easily bet on Iceberg if current trends continue.
- philippemnoel 2y agoWe agree. We plan to bring Iceberg support as a first-class citizen as soon as we can, but unfortunately the support in Rust these days is still limited. We and the community are working on it
- brunoqc 2y agoNice. I wish timescaledb open-sourced their s3 storage thing.
- philippemnoel 2y agoThey've been moving more and more towards closed source over the years, which is a shame but I understand why. We don't offer time-series features today, but we're not ruling out adding support for it eventually if it is desired by our users.
- sdairs 2y agoVery cool! Could you share the key difference between this and the previous pg_analytics, and motivation of making it a separate plugin?
- retakeming 2y agoWhereas pg_analytics stores the data in Postgres block storage, pg_lakehouse does not use Postgres storage at all. This makes it a much simpler (and in our opinion, more elegant) extension. We learned that many of our users already stored their Parquet files in S3, so it made sense to connect directly to S3 rather than asking them to ingest those Parquet files into Postgres. It also accelerates the path to production readiness, since we're not touching Postgres internals (no need to mess with Postgres MVCC, write ahead logs, transactions, etc.)
- nitinreddy88 2y agoIf users are already having datalake kind of system which is generating parquet files, the use case to use Postgres to query the data itself is questionable. I think having Postgres way of doing things should be prioritised if you want to keep your product in unique position.
- epsilonic 2y agoCan you elaborate on what you mean by the "Postgres way of doing things"? Also, what is wrong with using Postgres to query data in external object stores? It is a common occurrence for businesses to store parquet artefacts in object storage, and querying them is often desirable.
- philippemnoel 2y agoIt depends. If you're happy with Databricks, etc. you might be good. But we've seen many users want the simplicity of querying data from Postgres for analytics, especially in case of JOINing both analytics and transactional data
- mcdonje 2y agoLooks like pg as a replacement for databricks sql, which is already a query engine for datalakes. It's not a lakehouse, but it calls itself one. Seems like a cool and useful project, but the name is problematic.
- retakeming 2y agopg_house just wasn't as catchy! In all seriousness though, I see your point. While it's true that we don't provide the storage or table format, our belief is that companies actually want to own the data in their S3. We called it pg_lakehouse because it's the missing glue for companies already using Postgres + S3 + Delta Lake/Iceberg to have a lakehouse without new infrastructure.
- samber 2y agoIt seems very promising! 2 questions: - do you distribute query processing over multiple pg nodes ? - do you store the metadata in PG, instead of a traditional metastore?
- retakeming 2y agoThanks! 1. It's single node, but DataFusion parallelizes query execution across multiple cores. We do have plans for a distributed architecture, but we've found that you can get ~very~ far just by scaling up a single Postgres node. 2. The only information stored in Postgres are the options passed into the foreign data wrapper and the schema of the foreign table (this is standard for all Postgres foreign data wrappers).
- nathanwallace 2y agoReaders may also enjoy Steampipe [1], an open source tool to live query 140+ services with SQL (e.g. AWS, GitHub, CSV, Kubernetes, etc). It uses Postgres Foreign Data Wrappers under the hood and supports joins etc with other tables. (Disclaimer - I'm a lead on the project.) 1 - https://github.com/turbot/steampipe https://github.com/turbot/steampipe
- snthpy 2y agoI like steampipe but found the use of postgres a bit heavy for my use cases. Could you make it run with pg_lite in wasm or DuckDB?
- nathanwallace 2y agoIn addition to Postgres FDWs, Steampipe plugins are also available as a SQLite extension [1] or a CLI export tool [2] for lighter weight use cases. (Although a local Postgres has a surprisingly small footprint!) Building plugins as DuckDB extensions would be cool too, but we haven't done that yet. 1 - https://steampipe.io/blog/2023-12-sqlite-extensions https://steampipe.io/blog/2023-12-sqlite-extensions 2 - https://steampipe.io/blog/2023-12-steampipe-export https://steampipe.io/blog/2023-12-steampipe-export
- nikita 2y agoThis is great work! Could you please comment on the choice of your license. Lost Postgres extension that achieve wide adoption use Postgres, MIT or Apache license.
- philippemnoel 2y agoAll ParadeDB extensions are released under AGPL-3.0. We've found that it strikes the right balance between being open-source and enabling the community to adopt for free, while also protecting us from hyperscalers and enabling us to build a sustainable business. Perhaps the topic of a blog post someday :)
- nikita 2y agoIt looks like hyper scalers can still host it as long as they are publishing changes to the source code ? Am I reading the license right ?
- tomhallett 2y agoFor aws to make it available on rds Aurora, would it be safe to assume there would have to be some changes to the extension source to make it compatible with the Aurora engine? If we assume aws doesn’t want todo that, then their licensing provides some protection there.
- philippemnoel 2y agoYeah exactly. In practice, we've inspired ourselves from the likes of Citus and others who have adopted the AGPL-3.0 license as a good compromise and have found success. It's rather comment nowadays for infra startups to use AGPL-3.0. Other noteworthy examples: MinIO, Quickwit, Lago, etc.
- fulmicoton 2y agoYes
- francoismassot 2y ago
- arduanika 2y agoThe name seems to be an allusion to the author P.G. Wodehouse, creator of the character Jeeves. https://en.wikipedia.org/wiki/P._G._Wodehouse https://en.wikipedia.org/wiki/P._G._Wodehouse Very clever naming!
- pas 2y agoSorry, what do you base that on? To me it just seems like a straightforward inspiration from the "data lake" -> "lakehouse" terminology that Databricks started (?) using. https://www.databricks.com/product/data-lakehouse https://www.databricks.com/product/data-lakehouse edit: ah, but in a different comment someone noted that it's not actually a lakehouse, so who knows!? :)
- arduanika 2y agoBased on pure speculation. I may be reaching. My best guess is that Databricks and Pg_lakehouse both independently coined "lakehouse" from "data lake", and that for the latter team, it was partly a pun on Wodehouse. But the creators are welcome to chime in and confirm/deny! (Or to say, like, "Sure...uh, we totally meant that...yes we are very literary.")
- philippemnoel 2y agoI wish we were that clever, but it's really just the combination of "data lake" and "data warehouse", which isn't even coined by us :)
- adammarples 2y agoIt has absolutely nothing to do with Pg wodehouse, it's a "data warehouse" sitting on a "data lake", which simply combines those two terms while keeping the "lake" and "house" metaphors quite well because you do actually get lake houses on lakes.
- jeadie 2y agoThis looks functionally similar as using http://github.com/spiceai/spiceai http://github.com/spiceai/spiceai with a postgreSQL data accelerator.
- epsilonic 2y agoHow does this compare to Hydra? https://www.hydra.so/ https://www.hydra.so/
- koolba 2y agoThat is one hell of a logo!
- philippemnoel 2y agoYou can see performance comparison to Hydra on ClickBench: https://benchmark.clickhouse.com/ https://benchmark.clickhouse.com/ by selecting ParadeDB and Hydra. Tl;dr: It is much faster. From a feature-set perspective, in addition to querying local disk, we can query remote object stores (S3, GCS, etc.), table format providers (Delta Lake, soon Iceberg too). From a code perspective, we're written in Rust on top of open-source standards like OpenDAL and DataFusion, while Hydra is their own codebase built from a fork of Citus columnar, in C. Hydra is a cool project. Hope this helps! :)
- epsilonic 2y agoThanks for the prompt response, the support for OpenDAL is amazing!
- sgt 2y agoAnd when will you have GCS storage ready? I saw on the website that it is not yet available.
- philippemnoel 2y agoNext week (May 20-something)
- sgt 2y agoVery exciting. We use GCS but we don't have a data lake yet. If you were to "sell" the concept of a data lake, what would you refer to as great example usecase?
- nikita 2y agoI have another question. So far on the clickbench leaderboard it's 15x slower than baseline. The number 1 place is 1.67 slower the baseline. I assume that's DataFusion speed. What's the plan to improve upon it?
- retakeming 2y agoCould you clarify which result you're referring to as the baseline and "number 1 place?" I should clarify that our published Clickbench results are from our pg_analytics extension. New results with pg_lakehouse will be released. They're going to beat the old benchmarks because 1. No overhead from Postgres transactions/MVCC, since pg_analytics used the table access method whereas pg_lakehouse is just a foreign data wrapper 2. Uses the latest release of DataFusion. The performance differences that exist between DataFusion and other OLAP engine are rapidly becoming commoditized. DataFusion is already a world-class query engine and will only improve. pg_lakehouse absorbs all those improvements into Postgres.
- riku_iki 2y agoWould be great to also see new pg_lakehouse and datafusion benchmark results here: https://duckdblabs.github.io/db-benchmark/ https://duckdblabs.github.io/db-benchmark/ Currently Datafusion is much slower than duckdb or OOMing.
- q9tE6uHb7yKq 2y agolooks interesting!
- mustafabal 2y agoVery nice addition! Do you plan to support Snowflake as an object store in the near future? It's not currently in pg_lakehouse's README.
- philippemnoel 2y agoSnowflake is not in the list of supported stores on Apache OpenDAL, so likely not. It might not expose its storage APIs. I doubt users of Snowflake would want a separate query engine anyways
- xuanwo 2y agoHi, OpenDAL's maintainer here. I'm not sure what "Snowflake as an object store" means since Snowflake is a cloud data warehouse service and not intended for storage services.
- hardwaresofton 2y agoYet another amazing postgres plugin made possible by pgrx (https://github.com/pgcentralfoundation/pgrx https://github.com/pgcentralfoundation/pgrx) It's really crazy how some projects just instantly enable a whole generation of new possibilities. If you are impressed like this and want to build something like it -- check out pgrx, it's a pretty great experience.
- philippemnoel 2y agopgrx is indeed wonderful and we would not be able to do our work without it. Big kudos to Eric, Jubilee and rest of team!
- whalesalad 2y agoHow many folks here struggle to adopt tooling like this because it isn’t possible to add psql extensions to places like RDS?
- pid-1 2y agoMoreover, even when extensions are supported by RDS, they often make upgrading database versions a PITA.
- thenaturalist 2y agoNot familiar with the process, how do they make it a PITA?
- oulu2006 2y agoIt's gotten significantly easier lately, I upgraded from v11 -> v15 with almost no downtime.
- dewey 2y agoYep, that usually dampens my excitement pretty quickly after seeing a new extension. You‘d also not know if it’s available on new versions. Sometimes you can install them „manually“ like supabase audit but more often that’s not possible.
- philippemnoel 2y agoWe're working on getting our extensions approved on as many platforms as possible
- oulu2006 2y agoMe. I both love and hate RDS - the costs are exorbitant. I have to setup a kafka pipe streaming CDC events to replicate the data into other DB types to get the benefits of things like this.
- tarasglek 2y agoI am not up to date in various lakes. Is this read-only? Are you able to init a lake from scratch? What's the model to feed such a lake from some queue?
- philippemnoel 2y agoFor now it is read-only, but soon will be write-supported too. You can feed data via Kafka
- ahachete 2y agoThe (internal) use of DataFusion to create new, powerful extensions for Postgres is a very clever idea. Very good work for the ParadeDB team. I like this one very much. Very simple way to avoid having to use different set of tools and query languages (or more limited query languages) to query lakes.