Hello World

This section will cover the basic operations on columnstore tables.

Create the extension

Ensure the pg_mooncake extension is created:

CREATE EXTENSION pg_mooncake;

Create Columnstore Table

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;

Inserts, Updates, Deletes

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;

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;

Joins

Between columnstore tables:

SELECT * FROM user_activity ua
JOIN user_activity ua2 ON ua.user_id = ua2.user_id;

Between a columnstore table and a regular postgres table:

In this case, we have a regular postgres table, users, and we want to join it with our columnstore table user_activity on user_id:

SELECT * FROM users JOIN user_activity ON users.user_id = user_activity.user_id;