citus_columnar

Overview

conflict with hydra columnar, no pg18

Version

Build

You can build the RPM / DEB packages for citus using pig build:

pig build pkg citus         # build RPM / DEB packages

Install

You can install citus directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install citus;          # Install for current active PG version

pig ext install -y citus -v 18  # PG 18
pig ext install -y citus -v 17  # PG 17
pig ext install -y citus -v 16  # PG 16

dnf install -y citus_18       # PG 18
dnf install -y citus_17       # PG 17
dnf install -y citus_16       # PG 16

apt install -y postgresql-18-citus   # PG 18
apt install -y postgresql-17-citus   # PG 17
apt install -y postgresql-16-citus   # PG 16

Create Extension:

CREATE EXTENSION citus_columnar;

Usage

citus_columnar: Columnar storage access method for PostgreSQL

Citus Columnar provides a columnar storage engine for PostgreSQL. It stores data in a column-oriented format with automatic compression, making it ideal for analytical workloads on append-only data where queries typically read a subset of columns.

Key Documentation:

• Columnar Storage
• Columnar Compression

Creating Columnar Tables

Use the USING columnar clause when creating a table:

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

Compression Options

Configure compression per table. Supported methods: zstd (default), lz4, pglz, none.

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

Chunk Group and Stripe Settings

Columnar stores data in stripes, each containing chunk groups. Tuning these affects both compression ratio and query performance.

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)
);

When to Use Columnar

Columnar storage works best for:

• Analytics and reporting on wide tables where queries read few columns
• Append-only workloads (e.g., logs, events, time-series archives)
• Large fact tables scanned in bulk with aggregations
• Cold data archival where high compression is valuable

Limitations

• No UPDATE or DELETE: columnar tables are append-only
• No indexes: sequential/columnar scans only
• No TOAST: large values stored inline
• No logical replication as a publisher
• No tid scans

Column Projection and Chunk Group Skipping

Columnar automatically reads only the columns referenced in a query (column projection) and skips chunk groups whose min/max metadata does not match the query predicates:

-- 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;

Monitoring Columnar Storage

Inspect stripe and chunk metadata:

-- 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;

Converting Between Heap and Columnar

Convert an existing heap table to columnar:

-- 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');

Convert columnar back to heap:

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

Using with Partitioning

Combine columnar with partitioning to keep recent data in heap (for updates/indexes) and archive older partitions as columnar:

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;