Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation. **Trigger when user asks to:** - Migrate or convert PostgreSQL tables to hypertables - Execute hypertable migration with minimal downtime - Plan
The summary Claude sees to decide when to auto-load this skill.
Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation. **Trigger when user asks to:** - Migrate or convert PostgreSQL tables to hypertables - Execute hypertable migration with minimal downtime - Plan
📊 Stats
Stars1,793
Forks99
LanguagePython
LicenseApache-2.0
📦 Ships with pg-aiguide
</> SKILL.md
migrate-postgres-tables-to-hypertables.SKILL.md
---name: migrate-postgres-tables-to-hypertables
description: |
Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation.
**Trigger when user asks to:**
- Migrate or convert PostgreSQL tables to hypertables
- Execute hypertable migration with minimal downtime
- Plan blue-green migration for large tables
- Validate hypertable migration success
- Configure compression after migration
**Prerequisites:** Tables already identified as candidates (use find-hypertable-candidates first if needed)
**Keywords:** migrate to hypertable, convert table, Timescale, TimescaleDB, blue-green migration, in-place conversion, create_hypertable, migration validation, compression setup
Step-by-step migration planning including: partition column selection, chunk interval calculation, PK/constraint handling, migration execution (in-place vs blue-green), and performance validation queries.
license: Apache-2.0
compatibility: Requires PostgreSQL 15+ with TimescaleDB
metadata:
author: tigerdata
---# PostgreSQL to TimescaleDB Hypertable Migration
Migrate identified PostgreSQL tables to TimescaleDB hypertables with optimal configuration, migration planning and validation.
- 1 day chunks: 4.8GB chunk index size × 2 recent = 9.6GB ⚠️
Choose largest interval keeping 2+ recent chunk indexes under target.
### Primary Key/ Unique Constraints Compatibility
```sql
-- Check existing primary key/ unique constraints
SELECT conname, pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass AND contype = 'p' OR contype = 'u';
```
**Rules:** PK/UNIQUE must include partition column
**Actions:**
1. **No PK/UNIQUE:** No changes needed
2. **PK/UNIQUE includes partition column:** No changes needed
3. **PK/UNIQUE excludes partition column:** ⚠️ **ASK USER PERMISSION** to modify PK/UNIQUE
**Example: user prompt if needed:**
> "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:
```sql
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 segment_by and order_by 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`
```sql
-- 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;
```
**order_by:** Usually `timestamp DESC`. The (segment_by, order_by) combination should form a natural time-series progression.
- If column has <100 rows/chunk (too low for segment_by), prepend to order_by: `order_by='low_density_col, timestamp DESC'`
**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.
```sql
ALTER TABLE your_table_name SET (
timescaledb.enable_columnstore,
timescaledb.segmentby = 'entity_id',
timescaledb.orderby = 'timestamp DESC'
timescaledb.sparse_index = 'minmax(value_1),...'
);
-- Compress after data unlikely to change (adjust `after` parameter based on update patterns)
CALL add_columnstore_policy('your_table_name', after => 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
