# Migrate Postgres Tables To Hypertables

Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB...

- Repository: https://github.com/timescale/pg-aiguide (1877 stars)
- Category: Engineering
- License: Apache-2.0
- Last updated: 2026-10-28
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add timescale/pg-aiguide --skill migrate-postgres-tables-to-hypertables -g -a claude-code -y`
Codex: `npx skills add timescale/pg-aiguide --skill migrate-postgres-tables-to-hypertables -g -a codex -y`
Cursor: `npx skills add timescale/pg-aiguide --skill migrate-postgres-tables-to-hypertables -g -a cursor -y`
Gemini CLI: `npx skills add timescale/pg-aiguide --skill migrate-postgres-tables-to-hypertables -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add timescale/pg-aiguide`

## 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.

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# 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

```sql
-- 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:

```sql
-- 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

```sql
-- Ensure statistics are current
ANALYZE your_table_name;

The rest: https://github.com/timescale/pg-aiguide/blob/HEAD/skills/migrate-postgres-tables-to-hypertables/SKILL.md