Get Started & Installation

• 1: Hello World Tutorial
• 2: Known Limitations

# 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

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

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

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

# 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

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