Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Pg_lake: Postgres with Iceberg and data lake access

github.com

Post reply on HN
Print view View original

111–120 of 126 posts

Re: Pg_lake: Postgres with Iceberg and data lake access

#111

Post by NeutralCrane »

ggregoire wrote: post #82
NeutralCrane wrote: post #80

I’m not a data engineer but work in an adjacent role. Is there anyone here who could dumb the use case down? Maybe an example of a problem this solves. I am struggling to understand the value proposition here.

> Maybe an example of a problem this solves. Some service writes a lot of data in parquet files stored on S3 (e.g. logs), and now you want that data to be queryable from your application as if it was in postgres (e.g. near real-time analytics dashboard). pg_lake allows you to load these parquet files into postgres and query the data. You can also join that data with existing tables in postgres.

I guess my confusion is that there already are ways to query this data with DuckDB or something like that. So is the magic here that it’s Postgres? What makes being able to query something in Postgres special? And when we say it’s now queryable by Postgres, does this mean that it takes that data and stores it in your PG db? Or it remains in S3 and this is a translation layer for querying with PG?

Re: Pg_lake: Postgres with Iceberg and data lake access

#112

Post by inglor »

pgguru wrote: post #28
inglor wrote: post #23

This is really nice though looking at the code - a lot of the postgres types are missing as well a lot of the newer parquet logical types - but this is a great start and a nice use of FDW.

Hi, what types are you expecting to see that aren't supported? I believe we had support for most/all builtin postgres types.

Postgres has like 300+ types but mostly stuff like decimals should work the same way it does with Postgres (with the edge cases like NaN existing in Postgres but not parquets accordingly)

Re: Pg_lake: Postgres with Iceberg and data lake access

#115

Post by mslot »

inglor wrote: post #112
pgguru wrote: post #28

Earlier quoted context omitted.

Hi, what types are you expecting to see that aren't supported? I believe we had support for most/all builtin postgres types.

Postgres has like 300+ types but mostly stuff like decimals should work the same way it does with Postgres (with the edge cases like NaN existing in Postgres but not parquets accordingly)

In principle, Postgres has an infinite number of possible types :).

pg_lake maps types into their Parquet equivalent and otherwise stores as text representation, there are a few limitations like very large numerics.

https://github.com/Snowflake-Labs/pg_lake/blob/main/docs/ice...

Re: Pg_lake: Postgres with Iceberg and data lake access

#116

Post by gregw2 »

drchaim wrote: post #44

More integrations are great. Anyway, the "this is awesome" moment (for me) will be when you could mix row- and column-oriented tables in Postgres, a bit like Timescale but native Postgres and well done. Hopefully one day.

I want MPP HTAP where SQL inserts/COPYs store data in three(!) formats: - row-based (low latency insert, fast row-based indexed query for single-row OLTP) - columnar-based (slow inserts/updates, fast aggregates/projections) - iceberg-columnar-based (better OLAP price/performance and less lockin than native columnar) And for SELECTs the query engine picks which storage engine satisfies the query using some SQL extension like DB2 "WAITFORDATA" or TiDB @@tidb_read_staleness or MemSQL columnstore_latency and/or similar signalling for performance-vs-cost preference.

And a common permissioning/datasharing layer so I can share data to external and internal parties who can in turn bring their own compute to make their own latency choices.

Re: Pg_lake: Postgres with Iceberg and data lake access

#117

Post by kdazzle »

gigatexal wrote: post #91
thejosh wrote: post #81

Earlier quoted context omitted.

Sounds great until you're locked into Snowflake - so glad iceberg is becoming the standard, anything is great. The trap you end up in is you have to pay snowflake to access your data, iceberg and other technology help with the walled garden. Not just snowflake, any pay on use provider. (Context - have spent 5+ years working with Snowflake, it's great, have built drivers for various languages, etc).

Locked in? I mean they’re your partner. As long as you’re deriving value from them the partnership is still valuable no?

Snowflake is expensive, even compared to Databricks, and you pay their pre-AWS discount storage price while they get the discount and pocket the difference as profit

Re: Pg_lake: Postgres with Iceberg and data lake access

#118

Post by ch71r22 »

NeutralCrane wrote: post #111
ggregoire wrote: post #82

Earlier quoted context omitted.

> Maybe an example of a problem this solves. Some service writes a lot of data in parquet files stored on S3 (e.g. logs), and now you want that data to be queryable from your application as if it was in postgres (e.g. near real-time analytics dashboard). pg_lake allows you to load these parquet files into postgres and query the data. You can also join that data with existing tables in postgres.

I guess my confusion is that there already are ways to query this data with DuckDB or something like that. So is the magic here that it’s Postgres? What makes being able to query something in Postgres special? And when we say it’s now queryable by Postgres, does this mean that it takes that data and stores it in your PG db? Or it remains in S3 and this is a translation layer for querying with PG?

Not sure if I have this right but this is how I understand it

> So is the magic here that it's Postgres? What makes being able to query something in Postgres special?

There are a bunch of pros and cons to using Postgres vs. DuckDB. The basic difference is OLTP vs. OLAP. It seems pg_lake aims to give you the best of both. You can combine analytics queries with transactional queries.

pg_lake also stores and manages the Iceberg catalog. If you use DuckDB you'll need to have an external catalog to get the same guarantees.

I think if you're someone who was happy using Postgres, but had to explore alternatives like DuckDB because Postgres couldn't meet your OLAP needs, a solution like pg_lake would make your life a lot simpler. Instead of deploying a whole new OLAP system, you basically just install this extension and create the tables you want OLAP performance from with `create table ... using iceberg`

> when we say it’s now queryable by Postgres, does this mean that it takes that data and stores it in your PG db?

Postgres basically stores pointers to the data in S3. These pointers are in the Iceberg catalog that pg_lake manages. The tables managed by pg_lake are special tables defined with `create table ... using iceberg` which stores the data in Iceberg/Parquet files on S3 and executes queries partially with the DuckDB engine and partially with the Postgres engine.

It looks like there is good support for copying between the Iceberg/DuckDB/Parquet world and the traditional Postgres world.

> Or it remains in S3 and this is a translation layer for querying with PG?

Yes I think that's right -- things stay in S3 and there is a translation layer so Postgres can use DuckDB to interact with the Iceberg tables on S3. If you're updating a table created with `create table ... using iceberg`, I think all the data remains in S3 and is stored in Parquet files, safely/transactionally managed via the Iceberg format.

https://github.com/Snowflake-Labs/pg_lake/blob/main/docs/ice...

Re: Pg_lake: Postgres with Iceberg and data lake access

#119

Post by andiz »

qaq wrote: post #60
ozkatz wrote: post #55

Earlier quoted context omitted.

Out of curiosity - can you share a few examples of functionality currently not supported with Iceberg but that works well with their internal format?

even partition elimination is pretty primitive. For Query optimizer Iceberg is really not a primary target. The overall interaction with even technical people gives strong this is a sales org that happens to own an OLAP db product vibe.

I have to very much disagree on that. All pruning techniques in Snowflake work equally well both on their proprietary format as well for Iceberg tables. Iceberg is nowadays a first-class citizen in Snowflake, with pruning working at the file level, row group level, and page level. Same is true for other query optimization techniques. There is even a paper on that: https://arxiv.org/abs/2504.11540

Where pruning differences might arise for Iceberg tables is the structure of Parquet files and the availability of metadata. Both depend on the writer of the Parquet files. Metadata might be completely missing (e.g., no per column min/max), or partially missing (e.g., no page indexes), which will indeed impact the perf. This is why it's super important to choose a writer that produces rich metadata. The metadata can be backfilled / recomputed after the fact by the querying engine, but it comes at a cost.

Another aspect is storage optimization: The ability to skip / prune files is intrinsically tied to the storage optimization quality of the table. If the table is neither clustered nor partitioned, or if the table has sub-optimally sized files, then all of these things will severely impact any engine's ability to skip files or subsets thereof.

I would be very curious if you can find a query on an Iceberg table that shows a better partition elimination rate in a different system.

Re: Pg_lake: Postgres with Iceberg and data lake access

#120

Post by fifilura »

wodenokoto wrote: post #93
fifilura wrote: post #86

How do you use your data lake? For me it is much more than just storing data, it is just as much for crunching numbers in unpredictable ways. And this is where postgres does not cut it. You need some more CPU and RAM than what you pay for in your postgres instance. I.e. a distributed engine where you don't have to worry about how big your database instance is today.

The point about a datalake is to separate computer and storage. Postgres isn’t a compute layer it’s an access layer. Your compute asks Postgres “what is the current data for these keys?” Or “what was the current data as of two weeks ago for these keys?” And your compute will then download and aggregate your analytics query directly from the parquet files.

Sounds more like you need postgres as a backend than vice versa.
Post reply on HN