pg_mooncake Documentation

pg_mooncake is a Postgres extension that creates a columnstore mirror of your Postgres tables in Apache Iceberg. It enables fast analytics queries with sub-second freshness by combining:

• Real-time ingestion: Powered by moonlink for streaming and batched INSERT/UPDATE/DELETE.
• Fast analytics: Accelerated by DuckDB.
• Postgres-native interface: Query columnstore tables just like regular Postgres tables.
• Iceberg-native storage: Data is stored in Iceberg format, making it accessible to other query engines.

To build from source, ensure you have [Rust], [pgrx], and [DuckDB build tools] installed.

1. Clone the repository with submodules:

git clone --recurse-submodules https://github.com/Mooncake-Labs/pg_mooncake.git

2. Build and install (example for Postgres 18):

cargo pgrx init --pg18=$(which pg_config)
make pg_duckdb
make install PG_VERSION=pg18

3. Configure postgresql.conf to load the libraries and enable logical replication:

duckdb.allow_community_extensions = true
shared_preload_libraries = 'pg_duckdb,pg_mooncake'
wal_level = logical

git clone --recurse-submodules https://github.com/Mooncake-Labs/pg_mooncake.git

# For Postgres 18
cargo pgrx init --pg18=$(which pg_config)
make pg_duckdb
make install PG_VERSION=pg18

For a quick start, use the official Docker image which comes with pg_mooncake preinstalled.

1. Run the container:

docker run --name mooncake --rm -e POSTGRES_PASSWORD=password mooncakelabs/pg_mooncake

2. Connect to the instance using psql as the postgres user:

docker exec -it mooncake psql -U postgres

Follow these steps to set up a real-time analytics mirror of a Postgres table using pg_mooncake.

1. Create the extension:

CREATE EXTENSION pg_mooncake CASCADE;

2. Create a source table:

CREATE TABLE trades(
  id bigint PRIMARY KEY,
  symbol text,
  time timestamp,
  price real
);

3. Create the Iceberg columnstore mirror: Use mooncake.create_table to create a mirror that stays in sync with the source table:

CALL mooncake.create_table('trades_iceberg', 'trades');

4. Verify synchronization: Insert data into the source table and query the mirror:

INSERT INTO trades VALUES
  (1,  'AMD', '2024-06-05 10:00:00', 119),
  (2, 'AMZN', '2024-06-05 10:05:00', 207),
  (3, 'AAPL', '2024-06-05 10:10:00', 203),
  (4, 'AMZN', '2024-06-05 10:15:00', 210);

SELECT avg(price) FROM trades_iceberg WHERE symbol = 'AMZN';

To use Mooncake as a table storage engine, you must first define the access method using the mooncake_am_handler function. This is typically done during the extension installation or setup phase. The following SQL commands define the handler and the mooncake access method type.

CREATE FUNCTION mooncake_am_handler(internal) RETURNS table_am_handler LANGUAGE c AS 'MODULE_PATHNAME', '@FUNCTION_NAME@';
CREATE ACCESS METHOD mooncake TYPE TABLE HANDLER mooncake_am_handler;

The mooncake access method (AM) is registered within PostgreSQL via a handler function. This allows PostgreSQL to use Mooncake's storage engine for table operations. The registration is performed by calling RegisterDuckdbTableAm with the name mooncake and the MOONCAKE_AM routine structure.

CREATE FUNCTION mooncake_am_handler(internal) RETURNS table_am_handler LANGUAGE c AS 'MODULE_PATHNAME', '@FUNCTION_NAME@';
CREATE ACCESS METHOD mooncake TYPE TABLE HANDLER mooncake_am_handler;