SQL Procedures, Views & Configuration Reference

• 1: Create Columnstore Table
• 2: Query Columnstore Table & Supported Query Shapes
• 3: Apache Iceberg v2 & Object Storage Configuration
• 4: Query Your Data Lake in Postgres

SELECT * FROM mooncake.list_tables();

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:secret@prod-db.example.com: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;

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 - 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');

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;