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;