Partitioning, Indexing & Optimization

Tiger Data 101 → TimescaleDB | Section: Hypertables | ⏱ Time: ~3 min

Partitioning strategies

Time-based partitioning (default)

TimescaleDB automatically partitions by time. This is the core optimization: queries with time filters skip irrelevant chunks entirely.

Space-based partitioning (optional)

For high-cardinality data (thousands of unique devices, sensors, or users), add a second dimension:

SELECT create_hypertable(
  'sensor_readings',
  by_range('time', INTERVAL '1 day'),
  by_hash('device_id', 4)  -- 4 sub-partitions per time chunk
);

This creates a 2D grid: time chunks are further split by device ID, keeping each sub-chunk smaller and more queryable.

---

Indexing hypertables

Effective indexes dramatically speed up queries. Best practices:

1. Time + tag index (most common):

   CREATE INDEX ON sensor_readings (device_id, time DESC);

2. Measure column (for range queries on values):

   CREATE INDEX ON sensor_readings (temperature) WHERE temperature > 30;

---

Optimization techniques

• Chunk exclusion: Queries automatically skip chunks outside the filter range
• Compression: Older chunks can be compressed to 10–40x smaller
• Retention policies: Automatically drop chunks older than your retention window
• Continuous Aggregates: Pre-compute expensive aggregations

---

Knowledge check

Knowledge Check

Question 1 of 3

A. When you query a hypertable with a time filter (e.g., `WHERE time > NOW() - INTERVAL '7 days'`), what optimization does TimescaleDB apply automatically?

A)It scans all chunks in parallel for maximum throughputB)It only reads the chunks that overlap with the time range (chunk exclusion)C)It decompresses matching rows before returning themD)It routes the query only to the most recently written chunk

Check Answer

---

→NEXT MODULE

Hypercore — Learn about columnar storage and compression for dramatic storage savings.