citus_columnar

Extensions

citus_columnar

citus : Citus columnar storage engine

Overview

ID	Extension	Package	Version	Category	License	Language
2401	citus_columnar	citus	`14.0.0`	OLAP	AGPL-3.0	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
Schemas	`pg_catalog`
See Also	columnar pg_parquet timescaledb pg_analytics pg_mooncake pg_duckdb duckdb_fdw orioledb
Siblings	citus

conflict with hydra columnar, no pg18

Packages

Type	Repo	Version	PG Major Compatibility	Package Pattern	Dependencies
EXT	PIGSTY	`14.0.0`	18 17 16 15 14	`citus`	-
RPM	PIGSTY	`14.0.0`	18 17 16 15 14	`citus_$v`	-
DEB	PIGSTY	`14.0.0`	18 17 16 15 14	`postgresql-$v-citus`	-

Linux / PG	PG18	PG17	PG16	PG15	PG14
el8.x86_64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
el8.aarch64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
el9.x86_64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
el9.aarch64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
el10.x86_64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	MISS
el10.aarch64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	MISS
d12.x86_64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
d12.aarch64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
d13.x86_64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	MISS
d13.aarch64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	MISS
u22.x86_64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
u22.aarch64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
u24.x86_64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0
u24.aarch64	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 14.0.0	PIGSTY 13.2.0	PIGSTY 13.0.0

Source

Repository

github.com/citusdata/citus

Source Tarball

citus-14.0.0.tar.gz

pig build pkg citus;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

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 this extension with:

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:

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;

Last updated on 2026-03-24

citus columnar