migrate-postgres-tables-to-hypertables_skill

Overview

This skill is a practical guide to migrate PostgreSQL tables to TimescaleDB hypertables with recommended configuration, migration patterns, and validation checks. It focuses on selecting partition columns, chunk sizing, compression setup, and safe migration strategies for production workloads. The goal is predictable performance, high compression, and minimal application impact.

How this skill works

The skill inspects table metadata, index and constraint definitions, and data distribution to recommend a partition column and chunk interval. It provides step-by-step SQL for in-place and blue-green migrations, compression and sparse index configuration, and checks for foreign-key or primary-key incompatibilities. Post-migration, it runs query and storage validation queries to verify chunk exclusion, compression ratios, and query plans.

When to use it

• You have high-volume, time-ordered or sequential data that benefits from time-series storage.
• You identified candidate tables with timestamp or monotonic integer columns.
• You need to reduce storage and improve analytical query performance via columnar compression.
• You plan a controlled migration with minimal downtime for tables >1GB.
• You must ensure PK/unique constraints and foreign keys remain compatible before conversion.

Best practices

• Choose a time or sequential column that represents event order (avoid updated_at unless it's primary query dim).
• Estimate chunk interval from index-size-per-time and keep recent chunk indexes <25% of RAM; default to 7 days if unsure.
• Do not modify PK/unique constraints without explicit user approval; include partition column in keys.
• Use blue-green migration for large tables; migrate in time-range batches and swap during a maintenance window.
• Configure compression with sensible segment_by and order_by; aim for >100 rows per segment for good compression.

Example use cases

• Migrating IoT device telemetry with device_id segment_by and daily or hourly chunks.
• Converting financial tick or trade tables to columnstore to speed aggregations and reduce storage.
• Blue-green migration of a multi-GB analytics table to avoid long lock times.
• Converting logs or metrics tables and validating chunk exclusion for time-range queries.
• Tuning chunk intervals so recent chunk indexes fit available RAM and maintain fast inserts.