docker pull mooncakelabs/pg_mooncake
docker run --name mooncake-demo -e POSTGRES_HOST_AUTH_METHOD=trust -p 5432:5432 -d mooncakelabs/pg_mooncake
docker run -it --rm --link mooncake-demo:postgres mooncakelabs/pg_mooncake psql -h postgres -U postgres

git submodule update --init --recursive
make release -j$(nproc)
make install

CREATE EXTENSION pg_mooncake;

CREATE EXTENSION pg_mooncake;

CREATE TABLE user_activity (
    user_id BIGINT,
    activity_type TEXT,
    activity_timestamp TIMESTAMP,
    duration INT
) USING columnstore;

INSERT INTO user_activity VALUES
    (1, 'login', '2024-01-01 08:00:00', 120),
    (2, 'page_view', '2024-01-01 08:05:00', 30),
    (3, 'logout', '2024-01-01 08:30:00', 60),
    (4, 'error', '2024-01-01 08:13:00', 60);

UPDATE user_activity SET duration = 180 WHERE user_id = 1;
DELETE FROM user_activity WHERE user_id = 2;

BEGIN;
INSERT INTO user_activity VALUES
    (5, 'login', '2024-01-02 10:00:00', 200),
    (6, 'login', '2024-01-02 10:30:00', 90);
ROLLBACK;
SELECT * FROM user_activity;

-- Between two columnstore tables
SELECT * FROM user_activity ua
JOIN user_activity ua2 ON ua.user_id = ua2.user_id;

-- Between a regular PostgreSQL table and a columnstore table
SELECT * FROM users JOIN user_activity ON users.user_id = user_activity.user_id;

SELECT mooncake.create_secret(
    '<name>', 'S3', '<key_id>', '<secret>',
    '{"REGION": "<s3-region>", "SCOPE": "s3://<bucket>"}'
);

SELECT mooncake.create_secret(
    '<name>', 'S3', '<key_id>', '<secret>',
    '{"ENDPOINT": "s3express-use1-az6.us-east-1.amazonaws.com", "REGION": "us-east-1"}'
);

SELECT mooncake.create_secret(
    '<name>', 'S3', '<key_id>', '<secret>',
    '{"ENDPOINT": "<ACCOUNT_ID>.r2.cloudflarestorage.com"}'
);

ALTER DATABASE <database> SET mooncake.default_bucket = 's3://<bucket>';
SHOW mooncake.default_bucket;

SET neon.allow_unstable_extensions = 'true';
CREATE EXTENSION pg_mooncake;

CREATE TABLE users_columnstore (
    id INT,
    name TEXT,
    created_at TIMESTAMP
) USING columnstore;

-- Or via CREATE TABLE ... AS SELECT
CREATE TABLE users_columnstore USING columnstore AS
    SELECT * FROM users_rowstore;

DROP TABLE users_columnstore;

SET default_table_access_method = 'columnstore';

SELECT * FROM mooncake.columnstore_tables;

    table_name     |                                          path
-------------------+----------------------------------------------------------------------------------------
 users_columnstore | /usr/local/pgsql/data/mooncake_local_tables/mooncake_postgres_users_columnstore_16446/
 events            | s3://my-bucket/mooncake_postgres_events_16459/

SELECT * FROM mooncake.data_files;

import polars as pl

local_path = "/usr/local/pgsql/data/mooncake_local_tables/mooncake_postgres_users_columnstore_16446/"
df = pl.scan_delta(local_path)
print(df.collect())

-- Default cloud storage bucket
ALTER DATABASE <database_name> SET mooncake.default_bucket = 's3://my-bucket';

-- Memory allocated to DuckDB (v0.1.1+, default 4GB)
ALTER DATABASE <database_name> SET mooncake.maximum_memory = '6GB';
CALL mooncake.reset_duckdb();

-- Number of threads for DuckDB (v0.1.1+, default -1 = all cores)
ALTER DATABASE <database_name> SET mooncake.maximum_threads = -1;

-- Local write cache (default true)
ALTER DATABASE <database_name> SET mooncake.enable_local_cache = false;

-- Memory cache for Parquet metadata (default true)
ALTER DATABASE <database_name> SET mooncake.enable_memory_metadata_cache = false;

INSERT INTO users_columnstore
SELECT * FROM users_rowstore;

-- Using COPY
COPY users_columnstore FROM 's3://bucket/file.parquet';
COPY users_columnstore FROM 's3://bucket/folder/*.parquet';
COPY users_columnstore FROM 's3://bucket/data.csv' WITH (FORMAT 'csv', HEADER true);

-- Using INSERT with mooncake.read_parquet
INSERT INTO users_columnstore
SELECT * FROM mooncake.read_parquet('s3://bucket/file.parquet') AS (
    id INT,
    name TEXT,
    created_at TIMESTAMP
);

-- Iceberg
INSERT INTO users_columnstore
SELECT * FROM iceberg_scan('s3://bucket/table') AS (id INT, name TEXT, created_at TIMESTAMP);

-- Delta Lake
INSERT INTO users_columnstore
SELECT * FROM delta_scan('s3://bucket/table') AS (id INT, name TEXT, created_at TIMESTAMP);

-- HuggingFace
COPY users_columnstore FROM 'hf://datasets/OpenLeecher/lmsys_chat_1m_clean/data/train-00000-of-00004.parquet';

-- Parquet
SELECT user_id, event_type, created_at
FROM mooncake.read_parquet('s3://bucket/events.parquet') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at > '2023-01-01';

-- CSV
SELECT r['user_id']::INT, r['event_type'], r['created_at']::TIMESTAMP
FROM mooncake.read_csv('s3://bucket/events.csv', AUTO_DETECT=TRUE) r
WHERE r['created_at']::TIMESTAMP > '2023-01-01';

-- JSON
SELECT
    json_extract_path_text(data, 'user_id')::INT AS user_id,
    json_extract_path_text(data, 'event_type') AS event_type
FROM mooncake.read_json('s3://bucket/events.json') AS (data JSONB);

SELECT * FROM iceberg_scan('s3://bucket/iceberg_table') AS (
    user_id INT, event_type TEXT, created_at TIMESTAMP
);

SELECT * FROM delta_scan('s3://bucket/delta_table') AS (
    user_id INT, event_type TEXT, created_at TIMESTAMP
);

SELECT
    region,
    date_trunc('quarter', sale_date) AS quarter,
    SUM(amount) AS total_sales,
    COUNT(*) AS num_transactions,
    AVG(amount) AS avg_sale
FROM sales_columnstore
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY region, quarter
ORDER BY region, quarter;

CREATE OR REPLACE FUNCTION time_bucket(interval, timestamp)
RETURNS timestamp AS $$
BEGIN
    RAISE EXCEPTION 'this is a placeholder for columnstore';
END;
$$ LANGUAGE plpgsql;

-- Create columnstore archive table
CREATE TABLE orders_archive (
    order_id INT,
    customer_id INT,
    order_date TIMESTAMP,
    amount NUMERIC(10, 2)
) USING columnstore;

-- Move old data to archive
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '1 year';

-- Delete from source and reclaim space
DELETE FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '1 year';
VACUUM FULL orders;

-- 1. Staging table
CREATE TABLE orders_staging (
    order_id INT PRIMARY KEY,
    customer_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    order_date TIMESTAMP
);

-- 2. Columnstore table
CREATE TABLE orders_analytics (
    order_id INT,
    customer_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    order_date TIMESTAMP,
    last_updated TIMESTAMP
) USING columnstore;

-- 3. Periodic batch sync function scheduled via pg_cron
CREATE OR REPLACE FUNCTION sync_to_columnstore() RETURNS void AS $$
BEGIN
    INSERT INTO orders_analytics
    SELECT s.*, NOW() FROM orders_staging s;
    TRUNCATE orders_staging;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE daily_sales_summary (
    date DATE,
    product_id INT,
    category_id INT,
    region_id INT,
    total_quantity INT,
    total_revenue DECIMAL(18, 2)
) USING columnstore;

{% macro on_run_start() %}
  {% set query %}
    SET default_table_access_method = 'columnstore';
  {% endset %}
  {% do run_query(query) %}
{% endmacro %}