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

-- 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'
);

-- 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'
);

-- 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;

-- 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

-- 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'
);

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

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

-- 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
);

-- 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');

-- 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.compress_segmentby = 'event_type',
  timescaledb.compress_orderby = 'user_id, time DESC'
);

-- Time descending for "most recent" queries
ALTER TABLE metrics SET (
  timescaledb.compress_orderby = 'time DESC'
);

-- Composite order for specific query patterns
ALTER TABLE logs SET (
  timescaledb.compress_orderby = 'level, time DESC'
);
-- Benefits: WHERE level = 'error' ORDER BY time DESC

-- Include frequently filtered columns
ALTER TABLE events SET (
  timescaledb.compress_orderby = 'device_id, time DESC'
);

-- Basic continuous aggregate
CREATE MATERIALIZED VIEW hourly_stats
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  avg(value) AS avg_value,
  min(value) AS min_value,
  max(value) AS max_value,
  count(*) AS sample_count
FROM metrics
GROUP BY bucket, device_id
WITH NO DATA;

-- Hierarchical aggregates (aggregate of aggregate)
CREATE MATERIALIZED VIEW daily_stats
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', bucket) AS bucket,
  device_id,
  avg(avg_value) AS avg_value,
  min(min_value) AS min_value,
  max(max_value) AS max_value,
  sum(sample_count) AS sample_count
FROM hourly_stats
GROUP BY 1, device_id
WITH NO DATA;

-- Add refresh policy
SELECT add_continuous_aggregate_policy('hourly_stats',
  start_offset => INTERVAL '3 days',   -- Refresh this far back
  end_offset => INTERVAL '1 hour',      -- Don't refresh latest (incomplete)
  schedule_interval => INTERVAL '1 hour'
);

-- Real-time aggregates (include unrefreshed data)
ALTER MATERIALIZED VIEW hourly_stats SET (
  timescaledb.materialized_only = false  -- Include real-time data
);

-- Force refresh
CALL refresh_continuous_aggregate('hourly_stats',
  '2026-01-01'::timestamptz,
  '2026-01-02'::timestamptz
);

-- Continuous aggregate with built-in compression
CREATE MATERIALIZED VIEW hourly_metrics
WITH (
  timescaledb.continuous,
  timescaledb.compress = true  -- New in 2.24.0
) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  avg(temperature) AS avg_temp,
  percentile_agg(temperature) AS temp_pct  -- For percentiles later
FROM sensor_readings
GROUP BY bucket, device_id
WITH NO DATA;

-- Add compression policy for the aggregate
SELECT add_compression_policy('hourly_metrics', INTERVAL '30 days');

-- Combined with refresh policy
SELECT add_continuous_aggregate_policy('hourly_metrics',
  start_offset => INTERVAL '7 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

-- Drop old raw data (keep aggregates)
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- View retention policies
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- Remove retention policy
SELECT remove_retention_policy('metrics');

-- Pattern: Raw → Hourly → Daily → Archive

-- 1. Raw data: Keep 7 days uncompressed
-- 2. Raw data: Keep 30 days compressed
-- 3. Raw data: Drop after 90 days

-- 4. Hourly aggregates: Keep 1 year
-- 5. Daily aggregates: Keep forever

-- Implementation:
-- Raw data policies
SELECT add_compression_policy('metrics', INTERVAL '7 days');
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- Hourly aggregate policies
SELECT add_compression_policy('hourly_stats', INTERVAL '30 days');
SELECT add_retention_policy('hourly_stats', INTERVAL '1 year');

-- Daily stats: No retention (keep forever)
SELECT add_compression_policy('daily_stats', INTERVAL '90 days');

-- Recent data (uses index)
SELECT * FROM metrics
WHERE time > now() - INTERVAL '1 hour'
  AND device_id = $1
ORDER BY time DESC
LIMIT 100;

-- Time range with aggregation
SELECT
  time_bucket('5 minutes', time) AS bucket,
  avg(value) AS avg_value
FROM metrics
WHERE time BETWEEN $1 AND $2
  AND device_id = $3
GROUP BY bucket
ORDER BY bucket;

-- Last value per device
SELECT DISTINCT ON (device_id)
  device_id,
  time,
  value
FROM metrics
WHERE time > now() - INTERVAL '1 day'
ORDER BY device_id, time DESC;

-- Query aggregate instead of raw data
SELECT * FROM hourly_stats
WHERE bucket > now() - INTERVAL '7 days'
  AND device_id = $1
ORDER BY bucket DESC;

-- Real-time aggregate (includes unrefreshed data)
SELECT * FROM hourly_stats
WHERE bucket > now() - INTERVAL '1 hour';
-- Automatically combines materialized + real-time data

-- Use percentile_agg for continuous aggregates
CREATE MATERIALIZED VIEW metrics_percentiles
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  percentile_agg(value) AS value_pct,  -- Aggregate percentile state
  stats_agg(value) AS value_stats       -- Statistical aggregates
FROM metrics
GROUP BY bucket, device_id;

-- Query percentiles from aggregate
SELECT
  bucket,
  device_id,
  approx_percentile(0.50, value_pct) AS median,
  approx_percentile(0.95, value_pct) AS p95,
  approx_percentile(0.99, value_pct) AS p99,
  average(value_stats) AS avg,
  stddev(value_stats) AS stddev
FROM metrics_percentiles
WHERE bucket > now() - INTERVAL '24 hours';

-- create_hypertable creates this by default:
-- CREATE INDEX ON metrics (time DESC);

-- Add composite indexes for common queries
CREATE INDEX idx_metrics_device_time ON metrics (device_id, time DESC);

-- Partial indexes for specific patterns
CREATE INDEX idx_metrics_errors ON metrics (time DESC)
WHERE value > threshold;

-- Indexes are not used on compressed chunks
-- Query planner uses:
-- 1. Chunk exclusion (time range)
-- 2. Segment filtering (compress_segmentby columns)
-- 3. Orderby optimization (compress_orderby columns)

-- Design compression settings for query patterns, not indexes
ALTER TABLE metrics SET (
  timescaledb.compress_segmentby = 'device_id',  -- Filter column
  timescaledb.compress_orderby = 'time DESC'      -- Sort column
);

-- 1. Ensure time column exists and is NOT NULL
ALTER TABLE legacy_metrics ALTER COLUMN time SET NOT NULL;

-- 2. Convert to hypertable
SELECT create_hypertable('legacy_metrics', 'time',
  migrate_data => true,
  chunk_time_interval => INTERVAL '1 day'
);

-- 3. Add compression
ALTER TABLE legacy_metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby = 'time DESC'
);

-- 4. Add policies
SELECT add_compression_policy('legacy_metrics', INTERVAL '7 days');
SELECT add_retention_policy('legacy_metrics', INTERVAL '90 days');

-- 1. Install extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 2. Verify version
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
-- Should show 2.24.0

-- 3. Check PostgreSQL compatibility
SELECT timescaledb_information.version();

<!-- TIMESCALEDB_IMPLEMENTATION:START -->

undefined