pg_mooncake

pig build pkg pg_mooncake         # build RPM / DEB packages

pig repo add pgsql -u          # Add repo and update cache

shared_preload_libraries = 'pg_duckdb, pg_mooncake';

CREATE EXTENSION pg_mooncake CASCADE;  -- requires: pg_duckdb

pig repo set
pig install pg_duckdb pg_mooncake

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

-- create the extension alone with pg_duckdb
CREATE EXTENSION pg_mooncake CASCADE;

-- Next, create a regular Postgres table trades:
CREATE TABLE trades(
  id bigint PRIMARY KEY,
  symbol text,
  time timestamp,
  price real
);

-- Then, create a columnstore mirror trades_iceberg that stays in sync with trades:
CALL mooncake.create_table('trades_iceberg', 'trades');

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

-- Finally, query it with duckdb
EXPLAIN
    SELECT avg(price) FROM trades_iceberg WHERE symbol = 'AMZN';

                         QUERY PLAN
------------------------------------------------------------
 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │    Aggregates: avg(#0)    │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │   CAST(price AS DOUBLE)   │
 │                           │
 │          ~0 rows          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       MOONCAKE_SCAN       │
 │    ────────────────────   │
 │   Table: trades_iceberg   │
 │     Projections: price    │
 │                           │
 │          Filters:         │
 │       symbol='AMZN'       │
 │                           │
 │          ~0 rows          │
 └───────────────────────────┘