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;