Use Cases & Architecture Patterns

• 1: Real-Time Operational Analytics (HTAP)
• 2: Archiving Large Postgres Tables
• 3: The Postgres Lakehouse
• 4: Log Analytics with OpenTelemetry

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.

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.

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