Welcome to pg_mooncake

• 1: Architecture
• 2: Moonlink Ingestion Engine
• 3: duckdb_mooncake & DuckDB 2.0 Integration
• 4: v0.1 vs v0.2 & v0.3 Evolution

pg_mooncake is a PostgreSQL extension that adds real-time columnstore mirror tables (USING mooncake), DuckDB 2.0 vectorized analytical execution (via pg_duckdb and duckdb_mooncake), and open Apache Iceberg v2 storage (powered by 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, the extension suite (pg_mooncake v0.3.0, duckdb_mooncake v0.0.2, and moonlink v0.0.1) is now maintained by the PGSTY community for PostgreSQL 14–18.

PGSTY Maintained Suite (v0.3.0)

• 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 (v0.0.1, BSL-1.1 with pg_mooncake Linking Exception): 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 (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

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.

How pg_mooncake Solves It

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

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

System Architecture

Understand how pg_mooncake, pg_duckdb, duckdb_mooncake, and moonlink coordinate writes, reads, and Iceberg snapshots.

Moonlink Ingestion Engine

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

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

Compare the legacy v0.1 Delta Lake (USING columnstore) architecture with v0.2 Moonlink mirrors and PGSTY’s v0.3.0 enhancements.

1 - Architecture

pg_mooncake (v0.3.0) combines three specialized components inside a single PostgreSQL deployment:

1. pg_duckdb (v1.2.0): Embeds the DuckDB v2.0.0 vectorized analytical execution engine inside PostgreSQL and routes queries against non-heap table access methods.
2. moonlink (v0.0.1): A Rust library and background worker embedded inside pg_mooncake that ingests PostgreSQL logical replication streams, maintains an in-memory Apache Arrow write buffer and Roaring Bitmap deletion vectors, flushes Parquet files, and emits Apache Iceberg v2 table metadata.
3. duckdb_mooncake (v0.0.2): A DuckDB 2.0 C++/Rust storage extension loaded automatically by pg_mooncake from a local extension repository (mooncake.extension_repository). It connects to Moonlink over a local Unix domain socket RPC (pg_mooncake/moonlink.sock) to read consistent snapshots across both flushed Parquet files and buffered Arrow batches.

Together, they expose a native PostgreSQL Table Access Method (USING mooncake) managed through CALL mooncake.create_table(dst, src).

Component Pinning in pg_mooncake 0.3.0

Write Path

In pg_mooncake, you never write directly to the USING mooncake columnstore table. Instead, you write to your regular PostgreSQL rowstore (heap or partitioned) table:

1. Initial Table Copy & Slot Creation: When you execute CALL mooncake.create_table('dst', 'src'), pg_mooncake:
   • Inspects the source table’s schema and creates the destination table with USING mooncake.
   • Sets REPLICA IDENTITY FULL on the source table (and, in v0.3.0, automatically on all existing leaf partitions if the source is a partitioned table) if the source table does not have a primary key.
   • Creates a PostgreSQL publication and logical replication slot (pgoutput), copies existing rows from src into Moonlink, and records the starting WAL LSN.
2. Continuous Logical Replication: A background worker (mooncake_bgworker) continuously consumes WAL changes (INSERT, UPDATE, DELETE) from the logical replication slot and streams them to moonlink.
3. Buffered Arrow + Deletion Vectors:
   • Inserts are appended to an in-memory Apache Arrow write buffer (write_buffer_size, default 32 MB), making new rows immediately visible to analytical queries without writing a tiny Parquet file per transaction.
   • Deletes record the target row’s location in an in-memory/on-disk Roaring Bitmap deletion vector, avoiding expensive full-file Parquet rewrites on every DELETE.
   • Updates are processed atomically as a deletion vector entry for the old tuple plus an append to the Arrow buffer for the new tuple.
4. Background Compaction & Parquet Flush: Once the write buffer reaches its threshold or when CALL mooncake.create_snapshot('dst') / CALL mooncake.optimize_table('dst', 'full') is invoked, Moonlink flushes the buffered Arrow records into compressed Parquet files and updates the Apache Iceberg v2 metadata.

Read Path

When you run a SELECT query that references a USING mooncake columnstore table:

1. Query Routing via pg_duckdb: PostgreSQL’s planner recognizes the mooncake table access method (registered with pg_duckdb via RegisterDuckdbTableAm("mooncake")) and hands the query plan over to the embedded DuckDB 2.0 engine.
2. Automatic Extension Attachment: On the first scan in a backend session, pg_mooncake configures SET extension_directory = '<mooncake.extension_repository>', loads the mooncake DuckDB extension (duckdb_mooncake v0.0.2), and executes ATTACH '' AS mooncake (TYPE mooncake, METADATA '<pgdata>/pg_mooncake').
3. LSN Synchronization Barrier: Before scanning a table, duckdb_mooncake queries PostgreSQL’s current WAL write/insert LSN and instructs Moonlink via RPC (scan_table_begin) to wait until the replication stream has ingested up to that LSN—guaranteeing read-your-own-writes transactional consistency.
4. Hybrid Arrow + Parquet Scan with Segment Elimination:
   • Moonlink returns the list of active Parquet data files, active deletion vectors, and a pointer to the current in-memory Arrow buffer.
   • duckdb_mooncake reads Parquet column min/max statistics through DuckDB 2.0’s exported multi-file reader interface to skip irrelevant files and row groups (segment elimination), applies deletion vectors, and streams the combined results back to PostgreSQL.

Storage Layout

By default (mooncake.default_bucket = ''), Moonlink stores table metadata, deletion vectors, and Parquet data files under your PostgreSQL data directory at $PGDATA/pg_mooncake/.

When mooncake.default_bucket is set to an s3:// or r2:// URI (with credentials configured in duckdb.secrets), Moonlink writes Parquet data files and Apache Iceberg v2 metadata (metadata/*.metadata.json and Avro manifest lists) directly to object storage. You can inspect the active warehouse path and LSN progress for every mirrored table at any time with:

SELECT * FROM mooncake.list_tables();

2 - Moonlink Ingestion Engine

Moonlink (pgsty/moonlink, current release v0.0.1) is a Rust library and standalone service designed to write and maintain real-time Apache Iceberg v2 and Delta Lake tables for operational systems and event streams without Apache Spark or Kafka.

Inside pg_mooncake, Moonlink runs as an embedded PostgreSQL background worker (mooncake_bgworker) communicating over a local Unix domain socket ($PGDATA/pg_mooncake/moonlink.sock). It can also be compiled and deployed as a standalone HTTP REST and gRPC ingestion server (moonlink_service).

Core Design Principles

Traditional data lake writers face a fundamental trade-off between data freshness and query performance: flushing every row mutation immediately creates thousands of tiny Parquet files, whereas batching writes for 5–15 minutes introduces unacceptable latency for operational analytics.

Moonlink resolves this trade-off with a multi-tiered storage architecture:

1. In-Memory Apache Arrow Write Buffer: Incoming INSERT and UPDATE rows are appended to an active in-memory Arrow RecordBatch buffer (write_buffer_size, default 32 MB). Readers (duckdb_mooncake) can scan this buffer directly with zero-copy Arrow integration before it is ever flushed to Parquet.
2. Equality & Position Deletion Vectors (Roaring Bitmaps): Instead of rewriting Parquet files on every UPDATE or DELETE, Moonlink indexes primary/replica keys via a B-tree index (moonlink_btree) and marks deleted row offsets in compressed Roaring Bitmaps.
3. LSN-Tracked Commits & Non-Blocking Snapshots: Every mutation ingested from PostgreSQL WAL is tagged with its commit lsn. Readers pass their target LSN to scan_table_begin, waiting only if the replication stream has not yet reached that LSN.
4. Asynchronous Compaction & Iceberg v2 Metadata: Background compaction merges flushed Parquet segments, reconciles deletion vectors, and writes standard Apache Iceberg v2 metadata.json and Avro manifest files to local disk or S3/R2 object storage.

What’s Fixed in moonlink v0.0.1

In PGSTY’s moonlink v0.0.1 (b298ef7444890e3c8af5544e350232518bcfd8e5) release:

• Non-blocking create_snapshot(lsn) under unrelated WAL progress: Previously, calling CALL mooncake.create_snapshot('table') when the PostgreSQL database WAL had advanced due to writes on unrelated tables caused Moonlink’s snapshot waiter to hang indefinitely waiting for a table-level commit at the latest database LSN. In v0.0.1, replication progress notifications advance the table’s safe LSN watermark and flush older committed data immediately, even when no new commits arrive for that specific table.
• 58/58 Table Handler Unit Tests: Verified across append, update, delete, rollback, compaction, and snapshot barrier scenarios on Rocky Linux 10 (aarch64) and Ubuntu 24.04 (aarch64).
• Security Dependency Audit Updates: Patched bytes, crossbeam-epoch, h2, lz4_flex, quinn-proto, rustls, rustls-webpki, webpki-roots, time, zstd, anyhow, event-listener, lru (0.18.5), memmap2, rand, tokio (1.51.6), aws-lc-rs, and serial_test.

Standalone Deployment (moonlink_service)

While pg_mooncake embeds Moonlink automatically, you can also run moonlink_service as a standalone ingestion server for direct HTTP REST or gRPC streaming into Iceberg tables.

Building moonlink_service from Source

git clone https://github.com/pgsty/moonlink.git
cd moonlink
cargo build --release -p moonlink_service
./target/release/moonlink_service

Standalone REST API Workflow

The standalone service listens on port 3030 by default and exposes JSON endpoints for table creation, row ingestion, and Iceberg snapshot generation:

import json
import requests

BASE_URL = "http://localhost:3030"

# 1. Create a Moonlink Iceberg table
create_payload = {
    "database": "default",
    "table": "events",
    "schema": {
        "fields": [
            {"name": "id", "data_type": "Int64", "nullable": False},
            {"name": "service", "data_type": "Utf8", "nullable": False},
            {"name": "latency_ms", "data_type": "Float64", "nullable": True},
        ],
        "primary_key": ["id"],
    },
    "table_config": {
        "write_buffer_size": 33554432,
        "iceberg_warehouse_location": "/tmp/moonlink_warehouse",
    },
}
resp = requests.post(f"{BASE_URL}/tables", json=create_payload)
resp.raise_for_status()

# 2. Insert or upsert rows in real time
insert_payload = {
    "database": "default",
    "table": "events",
    "row": {"id": 1, "service": "checkout", "latency_ms": 12.4},
}
resp = requests.post(f"{BASE_URL}/insert", json=insert_payload)
resp.raise_for_status()

# 3. Trigger an Iceberg v2 snapshot flush
snapshot_payload = {
    "database": "default",
    "table": "events",
    "lsn": resp.json().get("lsn", 1),
}
requests.post(f"{BASE_URL}/snapshot", json=snapshot_payload).raise_for_status()

Querying the Iceberg Snapshot from DuckDB

Once a snapshot is created, any Iceberg-compatible engine (DuckDB, Spark, Trino, Snowflake, PyIceberg) can read the table directly:

INSTALL iceberg;
LOAD iceberg;

SELECT service, COUNT(*), AVG(latency_ms)
FROM iceberg_scan('/tmp/moonlink_warehouse/default/events/metadata/v2.metadata.json')
GROUP BY service;

3 - duckdb_mooncake & DuckDB 2.0 Integration

duckdb_mooncake (pgsty/duckdb_mooncake, current release v0.0.2, MIT license) is a C++ and Rust FFI storage extension for DuckDB v2.0.0. It allows DuckDB (both standalone and embedded inside PostgreSQL via pg_duckdb 1.2.0) to attach directly to a moonlink metadata directory and execute vectorized scans over Moonlink tables.

Role Inside pg_mooncake 0.3.0

When pg_mooncake executes a query against a USING mooncake table inside PostgreSQL, pg_duckdb delegates the table scan to DuckDB. pg_mooncake automatically initializes the DuckDB session by running:

-- Executed automatically by pg_mooncake when initializing the DuckDB connection
SET extension_directory = '<mooncake.extension_repository>';
LOAD mooncake;
ATTACH '' AS mooncake (TYPE mooncake, METADATA '<pgdata>/pg_mooncake');

Local Extension Repository (mooncake.extension_repository)

Rather than downloading an unsigned binary from a remote community S3 bucket at runtime, pg_mooncake 0.3.0 builds and packages mooncake.duckdb_extension (v0.0.2) directly alongside the PostgreSQL extension into a local DuckDB extension repository:

$(pg_config --sharedir)/extension/pg_mooncake/
└── v2.0.0/
    └── <duckdb_platform>/          # e.g., linux_amd64, linux_arm64, osx_arm64
        └── mooncake.duckdb_extension

The PostgreSQL GUC mooncake.extension_repository points to this directory (or can be overridden in postgresql.conf when installing to a custom path), ensuring offline, deterministic, byte-for-byte reproducible loading with duckdb.allow_unsigned_extensions = true.

DuckDB 2.0 Adaptation in v0.0.2

In PGSTY’s duckdb_mooncake v0.0.2 (49765a369ad88986916ce53c969ed2ce4ba66fac) release, the extension was ported to DuckDB v2.0.0 (d54cbbe3df17e8e5ce68312f483b4155836112aa) and pg_duckdb 1.2.0:

1. DuckDB 2.0 Storage & Catalog APIs: Updated storage extension registration, catalog entry lookup, Arrow schema conversion (nanoarrow 0.7), table function binding, multi-file lists, and table filter pushdown to match DuckDB 2.0 signatures.
2. Exported Multi-File Parquet Statistics Interface: Earlier builds depended on internal ParquetReader symbols that are no longer exported by libduckdb.so in DuckDB 2.0. duckdb_mooncake v0.0.2 reads column statistics through DuckDB 2.0’s exported multi-file reader interface (MultiFileReader), verifying zero unresolved dynamic symbols (ldd -r) against pg_duckdb’s libduckdb.so.
3. Object-Cache Deadlock Elimination: Previously, constructing cached statistics entries inside the object-cache mutex could trigger a reentrant lock deadlock when loading Parquet file metadata also accessed the DuckDB object cache. v0.0.2 constructs statistics entries outside the object-cache mutex before inserting them into the cache.
4. CRoaring & Corrosion Build Hardening: Links against Corrosion v0.5, nanoarrow 0.7, and CRoaring v5.5.4 (with SHA-256 verification when a system libroaring is absent), and builds the Rust moonlink_ffi client with --locked Cargo dependencies (0 RustSec vulnerability matches).

Standalone Usage in DuckDB CLI

You can also load duckdb_mooncake directly in a standalone DuckDB v2.0.0 CLI (duckdb -unsigned) to inspect or query a Moonlink directory:

-- Point DuckDB to the local extension repository
SET extension_directory = '/usr/pgsql-18/share/extension/pg_mooncake';
LOAD mooncake;

-- Attach to a Moonlink metadata directory
ATTACH '' AS mooncake (TYPE mooncake, METADATA '/var/lib/pgsql/18/data/pg_mooncake');

-- Run vectorized analytical queries directly from DuckDB
SELECT * FROM mooncake.public.trades_analytics LIMIT 10;

4 - v0.1 vs v0.2 & v0.3 Evolution

pg_mooncake has evolved through three major architectural milestones:

1. v0.1 (Legacy Delta Lake Engine): Introduced direct CREATE TABLE ... USING columnstore tables backed by Delta Lake and DuckDB (pg_duckdb).
2. v0.2 (Moonlink & Apache Iceberg Redesign): Replaced direct columnstore writes with a columnstore mirror architecture (CALL mooncake.create_table(dst, src) + USING mooncake) powered by moonlink logical replication, in-memory Arrow write buffers, Roaring deletion vectors, and Apache Iceberg v2 metadata.
3. v0.3.0 (PGSTY Maintained Release): Upgraded pg_mooncake to pgrx 0.19.3 (PostgreSQL 14–18), ported duckdb_mooncake v0.0.2 to pg_duckdb 1.2.0 and DuckDB v2.0.0, fixed Moonlink (v0.0.1) snapshot LSN hangs and partitioned table replica identity handling, and patched 20+ RustSec dependency advisories.

Architectural Comparison

Why v0.2 / v0.3 Moved Away from v0.1’s Write Path

Limitations of v0.1

In v0.1, writing directly to a USING columnstore table worked well for large batch loads (100,000–1,000,000 rows per transaction), but struggled in real-world operational PostgreSQL workloads:

• No indexes or constraints: You could not create primary keys, unique constraints, or B-tree indexes on a USING columnstore table.
• Small-file amplification: Every INSERT or UPDATE transaction created a new Parquet file, quickly degrading scan performance under OLTP traffic.
• DuckDB write-path coupling: Routing transactional writes through DuckDB inside PostgreSQL’s executor created memory and transaction-handling bottlenecks.

How v0.2 and v0.3.0 Solve This

By decoupling the OLTP write path (standard PostgreSQL heap tables) from the analytical storage and compute path (moonlink + pg_duckdb 1.2.0 + duckdb_mooncake v0.0.2):

1. Zero Impact on OLTP: Your primary tables keep all PostgreSQL features—primary keys, foreign keys, B-tree/GIN/vchord indexes, triggers, and row-level locking.
2. High-Throughput Mutations: moonlink absorbs high-frequency INSERT, UPDATE, and DELETE streams in memory using Arrow buffers and Roaring deletion vectors, flushing optimized Parquet files in the background.
3. Schema Evolution: ALTER TABLE ... ADD COLUMN and DROP COLUMN on the source heap table automatically propagate to the columnstore mirror.
4. Remote Replication (src_uri): You can even run pg_mooncake on a dedicated analytical PostgreSQL instance and mirror tables from a remote production PostgreSQL database (including RDS, Aurora, Supabase, or Neon) via src_uri => 'postgresql://...'.