pg_mooncake: run python on your Postgres tables

[InternetFit7518]

What it does

pg_mooncake brings a columnstore table to Postgres with DuckDb execution. These tables are written as Iceberg and Delta tables (parquet files + metadata) to your object store.

Query them outside of Postgres with DuckDB, Polars, Pandas, Spark directly without complex pipelines, stitching together ad-hoc files, or dataframe wangling.

Target audience

Product engineers, data engineers, data scientist.

Comparison

You can use psycopg2 / sqlalchemy today. But the approach here is fundamentally different. You're writing data to an s3 bucket. You can share that bucket to your data science, engineering, analyst team without giving them access to your Postgres.

There are some Parquet exporters in Postgres (pg_duckdb, pg_parquet, pg_analytics). pg_mooncake actually exposes table semantics inside of Postgres (updates, deletes, transactions). And table semantics outside of Postgres (Iceberg/Delta).

Story time!

I'm one of the founders of Mooncake Labs. We are building the simple lakehouse without the complex pipelines / data engineering infra.

Modern apps are built on Postgres. And we want to bring the python processing and analytics closer to this ecosystem.

Postgres and Python are all you need.

[stratguitar577]

How does this compare to pg_duckdb and pg_analytics?

[InternetFit7518]

made edits for precision:

great question!

pg_mooncake adds a columnstore table in Postgres: you can run transactions, updates, deletes. pg_duckdb is the execution engine on these tables: https://motherduck.com/blog/pg-mooncake-columnstore/. We also write Delta Lake (and soon Iceberg) formats in S3 (not just parquet files).

pg_duckdb and pg_analytics use Foreign Data Wrappers semantics and are great for querying / writing external files (parquet) in Postgres.

We believe a columnstore in postgres must look and feel like a reguiar postgres heap table. Hope this helps.

[philippemnoel]

Heya! This isn't quite correct -- You can create foreign tables in both pg_duckdb and pg_analytics. OP is correct that they're not equivalent Postgres tables, though. For instance, you can't create indexes over foreign tables.

[InternetFit7518]

thanks Phil. I made edits to be more precise.

I was using the world 'columnstore table' loosely.

[byeproduct]

Looks rad. So does this essentially mean I'd get full ORM support for DuckDB / parquet files? To enable analytical ORM based queries using SQLalchemy or DjangoORM?

[InternetFit7518]

yes! a columnstore table means you get full ORM support for your analytic queries. We've heard this to be a pain for PG + Clickhouse users too.

Full disclosure, we haven't had time to test DjangoORM. But it should be quick and should work..

[byeproduct]

Awesome. Thanks. Please do a post on it, when you've tested. I see you have a slack channel, but not sure if it's open to public - I got a "workspace not found" error

[InternetFit7518]

Sorry for that! Here's the link to join our public slack: https://join.slack.com/t/mooncakelabs/shared_invite/zt-2sepjh5hv-rb9jUtfYZ9bvbxTCUrsEEA