pg_mooncake Platform

CLI name: pg-mooncake - use benchbox run --platform pg-mooncake

pg_mooncake is a PostgreSQL extension that adds native columnstore tables with DuckDB-powered vectorized execution. Data is stored in Parquet format with Iceberg metadata, providing 5-20x columnar compression and top-10 ClickBench performance. BenchBox provides first-class pg_mooncake support for benchmarking columnstore PostgreSQL workloads.

Features

Columnstore mirrors - BenchBox heap-loads with COPY, then promotes loaded tables with mooncake.create_table
DuckDB execution - Vectorized query engine for analytical workloads
Columnar compression - 5-20x compression ratios vs PostgreSQL heap tables
Object storage - Optional S3/GCS backend for Parquet data
PostgreSQL compatible - Uses standard PostgreSQL wire protocol and COPY loading before columnstore promotion
Top-10 ClickBench - Competitive with dedicated OLAP engines on real-world analytical queries

Quick Start

The first command runs a basic TPC-H benchmark with columnstore mirrors. The second uses a custom connection, and the third uses an S3 storage backend:

benchbox run --platform pg-mooncake --benchmark tpch --scale 0.01

benchbox run --platform pg-mooncake --benchmark tpch --scale 1.0 \
  --platform-option host=mooncake.example.com \
  --platform-option password=secret

benchbox run --platform pg-mooncake --benchmark tpch --scale 1.0 \
  --platform-option storage_mode=s3 \
  --platform-option mooncake_bucket=s3://my-bucket/mooncake-data

Installation

Python Dependencies

pg_mooncake uses the same Python driver as PostgreSQL (psycopg 3):

uv add benchbox --extra postgresql

Server Requirements

pg_mooncake must be installed on the PostgreSQL server. The recommended approach is Docker.

Docker (recommended):

docker run -d --name pg-mooncake \
  -e POSTGRES_PASSWORD=benchbox \
  -p 5432:5432 \
  mooncakelabs/pg_mooncake:latest

psql -h localhost -U postgres -c "CREATE EXTENSION pg_mooncake;"

The psql command verifies that the extension can be created.

See the pg_mooncake GitHub repository for additional installation methods.

How It Works

pg_mooncake transforms PostgreSQL into a hybrid analytical database:

Schema creation - BenchBox creates PostgreSQL heap tables so COPY remains supported
Data loading - PostgreSQL COPY loads benchmark files into the heap tables
Promotion - BenchBox renames the loaded heap tables and exposes mooncake mirrors with the original benchmark table names
Query execution - Queries on the mooncake mirrors are routed through DuckDB’s vectorized engine
Results - Results are returned through the standard PostgreSQL wire protocol

Storage Architecture

PostgreSQL Instance
├── pg_mooncake extension
│   ├── Columnstore Access Method (USING mooncake)
│   ├── pg_moonlink (WAL-based ingestion)
│   └── DuckDB execution engine
└── Storage Backend
    ├── Local disk (default) → Parquet files
    └── Object storage (S3/GCS) → Parquet files with Iceberg metadata

Configuration Options

Option	Default	Description
`host`	`localhost`	PostgreSQL server hostname
`port`	`5432`	PostgreSQL server port
`database`	auto-generated	Database name
`username`	`postgres`	PostgreSQL username
`password`	(none)	PostgreSQL password
`schema`	`public`	PostgreSQL schema name
`storage_mode`	`local`	Storage backend: `local` (disk) or `s3` (object storage)
`mooncake_bucket`	(none)	S3/GCS bucket URL (required when `storage_mode=s3`)

S3 Storage Configuration

Set the S3 bucket through an environment variable, or pass it as a platform option:

export MOONCAKE_S3_BUCKET=s3://my-bucket/mooncake-data

benchbox run --platform pg-mooncake --benchmark tpch \
  --platform-option storage_mode=s3 \
  --platform-option mooncake_bucket=s3://my-bucket/data

S3 mode requires standard AWS credentials (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY).

Comparison with Other Platforms

Aspect	pg_mooncake	pg_duckdb	Native DuckDB	PostgreSQL
Storage	Columnstore (Parquet)	PostgreSQL heap	DuckDB columnar	PostgreSQL heap
Execution	DuckDB vectorized	DuckDB vectorized	DuckDB vectorized	PostgreSQL row-based
Compression	5-20x (columnar)	No (heap)	Yes (columnar)	No (heap)
Load path	COPY into heap, then mooncake mirror promotion	COPY into heap	Native load	COPY into heap
Workload	OLAP columnstore	OLAP acceleration	Standalone analytics	General purpose
ClickBench rank	Top 10	Not ranked	Top 5	Not ranked

Known Limitations

Extension conflicts - pg_mooncake cannot coexist with standalone pg_duckdb (shared libduckdb.so). Use separate PostgreSQL instances for each.
Direct COPY limitation - pg_mooncake 0.2.0 does not support COPY directly into USING mooncake tables; BenchBox uses heap-load plus mirror promotion.
Data type support - Not all PostgreSQL data types are supported on columnstore tables. The extension is actively evolving.
No constraints - Columnstore tables don’t support PRIMARY KEY, FOREIGN KEY, or other constraints.
No indexes - B-tree and other indexes are not applicable to columnstore tables.
Server installation required - pg_mooncake must be pre-installed on the PostgreSQL server; Docker is the recommended setup method.

Recommended Benchmarks

TPC-H - Standard OLAP benchmark; demonstrates columnstore compression and DuckDB execution benefits
ClickBench - Real-world analytical queries; pg_mooncake is a top-10 performer
TPC-DS - Complex OLAP; note some data types may not be fully supported yet