mslot

Makes sense, we just shipped it https://www.linkedin.com/posts/craigkerstiens_barely-over-2-...

The challenge is converting primary key updates/deletes to row offsets in a columnar table. That requires maintaining an expensive mapping or doing expensive scans, and is not something you want Postgres itself to do. It's also this bursty, memory-intensive workload that you'd rather not have a lot of dedicated infrastructure for.

At Snowflake we use Snowflake to do the apply work. Hence end-to-end mirroring has more pieces than just Postgres, but the capturing of changes is cheap enough to do in Postgres directly.

(Author)

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

(1) We've thought about it, no current plans. We'd ideally reimplement DuckLake in Postgres directly such that we can preserve Postgres transaction boundaries, rather than reuse the Ducklake implementation that would run in a separate process. The double-edged sword is that there's a bunch of complexity around things like inlined data and passing the inlined data into DuckDB at query time, though if we can do that then you can get pretty high transaction performance.

(2) In principle, it's a bit easier for pg_duckdb to reuse the existing Ducklake implementation because DuckDB sits in every Postgres process and they can call into each other, but we feel that architecture is less appropriate in terms resource management and stability.

It's the same team and same project :). Crunchy Data was acquired by Snowflake.

When we first developed pg_lake at Crunchy Data and defined GTM we considered whether it could be a Snowflake competitor, but we quickly realised that did not make sense.

Data platforms like Snowflake are built as a central place to collect your organisation's data, do governance, large scale analytics, AI model training and inference, share data within and across orgs, build and deploy data products, etc. These are not jobs for a Postgres server.

Pg_lake foremost targets Postgres users who currently need complex ETL pipelines to get data in and out of Postgres, and accidental Postgres data warehouses where you ended up overloading your server with slow analytical queries, but you still want to keep using Postgres.

Yes, just COPY table TO 's3://mybucket/data.parquet'

Or COPY table TO STDOUT WITH (format 'parquet') if you need it on the client side.

Definitely similar goals, from the Mooncake author: https://news.ycombinator.com/item?id=43298145

I think pg_mooncake is still relatively early stage.

There's a degree of maturity to pg_lake resulting from our team's experience working on extensions like Citus, pg_documentdb, pg_cron, and many others in the past.

For instance, in pg_lake all SQL features and transactions just work, the hybrid query engine can delegate different fragments of the query into DuckDB if the whole query cannot be handled, and having a robust DuckDB integration with a single DuckDB instance (rather than 1 per session) in a separate server process helps make it production-ready. It is used in heavy production workloads already.

No compromise on Postgres features is especially hard to achieve, but after a decade of trying to get there with Citus, we knew we had to get that right from day 1.

Basically, we could speed run this thing into a comprehensive, production-ready solution. I think others will catch up, but we're not sitting still either. :)

DuckLake is pretty cool, and we obviously love everything the DuckDB is doing. It's what made pg_lake possible, and what motivated part of our team to step away from Microsoft/Citus.

DuckLake can do things that pg_lake cannot do with Iceberg, and DuckDB can do things Postgres absolutely can't (e.g. query data frames). On the other hand, Postgres can do a lot of things that DuckDB cannot do. For instance, it can handle >100k single row inserts/sec.

Transactions don't come for free. Embedding the engine in the catalog rather than the catalog in the engine enables transactions across analytical and operational tables. That way you can do a very high rate of writes in a heap table, and transactionally move data into an Iceberg table.

Postgres also has a more natural persistence & continuous processing story, so you can set up pg_cron jobs and use PL/pgSQL (with heap tables for bookkeeping) to do orchestration.

There's also the interoperability aspect of Iceberg being supported by other query engines.

You can use it as a read layer for for specific metadata JSON URL or a table in a REST catalog. The latter got merged quite recently, not yet in docs.

I gave a talk on that at Data Council, then still discussing the pg_lake extensions as part of Crunchy Data Warehouse.

https://youtu.be/HZArjlMB6W4?si=BWEfGjMaeVytW8M1

Also, nicer recording from POSETTE: https://youtu.be/tpq4nfEoioE?si=Qkmj8o990vkeRkUa

It comes down to the trade-offs made by operational and analytical query engines being fundamentally different at every level.

There are Postgres roles for read/write access to the S3 object that DuckDB has access to. Those roles can create tables from specific files or at specific locations, and can then assign more fine-grained privileges to other Postgres roles (e.g. read access on a specific view or table).

You could say

With DuckLake, the query frontend and query engine are DuckDB, and Postgres is used as a catalog in the background.

With pg_lake, the query frontend and catalog are Postgres, and DuckDB is used as a query engine in the background.

Of course, they also use different table formats (though similar in data layer) with different pros and cons, and the query frontends differ in significant ways.

An interesting thing about pg_lake is that it is effectively standalone, no external catalog required. You can point Spark et al. directly to Postgres with pg_lake by using the Iceberg JDBC driver.

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.

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.

I created pg_incremental because I keep running into the same challenge in PostgreSQL: You have a table of raw event data that you insert into, either individual rows when the event happens, or batches of events in other systems.

You then maybe want to aggregate the data, but the table is too big to keep reprocessing it, so you create a rollup table and only aggregate new data and insert into or update the rollup table.

However, how do you actually select the "new" data? That's more challenging than it seems, and you also need to orchestrate everything.

pg_incremental is a tool to help you create automated, reliable, incremental processing pipelines. It is built on top of pg_cron and around the idea of parameterized SQL commands.

You can define several types of pipelines:

- Sequence pipelines process a range of sequence values, to automatically aggregate or transform new data.

- Time interval pipelines process a range of time intervals after a time interval has passed, to automatically aggregate or export new data.

- File list pipelines process new files showing up in a directory, to automatically import data.

After defining a pipeline, new inserts will automatically get processed by the periodic background job. The SQL command is executed for a range of new sequence values, a new time interval, or a new file name, or skipped if there's no new work. Brin indexes are very useful for fast range scans.

The extension also ensures correct behaviour in the presence of concurrent inserts by waiting for ongoing writes to finish.

Overall, it simplifies the process of setting up an automated incremental processing pipeline to a single SQL command. There's not a lot of magic to it, but it's simple, reliable, and very versatile.

Not really. S3 tables provides storage & catalog for Iceberg tables, but is not a query/transaction engine. We'll definitely look for ways to take advantage of it as it matures.

I wrote a bit more about it at https://bsky.app/profile/marcoslot.com/post/3lcj6etdcg22p

Azure is not supported as a backend in pg_parquet right now, but shouldn't be hard to add (contributions welcome!)

https://github.com/CrunchyData/pg_parquet

It would not be safe to let any user access object storage. Therefore, pg_parquet has two roles called parquet_object_store_read and parquet_object_store_write that give permission to COPY FROM/TO object storage (but not local file system).

In pg_azure_storage there is a comparable azure_storage_admin role that needs to be granted to users that need Azure Blob Storage permission.

Fun fact, I created pg_azure_storage :)

(Marco from Crunchy Data)

With PostgreSQL extensions, we find it's most effective to have single-purpose modular extensions.

For instance, I created pg_cron a few years ago, and it's on basically every PostgreSQL service because it does one thing and does it well.

We wanted to create a light-weight implementation of Parquet that does not pull a multi-threaded library into every postgres process.

When you get to more complex features, a lot of questions around trade-offs, user experience, and deployment model start appearing. For instance, when querying an Iceberg table, caching becomes quite important, but that raises lots of other questions around cache management. Also, how do you deal with that memory hungry, multi-threaded query engine running in every process without things constantly falling over?

It's easier to answer those questions in the context of a managed service where you control the environment, so we have a product that can query Iceberg/Parquet/CSV/etc. in S3, does automatic caching, figures out the region of your bucket, can create tables directly from files, and uses DuckDB to accelerate queries in a reliable manner. This is partially powered by a set of custom extensions, partially by other things running on the managed service. https://docs.crunchybridge.com/analytics

However, some components can be neatly extracted and shared broadly like COPY TO/FROM Parquet. We find it very useful for archiving old partitions, importing public and private data sets, preparing data for analytics, and moving data between PostgreSQL servers.

Nice! Seems to be pretty well-crafted.

I think it comes down to every aspect of the DBMS being optimized differently. For instance, UDFs in DuckDB have vectors as input and output, while that would be confusing and unnecessary in PostgreSQL.

Nice comparison! Worth noting that pg_cron is available on almost all managed PostgreSQL services. Also, many thanks to Devrim Gunduz and Christoph Berg for providing community packages.

I wrote pg_cron with the intention of keeping it as simple, reliable, and low maintenance as possible, so it's likely to remain that way.

It is possible to implement a more advanced job schedulers on top of pg_cron if needed. For instance, you can set up a few parallel jobs that run every N seconds and take an item from a job queue table.

Crunchy Bridge is similar to RDS. It runs on EC2 and is a mature managed service with features such as VPC peering, and now analytics, so you could consider it as an alternative.

Crunchy Bridge is a managed PostgreSQL service by Crunchy Data available on AWS, Azure, and GCP.

Bridge for Analytics is a special instance/cluster type in Crunchy Bridge with additional extensions and infrastructure for querying data lakes. Currently AWS only.

The credentials are currently managed via the platform, so you enter them in the dashboard. We wanted to avoid specifying credentials via a SQL interface, because they can easily leak into logs and such. We'll add more authentication options over time.

It will do a sample, so usually much less than a table scan, but it can definitely take longer than for Parquet.

It caches the files on locally attached NVMe drives in the background, and continues to use range requests to S3 for queries until the full download completes.

It might be kind of nice if Postgres had a "force this statement through" option which kills any conflicting processes.

We created something like that in Citus when changing a node's hostname (e.g. during a failover). While node updates should be mutually exclusive with writes (otherwise we might lose them), we didn't want to wait for long-running or possibly frozen writers to release their locks. So after some initial waiting we'd start a background worker to kill anything that was blocking the node update.

Read committed mode (PostgreSQL's default) can get pretty funky.

If two transactions concurrently perform a SELECT (may be in a CTE) followed by an UPDATE, then they might see and try to update the same rows. That's often undesirable, for instance in the example of a queue where messages are supposed to arrive ~once. Serializable mode would "solve" the problem by letting one transaction fail, and expects the application to retry or otherwise deal with the consequences.

FOR UPDATE is a precision tool for working around read committed limitations. It ensures rows are locked by whichever transaction reads them first, such that the second reader blocks and (here's the funky part) when the first transaction is done it actually reads the latest row version instead of the one that was in the snapshot. That's semantically a bit weird, but nonetheless very useful, and actually matches how updates work in PostgreSQL.

The biggest issue with SELECT..FOR UPDATE is that it blocks waiting for concurrent updaters to finish, even if the rows no longer match its filter after the update. The SKIP LOCKED avoids all that by simply skipping the locked rows in the SELECT. Semantically even weirder, but very useful for queues.