OLAP Analytics (ivy_mooncake)

1. Overview

ivy_mooncake is the IvorySQL distribution of pg_mooncake, a Postgres extension that creates a columnstore mirror of your Postgres tables in Apache Iceberg, enabling fast analytics (OLAP) queries with sub-second freshness. It gives IvorySQL real-time analytical processing capability directly on top of its transactional tables:

Real-time ingestion powered by moonlink for streaming and batched INSERT/UPDATE/DELETE.
Fast analytics accelerated by DuckDB, ranking top 10 on ClickBench.
Postgres-native, allowing you to query a columnstore table just like a regular Postgres table.
Iceberg-native, making your data readily accessible by other query engines.

2. Documents

3. Installation

3.1. Run with Docker (preview)

A preview image bundling IvorySQL with pg_duckdb and pg_mooncake preloaded is available:

docker run --name ivy_mooncake \
  -e IVORYSQL_PASSWORD=password \
  -p 5432:5432 -p 1521:1521 \
  -v ivy_mooncake_data:/var/lib/ivorysql/data \
  -v ivy_mooncake_warehouse:/tmp/moonlink_iceberg \
  registry.highgo.com/mooncake/ivy_mooncake:0.1

4. Quick Start

Create the pg_mooncake extension
```
CREATE EXTENSION pg_mooncake CASCADE;
```

Create a regular Postgres table trades

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

Create a columnstore mirror trades_iceberg that stays in sync with trades
```
CALL mooncake.create_table('trades_iceberg', 'trades');
```

Insert some data into trades

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

Query trades_iceberg; it reflects the up-to-date state of trades
```
SELECT avg(price) FROM trades_iceberg WHERE symbol = 'AMZN';
```