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;

Target: Indexes of recent chunks "Primary key (id) doesn't include partition column (timestamp). Must modify to PRIMARY KEY (id, timestamp) to convert to hypertable. This may break application code. Is this acceptable?" > "Unique constraint (id) doesn't include partition column (timestamp). Must modify to UNIQUE (id, timestamp) to convert to hypertable. This may break application code. Is this acceptable?"

If the user accepts, modify the constraint:

BEGIN;
ALTER TABLE your_table_name DROP CONSTRAINT existing_pk_name;
ALTER TABLE your_table_name ADD PRIMARY KEY (existing_columns, partition_column);
COMMIT;

If the user does not accept, you should NOT migrate the table.

IMPORTANT: DO NOT modify the primary key/unique constraint without user permission.

Compression Configuration

For detailed segmentby and orderby selection, see "setup-timescaledb-hypertables" skill. Quick reference:

segment_by: Most common WHERE filter with >100 rows per value per chunk

• IoT: device_id
• Finance: symbol
• Analytics: user_id or session_id

-- Analyze cardinality for segment_by selection
SELECT column_name, COUNT(DISTINCT column_name) as unique_values,
       ROUND(COUNT(*)::float / COUNT(DISTINCT column_name), 2) as avg_rows_per_value
FROM your_table_name GROUP BY column_name;

orderby: Usually timestamp DESC. The (segmentby, order_by) combination should form a natural time-series progression.

• If column has INTERVAL '7 days');

## Step 2: Migration Planning

### Pre-Migration Checklist

- [ ] Partition column selected
- [ ] Chunk interval calculated (or using default)
- [ ] PK includes partition column OR user approved modification
- [ ] No Hypertable→Hypertable foreign keys
- [ ] Unique constraints include partition column
- [ ] Created compression configuration (segment_by, order_by, sparse indexes, compression policy)
- [ ] Maintenance window scheduled / backup created.

### Migration Options

#### Option 1: In-Place (Tables  INTERVAL '7 days',
    if_not_exists => TRUE
);

-- Configure compression
ALTER TABLE your_table_name SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC',
    timescaledb.sparse_index = 'minmax(value_1),...'
);

-- Adjust `after` parameter based on update patterns
CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

Option 2: Blue-Green (Tables > 1GB)

-- 1. Create new hypertable
CREATE TABLE your_table_name_new (LIKE your_table_name INCLUDING ALL);

-- 2. Convert to hypertable
SELECT create_hypertable('your_table_name_new', 'timestamp_column');

-- 3. Configure compression
ALTER TABLE your_table_name_new SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC'
);

-- 4. Migrate data in batches
INSERT INTO your_table_name_new
SELECT * FROM your_table_name
WHERE timestamp_column >= '2024-01-01' AND timestamp_column 1GB/10M rows):** Use blue-green migration, migrate during off-peak, test on subset first

## Step 3: Performance Validation

### Chunk & Compression Analysis

```sql
-- View chunks and compression
SELECT
    chunk_name,
    pg_size_pretty(total_bytes) as size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND((total_bytes - compressed_total_bytes::numeric) / total_bytes * 100, 1) as compression_pct,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
ORDER BY range_start DESC;

Look for:

• Consistent chunk sizes (within 2x)
• Compression >90% for time-series
• Recent chunks uncompressed
• Chunk indexes = NOW() - INTERVAL '1 day';

-- 2. Entity + time query (benefits from segmentby) EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM yourtablename WHERE entityid = 'X' AND timestamp >= NOW() - INTERVAL '1 week';

-- 3. Aggregation (benefits from columnstore) EXPLAIN (ANALYZE, BUFFERS) SELECT DATETRUNC('hour', timestamp), entityid, COUNT(*), AVG(value) FROM yourtablename WHERE timestamp >= NOW() - INTERVAL '1 month' GROUP BY 1, 2;

**✅ Good signs:**

- "Chunks excluded during startup: X" in EXPLAIN plan
- "Custom Scan (ColumnarScan)" for compressed data
- Lower "Buffers: shared read" in EXPLAIN ANALYZE plan than pre-migration
- Faster execution times

**❌ Bad signs:**

- "Seq Scan" on large chunks
- No chunk exclusion messages
- Slower than before migration

### Storage Metrics

```sql
-- Monitor compression effectiveness
SELECT
    hypertable_name,
    pg_size_pretty(total_bytes) as total_size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND(compressed_total_bytes::numeric / total_bytes * 100, 1) as compressed_pct_of_total,
    ROUND((uncompressed_total_bytes - compressed_total_bytes::numeric) /
          uncompressed_total_bytes * 100, 1) as compression_ratio_pct
FROM timescaledb_information.hypertables
WHERE hypertable_name = 'your_table_name';

Monitor:

• compressionratiopct >90% (typical time-series)
• compressedpctof_total growing as data ages
• Size growth slowing significantly vs pre-hypertable
• Decreasing compressionratiopct = poor segment_by

Troubleshooting

Poor Chunk Exclusion

-- Verify chunks are being excluded
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table_name
WHERE timestamp >= '2024-01-01' AND timestamp 100) => Low compression potential.

#### Poor insert performance

Check that you don't have too many indexes. Unused indexes hurt insert performance and should be dropped.

```sql
SELECT
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes
WHERE tablename LIKE '%your_table_name%'
ORDER BY idx_scan DESC;

Look for: Unused indexes via a low idx_scan value. Drop such indexes (but ask user permission).

Ongoing Monitoring

-- Monitor chunk compression status
CREATE OR REPLACE VIEW hypertable_compression_status AS
SELECT
    h.hypertable_name,
    COUNT(c.chunk_name) as total_chunks,
    COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL) as compressed_chunks,
    ROUND(
        COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL)::numeric /
        COUNT(c.chunk_name) * 100, 1
    ) as compression_coverage_pct,
    pg_size_pretty(SUM(c.total_bytes)) as total_size,
    pg_size_pretty(SUM(c.compressed_total_bytes)) as compressed_size
FROM timescaledb_information.hypertables h
LEFT JOIN timescaledb_information.chunks c ON h.hypertable_name = c.hypertable_name
GROUP BY h.hypertable_name;

-- Query this view regularly to monitor compression progress
SELECT * FROM hypertable_compression_status
WHERE hypertable_name = 'your_table_name';

Look for:

• compressioncoveragepct should increase over time as data ages and gets compressed.
• total_chunks should not grow too quickly (more than 10000 becomes a problem).
• You should not see unexpected spikes in totalsize or compressedsize.

Success Criteria

✅ Migration successful when:

• All queries return correct results
• Query performance equal or better
• Compression >90% for older data
• Chunk exclusion working for time queries
• Insert performance acceptable

❌ Investigate if:

• Query performance >20% worse
• Compression <80%
• No chunk exclusion
• Insert performance degraded
• Increased error rates

Focus on high-volume, insert-heavy workloads with time-based access patterns for best ROI.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: timescale
• Source: timescale/pg-aiguide
• License: Apache-2.0

Install and usage instructions live in the source repository linked above.