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)