Reference

Management Functions

colcompress

-- Full options signature
SELECT engine.alter_colcompress_table_set(
    'mytable'::regclass,
    stripe_row_limit      => 150000,   -- rows per stripe (1000 – 100000000)
    chunk_group_row_limit => 10000,    -- rows per chunk group (1000 – 100000000)
    compression           => 'zstd',   -- none | pglz | lz4 | zstd | deflate | zxc
    compression_level     => 9,        -- 1–19 for zstd
    orderby               => 'ts ASC', -- sort key for colcompress_merge
    index_scan            => false     -- true = enable index scan path for this table
);

-- Reset individual options
SELECT engine.alter_colcompress_table_reset(
    'mytable'::regclass,
    stripe_row_limit      => true,
    compression           => true,
    compression_level     => true,
    orderby               => true,
    index_scan            => true
);

-- Compact and globally sort
SELECT engine.colcompress_merge('mytable');

-- Incremental maintenance (merge only tables above dirty threshold)
SELECT engine.colcompress_merge_incremental('mytable');

rowcompress

SELECT engine.alter_rowcompress_table_set(
    'mylogs'::regclass,
    batch_size        => 10000,   -- rows per batch (100 – 100000000)
    compression       => 'zstd',
    compression_level => 5
);

SELECT engine.alter_rowcompress_table_reset(
    'mylogs'::regclass,
    compression => true,
    batch_size  => true
);

SELECT engine.rowcompress_repack('mylogs');

Storage Maintenance

-- Dry run: show what would be done
CALL engine.storage_maintenance_auto(dry_run => true);

-- Execute maintenance for colcompress tables only
CALL engine.storage_maintenance_auto(am_filter => 'colcompress', p_verbose => true);

-- Via pg_cron (runs every 5 minutes)
SELECT cron.schedule('storage-maintenance', '*/5 * * * *',
    $$CALL engine.storage_maintenance_auto()$$);

Catalog Views

colcompress_options

Per-table options for all colcompress tables.

SELECT * FROM engine.colcompress_options;

colcompress_stripes

Stripe-level metadata per table.

SELECT table_name, stripe_num, row_count, file_size_bytes, pruning_valid
FROM engine.colcompress_stripes
WHERE table_name = 'events'
ORDER BY stripe_num;

rowcompress_options

Per-table options for all rowcompress tables.

SELECT * FROM engine.rowcompress_options;

rowcompress_batches

Batch-level metadata for all rowcompress tables.

SELECT table_name, batch_num, row_count, deleted_count, pruning_valid
FROM engine.rowcompress_batches
WHERE table_name = 'logs'
ORDER BY batch_num;

storage_health

Unified health view for all colcompress and rowcompress tables.

SELECT
    table_name,
    am_name,
    total_units,
    dirty_units,
    tombstone_rows,
    live_rows,
    effective_pruning_ratio_est,
    recommended_action
FROM engine.storage_health
ORDER BY table_name;

Configuration GUCs

All parameters can be set in postgresql.conf (global) or SET (per-session).

Storage

Execution

Custom Scan / Pushdown

Cache

Debug

Auto-Maintenance Background Worker

Known Limitations

No AFTER ROW triggers / no foreign keys

colcompress and rowcompress do not support AFTER ROW triggers or foreign keys. This means pg_repack cannot be used. Use engine.colcompress_repack() instead:

SELECT engine.colcompress_repack('mytable'::regclass);

colcompress_repack acquires AccessExclusiveLock for the duration — schedule during a maintenance window for large tables.

AFTER STATEMENT triggers are supported

Only row-level (FOR EACH ROW) AFTER triggers are blocked. Statement-level (FOR EACH STATEMENT) AFTER triggers work fine.

Stripe pruning disabled in parallel mode

Each parallel worker reads its assigned stripes independently without a global pruning pass. Stripe pruning only applies in the sequential (non-parallel) scan path.

B-tree index on orderby column disables pruning

If a B-tree index exists on the orderby column, the planner prefers the index path over the sequential scan, bypassing stripe pruning. For analytical tables, avoid B-tree indexes on the sort key. Use SET storage_engine.enable_engine_index_scan = on at session level for occasional point lookups instead.

INSERT … SELECT with pre-existing indexes

Index TIDs may be corrupted when tables are populated via INSERT INTO … SELECT with pre-existing indexes. Workaround:

REINDEX TABLE CONCURRENTLY mytable;

engine.uint8 Type Reference