Hacker News Reader

How does this compare to https://www.mooncake.dev/pgmooncake? It seems there are several projects like this now, with each taking a slightly different approach optimized for different use cases?

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

FYI the mooncake team was acquired by Databricks so it's basically vendors trying to compete on features now :)

> For instance, it can handle >100k single row inserts/sec.

DuckLake already has data-inlining for the DuckDB catalog, seems this will be possible once it's supported in the pg catalog.

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

This is true, but it's not clear where I'd use this in practice. e.g. if I need to run a complex ETL job, I probably wouldn't do it in pg_cron.

> This is true, but it's not clear where I'd use this in practice. e.g. if I need to run a complex ETL job, I probably wouldn't do it in pg_cron.

Think "tiered storage."

See the example under https://github.com/Snowflake-Labs/pg_lake/blob/main/docs/ice...:

   select cron.schedule('flush-queue', '* * * * *', $$
     with new_rows as (
       delete from measurements_staging returning *
     )
     insert into measurements select * from new_rows;
   $$);

The "continuous ETL" process the GP is talking about would be exactly this kind of thing, and just as trivial. (In fact it would be this exact same code, just with your mental model flipped around from "promoting data from a staging table into a canonical iceberg table" to "evicting data from a canonical table into a historical-archive table".)

Not to mention one of my favorite tools for adding a postgres db to your backend service: PostgREST. Insanely powerful DB introspection and automatic REST endpoint. Pretty good performance too!

What does data frames mean in this context? I'm used to them in spark or pandas but does this relate to something in how duckDB operates or is it something else?

It's a python data frame.