v0.1 Hello World (USING columnstore)

Basic operations on v0.1 USING columnstore tables: CREATE TABLE, INSERT, UPDATE, DELETE, transactions, and joins.

Legacy pg_mooncake v0.1 Archive (Delta Lake Engine)

This page documents pg_mooncake v0.1, which used direct CREATE TABLE ... USING columnstore DDL and Delta Lake storage. Starting in v0.2 and PGSTY’s v0.3.0 release, pg_mooncake uses moonlink real-time columnstore mirrors (CALL mooncake.create_table + USING mooncake) and Apache Iceberg v2. For current documentation, see Introduction and v0.1 vs v0.2 & v0.3.

Create the Extension

CREATE EXTENSION pg_mooncake;

Create a `USING columnstore` Table (`v0.1`)

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

Inserts, Updates, and 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 two columnstore tables
SELECT * FROM user_activity ua
JOIN user_activity ua2 ON ua.user_id = ua2.user_id;

-- Between a regular PostgreSQL table and a columnstore table
SELECT * FROM users JOIN user_activity ON users.user_id = user_activity.user_id;