Choosing segmentby and orderby for Maximum Compression

The compress_segmentby and compress_orderby settings on a compressed hypertable quietly decide both your storage bill and how much data a query has to touch, because they control how TimescaleDB groups rows into columnar batches and how it orders values inside them. Get them from the wrong intuition and a table that should shrink 15× barely reaches 3×; derive them from your actual query predicates and column cardinality and the ratio takes care of itself. This page turns the two settings into a repeatable decision: profile the filters and cardinalities, apply two rules, and confirm the result in chunk_compression_stats.

# What the Two Settings Actually Do

TimescaleDB compression converts a chunk from row storage into an array-per-column columnar layout. Rows are first grouped into segments by the compress_segmentby columns, then sorted inside each segment by compress_orderby, and finally each column is packed into batches of up to 1000 values that get run-length, delta, delta-of-delta, or dictionary encoded depending on the data. Two facts fall out of that pipeline and drive every decision below:

• The segmentby columns become a single stored value per segment, so filtering on them after compression is a metadata lookup rather than a decompress-and-scan. This is what gives you query pruning on compressed chunks.
• Compression ratio comes from similarity between adjacent values in a batch. Ordering telemetry so that consecutive rows differ by little — same device, monotonic time — is exactly what lets the delta and run-length encoders collapse a column to a handful of bytes.

The rest of this page is about choosing columns that maximize both effects at once, and recognizing when a tempting column would wreck one to help the other. The parent overview of columnar compression models for high-frequency telemetry covers the encoders themselves; here the focus is purely the two grouping settings.

# Input Profiling: What to Measure First

You cannot pick these columns from the schema alone. Gather four things from a representative production window before touching ALTER TABLE:

• Frequent equality predicates — which columns appear in WHERE col = ... on your hot queries. These are segmentby candidates because pruning only helps filters you actually run.
• Column cardinality — the number of distinct values per column across a chunk. This decides whether a segmentby candidate produces useful segments or shatters the chunk.
• Range-scan direction — whether queries read newest-first (ORDER BY time DESC LIMIT n) or oldest-first. Match orderby to it so decompression streams in the requested order.
• Distinct values per segment — how many rows share a given segmentby value inside one chunk. Batches only fill (and encoders only pay off) when a segment holds hundreds to thousands of rows.

Cardinality is the pivotal input, so measure it directly rather than guessing:

-- Cardinality of every segmentby candidate over a recent chunk's worth of data.
SELECT
    count(DISTINCT device_id)   AS device_cardinality,
    count(DISTINCT metric_type) AS metric_cardinality,
    count(DISTINCT tenant_id)   AS tenant_cardinality,
    count(*)                    AS total_rows,
    count(*)::numeric
      / NULLIF(count(DISTINCT device_id), 0) AS rows_per_device
FROM sensor_readings
WHERE time > now() - INTERVAL '1 day';

The rows_per_device figure is the one to watch: it is the expected segment size for segmentby => device_id. If it is in the hundreds or thousands, batches fill and compression works. If it is close to 1, that column will make tiny segments and hurt you.

# The Decision: Two Rules and a Cardinality Bound

Once the profile is in hand the choice reduces to two rules:

• segmentby = the columns your hot queries filter on by equality, with MODERATE cardinality. Device id, sensor id, or tenant id are typical. Moderate means each distinct value still owns enough rows in a chunk to fill batches — hundreds or more, not single digits.
• orderby = time DESC, optionally preceded by a column correlated with the measured value. Time is almost always the right sort key because telemetry arrives roughly in time order, so time deltas are small and uniform. DESC aligns the on-disk order with newest-first reads. A leading correlated column (for example metric_type, time DESC) groups like-valued rows so the value column also delta-encodes well.

The cardinality bound exists because the number of segments a chunk is cut into equals the number of distinct segmentby combinations present:

$$N_{segments} = \prod_{c \,\in\, segmentby} \text{distinct}(c) \qquad \bar{n}_{rows/seg} = \frac{N_{rows}}{N_{segments}}$$

Each segment carries fixed per-batch overhead and cannot share encoding across segment boundaries. As $N_{segments}$ climbs toward $N_{rows}$, the average rows per segment $\bar{n}_{rows/seg}$ falls toward 1, batches never fill, and the ratio collapses. The healthy target is roughly $\bar{n}_{rows/seg} \gtrsim 1000$ so most batches reach the 1000-value cap. That single inequality is why “segment by the timestamp” and “segment by a UUID that is unique per row” are the two classic mistakes — both drive $N_{segments}$ to the row count.

# Worked Example: A Sensor Table

Take the same fleet used across this section: 50,000 devices, each emitting one reading every 20 seconds into sensor_readings (time, device_id, metric_type, value, payload). A one-day chunk holds about 216 million rows across the 50,000 devices and, say, 8 metric types. The dominant query is “give me the recent history for one device”:

SELECT time, value
FROM sensor_readings
WHERE device_id = '...'          -- equality filter, every dashboard query
  AND metric_type = 'temperature'
ORDER BY time DESC
LIMIT 500;

device_id is filtered on every query and has moderate cardinality relative to the chunk: 216M rows / 50k devices gives about 4,300 rows per device per chunk, comfortably above the 1000-value batch target. That makes it the right segmentby. Time is the read direction, so orderby => time DESC. The good configuration:

ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby   = 'time DESC'
);

-- Compression only converts chunks the policy later marks as cold.
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

Within each device’s segment the device_id column run-length encodes to one value, time delta-encodes to a near-constant 20-second step, and value (physically adjacent readings from one sensor) delta-encodes tightly. Ratios of 12–15× are typical for this shape.

Now the bad choice. Segmenting by time — tempting because “it’s a time-series table” — makes $N_{segments}$ equal the number of distinct timestamps, roughly the row count. Every segment holds a single row, no batch fills, per-batch overhead dominates, and a query for one device_id can no longer prune by segment metadata because device_id is buried inside the compressed payload. The same data lands near 2–3× and every device query decompresses the whole chunk. The columns did not change — only how they were grouped.

# Edge Cases & When to Deviate

• Very high-cardinality segmentby — a unique-per-row column (raw UUID event id, exact timestamp) produces tiny single-row segments that compress worse than uncompressed row storage after overhead. If your only equality filter is high-cardinality, leave it out of segmentby and let it live inside the ordered payload instead.
• No segmentby at all — valid and sometimes best. With segmentby empty the whole chunk is one segment ordered by orderby, which maximizes raw ratio but gives zero segment-level pruning, so every filtered query decompresses more. Choose this only when queries are broad time-range scans without a selective equality filter.
• Multi-column segmentby — allowed (compress_segmentby = 'tenant_id, device_id') but multiply the cardinalities: the segment count is the product, so two moderate columns can jointly become high-cardinality and shrink $\bar{n}_{rows/seg}$ below the batch target. Add a second column only when queries filter on both by equality and the product still leaves ample rows per segment.
• Correlated orderby prefix — when one physical column strongly predicts the measurement, ordering metric_type, time DESC clusters like readings so the value column delta-encodes better than under time DESC alone. Test both; the win is data-dependent.
• Settings only affect future compression — ALTER TABLE ... SET changes the recipe for chunks compressed after the change. Chunks already compressed keep their old layout until you decompress and recompress them. To re-apply new settings to history, run decompress_chunk then compress_chunk (or let a maintenance job do it), which is best coordinated with your broader chunk compression scheduling automation so the rewrite happens off-peak.

Note that a well-chosen segmentby also interacts with indexing: the same moderate-cardinality tag you segment on is usually the one worth an index, which is covered in best practices for chunk indexing on high-cardinality tags.

# Verification

Measure the actual ratio and segment shape rather than trusting the plan. chunk_compression_stats reports before/after sizes per chunk:

-- Per-chunk compression ratio for the resulting settings.
SELECT
    chunk_name,
    pg_size_pretty(before_compression_total_bytes) AS before,
    pg_size_pretty(after_compression_total_bytes)  AS after,
    round(
        before_compression_total_bytes::numeric
          / NULLIF(after_compression_total_bytes, 0)
    , 1) AS ratio
FROM chunk_compression_stats('sensor_readings')
WHERE after_compression_total_bytes IS NOT NULL
ORDER BY chunk_name DESC
LIMIT 10;

Confirm segments are large enough by counting rows per segment on a freshly compressed chunk — this is the empirical version of $\bar{n}_{rows/seg}$:

-- Average rows folded into each compressed batch; want this near 1000.
SELECT
    avg(_ts_meta_count) AS avg_rows_per_batch,
    count(*)            AS batch_count
FROM _timescaledb_internal._compressed_hypertable_<id>;  -- from show_chunks/compression view

To validate the before/after story cleanly: capture the ratio, ALTER to the alternate settings, decompress and recompress one chunk, and compare the two chunk_compression_stats rows. A good segmentby should show a markedly higher ratio and a lower decompressed-bytes count on your filtered test query. Tracking that ratio as it drifts over weeks — after schema changes or fleet growth shift cardinality — is the job of tracking compression ratio trends over time.

# Related

• Compression Models for High-Frequency Telemetry — the encoders and columnar layout these settings feed into
• Chunk Compression Scheduling Automation — running compression (and recompression after a settings change) off-peak
• Best Practices for Chunk Indexing on High-Cardinality Tags — indexing the same tags you segment on
• Tracking Compression Ratio Trends Over Time — watching the resulting ratio drift as cardinality changes

← Back to Compression Models for High-Frequency Telemetry · Up to Core Hypertable Architecture & Partitioning Strategy