citus_columnar

pig build pkg citus;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install citus;		# install via package name, for the active PG version
pig install citus_columnar;		# install by extension name, for the current active PG version

pig install citus_columnar -v 18;   # install for PG 18
pig install citus_columnar -v 17;   # install for PG 17
pig install citus_columnar -v 16;   # install for PG 16

CREATE EXTENSION citus_columnar;

CREATE TABLE events (
    event_id    BIGINT,
    event_time  TIMESTAMPTZ,
    event_type  TEXT,
    user_id     INT,
    payload     JSONB
) USING columnar;

ALTER TABLE events SET (
    columnar.compression = zstd,
    columnar.compression_level = 3
);

ALTER TABLE events SET (
    columnar.stripe_row_limit = 150000,    -- max rows per stripe (default 150000)
    columnar.chunk_group_row_limit = 10000 -- rows per chunk group (default 10000)
);

-- Only reads event_type and event_time columns; skips irrelevant chunks
SELECT event_type, count(*)
FROM events
WHERE event_time > '2025-01-01'
GROUP BY event_type;

-- View stripes for a columnar table
SELECT * FROM columnar.stripe WHERE relation = 'events'::regclass;

-- View chunk group details
SELECT * FROM columnar.chunk_group WHERE relation = 'events'::regclass;

-- Check storage size and compression ratio
SELECT pg_size_pretty(pg_total_relation_size('events')) AS total_size;

-- Create a columnar copy
CREATE TABLE events_columnar (LIKE events) USING columnar;
INSERT INTO events_columnar SELECT * FROM events;

-- Or use ALTER TABLE (Citus 11+)
SELECT alter_table_set_access_method('events', 'columnar');

SELECT alter_table_set_access_method('events', 'heap');

CREATE TABLE events (
    event_time TIMESTAMPTZ,
    data       JSONB
) PARTITION BY RANGE (event_time);

-- Recent data: heap (supports indexes and updates)
CREATE TABLE events_current PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Archived data: columnar (compressed, read-optimized)
CREATE TABLE events_archive PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    USING columnar;