0Pricing
SQL Academy · Lesson

Compression and Retention Policies

Compress old chunks with TimescaleDB's columnar compression, and drop expired chunks automatically.

Compression and Retention Policies is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Compress?

Time-series data is highly compressible. TimescaleDB compresses chunks in columnar form, often achieving 10–20× compression ratios.

Compressing a Hypertable

Configure which columns segment and order the compressed data:

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

Compression Policy

Automate compression of old chunks:

SELECT add_compression_policy('metrics', INTERVAL '7 days');
-- Chunks older than 7 days get compressed automatically.

Query Performance on Compressed Data

Reads on compressed chunks are still fast — TimescaleDB pushes filters down to the column store. Writes/updates on compressed chunks are limited (mostly append-only).

Retention Policy

Drop data older than N intervals — fast (drop chunk) vs slow (DELETE):

SELECT add_retention_policy('metrics', INTERVAL '90 days');
-- Chunks older than 90 days are dropped automatically.

Manual Chunk Operations

Explore chunks:

SELECT * FROM chunks_detailed_size('metrics');
SELECT show_chunks('metrics', older_than => INTERVAL '90 days');
SELECT drop_chunks('metrics', older_than => INTERVAL '90 days');

Storage Tiering

Move old chunks to slow disk by changing the tablespace per chunk. Combined with compression, you can keep years of data at low cost.

Compression Ratios in Practice

Typical ratios on sensor data:

  • Numeric metrics: 90–95%+ space savings
  • Categorical: 70–80%
  • String/JSON: depends on content

Decompressing for Updates

You can decompress a chunk to update old data, then recompress:

SELECT decompress_chunk(c) FROM show_chunks('metrics', newer_than => INTERVAL '60 days') c;
-- Update / fix
SELECT compress_chunk(c) FROM show_chunks('metrics', newer_than => INTERVAL '60 days') c;

Plain Postgres Equivalents

Without TimescaleDB you can roll your own with partitions:

DROP TABLE events_2022;            -- retention (fast)
-- No native columnar compression in vanilla Postgres for time-series.

Combine With Continuous Aggregates

Common pattern: keep raw data for 30 days, hourly aggregates for 1 year, daily aggregates forever. Each tier compressed or retained appropriately.

Monitoring

Track compressed size and ratio:

SELECT * FROM hypertable_compression_stats('metrics');

Recap

Compression + retention turn Postgres into a competent time-series store.

  • Compress old chunks 10–20×
  • Retention drops chunks instantly
  • Tier raw / hourly / daily for different retention
  • Decompress to backfill if needed

Quick Check

You want to keep 7 days of raw metrics and drop everything older automatically. What's the TimescaleDB way?

Frequently asked questions

Is the “Compression and Retention Policies” lesson free?

Yes — the full text of “Compression and Retention Policies” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Compression and Retention Policies”?

Compress old chunks with TimescaleDB's columnar compression, and drop expired chunks automatically. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Compression and Retention Policies” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. TimescaleDB Hypertables
  2. Continuous Aggregates
  3. Compression and Retention Policies
  4. Time-Series Index Choices
← Back to SQL Academy