Quick Start

1. Ensure the extension is enabled

CREATE EXTENSION pg_mooncake;

Note: Neon users will first have to run:

SET neon.allow_unstable_extensions='true';

2. Create a columnstore table with the 'USING columnstore' clause

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

This will create a columnstore table locally. You can inspect the directory to find Parquet files and a Delta log folder.

Note: To store data in S3 or R2, set up an object store bucket before creating a columnstore table. Neon Postgres must bring their own bucket before creating a columnstore table.

Features supported on columnstore

1. Inserts

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);
 
SELECT * FROM user_activity;

2. Updates and Deletes

UPDATE user_activity
SET activity_timestamp = '2024-01-01 09:50:00'
WHERE user_id = 3 AND activity_type = 'logout';
 
DELETE FROM user_activity
WHERE user_id = 4 AND activity_type = 'error';
 
SELECT * from user_activity;

3. Transactions

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;

3. ORM support like our friends at Drizzle ORM

4. Joins between rowstore and columnstore

SELECT * FROM users u
JOIN users_columnstore ON u.user_id = a.user_id;

5. Triggers sync rowstore and columnstore tables

CREATE TABLE users(id bigint);
CREATE TABLE users_log(user_id bigint, log_message text, logged_at timestamp) USING columnstore;
CREATE OR REPLACE FUNCTION log_user_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users_log (user_id, log_message, logged_at)
    VALUES (NEW.id, 'New user inserted', CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER user_insert_trigger
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_insert();
 
INSERT into users values(10013),(14141);
 
select * from users_log;
-- user_id |    log_message    |        logged_at        
-- ---------+-------------------+-------------------------
--   10013 | New user inserted | 2024-12-17 22:44:52.458
--   14141 | New user inserted | 2024-12-17 22:44:52.458
-- (2 rows)