pg_mooncake

pig build get pg_mooncake; # get pg_mooncake source code
pig build dep pg_mooncake; # install build dependencies
pig build pkg pg_mooncake; # build extension rpm or deb
pig build ext pg_mooncake; # build extension rpms

pig repo add pgsql -u   # add PGDG + Pigsty repo and update cache (leave existing repos)

pig ext install pg_mooncake; # install by extension name, for the current active PG version
pig ext install pg_mooncake; # install via package alias, for the active PG version
pig ext install pg_mooncake -v 18;   # install for PG 18
pig ext install pg_mooncake -v 17;   # install for PG 17
pig ext install pg_mooncake -v 16;   # install for PG 16
pig ext install pg_mooncake -v 15;   # install for PG 15
pig ext install pg_mooncake -v 14;   # install for PG 14

CREATE EXTENSION pg_mooncake;

pig repo add pigsty -u
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          │
 └───────────────────────────┘