Table and Column Compression

How to use compression in Firebolt to save storage space and improve query performance with practical examples

Overview

You can specify custom compression algorithms at both the table level and column level when creating tables. Compression helps reduce storage costs and improve query performance by decreasing disk usage and I/O operations.

Firebolt compression supports:

• Specialized codecs optimized for specific data types
• Chaining multiple compression algorithms for optimal results
• Fine-tuned compression parameters for your workload

For complete technical specifications, parameter details, and advanced configuration examples, see the Compression Codec Reference.

Supported compression codecs

Firebolt supports two categories of compression codecs:

General-purpose codecs

These codecs can compress any data type and work independently:

• lz4 – Default option; extremely fast compression and decompression, ideal for high-throughput workloads
• lz4hc – High-compression variant of LZ4; better compression ratios with slower compression but fast decompression
• zstd(level) – Modern algorithm with excellent compression ratios; configurable compression (levels 1-22, default 1)
• default – Uses the system default compression (equivalent to lz4)

Specialized codecs

These preprocessing codecs do not compress data themselves, but allow you to achieve better compression ratios in combination with general-purpose codecs:

• delta – Stores differences between consecutive values; excellent for monotonic numeric data

Compression syntax

Firebolt uses a flexible parentheses-based syntax that supports both simple codec specification and advanced chaining:

Basic syntax

-- Single codec without parameters:
COMPRESSION lz4

-- Single codec with parameters:
COMPRESSION zstd(5)

-- Chain two codecs:
COMPRESSION (delta, zstd(3))

Compression chaining

You can apply multiple compression codecs in sequence using chaining. You can chain up to three codecs, where specialized codecs preprocess data before general-purpose codecs compress it:

-- Two-stage chain: preprocessing + compression
COMPRESSION (delta, lz4)
COMPRESSION (delta, zstd(5))

Chaining rules:

• You can chain up to three codecs
• Only one general-purpose codec is allowed in each chain
• The general-purpose codec must be last in the chain
• All codecs must be compatible with the column’s data type

Codec selection guide

For detailed technical specifications, parameter ranges, and performance benchmarks, see the Compression Codec Reference.

When to use general-purpose codecs

Codec	Best for	Compression speed	Decompression speed	Typical ratio
lz4	High-throughput ingestion, frequently accessed data	Very fast	Very fast	Good
lz4hc	Balanced performance, storage-sensitive applications	Moderate	Very fast	Better
zstd(3)	General analytics, best overall balance	Fast	Fast	Better
zstd(15)	Archival data, maximum compression	Slow	Fast	Excellent

When to use specialized codecs

Codec	Ideal data patterns	Compatible types
delta	Sequential IDs, timestamps, monotonic counters	Integers, dates, timestamps

Table-level compression

Set default compression for all columns using the WITH clause:

-- Simple table-level compression
CREATE TABLE metrics (
    id INTEGER,
    value DOUBLE,
    timestamp TIMESTAMPTZ
) 
WITH (COMPRESSION zstd(5));

-- Using advanced codecs
CREATE TABLE time_series (
    sensor_id INTEGER,
    reading DOUBLE, 
    recorded_at TIMESTAMPTZ
)
WITH (COMPRESSION lz4hc);

Column-level compression

Override table defaults with column-specific compression:

CREATE TABLE sensor_data (
    -- Use table default compression
    id INTEGER,
    
    -- Optimize sequential sensor IDs with delta preprocessing
    sensor_id INTEGER NOT NULL COMPRESSION (delta, lz4),
    
    -- Use high compression for text data
    description TEXT COMPRESSION zstd(15)
)
WITH (COMPRESSION (lz4));

Advanced compression examples

Mixed workload optimization

CREATE TABLE analytics_events (
    -- Fast access primary key
    event_id BIGINT COMPRESSION (lz4),
    
    -- Sequential user IDs with delta preprocessing
    user_id INTEGER NOT NULL COMPRESSION (delta, lz4),

    -- High compression for JSON data
    event_data TEXT COMPRESSION (zstd(15))
)
WITH (COMPRESSION lz4);

Data type compatibility

Compatible data types by codec

Codec	Integers	Floats	Dates/Timestamps	Strings	Other
lz4, lz4hc, zstd	✅	✅	✅	✅	✅
delta	✅	❌	✅	❌	❌

The delta codec supports only NOT NULL columns.

Monitoring compression effectiveness

Use the information schema to monitor compression results:

-- View compression settings by column
SELECT 
    column_name,
    compression
FROM information_schema.columns 
WHERE table_name = 'sensor_data'
ORDER BY ordinal_position;

Example output:

column_name    | compression
---------------|---------------------------  
id             | lz4
sensor_id      | delta, lz4
temperature    | zstd(3)
description    | zstd(15)
recorded_at    | lz4

-- Compare compression ratios across tables
SELECT 
    table_name,
    compressed_bytes,
    uncompressed_bytes,
    ROUND((uncompressed_bytes::FLOAT / compressed_bytes), 2) as compression_ratio
FROM information_schema.tables 
WHERE table_schema = 'public'
ORDER BY compression_ratio DESC;

Performance considerations

Compression vs. query performance trade-offs

• High-throughput ingestion: Use lz4 for fastest writes
• Balanced workloads: Use lz4hc for good compression with reasonable speed
• Time series data: Chain specialized codecs like delta with general-purpose codecs

Codec parameter guidelines

Codec	Parameter	Recommended range	Notes
zstd	level	1-5 for hot data, 10-15 for cold data	Levels above 15 are rarely beneficial

Best practices

1. Start simple: Begin with lz4 and measure performance
2. Profile your data: Use specialized codecs for time series and sequential data
3. Test compression ratios: Compare storage savings with sample data before production
4. Consider query patterns: Balance compression ratio against decompression speed for frequently accessed data
5. Monitor resource usage: Higher compression levels increase CPU usage during ingestion

Backward compatibility

Legacy syntax support

For backward compatibility, Firebolt continues to support the older compression syntax for lz4 and zstd codecs only:

-- Legacy syntax (still supported for lz4 and zstd only)
CREATE TABLE legacy_table (
    id INTEGER COMPRESSION zstd COMPRESSION_LEVEL 5
);

Limitations of legacy syntax:

• Only supports lz4 and zstd codecs
• Cannot use specialized codecs (delta)
• Cannot chain multiple codecs
• No access to new codec parameters

Recommended migration:

-- Migrate to new syntax for full feature support
CREATE TABLE modern_table (
    id INTEGER COMPRESSION zstd(5)
);

---

See also

For detailed technical documentation on all compression codecs, including parameters, performance characteristics, and troubleshooting guidance, see the Compression Codec Reference.