{"id":"migrate-postgres-tables-to-hypertables","name":"migrate-postgres-tables-to-hypertables","summary":"このスキルを使って、特定されたPostgreSQLテーブルを最適な構成と検証でTimescale/TimescaleDBハイパーテーブルに移行します。","body":"# PostgreSQL to TimescaleDB Hypertable Migration\n\nMigrate identified PostgreSQL tables to TimescaleDB hypertables with optimal configuration, migration planning and validation.\n\n**Prerequisites**: Tables already identified as hypertable candidates (use companion \"find-hypertable-candidates\" skill if needed).\n\n## Step 1: Optimal Configuration\n\n### Partition Column Selection\n\n```sql\n-- Find potential partition columns\nSELECT column_name, data_type, is_nullable\nFROM information_schema.columns\nWHERE table_name = 'your_table_name'\n  AND data_type IN ('timestamp', 'timestamptz', 'bigint', 'integer', 'date')\nORDER BY ordinal_position;\n```\n\n**Requirements:** Time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or sequential integer (INT/BIGINT)\n\nShould represent when the event actually occurred or sequential ordering.\n\n**Common choices:**\n\n- `timestamp`, `created_at`, `event_time` - when event occurred\n- `id`, `sequence_number` - auto-increment (for sequential data without timestamps)\n- `ingested_at` - less ideal, only if primary query dimension\n- `updated_at` - AVOID (records updated out of order, breaks chunk distribution) unless primary query dimension\n\n#### Special Case: table with BOTH ID AND Timestamp\n\nWhen table has sequential ID (PK) AND timestamp that correlate:\n\n```sql\n-- Partition by ID, enable minmax sparse indexes on timestamp\nSELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);\nALTER TABLE orders SET (\n    timescaledb.sparse_index = 'minmax(created_at),...'\n);\n```\n\nSparse indexes on time column enable skipping compressed blocks outside queried time ranges.\n\nUse when: ID correlates with time (newer records have higher IDs), need ID-based lookups, time queries also common\n\n### Chunk Interval Selection\n\n```sql\n-- Ensure statistics are current\nANALYZE your_table_name;\n\n-- Estimate index size per time unit\nWITH time_range AS (\n    SELECT\n        MIN(timestamp_column) as min_time,\n        MAX(timestamp_column) as max_time,\n        EXTRACT(EPOCH FROM (MAX(timestamp_column) - MIN(timestamp_column)))/3600 as total_hours\n    FROM your_table_name\n),\ntotal_index_size AS (\n    SELECT SUM(pg_relation_size(indexname::regclass)) as total_index_bytes\n    FROM pg_stat_user_indexes\n    WHERE schemaname||'.'||tablename = 'your_schema.your_table_name'\n)\nSELECT\n    pg_size_pretty(tis.total_index_bytes / tr.total_hours) as index_size_per_hour\nFROM time_range tr, total_index_size tis;\n```\n\n**Target:** Indexes of recent chunks < 25% of RAM\n**Default:** IMPORTANT: Keep default of 7 days if unsure\n**Range:** 1 hour minimum, 30 days maximum\n\n**Example:** 32GB RAM → target 8GB for recent indexes. If index_size_per_hour = 200MB:\n\n- 1 hour chunks: 200MB chunk index size × 40 recent = 8GB ✓\n- 6 hour chunks: 1.2GB chunk index size × 7 recent = 8.4GB ✓\n- 1 day chunks: 4.8GB chunk index size × 2 recent = 9.6GB ⚠️\n  Choose largest interval keeping 2+ recent chunk indexes under target.\n\n### Primary Key/ Unique Constraints Compatibility\n\n```sql\n-- Check existing primary key/ unique constraints\nSELECT conname, pg_get_constraintdef(oid) as definition\nFROM pg_constraint\nWHERE conrelid = 'your_table_name'::regclass AND contype = 'p' OR contype = 'u';\n```\n\n**Rules:** PK/UNIQUE must include partition column\n\n**Actions:**\n\n1. **No PK/UNIQUE:** No changes needed\n2. **PK/UNIQUE includes partition column:** No changes needed\n3. **PK/UNIQUE excludes partition column:** ⚠️ **ASK USER PERMISSION** to modify PK/UNIQUE\n\n**Example: user prompt if needed:**\n\n> \"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?\"\n> \"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?\"\n\nIf the user accepts, modify the constraint:\n\n```sql\nBEGIN;\nALTER TABLE your_table_name DROP CONSTRAINT existing_pk_name;\nALTER TABLE your_table_name ADD PRIMARY KEY (existing_columns, partition_column);\nCOMMIT;\n```\n\nIf the user does not accept, you should NOT migrate the table.\n\nIMPORTANT: DO NOT modify the primary key/unique constraint without user permission.\n\n### Compression Configuration\n\nFor detailed segment_by and order_by selection, see \"setup-timescaledb-hypertables\" skill. Quick reference:\n\n**segment_by:** Most common WHERE filter with >100 rows per value per chunk\n\n- IoT: `device_id`\n- Finance: `symbol`\n- Analytics: `user_id` or `session_id`\n\n```sql\n-- Analyze cardinality for segment_by selection\nSELECT column_name, COUNT(DISTINCT column_name) as unique_values,\n       ROUND(COUNT(*)::float / COUNT(DISTINCT column_name), 2) as avg_rows_per_value\nFROM your_table_name GROUP BY column_name;\n```\n\n**order_by:** Usually `timestamp DESC`. The (segment_by, order_by) combination should form a natural time-series progression.\n\n- If column has <100 rows/chunk (too low for segment_by), prepend to order_by: `order_by='low_density_col, timestamp DESC'`\n\n**sparse indexes:** add minmax on the columns that are used in the WHERE clauses but are not in the segment_by or order_by. Use minmax for columns used in range queries.\n\n```sql\nALTER TABLE your_table_name SET (\n    timescaledb.enable_columnstore,\n    timescaledb.segmentby = 'entity_id',\n    timescaledb.orderby = 'timestamp DESC'\n    timescaledb.sparse_index = 'minmax(value_1),...'\n);\n\n-- Compress after data unlikely to change (adjust `after` parameter based on update patterns)\nCALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');\n```\n\n## Step 2: Migration Planning\n\n### Pre-Migration Checklist\n\n- [ ] Partition column selected\n- [ ] Chunk interval calculated (or using default)\n- [ ] PK includes partition column OR user approved modification\n- [ ] No Hypertable→Hypertable foreign keys\n- [ ] Unique constraints include partition column\n- [ ] Created compression configuration (segment_by, order_by, sparse indexes, compression policy)\n- [ ] Maintenance window scheduled / backup created.\n\n### Migration Options\n\n#### Option 1: In-Place (Tables < 1GB)\n\n```sql\n-- Enable extension\nCREATE EXTENSION IF NOT EXISTS timescaledb;\n\n-- Convert to hypertable (locks table)\nSELECT create_hypertable(\n    'your_table_name',\n    'timestamp_column',\n    chunk_time_interval => INTERVAL '7 days',\n    if_not_exists => TRUE\n);\n\n-- Configure compression\nALTER TABLE your_table_name SET (\n    timescaledb.enable_columnstore,\n    timescaledb.segmentby = 'entity_id',\n    timescaledb.orderby = 'timestamp DESC',\n    timescaledb.sparse_index = 'minmax(value_1),...'\n);\n\n-- Adjust `after` parameter based on update patterns\nCALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');\n```\n\n#### Option 2: Blue-Green (Tables > 1GB)\n\n```sql\n-- 1. Create new hypertable\nCREATE TABLE your_table_name_new (LIKE your_table_name INCLUDING ALL);\n\n-- 2. Convert to hypertable\nSELECT create_hypertable('your_table_name_new', 'timestamp_column');\n\n-- 3. Configure compression\nALTER TABLE your_table_name_new SET (\n    timescaledb.enable_columnstore,\n    timescaledb.segmentby = 'entity_id',\n    timescaledb.orderby = 'timestamp DESC'\n);\n\n-- 4. Migrate data in batches\nINSERT INTO your_table_name_new\nSELECT * FROM your_table_name\nWHERE timestamp_column >= '2024-01-01' AND timestamp_column < '2024-02-01';\n-- Repeat for each time range\n\n-- 4. Enter maintenance window and do the following:\n\n-- 5. Pause modification of the old table.\n\n-- 6. Copy over the most recent data from the old table to the new table.\n\n-- 7. Swap tables\nBEGIN;\nALTER TABLE your_table_name RENAME TO your_table_name_old;\nALTER TABLE your_table_name_new RENAME TO your_table_name;\nCOMMIT;\n\n-- 8. Exit maintenance window.\n\n-- 9. (sometime much later) Drop old table after validation\n-- DROP TABLE your_table_name_old;\n```\n\n### Common Issues\n\n#### Foreign Keys\n\n```sql\n-- Check foreign keys\nSELECT conname, confrelid::regclass as referenced_table\nFROM pg_constraint\nWHERE (conrelid = 'your_table_name'::regclass\n    OR confrelid = 'your_table_name'::regclass)\n  AND contype = 'f';\n```\n\n**Supported:** Plain→Hypertable, Hypertable→Plain\n**NOT supported:** Hypertable→Hypertable\n\n⚠️ **CRITICAL:** Hypertable→Hypertable FKs must be dropped (enforce in application). **ASK USER PERMISSION**. If no, **STOP MIGRATION**.\n\n#### Large Table Migration Time\n\n```sql\n-- Rough estimate: ~75k rows/second\nSELECT\n    pg_size_pretty(pg_total_relation_size(tablename)) as size,\n    n_live_tup as rows,\n    ROUND(n_live_tup / 75000.0 / 60, 1) as estimated_minutes\nFROM pg_stat_user_tables\nWHERE tablename = 'your_table_name';\n```\n\n**Solutions for large tables (>1GB/10M rows):** Use blue-green migration, migrate during off-peak, test on subset first\n\n## Step 3: Performance Validation\n\n### Chunk & Compression Analysis\n\n```sql\n-- View chunks and compression\nSELECT\n    chunk_name,\n    pg_size_pretty(total_bytes) as size,\n    pg_size_pretty(compressed_total_bytes) as compressed_size,\n    ROUND((total_bytes - compressed_total_bytes::numeric) / total_bytes * 100, 1) as compression_pct,\n    range_start,\n    range_end\nFROM timescaledb_information.chunks\nWHERE hypertable_name = 'your_table_name'\nORDER BY range_start DESC;\n```\n\n**Look for:**\n\n- Consistent chunk sizes (within 2x)\n- Compression >90% for time-series\n- Recent chunks uncompressed\n- Chunk indexes < 25% RAM\n\n### Query Performance Tests\n\n```sql\n-- 1. Time-range query (should show chunk exclusion)\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT COUNT(*), AVG(value)\nFROM your_table_name\nWHERE timestamp >= NOW() - INTERVAL '1 day';\n\n-- 2. Entity + time query (benefits from segment_by)\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT * FROM your_table_name\nWHERE entity_id = 'X' AND timestamp >= NOW() - INTERVAL '1 week';\n\n-- 3. Aggregation (benefits from columnstore)\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT DATE_TRUNC('hour', timestamp), entity_id, COUNT(*), AVG(value)\nFROM your_table_name\nWHERE timestamp >= NOW() - INTERVAL '1 month'\nGROUP BY 1, 2;\n```\n\n**✅ Good signs:**\n\n- \"Chunks excluded during startup: X\" in EXPLAIN plan\n- \"Custom Scan (ColumnarScan)\" for compressed data\n- Lower \"Buffers: shared read\" in EXPLAIN ANALYZE plan than pre-migration\n- Faster execution times\n\n**❌ Bad signs:**\n\n- \"Seq Scan\" on large chunks\n- No chunk exclusion messages\n- Slower than before migration\n\n### Storage Metrics\n\n```sql\n-- Monitor compression effectiveness\nSELECT\n    hypertable_name,\n    pg_size_pretty(total_bytes) as total_size,\n    pg_size_pretty(compressed_total_bytes) as compressed_size,\n    ROUND(compressed_total_bytes::numeric / total_bytes * 100, 1) as compressed_pct_of_total,\n    ROUND((uncompressed_total_bytes - compressed_total_bytes::numeric) /\n          uncompressed_total_bytes * 100, 1) as compression_ratio_pct\nFROM timescaledb_information.hypertables\nWHERE hypertable_name = 'your_table_name';\n```\n\n**Monitor:**\n\n- compression_ratio_pct >90% (typical time-series)\n- compressed_pct_of_total growing as data ages\n- Size growth slowing significantly vs pre-hypertable\n- Decreasing compression_ratio_pct = poor segment_by\n\n### Troubleshooting\n\n#### Poor Chunk Exclusion\n\n```sql\n-- Verify chunks are being excluded\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT * FROM your_table_name\nWHERE timestamp >= '2024-01-01' AND timestamp < '2024-01-02';\n-- Look for \"Chunks excluded during startup: X\"\n```\n\n#### Poor Compression\n\n```sql\n-- Get newest compressed chunk name\nSELECT chunk_name FROM timescaledb_information.chunks\nWHERE hypertable_name = 'your_table_name'\n  AND compressed_total_bytes IS NOT NULL\nORDER BY range_start DESC LIMIT 1;\n\n-- Analyze segment distribution\nSELECT segment_by_column, COUNT(*) as rows_per_segment\nFROM _timescaledb_internal._hyper_X_Y_chunk  -- Use actual chunk name\nGROUP BY 1 ORDER BY 2 DESC;\n```\n\n**Look for:** <20 rows per segment: Poor segment_by choice (should be >100) => Low compression potential.\n\n#### Poor insert performance\n\nCheck that you don't have too many indexes. Unused indexes hurt insert performance and should be dropped.\n\n```sql\nSELECT\n    schemaname,\n    tablename,\n    indexname,\n    idx_tup_read,\n    idx_tup_fetch,\n    idx_scan\nFROM pg_stat_user_indexes\nWHERE tablename LIKE '%your_table_name%'\nORDER BY idx_scan DESC;\n```\n\n**Look for:** Unused indexes via a low idx_scan value. Drop such indexes (but ask user permission).\n\n### Ongoing Monitoring\n\n```sql\n-- Monitor chunk compression status\nCREATE OR REPLACE VIEW hypertable_compression_status AS\nSELECT\n    h.hypertable_name,\n    COUNT(c.chunk_name) as total_chunks,\n    COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL) as compressed_chunks,\n    ROUND(\n        COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL)::numeric /\n        COUNT(c.chunk_name) * 100, 1\n    ) as compression_coverage_pct,\n    pg_size_pretty(SUM(c.total_bytes)) as total_size,\n    pg_size_pretty(SUM(c.compressed_total_bytes)) as compressed_size\nFROM timescaledb_information.hypertables h\nLEFT JOIN timescaledb_information.chunks c ON h.hypertable_name = c.hypertable_name\nGROUP BY h.hypertable_name;\n\n-- Query this view regularly to monitor compression progress\nSELECT * FROM hypertable_compression_status\nWHERE hypertable_name = 'your_table_name';\n```\n\n**Look for:**\n\n- compression_coverage_pct should increase over time as data ages and gets compressed.\n- total_chunks should not grow too quickly (more than 10000 becomes a problem).\n- You should not see unexpected spikes in total_size or compressed_size.\n\n## Success Criteria\n\n**✅ Migration successful when:**\n\n- All queries return correct results\n- Query performance equal or better\n- Compression >90% for older data\n- Chunk exclusion working for time queries\n- Insert performance acceptable\n\n**❌ Investigate if:**\n\n- Query performance >20% worse\n- Compression <80%\n- No chunk exclusion\n- Insert performance degraded\n- Increased error rates\n\nFocus on high-volume, insert-heavy workloads with time-based access patterns for best ROI.","author":"@timescale","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/migrate-postgres-tables-to-hypertables","license":"Apache-2.0","category":"data","lang":"en","tokens":3419,"stars":0,"calls30d":2,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":[]}}