Use this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table. **Trigger when user asks to:** - Create or design SQL schemas/t

TimescaleDB Complete Setup Instructions for insert heavy data patterns where data is inserted but rarely changed: Time series data (sensors, metrics, system monitoring) Event logs (user events, audit trails, application logs) Transaction records (orders, payments, financial transactions) Sequential data (records with auto incrementing IDs and timestamps) Append only datasets (immutable records, historical data) Step 1: Create Hypertable Compression Decision Enable by default for insert heavy patterns Disable if table has vector type columns (pgvector) indexes on vector columns incompatible with columnstore Partition Column Selection Must be time based (TIMESTAMP/TIMESTAMPTZ/DATE) or integer (INT/BIGINT) with good temporal/sequential distribution. Common patterns: TIME SERIES: timestamp , event time , measured at EVENT LOGS: event time , created at , logged at TRANSACTIONS: created at , transaction time , processed at SEQUENTIAL: id (auto increment when no timestamp), sequence number APPEND ONLY: created at , inserted at , id Less ideal: ingested at (when data entered system use only if it's your primary query dimension) Avoid: updated at (breaks time ordering unless it's primary query dimension) Segment By Column Selection PREFER SINGLE COLUMN multi column rarely optimal. Multi column can only work for highly correlated columns (e.g., metric name + metric type) with sufficient row density. Requirements: Frequently used in WHERE clauses (most common filter) Good row density ( 100 rows per value per chunk) Primary logical partition/grouping Examples: IoT: device id Finance: symbol Metrics: service name , service name, metric type (if sufficient row density), metric name, metric type (if sufficient row density) Analytics: user id if sufficient row density, otherwise session id E commerce: product id if sufficient row density, otherwise category id Row density guidelines: Target: 100 rows per segment by value within each chunk. Poor: <10 rows per segment by value per chunk → choose less granular column What to do with low density columns: prepend to order by column list. Query pattern drives choice: Avoid: timestamps, unique IDs, low density columns (<100 rows/value/chunk), columns rarely used in filtering Order By Column Selection Creates natural time series progression when combined with segment by for optimal compression. Most common: timestamp DESC Examples: IoT/Finance/E commerce: timestamp DESC Metrics: metric name, timestamp DESC (if metric name has too low density for segment by) Analytics: user id, timestamp DESC (user id has too low density for segment by) Alternative patterns: sequence id DESC for event streams with sequence numbers timestamp DESC, event order DESC for sub ordering within same timestamp Low density column handling: If a column has <100 rows per chunk (too low for segment by), prepend it to order by: Example: metric name has 20 rows/chunk → use segment by='service name' , order by='metric name, timestamp DESC' Groups similar values together (all temperature readings, then pressure readings) for better compression Good test: ordering created by (segment by column, order by column) should form a natural time series progression. Values close to each other in the progression should be similar. Avoid in order by: random columns, columns with high variance between adjacent rows, columns unrelated to segment by Compression Sparse Index Selection Sparse indexes enable query filtering on compressed data without decompression. Store metadata per batch (~1000 rows) to eliminate batches that don't match query predicates. Types: minmax: Min/max values per batch for range queries ( , <, BETWEEN) on numeric/temporal columns Use minmax for: price, temperature, measurement, timestamp (range filtering) Use for: minmax for outlier detection (temperature 90). minmax for fields that are highly correlated with segmentby and orderby columns (e.g. if orderby includes created at , minmax on updated at is useful). Avoid: rarely filtered columns. IMPORTANT: NEVER index columns in segmentby or orderby. Orderby columns will always have minmax indexes without any configuration. Configuration: The format is a comma separated list of type of index(column name). Explicit configuration available since v2.22.0 (was auto created since v2.16.0). Chunk Time Interval (Optional) Default: 7 days (use if volume unknown, or ask user). Adjust based on volume: High frequency: 1 hour 1 day Medium: 1 day 1 week Low: 1 week 1 month Good test: recent chunk indexes should fit in less than 25% of RAM. Indexes & Primary Keys Common index patterns composite indexes on an id and timestamp: Important: Only create indexes you'll actually use each has maintenance overhead. Primary key and unique constraints rules: Must include partition column. Option 1: Composite PK with partition column Option 2: Single column PK (only if it's the partition column) Option 3: No PK : strict uniqueness is often not required for insert heavy patterns. Step 2: Compression Policy (Optional) IMPORTANT : If you used tsdb.enable columnstore=true in Step 1, starting with TimescaleDB version 2.23 a columnstore policy is automatically created with after = INTERVAL '7 days' . You only need to call add columnstore policy() if you want to customize the after interval to something other than 7 days. Set after interval for when: data becomes mostly immutable (some updates/backfill OK) AND B tree indexes aren't needed for queries (less common criterion). Step 3: Retention Policy IMPORTANT: Don't guess ask user or comment out if unknown. Step 4: Create Continuous Aggregates Use different aggregation intervals for different uses. Short term (Minutes/Hours) For up to the minute dashboards on high frequency data. Long term (Days/Weeks/Months) For long term reporting and analytics. Step 5: Aggregate Refresh Policies Set up refresh policies based on your data freshness requirements. start offset: Usually omit (refreshes all). Exception: If you don't care about refreshing data older than X (see below). With retention policy on raw data: match the retention policy. end offset: Set beyond active update window (e.g., 15 min if data usually arrives within 10 min). Data newer than end offset won't appear in queries without real time aggregation. If you don't know your update window, use the size of the time bucket in the query, but not less than 5 minutes. schedule interval: Set to the same value as the end offset but not more than 1 hour. Hourly frequent refresh for dashboards: Daily less frequent for reports: Use start offset only if you don't care about refreshing old data Use for high volume systems where query accuracy on older data doesn't matter: IMPORTANT: you MUST set a start offset to be less than the retention policy on raw data. By default, set the start offset equal to the retention policy. If the retention policy is commented out, comment out the start offset as well. like this: Step 6: Real Time Aggregation (Optional) Real time combines materialized + recent raw data at query time. Provides up to date results at the cost of higher query latency. More useful for fine grained aggregates (e.g., minutely) than coarse ones (e.g., daily/monthly) since large buckets will be mostly incomplete with recent data anyway. Disabled by default in v2.13+, before that it was enabled by default. Use when: Need data newer than end offset, up to minute dashboards, can tolerate higher query latency Disable when: Performance critical, refresh policies sufficient, high query volume, missing and stale data for recent data is acceptable Enable for current results (higher query cost): Disable for performance (but with stale results): Step 7: Compress Aggregates Rule: segment by = ALL GROUP BY columns except time bucket, order by = time bucket DESC Step 8: Aggregate Retention Aggregates are typically kept longer than raw data. IMPORTANT: Don't guess ask user or you MUST comment out if unknown . Step 9: Performance Indexes on Continuous Aggregates Index strategy: Analyze WHERE clauses in common queries → Create indexes matching filter columns + time ordering Pattern: (filter column, bucket DESC) supports WHERE filter column = X AND bucket = Y ORDER BY bucket DESC Examples: Multi column filters: Create composite indexes for WHERE entity id = X AND category = Y : Important: Only create indexes you'll actually use each has maintenance overhead. Step 10: Optional Enhancements Space Partitioning (NOT RECOMMENDED) Only for query patterns where you ALWAYS filter by the space partition column with expert knowledge and extensive benchmarking. STRONGLY prefer time only partitioning. Step 11: Verify Configuration Performance Guidelines Chunk size: Recent chunk indexes should fit in less than 25% of RAM Compression: Expect 90%+ reduction (10x) with proper columnstore config Query optimization: Use continuous aggregates for historical queries and dashboards Memory: Run timescaledb tune for self hosting (auto configured on cloud) Schema Best Practices Do's and Don'ts ✅ Use TIMESTAMPTZ NOT timestamp ✅ Use = and < NOT BETWEEN for timestamps ✅ Use TEXT with constraints NOT char(n) / varchar(n) ✅ Use snake case NOT CamelCase ✅ Use BIGINT GENERATED ALWAYS AS IDENTITY NOT SERIAL ✅ Use BIGINT for IDs by default over INTEGER or SMALLINT ✅ Use DOUBLE PRECISION by default over REAL / FLOAT ✅ Use NUMERIC NOT MONEY ✅ Use NOT EXISTS NOT NOT IN ✅ Use time bucket() or date trunc() NOT timestamp(0) for truncation API Reference (Current vs Deprecated) Deprecated Parameters → New Parameters: timescaledb.compress → timescaledb.enable columnstore timescaledb.compress segmentby → timescaledb.segmentby timescaledb.compress orderby → timescaledb.orderby Deprecated Functions → New Functions: add compression policy() → add columnstore policy() remove compression policy() → remove columnstore policy() compress chunk() → convert to columnstore() (use with CALL , not SELECT ) decompress chunk() → convert to rowstore() (use with CALL , not SELECT ) Compression Stats (use functions, not views): Use function: hypertable compression stats('table name') Use function: chunk compression stats(' timescaledb internal. hyper X Y chunk') Note: Views like columnstore settings may not be available in all versions; use functions instead Manual Compression Example: Questions to Ask User 1. What kind of data will you be storing? 2. How do you expect to use the data? 3. What queries will you run? 4. How long to keep the data? 5. Column types if unclear

setup-timescaledb-hypertables