By Tiger Data Team
Updated at Jun 4, 2026
PostgreSQL ships with TOAST, which compresses individual large values automatically - and most teams stop there. For workloads storing millions of rows of time-series, IoT, or analytical data, TOAST's 20-40% storage reduction on eligible values is rarely enough, because TOAST never fires on the rows where it's needed most: narrow rows with small scalar values.
This page maps every PostgreSQL compression option, when to use each, and what real-world storage reduction to expect. It covers the full range from built-in TOAST to filesystem-level compression to TimescaleDB's columnar engine via Hypercore. Tiger Data builds TimescaleDB, so that option gets the most depth - it's the only approach that extends PostgreSQL's compression into the columnar tier. The others are covered because most teams will reach for them first, and they're the right choice for many workloads.
By the end of this page, you should be able to choose a compression strategy for your workload, write the SQL to enable it, and know what storage reduction to expect. For data compression concepts more broadly, see What is data compression and how does it work?.
PostgreSQL compression operates at four distinct levels. Each solves a different problem for a different scope of data.
Tier | Scope | Best for | Expected reduction | Extension required? |
TOAST (built-in) | Individual large column values (>2 KB) | Variable-length text, JSONB, arrays | 20-40% on eligible values | No |
Column-level COMPRESSION setting (PG14+) | Per-column, still row-stored | Tuning pglz vs LZ4 per column type | Marginal vs default TOAST | No |
Filesystem compression (ZFS/Btrfs) | All files on the storage volume | Ops-level compression, no SQL changes | 30-60% depending on data | No (OS/infra level) |
TimescaleDB columnar via Hypercore | Chunks of time-partitioned data | Time-series, IoT, append-heavy workloads | Up to 98%; typical 90-97% | Yes (TimescaleDB extension) |
The sections below cover each tier in order, with SQL and expected ratios. Jump to the decision framework if you already know which tier fits your workload.
TOAST stands for The Oversized Attribute Storage Technique. When a row's stored value exceeds roughly 2 KB, PostgreSQL automatically moves it to a separate TOAST table and optionally compresses it. This is transparent to queries - you read and write the table normally and decompression happens automatically.
Each column has one of four storage strategies:
PLAIN - No compression, no out-of-line storage. Data always stays in the main table. Used for fixed-width types like integers and timestamps.
EXTENDED - Compress first, then store out-of-line if still too large. This is the default for text, bytea, and jsonb.
EXTERNAL - Store out-of-line without compression. Useful when you need fast access to substrings without decompression overhead.
MAIN - Compress if possible, prefer inline storage. A middle ground between PLAIN and EXTENDED.
Two algorithms are available for TOAST compression:
pglz - PostgreSQL's native algorithm, the default prior to PG14. Slower to compress but built-in with no configuration needed.
LZ4 - Available from PG14+. Faster compression and decompression than pglz, with slightly lower compression ratio in benchmarks (pglz achieves roughly 2.23x, LZ4 roughly 2.07x for typical TOAST workloads). For most workloads on PG14+, LZ4 is the better default.
To inspect and change TOAST settings:
-- Check TOAST storage strategy for all columns in a table
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'your_table'::regclass AND attnum > 0;
-- Change TOAST storage strategy
ALTER TABLE your_table ALTER COLUMN your_column SET STORAGE EXTENDED;
-- Set default TOAST compression to LZ4 (PG14+)
ALTER TABLE your_table SET (toast_compression = lz4);TOAST compresses individual column values, not entire rows or the table as a whole. If you have a table with millions of narrow rows - a sensor reading schema like (device_id INT, time TIMESTAMPTZ, value FLOAT) - where no single value exceeds 2 KB, TOAST does nothing. Every value is too small to trigger TOAST, so all those rows sit uncompressed regardless of how many billions of them accumulate.
This is the fundamental limitation for time-series workloads. For a full breakdown of TOAST mechanics and its limits, see What Is TOAST and Why It Isn't Enough for Data Compression in Postgres.
PostgreSQL 14 introduced the ability to set a per-column compression algorithm - pglz or lz4 - at the DDL level. This controls which algorithm TOAST uses for that column when it does compress.
-- Set LZ4 compression for a specific column at table creation
CREATE TABLE metrics (
time TIMESTAMPTZ NOT NULL,
device_id INT,
payload JSONB COMPRESSION lz4
);
-- Change compression algorithm on an existing column
ALTER TABLE metrics ALTER COLUMN payload SET COMPRESSION lz4;
-- Check compression setting per column
SELECT attname, compression
FROM pg_attribute
WHERE attrelid = 'metrics'::regclass AND attnum > 0;You can also set the database-wide default using the default_toast_compression GUC parameter:
-- Set LZ4 as the default for all new toast-eligible columns
ALTER DATABASE mydb SET default_toast_compression = lz4;This setting controls the algorithm used within TOAST compression - it does not enable row-level or table-level compression. It's a tuning lever, not a compression strategy. For JSONB and large text columns, switching from pglz to LZ4 typically yields faster compression with a marginally lower ratio.
When to use this tier: teams running PG14+ on workloads with large variable-length columns (JSONB payloads, text blobs) who want to tune pglz vs LZ4 without adding extensions. Not useful for time-series tables with small float or integer values. For ratio and speed data, see our benchmark of pglz vs. LZ4 compression in PostgreSQL.
Filesystem compression is real, but it operates outside PostgreSQL entirely. ZFS and Btrfs support transparent compression at the storage layer. PostgreSQL data files are compressed on disk by the OS, with no SQL-level configuration needed. ZFS compression=lz4 and compression=zstd are common configurations for Postgres data directories.
Expected reduction is typically 30-60% depending on data type and compressibility. This complements TOAST rather than replacing it - the two operate at different layers and stack.
This option applies to teams managing their own Postgres infrastructure on Linux with control over the storage layer. Managed cloud databases (Tiger Cloud, RDS, Cloud SQL) handle storage-level optimization at the infrastructure level - it is not user-configurable.
One practical note: Btrfs has a known regression with PostgreSQL where fallocate() calls cause Btrfs to skip compression on PostgreSQL data files under normal database I/O patterns; the compression that appears to work on bulk restores does not apply during regular operations. This became especially pronounced in PostgreSQL 17, which introduced FileFallocate(): any file touched by this call is permanently marked NOCOW by Btrfs, disabling compression regardless of mount options.
The limitation: filesystem compression applies uniformly to all files, including indexes and WAL. It has no awareness of data structure or column types. It cannot achieve the column-aware ratios that TimescaleDB's Hypercore delivers for numeric time-series, because the filesystem sees bytes, not database columns.
This is where the first three tiers run out for time-series workloads, and why a fourth tier exists.
TOAST compresses column values, not rows. A sensor reading of (timestamp, device_id, value) has no individual value over 2 KB - TOAST never fires. Filesystem compression is blunt - it applies the same algorithm regardless of whether it's compressing a float column, a timestamp sequence, or a WAL segment. The problem requires a fundamentally different approach: storing data by column rather than by row, then applying type-aware algorithms to each column.
That's what TimescaleDB's Hypercore does. For background on why columnar storage enables better compression, see columnar databases vs. row-oriented databases.
TimescaleDB automatically partitions time-series data into fixed-time chunks via hypertables. When a chunk ages past a configurable threshold, Hypercore converts it from row-oriented storage to columnar format, then applies type-aware compression algorithms per column:
XOR-based compression (Gorilla-style) - For floating-point values (sensor readings, metrics). XOR-encodes consecutive float values; when values change gradually between timestamps, the XOR result contains many leading zeros that can be stripped. This achieves very high compression for slowly-varying float series - the dominant data type in IoT and monitoring workloads.
Delta-of-delta encoding - For timestamps and integer-like types. Stores the second derivative of the data: the difference of differences. For regular-interval time-series (one reading every 5 seconds), the delta-of-delta reduces to a series of zeroes. This compresses an 8-byte timestamp down to a single bit in the ideal case - 64x compression on the time column alone.
Simple-8b - For low-cardinality integer sequences (device IDs, status codes). Packs multiple values into 64-bit integers.
Dictionary compression - For all other types, and for columns with high repeated-value counts.
These algorithms are not available in vanilla PostgreSQL - they are part of the TimescaleDB extension's Hypercore storage engine. The compression methods in hypercore docs cover each algorithm in depth.
The current API uses timescaledb.enable_columnstore to convert chunks to the columnar format and add_columnstore_policy() to automate it:
-- Step 1: Create a hypertable (time-based partitioning)
CREATE TABLE metrics (
time TIMESTAMPTZ NOT NULL,
device_id INT,
value FLOAT
) WITH (tsdb.hypertable);
-- Step 2: Enable the columnstore on an existing hypertable
ALTER TABLE metrics SET (
timescaledb.enable_columnstore,
timescaledb.segmentby = 'device_id',
timescaledb.orderby = 'time DESC'
);
-- Step 3: Add an automated columnstore policy (convert chunks older than 7 days)
CALL add_columnstore_policy('metrics', after => INTERVAL '7 days');
-- Manually convert a specific chunk to the columnstore
SELECT convert_to_columnstore(c)
FROM show_chunks('metrics', older_than => INTERVAL '7 days') c;The segmentby column controls how rows are grouped within a chunk for compression. Grouping by device_id means all readings from a single device are stored together - improving both compression ratios and query performance for per-device queries.
Data type | TOAST (pglz) | TOAST (LZ4) | TimescaleDB columnar |
Float time-series (sensor data) | 0-5% (no compression if < 2 KB per value) | 0-5% | 90-97% storage reduction |
Integer sequences | 0-5% | 0-5% | 85-95% storage reduction |
JSONB payloads | 40-60% | 35-55% | 70-90% (varies by key cardinality) |
Text / log data | 50-70% | 45-65% | 60-80% |
The real-world numbers match the theory. Ndustrial achieved 97% storage reduction on industrial energy data using TimescaleDB compression. Cloudflare uses TimescaleDB's columnar engine for analytics at scale - see How TimescaleDB Helped Cloudflare Scale Analytics and Reporting. Across Tiger Data's case studies, 90-97% reduction is typical for regular-interval float time-series. Current docs indicate chunks can be compressed by up to 98%.
For the engineering backstory on how this was built, see Building Columnar Compression in a Row-Oriented Database.
Traditionally, compression ran as a background job that converted older chunks to the columnstore asynchronously. Direct Compress changes this: data is compressed in memory during insertion and written directly to the columnstore, eliminating the write-amplification of compressing already-written data.
-- For COPY operations
SET timescaledb.enable_direct_compress_copy = on;The result is significantly reduced I/O footprint at ingest time. This removes the operational trade-off between ingestion speed and compression - previously, teams had to decide between writing data fast (uncompressed) and managing a background compression lag.
Note that this feature is a tech preview and is not yet production-ready; it works best for batch ingestion of 1,000 or more rows per segmentby value, and does not work on tables with unique constraints, triggers, or continuous aggregates. For the announcement details, see Introducing Direct Compress.
Two objections come up consistently when teams evaluate columnar compression. Both have been resolved.
"I can't query compressed chunks efficiently." Hypercore supports B-tree and hash indexes on compressed data. Lookup queries on compressed data are up to 1,185x faster than unindexed scans. Compressed data is fully indexable. See PostgreSQL Indexes for Columnstore: 1,185x Faster Lookup Queries.
"I can't UPDATE or DELETE compressed rows." Compression tuple filtering resolved this. UPDATEs and DELETEs on compressed chunks are supported without decompressing the entire chunk. Only batches matching the query filter are decompressed, delivering up to 500x faster updates and deletes and up to 10x faster upserts. See Bridging the Gap: Introducing Compression Tuple Filtering.
You are on PostgreSQL 14+ and have not yet set default_toast_compression = lz4 - this is a free, zero-risk upgrade
Your tables include large variable-length columns: JSONB, text, bytea, xml
You want zero additional dependencies - TOAST is built in and always on
Your workload is mixed OLTP (reads and writes with large values, not narrow time-series rows)
You are on PG14+ and want to tune the algorithm per column without changing storage strategy
You have specific columns where LZ4 is the right trade-off (faster, slightly lower ratio) vs. pglz (slower, slightly higher ratio)
You are not adding extensions and need to stay within the vanilla PostgreSQL feature set
You control the storage layer (self-managed Linux deployment with ZFS or Btrfs)
You want database-agnostic compression that applies to all files, not just data that triggers TOAST
Your primary goal is reducing infrastructure cost without any application or schema changes
You understand the limitation: no data-type awareness, applies uniformly to all files including indexes and WAL
Your data is time-series: sensor readings, metrics, events, logs - anything with a timestamp as the primary partitioning key
You need 90%+ storage reduction and TOAST gives you 0-5% on your narrow rows
You want to keep full SQL semantics and stay in PostgreSQL - not migrate to a specialized columnar database
You need to run analytical queries on compressed data without decompressing to a separate system
You are on Tiger Cloud or running the TimescaleDB extension on self-managed Postgres
Pure OLTP workloads with many point-row UPDATEs and DELETEs (though compression tuple filtering has substantially reduced this constraint)
Tables where rows are not time-ordered and do not benefit from time-partitioned chunks
Teams who cannot or will not add a PostgreSQL extension to their deployment
For implementation details, the TimescaleDB compression documentation covers setup, segmentby/orderby optimization, and policy configuration.
PostgreSQL compresses data primarily through TOAST (Oversized Attribute Storage Technique). When a column value exceeds roughly 2 KB, PostgreSQL automatically compresses it using pglz (default) or LZ4 (PG14+). The compression is transparent - queries return decompressed data automatically. For time-series or analytical data, the TimescaleDB extension adds columnar compression at the chunk level, applying type-aware algorithms (XOR-based for floats, delta-of-delta for timestamps, simple-8b for integers) and delivering 90-97% storage reduction.
Vanilla PostgreSQL offers three levels: TOAST (automatic compression for large column values), column-level COMPRESSION settings that choose between pglz and LZ4 per column (PG14+), and no native table-level compression. At the infrastructure level, filesystem compression (ZFS/Btrfs) can be applied transparently. The TimescaleDB extension adds a fourth tier - columnar compression via Hypercore - which is the only PostgreSQL-native option that achieves 90%+ storage reduction on time-series data.
For standard PostgreSQL, TOAST compression activates automatically for columns with the EXTENDED storage strategy (the default for text, jsonb, bytea). On PG14+, run ALTER TABLE t ALTER COLUMN c SET COMPRESSION lz4 to use LZ4 instead of pglz. For time-series tables, enable TimescaleDB hypertables, configure the columnstore with ALTER TABLE t SET (timescaledb.enable_columnstore = true), then add CALL add_columnstore_policy('t', after => INTERVAL '7 days') to convert chunks automatically.
TOAST compresses individual column values that exceed roughly 2 KB - it fires on a per-value basis and is transparent. It delivers 20-60% reduction on large variable-length values but does nothing for narrow rows with small scalar values (the typical time-series schema). TimescaleDB columnar compression works at the chunk level: it converts entire time-partitioned chunks from row to columnar format, then applies type-aware algorithms. The result is 90-97% reduction for time-series data regardless of row width.
The most effective approach is TimescaleDB's columnar compression via Hypercore. Steps: (1) create a hypertable with CREATE TABLE ... WITH (tsdb.hypertable), (2) configure the columnstore with ALTER TABLE your_table SET (timescaledb.enable_columnstore, timescaledb.segmentby = 'device_id'), (3) add an automated policy with CALL add_columnstore_policy('your_table', after => INTERVAL '7 days'). Chunks older than the policy interval are converted to the columnstore automatically. Typical results for sensor and IoT data: 90-97% storage reduction.
Columnar compression stores data by column rather than by row, enabling far higher compression ratios for repeated or slowly-changing values across many rows - the pattern typical in time-series and analytical data. Vanilla PostgreSQL is row-oriented and does not support columnar storage natively. The TimescaleDB extension adds columnar storage (via Hypercore) to PostgreSQL through time-partitioned chunks. Each chunk is converted to columnar format and compressed with type-aware algorithms, distinct from TOAST which compresses individual column values within row-oriented storage.
TimescaleDB's Hypercore engine applies type-aware compression algorithms: XOR-based compression for floating-point values, delta-of-delta encoding for timestamps and integer-like types, and simple-8b for low-cardinality integer sequences. Because time-series data is stored in ordered chunks by time, consecutive values in the same column are highly correlated - producing very high compression ratios. Ndustrial achieved 97% storage reduction on industrial energy data.
Gorilla compression is an XOR-based encoding algorithm originally developed by Facebook for their Gorilla time-series database (published 2015). It compresses floating-point values by XOR-encoding consecutive readings. When a float value changes by a small amount between timestamps - as is typical for sensor data - the XOR result contains many leading zeros that are stripped to store only the changing bits. TimescaleDB implements XOR-based compression for float columns in Hypercore following the same approach.