Skip to main content
Version: 0.1

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;