timescaledb

What this skill does

# TimescaleDB 2.24.0 Time-Series Database

## Overview

TimescaleDB 2.24.0 introduces transformational features: lightning-fast recompression (100x faster updates), Direct Compress integration with continuous aggregates, UUIDv7 support in aggregates, and bloom filter sparse index changes. This skill ensures you leverage these capabilities correctly.

**Core principle:** Time-series data has unique access patterns. Design for append-heavy, time-range queries from the start.

**Announce at start:** "I'm applying timescaledb to ensure TimescaleDB 2.24.0 best practices."

## When This Skill Applies

This skill is MANDATORY when ANY of these patterns are touched:

| Pattern | Examples |
|---------|----------|
| `**/*hypertable*` | migrations/create_hypertable.sql |
| `**/*timeseries*` | models/timeseries.ts |
| `**/*metrics*` | services/metricsService.ts |
| `**/*events*` | db/events.sql |
| `**/*logs*` | tables/logs.sql |
| `**/*sensor*` | iot/sensor_data.sql |
| `**/*continuous_agg*` | views/hourly_stats.sql |
| `**/*compression*` | policies/compression.sql |

Or when files contain:

```sql
-- These patterns trigger this skill
create_hypertable
continuous aggregate
compress_chunk
add_compression_policy
```

## TimescaleDB 2.24.0 Features

### 1. Lightning-Fast Recompression

TimescaleDB 2.24.0 introduces `recompress := true` for dramatically faster updates to compressed data:

```sql
-- OLD (2.23 and earlier): Decompress entire chunk, update, recompress
-- Could take minutes for large chunks

-- NEW (2.24.0): Update compressed data directly
UPDATE sensor_data
SET value = corrected_value
WHERE time BETWEEN '2026-01-01' AND '2026-01-02';
-- 100x faster for compressed chunks

-- Enable recompression mode (automatic in 2.24.0)
-- Updates to compressed chunks now:
-- 1. Identify affected segments
-- 2. Decompress only those segments
-- 3. Apply updates
-- 4. Recompress immediately

-- Verify recompression is happening
SELECT * FROM timescaledb_information.job_stats
WHERE job_id IN (
  SELECT job_id FROM timescaledb_information.jobs
  WHERE proc_name = 'policy_recompression'
);
```

**When this matters:**
- Late-arriving data corrections
- Backfill operations
- Data quality fixes
- Retroactive updates

### 2. Direct Compress with Continuous Aggregates

Continuous aggregates can now compress directly without materialized hypertable overhead:

```sql
-- Create continuous aggregate with direct compression
CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous, timescaledb.compress = true) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  avg(temperature) AS avg_temp,
  min(temperature) AS min_temp,
  max(temperature) AS max_temp,
  count(*) AS sample_count
FROM sensor_readings
GROUP BY bucket, device_id
WITH NO DATA;

-- Add compression policy directly to continuous aggregate
SELECT add_compression_policy('hourly_metrics', INTERVAL '7 days');

-- Refresh policy
SELECT add_continuous_aggregate_policy('hourly_metrics',
  start_offset => INTERVAL '1 month',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);
```

**Benefits:**
- No intermediate materialized hypertable
- Automatic compression of aggregate data
- Reduced storage for historical aggregates
- Simpler management

### 3. UUIDv7 in Continuous Aggregates

TimescaleDB 2.24.0 supports PostgreSQL 18's native UUIDv7 in continuous aggregates:

```sql
-- Hypertable with UUIDv7 primary key (PostgreSQL 18)
CREATE TABLE events (
  id uuid DEFAULT uuidv7(),
  time timestamptz NOT NULL,
  event_type text NOT NULL,
  payload jsonb,
  PRIMARY KEY (id, time)
);

SELECT create_hypertable('events', 'time');

-- Continuous aggregate can now reference UUIDv7 columns
CREATE MATERIALIZED VIEW event_counts
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  event_type,
  count(*) AS event_count,
  count(DISTINCT id) AS unique_events  -- UUIDv7 works here now
FROM events
GROUP BY bucket, event_type
WITH NO DATA;
```

### 4. Bloom Filter Sparse Index Changes

TimescaleDB 2.24.0 modifies bloom filter behavior for sparse indexes:

```sql
-- Bloom filters for sparse data patterns
-- Useful for columns with many NULLs or low cardinality

CREATE TABLE logs (
  time timestamptz NOT NULL,
  level text,
  message text,
  error_code text,  -- Often NULL, sparse
  trace_id uuid     -- Often NULL, sparse
);

SELECT create_hypertable('logs', 'time');

-- Configure compression with bloom filter for sparse columns
ALTER TABLE logs SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'level',
  timescaledb.compress_orderby = 'time DESC',
  -- Bloom filter helps find rare non-NULL values
  timescaledb.compress_bloomfilter = 'error_code, trace_id'
);

-- Query efficiency: Bloom filter skips segments without matches
SELECT * FROM logs
WHERE error_code = 'E500'
  AND time > now() - INTERVAL '1 day';
-- Scans only segments where bloom filter indicates possible match
```

**When to use bloom filters:**
- Sparse columns (many NULLs)
- Rare value queries (finding errors in logs)
- High-cardinality exact match queries
- NOT useful for range queries

## Hypertable Design

### Creating Hypertables

```sql
-- Standard time-series table
CREATE TABLE metrics (
  time timestamptz NOT NULL,
  device_id uuid NOT NULL,
  metric_name text NOT NULL,
  value double precision,
  metadata jsonb DEFAULT '{}'
);

-- Convert to hypertable
SELECT create_hypertable('metrics', 'time',
  chunk_time_interval => INTERVAL '1 day',  -- Chunk size
  create_default_indexes => true
);

-- With space partitioning (for high-cardinality dimensions)
SELECT create_hypertable('metrics', 'time',
  partitioning_column => 'device_id',
  number_partitions => 4,
  chunk_time_interval => INTERVAL '1 day'
);
```

### Chunk Interval Selection

| Data Volume | Suggested Interval | Rationale |
|-------------|-------------------|-----------|
| < 1GB/day | 1 week | Fewer chunks, simpler management |
| 1-10 GB/day | 1 day | Balance between size and granularity |
| 10-100 GB/day | 6 hours | Faster compression, better parallelism |
| > 100 GB/day | 1 hour | Maximum parallelism, fast drops |

```sql
-- Adjust chunk interval
SELECT set_chunk_time_interval('metrics', INTERVAL '6 hours');

-- View current chunks
SELECT show_chunks('metrics', older_than => INTERVAL '1 day');
```

### Primary Key Design

```sql
-- CORRECT: Time column in primary key for efficient chunk pruning
CREATE TABLE events (
  id uuid DEFAULT uuidv7(),
  time timestamptz NOT NULL,
  event_type text NOT NULL,
  PRIMARY KEY (id, time)  -- time included
);

-- WRONG: Time not in primary key (inefficient queries)
CREATE TABLE events_bad (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  time timestamptz NOT NULL  -- Not in PK
);
```

## Compression Strategy

### Enabling Compression

```sql
-- Configure compression
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',      -- Group by this
  timescaledb.compress_orderby = 'time DESC',         -- Sort order
  timescaledb.compress_chunk_time_interval = '1 day'  -- Recompress interval
);

-- Manual compression
SELECT compress_chunk(c)
FROM show_chunks('metrics', older_than => INTERVAL '7 days') c;

-- Automatic compression policy
SELECT add_compression_policy('metrics', INTERVAL '7 days');
```

### Segment By Selection

```sql
-- GOOD: Segment by commonly filtered dimension
-- Queries filter on device_id get excellent performance
ALTER TABLE metrics SET (
  timescaledb.compress_segmentby = 'device_id'
);

-- GOOD: Multiple segment columns for flexible queries
ALTER TABLE metrics SET (
  timescaledb.compress_segmentby = 'device_id, metric_name'
);

-- BAD: High cardinality segment (too many segments)
-- Don't segment by user_id if you have millions of users
ALTER TABLE events SET (
  timescaledb.compress_segmentby = 'user_id'  -- Too many segments!
);

-- BETTER for high cardinality: Include in orderby instead
ALTER TABLE events SET (
  timescaledb.compr