setup-timescaledb-hypertables

Design time-series schemas with hypertables, optimal partitioning, indexing, and compression for insert-heavy data.

• Time-series data with time-based partitioning
• Hypertables with segment_by and order_by for efficient compression
• Sparse indexes for range filtering on compressed data
• Chunk time intervals for performance tuning

• Use time-based or sequential partition columns
• Prefer single segment_by columns with high row density (>100 rows/value/chunk)
• Order_by must support natural time-series progression
• Sparse indexes use minmax for range queries on numeric/temporal columns
• Enable compression by default unless using vector columns
• Chunk time interval adjusts based on data volume and frequency
• Avoid indexing in segment_by or order_by columns
• Sparse index format: type(column_name), e.g., minmax(value_1)

1. 1.Choose time-based or sequential partition column (e.g., timestamp)
2. 2.Set segment_by to frequently filtered, high-density column (e.g., device_id)
3. 3.Set order_by to support time progression (e.g., timestamp DESC)
4. 4.Add minmax sparse index for range queries (e.g., minmax(value_1))
5. 5.Adjust chunk time interval based on data volume (e.g., 1h–1d for high freq)
6. 6.Never index columns in segment_by or order_by