# Welcome to pg_mooncake

> Overview of pg_mooncake (v0.3.0), real-time columnstore mirror tables, Moonlink CDC ingestion, and DuckDB 2.0 vectorized execution in PostgreSQL.

---

LLMS index: [llms.txt](/llms.txt)

---

**`pg_mooncake`** is a PostgreSQL extension that adds **real-time columnstore mirror tables** (`USING mooncake`), **DuckDB 2.0** vectorized analytical execution (via [`pg_duckdb`](https://pgext.cloud/e/pg_duckdb) and [`duckdb_mooncake`](/docs/intro/duckdb-mooncake/)), and open **Apache Iceberg v2** storage (powered by [`moonlink`](/docs/intro/moonlink/)) directly inside PostgreSQL.

PostgreSQL with `pg_mooncake` ranks among the fastest analytical databases on ClickBench while keeping your existing PostgreSQL transactional write path untouched. Originally created by [Mooncake Labs](https://github.com/Mooncake-Labs), the extension suite (`pg_mooncake v0.3.0`, `duckdb_mooncake v0.0.2`, and `moonlink v0.0.1`) is now maintained by the [PGSTY](https://pigsty.io/) community for **PostgreSQL 14–18**.

> [!NOTE] PGSTY Maintained Suite (`v0.3.0`)
> - **[`pgsty/pg_mooncake`](https://github.com/pgsty/pg_mooncake)** (`v0.3.0`, `MIT`): PostgreSQL extension providing the `USING mooncake` table access method, `mooncake.*` SQL procedures, and the embedded Moonlink background worker (`pgrx 0.19.3`).
> - **[`pgsty/moonlink`](https://github.com/pgsty/moonlink)** (`v0.0.1`, `BSL-1.1` with [`pg_mooncake` Linking Exception](/docs/about/license/)): Real-time Rust storage engine that ingests PostgreSQL logical replication streams into in-memory Arrow buffers, Roaring Bitmap deletion vectors, and Apache Iceberg v2 / Parquet files.
> - **[`pgsty/duckdb_mooncake`](https://github.com/pgsty/duckdb_mooncake)** (`v0.0.2`, `MIT`): DuckDB 2.0 storage extension bundled with `pg_mooncake` that scans Moonlink tables with multi-file Parquet statistics pruning.

## The Problem {#problem}

PostgreSQL is an exceptional transactional (OLTP) database, but its row-oriented heap storage and tuple-at-a-time executor are not designed for large-scale analytical scans (OLAP). Historically, PostgreSQL teams faced two flawed choices:

1. **Adopt a separate analytical system (ClickHouse, Snowflake, BigQuery)**
   Requires maintaining fragile CDC pipelines (Debezium + Kafka), duplicating data models, dealing with eventual consistency lag, and losing the ability to join live operational tables with analytical history in a single query.
2. **Use in-database columnstores without vectorized execution or real-time CDC**
   Traditional columnar extensions either lack a modern vectorized execution engine or cannot handle high-frequency transactional `INSERT`, `UPDATE`, and `DELETE` operations without generating thousands of tiny Parquet files.

![pg_mooncake Venn Diagram](/img/mooncake/pg_mooncake_intro.jpg)

## How `pg_mooncake` Solves It {#solution}

Starting in `v0.2` and refined in PGSTY's **`v0.3.0`** release, `pg_mooncake` introduces a **columnstore mirror** architecture:

```sql
-- 1. Enable pg_mooncake (automatically loads pg_duckdb 1.2.0)
CREATE EXTENSION IF NOT EXISTS pg_mooncake CASCADE;

-- 2. Your regular OLTP rowstore table remains unchanged
CREATE TABLE trades (
    id BIGINT PRIMARY KEY,
    symbol TEXT,
    price DOUBLE PRECISION,
    volume INT,
    traded_at TIMESTAMPTZ
);

-- 3. Create a real-time columnstore mirror in one command
CALL mooncake.create_table('trades_analytics', 'trades');
```

1. **Write to Rowstore, Read from Columnstore**: Your application continues writing (`INSERT`, `UPDATE`, `DELETE`) to the standard PostgreSQL `trades` heap table with full ACID guarantees and indexes.
2. **Sub-Second Logical Replication via Moonlink**: Behind the scenes, `pg_mooncake`'s background worker streams WAL changes into `moonlink`, buffering recent mutations in memory as **Apache Arrow** batches and tracking updates/deletes via **Roaring Bitmap deletion vectors** before compacting into **Parquet** files.
3. **Vectorized DuckDB 2.0 Queries**: Analytical queries against `trades_analytics` are routed through `pg_duckdb 1.2.0` and `duckdb_mooncake v0.0.2`, merging in-memory Arrow buffers and on-disk Parquet segments with segment elimination and LSN consistency barriers.
4. **Open Apache Iceberg v2 Lakehouse**: With `CALL mooncake.create_snapshot('trades_analytics')`, Moonlink emits standard Apache Iceberg v2 metadata on local disk, AWS S3, or Cloudflare R2—ready for Spark, Snowflake, DuckDB, or PyIceberg without any ETL pipeline.

## Explore This Section {#explore}


- [System Architecture](/docs/intro/architecture/) — Understand how `pg_mooncake`, `pg_duckdb`, `duckdb_mooncake`, and `moonlink` coordinate writes, reads, and Iceberg snapshots.
- [Moonlink Ingestion Engine](/docs/intro/moonlink/) — Deep dive into Moonlink (`v0.0.1`), in-memory Arrow write buffers, deletion vectors, and standalone HTTP/RPC server deployment.
- [duckdb\_mooncake \& DuckDB 2\.0](/docs/intro/duckdb-mooncake/) — Learn how `duckdb_mooncake v0.0.2` integrates with DuckDB 2.0 and `pg_duckdb 1.2.0` for vectorized scans and Parquet statistics pruning.
- [v0\.1 vs v0\.2 \& v0\.3 Evolution](/docs/intro/0.1-vs-0.2/) — Compare the legacy `v0.1` Delta Lake (`USING columnstore`) architecture with `v0.2` Moonlink mirrors and PGSTY's `v0.3.0` enhancements.

---

Section pages:

- [Architecture](/docs/intro/architecture/): How pg_mooncake (v0.3.0), pg_duckdb (1.2.0), duckdb_mooncake (v0.0.2), and moonlink (v0.0.1) work together to deliver real-time columnar analytics in PostgreSQL.
- [Moonlink Ingestion Engine](/docs/intro/moonlink/): Deep dive into Moonlink (v0.0.1): real-time CDC ingestion, Arrow buffers, Roaring deletion vectors, LSN snapshot barriers, and standalone HTTP/RPC deployment.
- [duckdb_mooncake & DuckDB 2.0 Integration](/docs/intro/duckdb-mooncake/): How duckdb_mooncake (v0.0.2) connects DuckDB 2.0 and pg_duckdb 1.2.0 to Moonlink tables, performs multi-file Parquet statistics pruning, and installs via a local extension repository.
- [v0.1 vs v0.2 & v0.3 Evolution](/docs/intro/0.1-vs-0.2/): Architectural comparison between pg_mooncake v0.1 (USING columnstore + Delta Lake), v0.2 (USING mooncake + Moonlink + Iceberg), and PGSTY's v0.3.0 release.

---

Backlinks:

- [Clickbench says Postgres is a great analytics database](/blog/post/clickbench-v0.1/)
- [How to improve DuckDB performance on Parquet](/blog/post/duckdb-parquet/)
- [pg_mooncake: Fast Analytics in Postgres with Columnstore Tables and DuckDB](/blog/post/how-we-built-pgmooncake/)
- [Mooncake is joining Databricks!](/blog/post/mooncake-and-databricks/)
- [Mooncake: next-gen search and analytic systems](/blog/post/mooncake-launch/)
- [pg_mooncake 🤝 drizzle: typed SDKs for your analytic workloads](/blog/post/pgmooncake-drizzle/)
- [pg_mooncake + Neon: serverless analytics in Postgres](/blog/post/pgmooncake-neon/)
- [but, my Postgres (+ lakehouse) is free](/blog/post/postgres-lakehouse-free/)
- [Mooncake: a real-time storage engine for Apache Iceberg](/blog/post/white-paper/)
- [What does Analytics in Postgres really mean?](/blog/post/why-postgres-analytics/)
- [pg_mooncake v0.2: what we're cooking](/blog/release/pgmooncake-v02discussion/)
- [Moonlink Engine](/docs/intro/moonlink/)
- [v0.1 Archive (Delta Lake)](/docs/v0.1/)
- [v0.1 Architecture](/docs/v0.1/architecture/)
- [v0.1 Data Archiving](/docs/v0.1/archiving/)
- [v0.1 Cloud Storage](/docs/v0.1/cloud-storage/)
- [v0.1 Columnstore DDL](/docs/v0.1/columnstore-ddl/)
- [v0.1 Metadata & Polars](/docs/v0.1/columnstore-metadata/)
- [v0.1 Configurations](/docs/v0.1/configurations/)
- [v0.1 Get Started](/docs/v0.1/get-started/)
- [v0.1 Hello World](/docs/v0.1/hello-world/)
- [v0.1 Load Data](/docs/v0.1/load-data/)
- [v0.1 on Neon](/docs/v0.1/neon-postgres/)
- [v0.1 Operational Analytics](/docs/v0.1/operational-analytics/)
- [v0.1 Query External Files](/docs/v0.1/query-external-files/)
- [v0.1 Supported Queries](/docs/v0.1/query-shapes/)
- [v0.1 Reference](/docs/v0.1/reference/)
- [v0.1 Data Warehousing](/docs/v0.1/warehousing/)
- [Download](/download/)