migrate-postgres-tables-to-hypertables

This skill provides a robust framework for converting standard PostgreSQL tables into TimescaleDB hypertables, ensuring optimal performance for large-scale time-series or sequential datasets. It is designed for database administrators and backend engineers looking to leverage TimescaleDB's advanced features without disrupting production workloads. By guiding the selection of partitioning columns and determining appropriate chunk intervals, the skill helps prevent common performance pitfalls, such as overly granular chunks or misconfigured index strategies. Users can expect a methodical approach that addresses data alignment, constraint compatibility, and storage efficiency, ultimately transforming standard relational tables into high-performance analytical structures suitable for time-centric queries.

• Automated identification and validation of candidate partition columns including timestamp, timestamptz, bigint, and date types.

• Advanced chunk interval calculation to balance memory usage and index size, ensuring recent chunk indexes remain under 25% of system RAM.

• Enforcement of primary key and unique constraint compatibility with partition columns, including workflows for safe modifications.

• Compression configuration including segmentation (segment_by) for high-cardinality IoT or financial data and ordering (order_by) for query performance.

• Support for advanced features like minmax sparse indexes to accelerate range queries on non-partitioning columns.

• Comprehensive pre-migration checklists to ensure data integrity and system stability across in-place or blue-green migration patterns.

• The skill requires tables to be pre-identified as hypertable candidates; consider using the find-hypertable-candidates skill for initial assessment.

• Always analyze statistics using ANALYZE before calculating chunk intervals to ensure accurate index size estimation.

• Carefully review PK/unique constraint changes, as these modifications can impact downstream application logic.

• Compression policies are best applied to data that is no longer frequently updated; verify business logic for record updates before finalizing the compression interval.

• Supported on PostgreSQL 15+ with the TimescaleDB extension properly configured in the current database environment.