migrate-postgres-tables-to-hypertables

PostgreSQL to TimescaleDB Hypertable Migration

Migrate identified PostgreSQL tables to TimescaleDB hypertables with optimal configuration, migration planning and validation.

Prerequisites: Tables already identified as hypertable candidates (use companion "find-hypertable-candidates" skill if needed).

Step 1: Optimal Configuration

Partition Column Selection

-- Find potential partition columns
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table_name'
  AND data_type IN ('timestamp', 'timestamptz', 'bigint', 'integer', 'date')
ORDER BY ordinal_position;

Requirements: Time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or sequential integer (INT/BIGINT)

Should represent when the event actually occurred or sequential ordering.

Common choices:

• timestamp, created_at, event_time - when event occurred
• id, sequence_number - auto-increment (for sequential data without timestamps)
• ingested_at - less ideal, only if primary query dimension
• updated_at - AVOID (records updated out of order, breaks chunk distribution) unless primary query dimension

Special Case: table with BOTH ID AND Timestamp

When table has sequential ID (PK) AND timestamp that correlate:

-- Partition by ID, enable minmax sparse indexes on timestamp
SELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);
ALTER TABLE orders SET (
    timescaledb.sparse_index = 'minmax(created_at),...'
);

Sparse indexes on time column enable skipping compressed blocks outside queried time ranges.

Use when: ID correlates with time (newer records have higher IDs), need ID-based lookups, time queries also common

Chunk Interval Selection

-- Ensure statistics are current
ANALYZE your_table_name;

-- Estimate index size per time unit
WITH time_range AS (
    SELECT
        MIN(timestamp_column) as min_time,
        MAX(timestamp_column) as max_time,
        EXTRACT(EPOCH FROM (MAX(timestamp_column) - MIN(timestamp_column)))/3600 as total_hours
    FROM your_table_name
),
total_index_size AS (
    SELECT SUM(pg_relation_size(indexname::regclass)) as total_index_bytes
    FROM pg_stat_user_indexes
    WHERE schemaname||'.'||tablename = 'your_schema.your_table_name'
)
SELECT
    pg_size_pretty(tis.total_index_bytes / tr.total_hours) as index_size_per_hour
FROM time_range tr, total_index_size tis;