timescaledb_toolkit

pig build pkg timescaledb_toolkit;		# build rpm / deb with pig

pig repo add pgdg pigsty -u   # add both repo and update cache

pig install timescaledb_toolkit;		# install via package name, for the active PG version

pig install timescaledb_toolkit -v 18;   # install for PG 18
pig install timescaledb_toolkit -v 17;   # install for PG 17
pig install timescaledb_toolkit -v 16;   # install for PG 16
pig install timescaledb_toolkit -v 15;   # install for PG 15

CREATE EXTENSION timescaledb_toolkit;

-- Estimate unique users per day
SELECT 
    date_trunc('day', timestamp) as day,
    distinct_count(hyperloglog(64, user_id)) as unique_users
FROM events 
GROUP BY day;

-- Combine counts across partitions
SELECT distinct_count(rollup(hll))
FROM (SELECT hyperloglog(32, session_id) as hll FROM events_2023
      UNION ALL 
      SELECT hyperloglog(32, session_id) FROM events_2024) t;

-- Track response time percentiles
SELECT 
    service_name,
    approx_percentile(0.50, tdigest(100, response_time)) as p50,
    approx_percentile(0.95, tdigest(100, response_time)) as p95,
    approx_percentile(0.99, tdigest(100, response_time)) as p99
FROM api_metrics 
GROUP BY service_name;

-- Hourly percentiles with continuous aggregation
CREATE MATERIALIZED VIEW hourly_percentiles AS
SELECT 
    time_bucket('1 hour', timestamp) as hour,
    tdigest(200, response_time) as digest
FROM requests GROUP BY hour;

-- CPU utilization percentiles with 1% max error
SELECT 
    host_id,
    approx_percentile(0.95, uddsketch(100, 0.01, cpu_percent)) as p95_cpu,
    error(uddsketch(100, 0.01, cpu_percent)) as actual_error
FROM system_metrics 
GROUP BY host_id;

-- Request rate calculation
SELECT 
    time_bucket('5 min', timestamp) as bucket,
    rate(counter_agg(timestamp, request_count)) as requests_per_sec,
    delta(counter_agg(timestamp, request_count)) as total_requests
FROM metrics 
GROUP BY bucket;

-- Extrapolated rate for partial buckets
SELECT 
    extrapolated_rate(
        counter_agg(timestamp, bytes_sent, 
                   bounds => time_bucket_range('1 hour', timestamp))
    ) as bytes_per_second
FROM network_stats;

-- Temperature change analysis
SELECT 
    sensor_id,
    delta(gauge_agg(timestamp, temperature)) as temp_delta,
    rate(gauge_agg(timestamp, temperature)) as temp_rate_per_sec
FROM weather_data 
GROUP BY sensor_id;

-- Weighted average for irregular sensor readings
SELECT 
    device_id,
    average(time_weight('LOCF', timestamp, sensor_value)) as weighted_avg,
    average(time_weight('Linear', timestamp, sensor_value)) as linear_avg
FROM iot_readings 
GROUP BY device_id;

-- Combining multiple time ranges
SELECT average(rollup(tw))
FROM (SELECT time_weight('LOCF', ts, val) as tw FROM readings_2023
      UNION ALL
      SELECT time_weight('LOCF', ts, val) FROM readings_2024) t;

-- Reduce 100K points to 1K for visualization  
SELECT time, value
FROM unnest((
    SELECT lttb(timestamp, price, 1000)
    FROM stock_prices 
    WHERE symbol = 'AAPL'
));

-- Smooth daily data to weekly resolution
SELECT time, value 
FROM unnest((
    SELECT asap_smooth(date, daily_sales, 52)
    FROM sales_data
    WHERE date >= '2023-01-01'
));

-- Multi-variable analysis
SELECT 
    -- Basic statistics
    average(stats_agg(response_time)) as avg_response,
    stddev(stats_agg(response_time)) as response_stddev,
    
    -- Regression analysis
    slope(stats_agg(response_time, request_size)) as size_impact,
    corr(stats_agg(response_time, request_size)) as correlation,
    determination_coeff(stats_agg(response_time, request_size)) as r_squared
FROM performance_data;

-- Create and manipulate timevector
CREATE VIEW cpu_series AS 
SELECT host_id, timevector(timestamp, cpu_percent) as ts
FROM system_metrics GROUP BY host_id;

-- Chain operations on timevector
SELECT host_id, unnest(lttb(ts, 100)) 
FROM cpu_series;

CREATE MATERIALIZED VIEW hourly_analytics AS
SELECT 
    time_bucket('1 hour', timestamp) as hour,
    service_name,
    tdigest(100, response_time) as response_digest,
    counter_agg(timestamp, request_count) as request_counter,
    hyperloglog(64, user_id) as unique_users
FROM api_events
GROUP BY hour, service_name;

-- Query pre-computed aggregates
SELECT 
    hour,
    approx_percentile(0.95, response_digest) as p95_response,
    rate(request_counter) as req_per_sec,
    distinct_count(unique_users) as unique_users
FROM hourly_analytics
WHERE hour >= NOW() - INTERVAL '24 hours';

-- Step 1: Create aggregates
CREATE TABLE daily_summaries AS
SELECT 
    date_trunc('day', timestamp) as day,
    tdigest(200, response_time) as response_digest,
    stats_agg(response_time, request_size) as stats
FROM requests GROUP BY day;

-- Step 2: Multiple analyses from same data
SELECT 
    day,
    approx_percentile(0.50, response_digest) as median,
    approx_percentile(0.99, response_digest) as p99,
    average(stats) as avg_response,
    slope(stats) as size_correlation
FROM daily_summaries;