TigerData: how do I set up compression and retention policies for telemetry tables?

Telemetry workloads grow fast—then they get expensive. Compression and retention policies are how you tell TigerData “keep everything fresh and fast up to here, compress and tier this band, and safely drop data after that.” Done right, you get predictable performance and costs without constantly hand-tuning tables and cron jobs.

Below I’ll walk through how to set up compression and retention for telemetry hypertables on TigerData (TimescaleDB), with concrete SQL you can paste into psql or your migration scripts.

Quick Answer: Turn your telemetry table into a hypertable, enable compression with the right sort/segment keys, then add add_compression_policy and add_retention_policy jobs to automatically compress older chunks and drop fully expired data. Tiger Cloud handles the scheduling and storage mechanics; you just define the rules.

---

The Quick Overview

• What It Is: Compression and retention policies are automated jobs that periodically compress “warm” telemetry data and drop “cold” data you no longer need—at the chunk level—inside Postgres.
• Who It Is For: Teams running high-ingest time-series / telemetry tables (IoT, metrics, trading ticks, events) on TigerData who need fast recent queries, cheap long-term storage, and predictable performance.
• Core Problem Solved: Plain Postgres tables bloat as telemetry accumulates, making queries slow and storage expensive. Manual archiving is fragile and high-maintenance. Policies give you Postgres-native lifecycle management.

---

How It Works

Under the hood, TigerData uses TimescaleDB primitives:

• Hypertables for automatic partitioning by time (and optionally a key, like device_id).
• Compression for turning old row data into columnar, highly compressed chunks (often up to 98% smaller).
• Policies for scheduling background jobs that:
  • Compress chunks older than a threshold.
  • Drop chunks older than a retention horizon.
  • Optionally combine with tiered storage so compressed chunks move to low-cost object storage.

Think of it as a three-phase lifecycle for every telemetry row:

1. Hot (uncompressed, on primary storage)

   • New data, heavy writes.
   • Indexed for low-latency OLTP-style queries.

2. Warm (compressed, on primary or tiered storage)

   • Read-heavy, write-rarely.
   • Stored in Hypercore’s columnar format for fast scans and aggregations with a much smaller footprint.

3. Cold (expired and dropped)

   • Beyond your regulatory or business window.
   • Freed disk space and reduced index maintenance.

You control the boundaries between hot, warm, and cold with two functions:

1. add_compression_policy(hypertable, compress_after => interval)
2. add_retention_policy(hypertable, drop_after => interval)

The rest is handled by Tiger Cloud’s background scheduler.

---

Step 1: Confirm your telemetry table is a hypertable

Compression and retention policies operate on hypertables, not plain tables. If your telemetry table isn’t a hypertable yet, convert it.

Assume a typical telemetry schema:

CREATE TABLE telemetry (
  time        timestamptz      NOT NULL,
  device_id   text             NOT NULL,
  metric_name text             NOT NULL,
  value       double precision NOT NULL,
  tags        jsonb,
  PRIMARY KEY (time, device_id, metric_name)
);

Convert it:

SELECT create_hypertable(
  'telemetry',
  by_range('time'),
  chunk_time_interval => INTERVAL '1 day'
);

Optional: add a space partition for high-cardinality devices:

SELECT create_hypertable(
  'telemetry',
  by_range('time'),
  partitioning := by_hash('device_id', 4),
  chunk_time_interval => INTERVAL '1 day'
);

Important: Only run create_hypertable once per table. If you’re in Tiger Cloud, new telemetry services created via the console or automation often already use hypertables for the “main” tables.

---

Step 2: Design a compression strategy for telemetry

Compression turns chunks into columnar storage. Choosing sort and segment keys correctly keeps queries fast.

A common telemetry pattern:

• You filter on time ranges and device_id or metric_name.
• You aggregate (AVG, MAX, PERCENTILE) over ranges.
• You rarely update old data.

A typical compression configuration:

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

What this does:

• compress_segmentby = 'device_id'
  Groups rows per device into the same compressed chunk segment, improving device-centric queries.

• compress_orderby = 'time DESC'
  Sorts inside compressed chunks by time. Range scans (WHERE time BETWEEN ...) become efficient columnar reads.

Note: You can segment by multiple columns, e.g. (device_id, metric_name), but don’t overdo it—too many segment keys can reduce compression efficiency.

If your workloads are more metric-centric than device-centric, flip the segment key:

ALTER TABLE telemetry SET (
  timescaledb.compress = 'true',
  timescaledb.compress_segmentby = 'metric_name',
  timescaledb.compress_orderby = 'time DESC'
);

---

Step 3: Add an automatic compression policy

Now that the hypertable is compression-enabled, define when chunks should be compressed.

Example: keep the last 7 days uncompressed, compress anything older:

SELECT add_compression_policy(
  'telemetry',
  compress_after => INTERVAL '7 days'
);

Mechanics:

• Background jobs periodically inspect telemetry chunks.
• Every chunk whose max time is older than NOW() - '7 days' is compressed.
• New chunks (last 7 days) remain uncompressed for fast writes and updates.

Important: Compression policies are chunk-level. If your chunks are 1 day wide and compress_after is 7 days, you’ll have ~7 uncompressed chunks at any time.

To check compression status:

SELECT
  chunk_schema,
  chunk_name,
  is_compressed,
  range_start,
  range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'telemetry'
ORDER BY range_start;

---

Step 4: Add a retention (data expiration) policy

Retention policies drop old chunks once they’re beyond your required data window.

Example: keep 90 days of telemetry; anything older is dropped:

SELECT add_retention_policy(
  'telemetry',
  drop_after => INTERVAL '90 days'
);

Now your lifecycle looks like:

• 0–7 days: uncompressed, hot.
• 7–90 days: compressed, warm.
• > 90 days: dropped, cold.

Warning: add_retention_policy permanently deletes data. Confirm your regulatory and business requirements (e.g., logs needed for audits) before setting an aggressive drop_after.

You can inspect retention policies via:

SELECT *
FROM timescaledb_information.jobs
WHERE hypertable_name = 'telemetry'
ORDER BY scheduled_start;

---

Step 5: Combine with tiered storage (Tiger Cloud)

On Tiger Cloud, compression and retention policies interplay with tiered storage:

• Uncompressed chunks start on high-performance storage.
• Compressed chunks can be automatically moved to low-cost object storage while remaining queryable.
• Retention still drops chunks according to drop_after, regardless of tier.

Typical telemetry lifecycle on Tiger Cloud:

1. Day 0–7:

   • Uncompressed, high-performance storage.
   • Heavy writes and low-latency reads.

2. Day 7–90:

   • Compressed, potentially tiered to low-cost storage.
   • Up to 98% compression, lower storage bill.
   • Analytical queries (dashboards, reports) stay fast via columnar scans.

3. 90 days:

   • Chunks dropped by retention job.
   • No hidden tier fees or per-query charges; you stop paying for those bytes.

Exact tiering controls are configured at the service level in Tiger Console; compression and retention policies are how you mark data as “eligible” for cheap storage and eventual deletion.

---

Concrete policy recipes for telemetry tables

Here are a few battle-tested configurations.

1. High-frequency metrics (1s–10s intervals)

• Use case: Infrastructure metrics, observability.
• Goal: Very fast last-24h queries; cheap 1-year history.

ALTER TABLE metrics SET (
  timescaledb.compress = 'true',
  timescaledb.compress_segmentby = 'series_id',
  timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy(
  'metrics',
  compress_after => INTERVAL '3 days'
);

SELECT add_retention_policy(
  'metrics',
  drop_after => INTERVAL '365 days'
);

Outcome:

• 0–3 days: uncompressed, ideal for real-time dashboards.
• 3–365 days: compressed (often 90–98% smaller), still queryable for historical reports.

• 365 days: dropped.

---

2. Device telemetry with moderate volume

• Use case: IoT sensors sending every 1–5 minutes.
• Goal: Balance quick point lookups with long-term trending.

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

SELECT add_compression_policy(
  'telemetry',
  compress_after => INTERVAL '14 days'
);

SELECT add_retention_policy(
  'telemetry',
  drop_after => INTERVAL '730 days'  -- 2 years
);

Outcome:

• Hot window is 14 days (debugging, support investigations).
• Everything else compressed and optionally tiered, with predictable two‑year retention.

---

3. Trading ticks / event streams (ultra-high ingest)

• Use case: Tick data, order book events.
• Goal: Keep the last few days blazing fast, aggressively compress and expire older data.

ALTER TABLE ticks SET (
  timescaledb.compress = 'true',
  timescaledb.compress_segmentby = 'symbol',
  timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy(
  'ticks',
  compress_after => INTERVAL '1 day'
);

SELECT add_retention_policy(
  'ticks',
  drop_after => INTERVAL '30 days'
);

Outcome:

• Last 24 hours: uncompressed for ultra-low-latency access.
• 1–30 days: compressed, still useful for recent backtesting / analysis.

• 30 days: dropped (or moved to a dedicated archive / Iceberg via lakehouse integration, outside the scope of this page).

---

Operational considerations and best practices

Indexing and compression

• Indexes on a compressed hypertable’s uncompressed chunks work as usual.
• Compressed chunks use internal columnar indexes; some btree indexes may be skipped on compressed data.

Important: If you rely heavily on non-time, non-segment filters (e.g., WHERE region = 'us-east'), consider including them as segment keys or rethinking your schema to keep compressed queries efficient.

Effect on writes and updates

• Compression is chunk-level. A compressed chunk is mostly immutable.
• Inserts always go into current, uncompressed chunks.
• Updates/deletes on compressed chunks are supported but slower; expect a decompress/modify/recompress path in some cases.

If you need to backfill:

-- Backfill older data, then recompress that chunk if needed
CALL decompress_chunk('_timescaledb_internal._hyper_1_42_chunk');

-- After modifications
CALL recompress_chunk('_timescaledb_internal._hyper_1_42_chunk');

Policy scheduling and monitoring

Tiger Cloud runs the background workers for you. As an operator you should:

• Periodically inspect jobs:

  SELECT job_id, application_name, hypertable_name, schedule_interval, last_run_success
  FROM timescaledb_information.jobs
  WHERE hypertable_name = 'telemetry';
  

• Inspect chunk stats:

  SELECT
    hypertable_name,
    compression_status,
    total_chunks,
    compressed_chunks,
    uncompressed_chunks,
    before_compression_total_bytes,
    after_compression_total_bytes
  FROM timescaledb_information.compressed_hypertables
  WHERE hypertable_name = 'telemetry';
  

This lets you validate compression ratios and ensure policies are doing what you expect.

---

Limitations & edge cases

• Late-arriving data:
  If you insert data into time ranges that are already compressed, that data may land in compressed chunks. For heavy late-arrival workloads, expand your hot window (compress_after) or consider a dedicated staging hypertable for backfill.

• Very small tables:
  On low-volume telemetry, compression can provide less dramatic gains, and chunk overhead might dominate. It still simplifies lifecycle management, but tune chunk_time_interval so each chunk has a meaningful number of rows.

• Irreversible retention:
  Once retention drops a chunk, recovery relies on your backups and PITR (Tiger Cloud provides automated backups and point-in-time recovery; you do not pay extra for backup storage). Plan retention windows accordingly.

---

Frequently Asked Questions

How do I change an existing compression or retention policy for a telemetry hypertable?

Short Answer: Drop the old policy with remove_*_policy and create a new one with the desired interval.

Details:

To view existing policies:

SELECT
  job_id,
  hypertable_name,
  proc_name,
  config
FROM timescaledb_information.jobs
WHERE hypertable_name = 'telemetry';

To remove a compression policy:

SELECT remove_compression_policy('telemetry');

To remove a retention policy:

SELECT remove_retention_policy('telemetry');

Then create new ones:

SELECT add_compression_policy(
  'telemetry',
  compress_after => INTERVAL '10 days'
);

SELECT add_retention_policy(
  'telemetry',
  drop_after => INTERVAL '180 days'
);

Changes apply to future policy runs; existing compressed chunks stay compressed. Retention will skip chunks that no longer qualify for deletion under the new drop_after.

---

How do compression and continuous aggregates work together for telemetry?

Short Answer: Compress the raw telemetry hypertable; use continuous aggregates for pre-aggregated rollups. Both can have their own policies.

Details:

For busy telemetry systems, a common design is:

1. Raw telemetry hypertable:
   • Compression and retention policies to manage raw events.
2. Continuous aggregate(s) for rollups:
   • e.g., 1-minute or 5-minute metrics.

Example:

CREATE MATERIALIZED VIEW telemetry_5m
WITH (timescaledb.continuous) AS
SELECT
  time_bucket(INTERVAL '5 minutes', time) AS bucket,
  device_id,
  metric_name,
  avg(value) AS avg_value,
  max(value) AS max_value
FROM telemetry
GROUP BY bucket, device_id, metric_name;

Add a refresh policy:

SELECT add_continuous_aggregate_policy(
  'telemetry_5m',
  start_offset => INTERVAL '7 days',
  end_offset   => INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute'
);

Then independently:

• Compress and retain the raw hypertable (for detailed debugging).
• Optionally compress the continuous aggregate hypertable too.
• Use a longer retention on the aggregate than on the raw data if you only need detailed events for a shorter window.

This lets you keep years of aggregated telemetry on cheap compressed/tiered storage, even if you retain raw events for only weeks or months.

---

Summary

Setting up compression and retention policies for telemetry tables in TigerData is about defining a clear lifecycle:

1. Turn telemetry tables into hypertables.
2. Enable compression with deliberate compress_segmentby and compress_orderby keys that match your query patterns.
3. Add add_compression_policy to compress chunks after your chosen “hot” window.
4. Add add_retention_policy to drop data after your required retention period.
5. Leverage Tiger Cloud’s tiered storage so compressed chunks automatically move to low-cost storage while staying queryable.

The result: a Postgres-native telemetry store that ingests at production scale, stays fast for real-time queries, keeps historical analytics cheap, and avoids the fragile, high-maintenance scripts and external pipelines typical of DIY lifecycle management.

---

Next Step

Get Started