storage_engine

Overview

release 1.0.7; SQL v1.0

Version

Build

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

pig build pkg storage_engine         # build RPM / DEB packages

Install

You can install storage_engine 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 storage_engine;          # Install for current active PG version

pig ext install -y storage_engine -v 18  # PG 18
pig ext install -y storage_engine -v 17  # PG 17
pig ext install -y storage_engine -v 16  # PG 16
pig ext install -y storage_engine -v 15  # PG 15
pig ext install -y storage_engine -v 14  # PG 14

dnf install -y storage_engine_18       # PG 18
dnf install -y storage_engine_17       # PG 17
dnf install -y storage_engine_16       # PG 16
dnf install -y storage_engine_15       # PG 15
dnf install -y storage_engine_14       # PG 14

apt install -y postgresql-18-storage-engine   # PG 18
apt install -y postgresql-17-storage-engine   # PG 17
apt install -y postgresql-16-storage-engine   # PG 16
apt install -y postgresql-15-storage-engine   # PG 15
apt install -y postgresql-14-storage-engine   # PG 14

Preload:

shared_preload_libraries = 'storage_engine';

Create Extension:

CREATE EXTENSION storage_engine;

Usage

Sources: README, release 1.0.7, META.json

storage_engine provides two PostgreSQL table access methods in the engine schema:

• colcompress for column-oriented compressed storage with vectorized execution, min/max pruning, and parallel scans.
• rowcompress for row-batch compression with parallel scans.

CREATE EXTENSION storage_engine;

Quick Start

Create tables using either access method:

CREATE TABLE events (
  ts timestamptz NOT NULL,
  user_id bigint,
  event_type text,
  value float8
) USING colcompress;

CREATE TABLE logs (
  id bigserial,
  logged_at timestamptz NOT NULL,
  message text
) USING rowcompress;

Main Tuning Knobs

Session-level GUCs documented upstream include:

• storage_engine.enable_parallel_execution
• storage_engine.enable_vectorization
• storage_engine.enable_column_cache
• storage_engine.enable_columnar_index_scan
• storage_engine.enable_dml
• storage_engine.stripe_row_limit
• storage_engine.chunk_group_row_limit
• storage_engine.compression_level

The README says these GUCs become visible once the library is loaded; add storage_engine to shared_preload_libraries if you want them available immediately in every session.

Useful Management Functions

For colcompress tables:

SELECT engine.alter_colcompress_table_set(
  'events'::regclass,
  orderby => 'ts ASC, user_id ASC',
  compression => 'zstd',
  compression_level => 9
);

SELECT engine.colcompress_merge('events');
SELECT engine.colcompress_repack('events');

For rowcompress tables:

SELECT engine.alter_rowcompress_table_set(
  'logs'::regclass,
  batch_size => 10000,
  compression => 'zstd',
  compression_level => 5
);

SELECT engine.rowcompress_repack('logs');

When to Use Which AM

• Use colcompress for analytical scans, aggregates, and range predicates where projection, vectorization, and stripe/chunk pruning pay off.
• Use rowcompress for append-heavy logs or wide rows that are usually fetched together, where compression matters more than column projection.
• For point lookups on colcompress, upstream recommends enabling storage_engine.enable_columnar_index_scan or per-table index_scan.

Caveats

• colcompress and rowcompress do not support foreign keys or AFTER ROW triggers.
• VACUUM FULL and CREATE UNLOGGED TABLE ... USING colcompress are not supported; upstream recommends the extension’s repack functions instead.
• On colcompress, combining orderby with B-tree indexes can disable the sort-on-write path; run engine.colcompress_merge() after loading data when global ordering matters.