Mooncake Documentation

• 1: Welcome to pg_mooncake
  • 1.1: Architecture
  • 1.2: Moonlink Ingestion Engine
  • 1.3: duckdb_mooncake & DuckDB 2.0 Integration
  • 1.4: v0.1 vs v0.2 & v0.3 Evolution
• 2: Get Started & Installation
  • 2.1: Hello World Tutorial
  • 2.2: Known Limitations
• 3: SQL Procedures, Views & Configuration Reference
  • 3.1: Create Columnstore Table
  • 3.2: Query Columnstore Table & Supported Query Shapes
  • 3.3: Apache Iceberg v2 & Object Storage Configuration
  • 3.4: Query Your Data Lake in Postgres
• 4: Use Cases & Architecture Patterns
  • 4.1: Real-Time Operational Analytics (HTAP)
  • 4.2: Archiving Large Postgres Tables
  • 4.3: The Postgres Lakehouse
  • 4.4: Log Analytics with OpenTelemetry
• 5: v0.1 Archive: Welcome to pg_mooncake (Delta Lake)
  • 5.1: v0.1 Architecture (Delta Lake & USING columnstore)
  • 5.2: v0.1 Get Started
  • 5.3: v0.1 Hello World (USING columnstore)
  • 5.4: v0.1 Cloud Storage (mooncake.create_secret)
  • 5.5: v0.1 on Neon Postgres
  • 5.6: v0.1 Reference Overview
  • 5.7: v0.1 Columnstore DDL (USING columnstore)
  • 5.8: v0.1 Querying Columnstore Outside Postgres
  • 5.9: v0.1 GUC Configurations
  • 5.10: v0.1 Load Data
  • 5.11: v0.1 Query External Files
  • 5.12: v0.1 Supported Queries & Query Shapes
  • 5.13: v0.1 Data Archiving
  • 5.14: v0.1 Operational Analytics (Trigger + pg_cron Staging)
  • 5.15: v0.1 Data Warehousing & dbt
• 6: About
  • 6.1: Roadmap
  • 6.2: License
  • 6.3: Attribution
  • 6.4: Trademark Policy
  • 6.5: SBOM & Security Audit

1 - Welcome to pg_mooncake

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.

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

1.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();

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

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

1.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://...'.

2 - Get Started & Installation

This guide covers installing pg_mooncake (v0.3.0) (which bundles moonlink (v0.0.1) and duckdb_mooncake (v0.0.2)) alongside pg_duckdb (v1.2.0) on PostgreSQL 14, 15, 16, 17, and 18.

1. Install Binary Packages via Pigsty (Recommended)

The fastest way to install pg_mooncake on Linux (el8, el9, el10, d12, d13, u22, u24, u26 across x86_64 and aarch64) is with the pig package manager maintained by PGSTY. See the Download & Availability Matrix for direct package links.

# 1. Install the pig CLI package manager
curl -fsSL https://repo.pigsty.io/pig | bash

# 2. Configure PGDG + Pigsty PGSQL repositories
pig repo add pgsql -u

# 3. Install pg_duckdb and pg_mooncake (or pass -v 18 for a specific PG version)
pig install pg_duckdb pg_mooncake

# 1. Install the pig CLI via the mainland China mirror
curl -fsSL https://repo.pigsty.cc/pig | bash

# 2. Configure repositories using the China CDN mirror (-r china)
pig repo add pgsql -u -r china

# 3. Install pg_duckdb and pg_mooncake
pig install pg_duckdb pg_mooncake

# Debian / Ubuntu (APT) — example for PostgreSQL 18:
sudo apt-get update
sudo apt-get install -y postgresql-18-pg-duckdb postgresql-18-pg-mooncake

# RHEL / Rocky / AlmaLinux (DNF) — example for PostgreSQL 18:
sudo dnf install -y pg_duckdb_18 pg_mooncake_18

2. Configure postgresql.conf & Enable Extension

pg_mooncake uses PostgreSQL logical replication to stream rowstore changes into Moonlink and uses pg_duckdb to load the bundled mooncake.duckdb_extension binary. Add the following settings to postgresql.conf (or via ALTER SYSTEM):

# Required for Moonlink logical replication
wal_level = logical
max_replication_slots = 32
max_wal_senders = 32
max_worker_processes = 16

# Preload both pg_duckdb and pg_mooncake
shared_preload_libraries = 'pg_duckdb,pg_mooncake'

# Allow pg_duckdb to load the local mooncake.duckdb_extension
duckdb.allow_community_extensions = true
duckdb.allow_unsigned_extensions = true

# Optional: override only if mooncake.duckdb_extension is outside $(pg_config --sharedir)/extension/pg_mooncake
# mooncake.extension_repository = '/usr/pgsql-18/share/extension/pg_mooncake'

Restart PostgreSQL (sudo systemctl restart postgresql-18 or pig pg restart), then connect with psql and create the extension:

-- CASCADE automatically creates the required pg_duckdb (1.2.0) extension
CREATE EXTENSION IF NOT EXISTS pg_mooncake CASCADE;

3. Build from Source (v0.3.0)

To build pg_mooncake 0.3.0, pg_duckdb 1.2.0, duckdb_mooncake v0.0.2, and moonlink v0.0.1 from source:

Prerequisites

• PostgreSQL development packages: postgresql-server-dev-14 through 18 (or postgresql17-devel / postgresql18-devel)
• C/C++ build toolchain: cmake, ninja-build, g++ or clang, libssl-dev, libcurl4-openssl-dev, liblz4-dev, libzstd-dev, protobuf-compiler (protoc)
• Rust toolchain: Rust 1.99.0+ (stable) and cargo-pgrx 0.19.3:

  cargo install --locked cargo-pgrx --version 0.19.3

Clone, Verify Pins & Compile

# 1. Clone pg_mooncake with all submodules (pg_duckdb, duckdb_mooncake, moonlink)
git clone --recurse-submodules https://github.com/pgsty/pg_mooncake.git
cd pg_mooncake
git fetch --tags --force moonlink
git fetch --tags --force duckdb_mooncake

# 2. Verify all submodule commit SHAs, tags, and cargo-pgrx 0.19.3
make check-versions

# 3. Build and install pg_duckdb 1.2.0 (with DuckDB v2.0.0)
export PGRX_PG_CONFIG_PATH=/usr/pgsql-18/bin/pg_config
make pg_duckdb PG_CONFIG="$PGRX_PG_CONFIG_PATH" PG_VERSION=pg18 JOBS=$(nproc)

# 4. Build and install pg_mooncake 0.3.0 + duckdb_mooncake v0.0.2 + moonlink v0.0.1
make install PG_CONFIG="$PGRX_PG_CONFIG_PATH" PG_VERSION=pg18 JOBS=$(nproc)

4. Docker Image (Obsolete)

# Pull and start the historical upstream container
docker pull mooncakelabs/pg_mooncake
docker run --name mooncake-demo \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  -p 5432:5432 -d \
  mooncakelabs/pg_mooncake

# Connect with psql
docker exec -it mooncake-demo psql -U postgres

Next Steps

2.1 - Hello World Tutorial

This tutorial walks through the core operations of pg_mooncake: creating a regular PostgreSQL rowstore table, creating a real-time columnstore mirror (USING mooncake), running transactional INSERT, UPDATE, and DELETE statements, testing rollback semantics, and joining columnstore and rowstore tables.

1. Enable the Extension

Connect to your PostgreSQL database and ensure pg_mooncake (along with pg_duckdb) is enabled:

CREATE EXTENSION IF NOT EXISTS pg_mooncake CASCADE;

2. Create a Rowstore Table & Columnstore Mirror

First, create a standard PostgreSQL heap table (user_activity) for your transactional writes, then call mooncake.create_table to create its real-time columnstore mirror (user_activity_columnstore):

-- 1. Create a standard PostgreSQL rowstore (heap) table
CREATE TABLE user_activity (
    user_id BIGINT PRIMARY KEY,
    activity_type TEXT,
    activity_timestamp TIMESTAMP,
    duration INT
);

-- 2. Create a real-time columnstore mirror of user_activity
CALL mooncake.create_table('user_activity_columnstore', 'user_activity');

Behind the scenes, mooncake.create_table creates user_activity_columnstore USING mooncake, sets up a logical replication slot on user_activity, and registers the table with moonlink.

3. Insert, Update, and Delete Rows

Perform all writes (INSERT, UPDATE, DELETE) on the primary rowstore table (user_activity), and run your analytical SELECT queries on the columnstore mirror (user_activity_columnstore):

-- Insert rows into the rowstore table
INSERT INTO user_activity VALUES
    (1, 'login', '2024-01-01 08:00:00', 120),
    (2, 'page_view', '2024-01-01 08:05:00', 30),
    (3, 'logout', '2024-01-01 08:30:00', 60),
    (4, 'error', '2024-01-01 08:13:00', 60);

-- Update a row in the rowstore table
UPDATE user_activity SET duration = 180 WHERE user_id = 1;

-- Delete a row from the rowstore table
DELETE FROM user_activity WHERE user_id = 2;

-- Query the columnstore mirror (automatically waits for the latest WAL LSN)
SELECT * FROM user_activity_columnstore ORDER BY user_id;

Output:

 user_id | activity_type | activity_timestamp  | duration
---------+---------------+---------------------+----------
       1 | login         | 2024-01-01 08:00:00 |      180
       3 | logout        | 2024-01-01 08:30:00 |       60
       4 | error         | 2024-01-01 08:13:00 |       60
(3 rows)

4. Transactional Consistency & Rollback

Moonlink only ingests committed PostgreSQL transactions from the logical replication stream. Uncommitted or rolled-back transactions never appear in the columnstore mirror:

BEGIN;
INSERT INTO user_activity VALUES
    (5, 'login', '2024-01-02 10:00:00', 200),
    (6, 'login', '2024-01-02 10:30:00', 90);
ROLLBACK;

-- Rolled-back rows (user_id 5 and 6) are not present
SELECT * FROM user_activity_columnstore ORDER BY user_id;

5. Joins Across Columnstore & Rowstore Tables

You can join two columnstore tables together, or join a USING mooncake columnstore table directly with a regular PostgreSQL heap table in the same SQL query:

-- Join between two columnstore tables
SELECT ua.user_id, ua.activity_type, ua.duration
FROM user_activity_columnstore ua
JOIN user_activity_columnstore ua2 ON ua.user_id = ua2.user_id;

-- Create a regular PostgreSQL heap table for user profiles
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    username TEXT,
    tier TEXT
);

INSERT INTO users VALUES
    (1, 'alice', 'enterprise'),
    (3, 'bob', 'pro'),
    (4, 'charlie', 'free');

-- Join the regular Postgres heap table with the columnstore mirror
SELECT u.username, u.tier, ua.activity_type, ua.duration
FROM users u
JOIN user_activity_columnstore ua ON u.user_id = ua.user_id
ORDER BY u.user_id;

6. Inspect Replication Status & Create an Iceberg Snapshot

Check the current commit LSN, flushed LSN, and Iceberg warehouse directory using mooncake.list_tables(), or force-flush buffered Arrow data into Parquet + Apache Iceberg v2 metadata with mooncake.create_snapshot:

-- View replication LSN progress and warehouse path
SELECT * FROM mooncake.list_tables();

-- Flush in-memory Arrow buffer into a new Apache Iceberg v2 snapshot
CALL mooncake.create_snapshot('user_activity_columnstore');

2.2 - Known Limitations

Before deploying pg_mooncake (v0.3.0) in production, review the following architectural constraints and known limitations.

1. Read-Only Columnstore Mirror Tables

Tables created with CALL mooncake.create_table(dst, src) use the USING mooncake table access method and are read-only replicas.

• Direct INSERT, UPDATE, DELETE, or COPY statements targeting the USING mooncake table are rejected by PostgreSQL (ERROR:cannot modify table "..." because it uses the mooncake access method).
• Always execute DML operations against the source rowstore table (src); changes replicate automatically via moonlink.

2. Primary Keys & REPLICA IDENTITY FULL

To replicate UPDATE and DELETE operations accurately over PostgreSQL logical replication, the source table must provide complete old-row identification in the WAL stream:

• Tables with a Primary Key: Work out of the box using the primary key.
• Tables without a Primary Key: CALL mooncake.create_table(dst, src) automatically executes ALTER TABLE src REPLICA IDENTITY FULL before starting replication.
• Partitioned Tables (v0.3.0 behavior):
  • In pg_mooncake 0.3.0, calling mooncake.create_table on a partitioned table without a primary key automatically sets REPLICA IDENTITY FULL on both the partitioned root table and all existing leaf partitions (fixing an earlier bug where partition UPDATE events lacked complete old tuples and caused a replication worker panic).
  • Important: If you attach or create new partitions after mooncake.create_table has already run, you must explicitly run ALTER TABLE <new_partition> REPLICA IDENTITY FULL; on that new partition before executing UPDATE or DELETE statements on it.

3. TRUNCATE Behavior

• Regular Heap Tables: TRUNCATE on a mirrored regular heap table is not replicated to the columnstore mirror.
• Partitioned Tables (Archiving Pattern): TRUNCATE or DROP TABLE on individual old partitions of a partitioned source table is intentionally ignored by the columnstore mirror so you can drop cold partitions from PostgreSQL heap storage while retaining the complete historical dataset in the columnstore. See Archiving Large Postgres Tables.

4. Schema Evolution (ALTER TABLE)

Only the following ALTER TABLE operations on the source rowstore table are automatically replicated to the columnstore mirror:

• ALTER TABLE <src> ADD COLUMN <col> <type>
• ALTER TABLE <src> DROP COLUMN <col>

Other DDL modifications (such as ALTER COLUMN ... TYPE or RENAME COLUMN) are not automatically propagated; drop and recreate the columnstore mirror (DROP TABLE <dst>; CALL mooncake.create_table('<dst>', '<src>');) when performing complex schema migrations.

5. Unsupported Data Types

While pg_mooncake supports all common PostgreSQL numeric, text, boolean, date/time, UUID, JSON/JSONB, and array types, the following types are currently unsupported or have caveats:

• PostgreSQL custom ENUM types and composite/domain types
• Geometric and network address types not mapped by pg_duckdb
• Very high-precision NUMERIC values exceeding DuckDB’s DECIMAL(38, s) width

6. mooncake.load_files (SQLSTATE 0A000)

In pg_mooncake 0.3.0, calling CALL mooncake.load_files(dst, files) explicitly raises SQLSTATE 0A000 (ERRCODE_FEATURE_NOT_SUPPORTED: "mooncake.load_files is not implemented by the bundled Moonlink version").

• Previously, the bundled Moonlink server’s import_data handler was a no-op stub that returned success without ingesting any rows.
• To load external Parquet or CSV files, either load them into the source PostgreSQL table with COPY / INSERT INTO ... SELECT * FROM duckdb.read_parquet(...), or query them directly in place as described in Query Your Data Lake in Postgres.

3 - SQL Procedures, Views & Configuration Reference

This section provides the complete reference for all SQL procedures, catalog inspection functions, table access methods, and PostgreSQL GUC configuration parameters exposed by pg_mooncake (v0.3.0).

SQL Procedures & Functions (mooncake schema)

mooncake.list_tables() Output Columns

SELECT * FROM mooncake.list_tables();

Table Access Method & Event Triggers

• Table Access Method (USING mooncake): Created automatically by mooncake.create_table and registered with pg_duckdb 1.2.0 via RegisterDuckdbTableAm("mooncake").
• Drop Cleanup Trigger (mooncake_drop_trigger): An ON sql_drop event trigger (mooncake_drop_trigger_func) automatically unregisters the table from Moonlink and cleans up its logical replication subscription when you execute DROP TABLE <dst>;.

GUC Configuration Parameters

Detailed Reference Guides

3.1 - Create Columnstore Table

In pg_mooncake, columnstore tables are created as real-time mirrors of existing PostgreSQL rowstore (heap or partitioned) tables using the mooncake.create_table procedure.

Procedure Signature

CALL mooncake.create_table(
    dst text,
    src text,
    src_uri text DEFAULT NULL,
    table_config json DEFAULT NULL
);

1. Mirror a Table in the Same Database

When src_uri is omitted (or NULL), pg_mooncake replicates directly from src in the current database:

-- Create a source OLTP table
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    amount NUMERIC(12, 2) NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create the columnstore mirror table
CALL mooncake.create_table('orders_analytics', 'orders');

What happens when CALL mooncake.create_table('orders_analytics', 'orders') runs:

1. pg_mooncake inspects the columns of orders and creates CREATE TABLE orders_analytics (...) USING mooncake.
2. If orders does not have a primary key, pg_mooncake runs ALTER TABLE orders REPLICA IDENTITY FULL (and in v0.3.0, automatically applies REPLICA IDENTITY FULL to all existing leaf partitions if orders is partitioned).
3. moonlink creates a logical replication slot (pgoutput), copies all existing rows from orders, and begins streaming ongoing INSERT, UPDATE, and DELETE changes in real time.

2. Mirror a Table from a Remote PostgreSQL Instance (src_uri)

You can run pg_mooncake on a dedicated analytical PostgreSQL node and mirror tables from a remote production PostgreSQL database (including self-hosted PostgreSQL, AWS RDS, Aurora, Supabase, or Neon):

CALL mooncake.create_table(
    dst => 'orders_analytics',
    src => 'public.orders',
    src_uri => 'postgresql://replicator:[email protected]:5432/appdb'
);

Requirements on the remote source database:

• wal_level = logical must be enabled.
• The user in src_uri must have REPLICATION privileges and SELECT permission on src.
• If src has no primary key, REPLICA IDENTITY FULL must be set on src.

3. Mirror a Partitioned Table

mooncake.create_table supports declarative PostgreSQL partitioned tables (PARTITION BY RANGE, LIST, or HASH). Specify the root partitioned table as src:

CREATE TABLE events (
    id BIGINT,
    event_type TEXT,
    payload JSONB,
    created_at TIMESTAMPTZ
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- Mirror the partitioned root table
CALL mooncake.create_table('events_analytics', 'events');

4. Advanced table_config Options

You can pass a JSON object via table_config to customize Moonlink’s per-table behavior (TableConfig in moonlink_backend/src/table_config.rs), including append-only optimization, background compaction flags, and custom filesystem / S3 / GCS storage backends for Iceberg data and WAL:

-- Example 1: Append-only table (skips primary-key delete hash index)
CALL mooncake.create_table(
    dst => 'events_analytics',
    src => 'events',
    src_uri => NULL,
    table_config => '{
        "mooncake": {
            "append_only": true,
            "skip_index_merge": false,
            "skip_data_compaction": false
        }
    }'::json
);

-- Example 2: Custom S3 storage backend for Iceberg warehouse and WAL
CALL mooncake.create_table(
    dst => 'orders_analytics',
    src => 'orders',
    src_uri => NULL,
    table_config => '{
        "iceberg": {
            "storage_config": {
                "s3": {
                    "region": "us-east-1",
                    "bucket": "my-mooncake-warehouse",
                    "access_key_id": "<AWS_ACCESS_KEY_ID>",
                    "secret_access_key": "<AWS_SECRET_ACCESS_KEY>"
                }
            }
        }
    }'::json
);

5. Dropping a Columnstore Mirror Table

To remove a columnstore mirror table and automatically clean up its Moonlink metadata and logical replication slot, simply run DROP TABLE:

DROP TABLE orders_analytics;

3.2 - Query Columnstore Table & Supported Query Shapes

Columnstore mirror tables created with CALL mooncake.create_table(dst, src) are queried using standard PostgreSQL SELECT syntax. Behind the scenes, pg_mooncake and pg_duckdb 1.2.0 translate and route analytical scans to the embedded DuckDB v2.0.0 vectorized execution engine.

How Queries Work

When you query a USING mooncake table:

1. LSN Synchronization: duckdb_mooncake checks PostgreSQL’s current WAL write LSN and waits for moonlink to ingest all committed transactions up to that LSN.
2. Segment Elimination: DuckDB 2.0 inspects multi-file Parquet column min/max statistics to skip files and row groups that cannot match your WHERE predicates.
3. Zero-Copy Hybrid Scan: Flushed Parquet files, Roaring Bitmap deletion vectors, and the in-memory Apache Arrow write buffer are scanned in parallel across DuckDB worker threads (duckdb.threads).

You can verify that DuckDB is executing your query by running EXPLAIN:

EXPLAIN SELECT customer_id, SUM(amount)
FROM orders_analytics
WHERE created_at >= '2026-01-01'
GROUP BY customer_id;

Analytical Query Shapes (Recommended)

Columnstore tables excel at analytical workloads that scan large numbers of rows across a subset of columns, often delivering 10× to 1000× speedups over heap tables.

1. Multi-Dimensional Aggregations

SELECT
    region,
    date_trunc('quarter', sale_date) AS quarter,
    SUM(amount) AS total_sales,
    COUNT(*) AS num_transactions,
    AVG(amount) AS avg_sale
FROM sales_analytics
WHERE sale_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY region, quarter
ORDER BY region, quarter;

2. High-Selectivity Filtering & Top-N Ranking

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM sales_analytics
WHERE sale_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY customer_id
HAVING SUM(amount) > 10000
ORDER BY total_spent DESC
LIMIT 50;

3. Time-Series Rollups & Window Functions

SELECT
    date_trunc('day', event_time) AS day,
    COUNT(*) AS num_events,
    SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM events_analytics
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY day
ORDER BY day;

4. Hybrid Joins with Regular PostgreSQL Heap Tables

You can join a USING mooncake columnstore mirror directly with regular PostgreSQL rowstore tables (such as dimension tables or lookup tables):

SELECT
    u.tier,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_revenue
FROM users u
JOIN orders_analytics o ON u.id = o.customer_id
WHERE o.created_at >= NOW() - INTERVAL '7 days'
GROUP BY u.tier;

Operational Point Lookups (Use the Rowstore Table)

While pg_mooncake supports arbitrary SELECT queries, single-row point lookups (WHERE id = 12345) and tiny index range scans should be routed to your source rowstore table, where PostgreSQL’s B-tree indexes provide sub-millisecond lookup latency.

-- Point lookup -> query the primary rowstore table (uses B-tree index)
SELECT * FROM orders WHERE order_id = 12345;

-- Large analytical scan -> query the columnstore mirror (uses DuckDB 2.0)
SELECT status, COUNT(*), SUM(amount) FROM orders_analytics GROUP BY status;

Query Translation & DuckDB Functions

Because queries on USING mooncake tables are executed by DuckDB (pg_duckdb), certain PostgreSQL-specific functions that lack a direct DuckDB equivalent (such as to_char(), certain regexp_* overloads, or specific jsonb_* operators) may require DuckDB-compatible expressions.

Exposing DuckDB Functions in PostgreSQL

You can expose native DuckDB analytical functions (such as time_bucket) inside PostgreSQL by defining a placeholder PL/pgSQL function with the matching signature. When a query targets a columnstore table, pg_duckdb intercepts the call and executes DuckDB’s native vectorized implementation:

CREATE OR REPLACE FUNCTION time_bucket(interval, timestamp)
RETURNS timestamp AS $$
BEGIN
    RAISE EXCEPTION 'placeholder function for DuckDB columnstore execution';
END;
$$ LANGUAGE plpgsql;

-- Executed natively by DuckDB when querying a columnstore table
SELECT
    time_bucket(INTERVAL '5 minutes', created_at) AS five_min_bucket,
    COUNT(*) AS requests
FROM events_analytics
WHERE created_at >= NOW() - INTERVAL '6 hours'
GROUP BY five_min_bucket
ORDER BY five_min_bucket DESC;

3.3 - Apache Iceberg v2 & Object Storage Configuration

Every columnstore mirror table in pg_mooncake (v0.3.0) is backed by moonlink, which writes data as standard Parquet files and maintains Apache Iceberg v2 table metadata.

By default, Moonlink writes files to the local filesystem under $PGDATA/pg_mooncake/. You can configure pg_mooncake to write directly to Amazon S3, Cloudflare R2, MinIO, or Google Cloud Storage (GCS) so external engines (DuckDB, Apache Spark, Trino, Snowflake, PyIceberg) can query your PostgreSQL tables directly from object storage.

1. Configure Object Storage Credentials (duckdb.secrets)

pg_mooncake reads object storage credentials from pg_duckdb’s duckdb.secrets table and passes them to both Moonlink and DuckDB.

Amazon S3

INSERT INTO duckdb.secrets (name, type, key_id, secret, region, scope)
VALUES (
    's3_warehouse',
    'S3',
    '<AWS_ACCESS_KEY_ID>',
    '<AWS_SECRET_ACCESS_KEY>',
    'us-east-1',
    's3://my-mooncake-bucket'
);

Cloudflare R2 / S3-Compatible (MinIO)

INSERT INTO duckdb.secrets (name, type, key_id, secret, endpoint, scope)
VALUES (
    'r2_warehouse',
    'S3',
    '<R2_ACCESS_KEY_ID>',
    '<R2_SECRET_ACCESS_KEY>',
    '<ACCOUNT_ID>.r2.cloudflarestorage.com',
    's3://my-r2-bucket'
);

2. Configure Table Storage (table_config & mooncake.default_bucket)

In pg_mooncake v0.3.0, you can configure S3 / R2 / GCS storage directly in the table_config JSON parameter of mooncake.create_table (or set mooncake.default_bucket as documented in upstream v0.1/v0.2 guides):

-- Option A (v0.2 / v0.3 Moonlink per-table storage_config):
CALL mooncake.create_table(
    dst => 'orders_analytics',
    src => 'orders',
    src_uri => NULL,
    table_config => '{
        "iceberg": {
            "storage_config": {
                "s3": {
                    "region": "us-east-1",
                    "bucket": "my-mooncake-bucket",
                    "access_key_id": "<AWS_ACCESS_KEY_ID>",
                    "secret_access_key": "<AWS_SECRET_ACCESS_KEY>",
                    "endpoint": "https://s3.us-east-1.amazonaws.com"
                }
            }
        }
    }'::json
);

-- Option B (Default local filesystem warehouse under $PGDATA/pg_mooncake):
CALL mooncake.create_table('orders_analytics', 'orders');

3. Materialize an Apache Iceberg v2 Snapshot

While in-database queries on orders_analytics automatically read both flushed Parquet files and Moonlink’s in-memory Arrow write buffer, external Iceberg readers only see data that has been flushed into an Iceberg metadata snapshot.

To force-flush all committed rows up to the current PostgreSQL WAL LSN and write a new v*.metadata.json file:

CALL mooncake.create_snapshot('orders_analytics');

4. Locate the Iceberg Warehouse & Query Externally

Use mooncake.list_tables() to inspect the commit LSN, flushed LSN, and exact iceberg_warehouse_location:

SELECT * FROM mooncake.list_tables();

Example output:

          table          | commit_lsn | flush_lsn  |              iceberg_warehouse_location
-------------------------+------------+------------+-------------------------------------------------------
 public.orders_analytics | 0/1A4F890  | 0/1A4F890  | s3://my-mooncake-bucket/warehouse/postgres/public/orders_analytics

Querying from External DuckDB / PyIceberg / Spark

Inside the iceberg_warehouse_location directory, Moonlink writes standard Apache Iceberg v2 metadata (metadata/v1.metadata.json, v2.metadata.json, etc.):

-- From any external DuckDB instance:
INSTALL iceberg;
LOAD iceberg;

SELECT *
FROM iceberg_scan('s3://my-mooncake-bucket/warehouse/postgres/public/orders_analytics/metadata/v2.metadata.json');

5. Compacting Files (mooncake.optimize_table)

To compact small Parquet files and reconcile Roaring Bitmap deletion vectors into clean base files:

-- Run full compaction on the table
CALL mooncake.optimize_table('orders_analytics', 'full');

3.4 - Query Your Data Lake in Postgres

Because pg_mooncake (v0.3.0) embeds DuckDB v2.0.0 via pg_duckdb 1.2.0, you can query external files in your data lake (local disk, S3, Cloudflare R2, GCS, HTTP, or HuggingFace hf:// datasets) directly from PostgreSQL without loading them into heap tables first.

Supported External Formats

• Apache Parquet (duckdb.read_parquet)
• CSV / TSV (duckdb.read_csv)
• JSON / NDJSON (duckdb.read_json)
• Apache Iceberg (duckdb.iceberg_scan)
• Delta Lake (duckdb.delta_scan)

1. Querying Parquet Files (duckdb.read_parquet)

Use duckdb.read_parquet and specify the expected output columns in the AS (...) clause:

-- Query a single Parquet file or wildcard glob pattern on S3
SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.read_parquet('s3://my-bucket/events/*.parquet') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

You can also query Hive-partitioned directory structures:

SELECT *
FROM duckdb.read_parquet('s3://my-bucket/events/year=2026/month=*/day=*/*.parquet') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
);

2. Querying CSV Files (duckdb.read_csv)

SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.read_csv('s3://my-bucket/events.csv') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

3. Querying External Iceberg & Delta Lake Tables

First install and load the iceberg or delta DuckDB extension via duckdb.install_extension:

SELECT duckdb.install_extension('iceberg');
SELECT duckdb.install_extension('delta');

-- Query an external Apache Iceberg table
SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.iceberg_scan('s3://my-bucket/iceberg_events/metadata/v1.metadata.json') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

-- Query an external Delta Lake table
SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.delta_scan('s3://my-bucket/delta_events') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

4. Joining External Files with PostgreSQL Tables

You can join external Parquet, Iceberg, or Delta Lake datasets directly with regular PostgreSQL heap tables or USING mooncake columnstore mirrors in a single SQL query:

SELECT
    u.username,
    u.email,
    e.event_type,
    e.created_at
FROM users u
JOIN duckdb.read_parquet('s3://my-bucket/events.parquet') AS e(
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
) ON u.id = e.user_id
WHERE e.created_at >= '2026-01-01';

5. Querying HuggingFace Datasets (hf://)

DuckDB supports reading public HuggingFace datasets directly via the hf://datasets/<org>/<repo>/<path> URI scheme:

SELECT id, message, timestamp
FROM duckdb.read_parquet(
    'hf://datasets/OpenLeecher/lmsys_chat_1m_clean/data/train-00000-of-00004.parquet'
) AS (
    id INT,
    message TEXT,
    timestamp TIMESTAMP
)
LIMIT 20;

4 - Use Cases & Architecture Patterns

pg_mooncake enables four high-value analytical patterns directly inside PostgreSQL without introducing Kafka, Debezium, or an external data warehouse.

4.1 - Real-Time Operational Analytics (HTAP)

If you are running slow analytical queries (dashboards, customer-facing metrics, multi-table aggregations) on PostgreSQL tables, pg_mooncake gives you a straightforward route to accelerate them:

1. Keep your application write path 100% unchanged.
2. Create a real-time columnstore mirror with CALL mooncake.create_table(dst, src).
3. Route analytical SELECT queries to the columnstore mirror table.

Key Advantages

• Schema changes supported: ALTER TABLE ... ADD COLUMN and DROP COLUMN on the source table automatically sync to the columnstore mirror.
• Fast UPDATE and DELETE handling: moonlink uses in-memory Apache Arrow buffers and Roaring Bitmap deletion vectors, handling high-frequency OLTP updates and deletes far more efficiently than traditional Parquet/Iceberg writers.
• Zero-ETL: No Debezium, Kafka, or Flink clusters to operate—pg_mooncake uses PostgreSQL’s built-in logical replication.
• Transactional consistency: Analytical queries wait for the current PostgreSQL WAL LSN barrier before scanning, guaranteeing a consistent view of committed data.

Implementation Pattern: Write to Rowstore, Read from Columnstore

-- 1. Your existing OLTP table (for transactional writes and point lookups)
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_status TEXT NOT NULL DEFAULT 'pending',
    order_date TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 2. Create the real-time columnstore mirror (for analytics)
CALL mooncake.create_table('orders_analytics', 'orders');

-- 3. Write operations: always use the original rowstore table
INSERT INTO orders (customer_id, total_amount) VALUES (123, 99.99);
UPDATE orders SET total_amount = 149.99, order_status = 'shipped' WHERE order_id = 1;

-- 4. Analytical read operations: query the columnstore mirror
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value
FROM orders_analytics
WHERE order_date >= CURRENT_DATE
GROUP BY customer_id;

Manual Query Routing

-- Transactional DML & indexed point lookups -> Original rowstore table (orders)
INSERT INTO orders (customer_id, total_amount) VALUES (123, 99.99);
UPDATE orders SET order_status = 'shipped' WHERE order_id = 1;
DELETE FROM orders WHERE order_id = 1;
SELECT * FROM orders WHERE order_id = 1;

-- Large analytical scans & aggregations -> Columnstore mirror (orders_analytics)
SELECT customer_id, SUM(total_amount) FROM orders_analytics GROUP BY customer_id;
SELECT COUNT(*) FROM orders_analytics WHERE order_date >= CURRENT_DATE;
SELECT * FROM orders_analytics ORDER BY total_amount DESC LIMIT 10;

This explicit separation gives you predictable performance: transactional operations stay fast on the B-tree-indexed heap table, while analytical scans leverage DuckDB 2.0’s vectorized execution over columnar storage.

4.2 - Archiving Large Postgres Tables

pg_mooncake’s columnstore mirror writes data as compressed Parquet files (on local disk or S3 / Cloudflare R2 object storage), providing cost-effective, durable history while PostgreSQL heap storage only maintains hot recent partitions.

Architecture Pattern

The partition archiving pattern follows three rules:

1. Create a columnstore mirror (CALL mooncake.create_table) of a time- or range-partitioned PostgreSQL table.
2. Periodically DROP or TRUNCATE old partitions from the PostgreSQL rowstore once they age out of your hot operational window.
3. Retain complete history in the columnstore mirror—pg_mooncake intentionally ignores DROP TABLE and TRUNCATE events on child partitions of a partitioned source table, so all historical rows remain queryable in the columnstore!

Step-by-Step Walkthrough

Below is a complete example using a range-partitioned transactions table.

Step 1: Create a Partitioned Rowstore Table

CREATE TABLE transactions (
    id INT,
    amount DECIMAL(10, 2),
    user_id INT,
    created_at TIMESTAMP,
    PRIMARY KEY (id)
) PARTITION BY RANGE (id);

Step 2: Create a Columnstore Mirror

CALL mooncake.create_table('transactions_archive', 'transactions');

Step 3: Add First Partition & Insert Data

-- Create first partition for IDs 0..50
CREATE TABLE transactions_p1 PARTITION OF transactions
    FOR VALUES FROM (0) TO (50);

-- Insert sample data
INSERT INTO transactions_p1 VALUES
    (1, 99.99, 101, '2024-01-01 10:00:00'),
    (2, 150.50, 102, '2024-01-01 11:00:00'),
    (3, 75.25, 103, '2024-01-01 12:00:00');

Step 4: Drop the Old Partition from Rowstore

When transactions_p1 is cold and you want to reclaim PostgreSQL disk space immediately:

-- Drop the old partition from the PostgreSQL rowstore
DROP TABLE transactions_p1;

Step 5: Query Historical Data in the Columnstore

Even though transactions_p1 was dropped from the rowstore, all three rows remain intact in transactions_archive:

SELECT * FROM transactions_archive ORDER BY id;

Step 6: Continue with New Partitions

-- Add new partition for recent data
CREATE TABLE transactions_p2 PARTITION OF transactions
    FOR VALUES FROM (50) TO (100);

-- Note: if your partitioned table does NOT have a primary key, set REPLICA IDENTITY FULL
-- on newly added partitions before running UPDATE/DELETE:
-- ALTER TABLE transactions_p2 REPLICA IDENTITY FULL;

INSERT INTO transactions_p2 VALUES
    (51, 200.00, 104, '2024-01-02 10:00:00'),
    (52, 89.99, 105, '2024-01-02 11:00:00'),
    (53, 125.75, 106, '2024-01-02 12:00:00');

-- Querying the columnstore returns all 6 historical + recent rows
SELECT * FROM transactions_archive ORDER BY id;

Best Practices

• Never re-insert overlapping keys into dropped partition ranges: After dropping or truncating a rowstore partition, do not recreate a partition covering the same key range and insert conflicting rows, as the historical rows from the dropped partition are still retained in the columnstore.
• Always use declarative partitioned tables for archiving: pg_mooncake safely ignores partition drops and truncates on partitioned tables.
• Pair with S3 / R2 object storage: Set mooncake.default_bucket = 's3://...' before creating the mirror so archived partitions are stored directly in low-cost cloud object storage. See Iceberg & Object Storage.

4.3 - The Postgres Lakehouse

The Lakehouse architecture stores data once in open table formats (Apache Iceberg or Delta Lake) on object storage so multiple compute engines—PostgreSQL, DuckDB, Apache Spark, Snowflake, Trino, and Python (Polars / PyIceberg)—can share the same dataset without copying it.

pg_mooncake makes PostgreSQL a first-class citizen of your lakehouse in both directions:

1. Postgres $\rightarrow$ Iceberg Lakehouse (Zero-ETL Export): Replicate operational PostgreSQL tables directly into Apache Iceberg v2 tables on S3, Cloudflare R2, or local storage via moonlink.
2. Lakehouse $\rightarrow$ Postgres (Direct In-DB Scan): Query external Parquet, Apache Iceberg, and Delta Lake tables directly from PostgreSQL and join them with live operational tables via pg_duckdb.

1. Export Operational Postgres Tables as Apache Iceberg v2

Traditionally, landing PostgreSQL data in Apache Iceberg required deploying Debezium, Kafka, Schema Registry, and Spark/Flink consumers. With pg_mooncake, it takes three SQL commands:

-- 1. Configure your S3 or Cloudflare R2 bucket credentials
INSERT INTO duckdb.secrets (name, type, key_id, secret, region, scope)
VALUES ('lakehouse_s3', 'S3', '<ACCESS_KEY>', '<SECRET_KEY>', 'us-east-1', 's3://company-lakehouse');

SET mooncake.default_bucket = 's3://company-lakehouse/iceberg';

-- 2. Mirror your operational table into an Iceberg-backed columnstore table
CALL mooncake.create_table('customers_iceberg', 'customers');

-- 3. Flush an Iceberg v2 metadata snapshot for external engines
CALL mooncake.create_snapshot('customers_iceberg');

Inspect the generated warehouse path with SELECT * FROM mooncake.list_tables(); and point Spark, Snowflake, Trino, or PyIceberg directly at <iceberg_warehouse_location>/metadata/v*.metadata.json.

2. Query External Lakehouse Tables Inside PostgreSQL

At the same time, your PostgreSQL applications can query tables written by Spark or Databricks in S3 and join them with live PostgreSQL heap tables:

SELECT duckdb.install_extension('iceberg');

SELECT
    c.customer_id,
    c.email,
    ml.churn_probability
FROM customers c
JOIN duckdb.iceberg_scan('s3://company-lakehouse/ml_features/churn_scores/metadata/v3.metadata.json') AS ml(
    customer_id BIGINT,
    churn_probability DOUBLE PRECISION
) ON c.customer_id = ml.customer_id
WHERE ml.churn_probability > 0.80;

4.4 - Log Analytics with OpenTelemetry

PostgreSQL is rarely used for large-scale log analytics because uncompressed heap tables and indexes grow expensive quickly, and full-table scans over millions of log entries are slow.

With pg_mooncake, you can combine partitioned PostgreSQL log ingestion with an object-storage-backed columnstore mirror:

• Low storage cost: Historical logs are compressed into columnar Parquet files on S3, Cloudflare R2, or local disk.
• Simple self-hosting: Just PostgreSQL + object storage—no Elasticsearch or ClickHouse cluster required.
• Fast vectorized scans: DuckDB 2.0 scans millions of log records per second with segment elimination on timestamps.

Implementation Pattern

Combine a time-partitioned rowstore table for log ingestion with a USING mooncake columnstore mirror for analytical queries:

-- 1. Create your partitioned log ingestion table
CREATE TABLE application_logs (
    log_id BIGSERIAL,
    timestamp TIMESTAMPTZ NOT NULL,
    level TEXT NOT NULL,
    service TEXT NOT NULL,
    message TEXT,
    trace_id TEXT,
    span_id TEXT,
    attributes JSONB,
    PRIMARY KEY (log_id, timestamp)
) PARTITION BY RANGE (timestamp);

CREATE TABLE application_logs_today PARTITION OF application_logs
    FOR VALUES FROM ('2026-11-05') TO ('2026-11-06');

-- 2. Create the columnstore mirror for fast log analytics
CALL mooncake.create_table('application_logs_analytics', 'application_logs');

-- 3. Ingest logs into the rowstore table
INSERT INTO application_logs (timestamp, level, service, message, trace_id, span_id, attributes)
VALUES (
    NOW(),
    'ERROR',
    'api-service',
    'Database connection timeout',
    'trace-123',
    'span-456',
    '{"user_id": 1001, "region": "us-east-1"}'
);

-- 4. Run analytical queries against the columnstore mirror
SELECT
    service,
    level,
    COUNT(*) AS error_count
FROM application_logs_analytics
WHERE timestamp >= NOW() - INTERVAL '1 hour'
  AND level = 'ERROR'
GROUP BY service, level
ORDER BY error_count DESC;

As daily log partitions age out (for example, after 24 or 48 hours), drop the old partitions from application_logs (DROP TABLE application_logs_2026_11_01;) as described in Archiving Large Postgres Tables. The complete log history remains queryable in application_logs_analytics.

5 - v0.1 Archive: Welcome to pg_mooncake (Delta Lake)

pg_mooncake v0.1 is the first-generation PostgreSQL extension that added USING columnstore tables backed by Delta Lake and DuckDB (pg_duckdb) for analytical workloads.

Key Features of v0.1

1. Transactional INSERT, UPDATE, and DELETE directly on USING columnstore tables
2. Columnar Parquet + Delta Lake metadata stored on local disk or S3 / R2 object storage
3. Vectorized analytical execution via embedded DuckDB
4. Seamless joins between regular PostgreSQL heap tables and USING columnstore tables

v0.1 Archive Contents

5.1 - v0.1 Architecture (Delta Lake & USING columnstore)

In pg_mooncake v0.1, table metadata is stored inside PostgreSQL catalogs for transactional consistency, while columnstore data is written as Parquet files with Delta Lake metadata on local disk or object storage.

Columnstore tables are exposed as a PostgreSQL Table Access Method (USING columnstore). Queries involving columnstore tables are routed from PostgreSQL to DuckDB and streamed back via pg_duckdb.

Read Path

Queries involving USING columnstore tables are routed from PostgreSQL to DuckDB, with minor SQL query rewrites applied to bridge syntax differences.

pg_mooncake v0.1 caches Parquet metadata and column statistics inside PostgreSQL (mooncake.data_files) and uses them for segment elimination during table scans.

Write Path

In v0.1, transactional INSERT, UPDATE, and DELETE statements run directly on USING columnstore tables. To support DELETE and UPDATE, an implicit row_id column consisting of (file_id, row_offset) identifies rows during table scans.

Each write transaction creates a new Parquet file. Because of this, pg_mooncake v0.1 is designed for batched writes (100,000–1,000,000 rows) rather than small, frequent OLTP transactions.

Local Disk Caching

On writes, data is cached on local disk (mooncake.enable_local_cache = true) to accelerate queries on recently written data before or alongside cloud object storage uploads.

5.2 - v0.1 Get Started

Historical Docker Image (Obsolete)

docker pull mooncakelabs/pg_mooncake
docker run --name mooncake-demo -e POSTGRES_HOST_AUTH_METHOD=trust -p 5432:5432 -d mooncakelabs/pg_mooncake
docker run -it --rm --link mooncake-demo:postgres mooncakelabs/pg_mooncake psql -h postgres -U postgres

Building v0.1 from Source

git submodule update --init --recursive
make release -j$(nproc)
make install

Create the Extension

CREATE EXTENSION pg_mooncake;

5.3 - v0.1 Hello World (USING columnstore)

Create the Extension

CREATE EXTENSION pg_mooncake;

Create a USING columnstore Table (v0.1)

CREATE TABLE user_activity (
    user_id BIGINT,
    activity_type TEXT,
    activity_timestamp TIMESTAMP,
    duration INT
) USING columnstore;

Inserts, Updates, and Deletes

INSERT INTO user_activity VALUES
    (1, 'login', '2024-01-01 08:00:00', 120),
    (2, 'page_view', '2024-01-01 08:05:00', 30),
    (3, 'logout', '2024-01-01 08:30:00', 60),
    (4, 'error', '2024-01-01 08:13:00', 60);

UPDATE user_activity SET duration = 180 WHERE user_id = 1;
DELETE FROM user_activity WHERE user_id = 2;

Transactions

BEGIN;
INSERT INTO user_activity VALUES
    (5, 'login', '2024-01-02 10:00:00', 200),
    (6, 'login', '2024-01-02 10:30:00', 90);
ROLLBACK;
SELECT * FROM user_activity;

Joins

-- Between two columnstore tables
SELECT * FROM user_activity ua
JOIN user_activity ua2 ON ua.user_id = ua2.user_id;

-- Between a regular PostgreSQL table and a columnstore table
SELECT * FROM users JOIN user_activity ON users.user_id = user_activity.user_id;

5.4 - v0.1 Cloud Storage (mooncake.create_secret)

S3 Buckets (v0.1)

SELECT mooncake.create_secret(
    '<name>', 'S3', '<key_id>', '<secret>',
    '{"REGION": "<s3-region>", "SCOPE": "s3://<bucket>"}'
);

For S3 Express buckets:

SELECT mooncake.create_secret(
    '<name>', 'S3', '<key_id>', '<secret>',
    '{"ENDPOINT": "s3express-use1-az6.us-east-1.amazonaws.com", "REGION": "us-east-1"}'
);

Cloudflare R2 Buckets (v0.1)

SELECT mooncake.create_secret(
    '<name>', 'S3', '<key_id>', '<secret>',
    '{"ENDPOINT": "<ACCOUNT_ID>.r2.cloudflarestorage.com"}'
);

Set Default Bucket

ALTER DATABASE <database> SET mooncake.default_bucket = 's3://<bucket>';
SHOW mooncake.default_bucket;

S3 and R2 Parameters

5.5 - v0.1 on Neon Postgres

pg_mooncake v0.1 was offered as an experimental extension on Neon.

Enable the Extension on Neon (v0.1)

SET neon.allow_unstable_extensions = 'true';
CREATE EXTENSION pg_mooncake;

Cloud Storage Requirement

Because Neon does not allow extensions to persist columnstore files on local compute disk, using pg_mooncake v0.1 on Neon required configuring an external S3 or Cloudflare R2 bucket as described in v0.1 Cloud Storage, and disabling Neon’s scale-to-zero feature to preserve local metadata caches.

5.6 - v0.1 Reference Overview

• v0.1 Columnstore DDL (USING columnstore)
• v0.1 Querying Columnstore Outside Postgres (mooncake.columnstore_tables)
• v0.1 GUC Configurations (mooncake.maximum_memory, mooncake.maximum_threads)
• v0.1 Load Data (COPY & mooncake.read_parquet)
• v0.1 Query External Files (mooncake.read_parquet, mooncake.read_csv, iceberg_scan, delta_scan)
• v0.1 Supported Queries & Query Shapes

5.7 - v0.1 Columnstore DDL (USING columnstore)

Create Columnstore Table (v0.1)

In v0.1, columnstore tables were created directly with the USING columnstore clause:

CREATE TABLE users_columnstore (
    id INT,
    name TEXT,
    created_at TIMESTAMP
) USING columnstore;

-- Or via CREATE TABLE ... AS SELECT
CREATE TABLE users_columnstore USING columnstore AS
    SELECT * FROM users_rowstore;