# Timescaledb Management

> Guidelines for managing TimescaleDB hypertables, continuous aggregates, and migrations. Use this when creating new tables or modifying time-series data schemas. Use when this capability is needed.

- Skill: `tomevault-io/timescaledb-management` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/timescaledb-management`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/timescaledb-management/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/timescaledb-management

---


When managing database migrations for the Cycle Navigator Dashboard, follow these mandatory steps:

1. **Mandatory Backup**: Always back up the production database before any migration.  
   - **Linux (Bash)**: `pg_dump -h localhost -U cycle_user cycle_navigator > backup_$(date +%Y%m%d).sql`  
   - **Linux (Fish)**: `pg_dump -h localhost -U cycle_user cycle_navigator > backup_(date +%Y%m%d).sql`  
   - **Windows (PowerShell)**: `pg_dump -h localhost -U cycle_user cycle_navigator > backup_$(Get-Date -Format 'yyyyMMdd').sql`  
   - **Windows (CMD)**: `pg_dump -h localhost -U cycle_user cycle_navigator > backup_%date:~10,4%%date:~4,2%%date:~7,2%.sql`  

2. **Pre-Migration Checks**: Run the check script to verify compatibility before applying migrations. This script checks for schema conflicts, data type mismatches, and TimescaleDB extension availability.  
   Prerequisites: Ensure Python 3.x is installed and dependencies are met (run `pip install -r requirements.txt` if needed).  
   Command: `python3 scripts/run_timescale_migrations.py --check-only` (use `python` on Windows if aliased).  
   If checks fail, review the output for issues and resolve before proceeding.  

3. **Hypertable Conversion**: Converting a table to a hypertable is **irreversible**. Verify the `chunk_time_interval` before conversion (standard is '1 month' for FRED data due to lower update frequency, and '7 days' for crypto data due to high-frequency volatility).  
   Command: `python3 scripts/run_timescale_migrations.py --convert-table <table_name> --chunk-interval <interval>`  
   After conversion, run `SELECT * FROM timescaledb_information.hypertables;` to confirm the hypertable exists and chunk settings are correct.

4. **Compression Policy**: Ensure new hypertables include a compression policy to optimize storage and query performance. The standard is 90 days for macro data (slower update cadence) and 30 days for crypto data (high-frequency updates).  
   Command: `python3 scripts/run_timescale_migrations.py --compress-table <table_name> --compress-after <days>`  
   Verify with: `SELECT * FROM timescaledb_information.compression_settings WHERE hypertable_name = '<table_name>';`

5. **Continuous Aggregates**: When pre-calculating metrics, use materialized views with a refresh policy to reduce query latency. Use hourly refreshes for crypto data (high-frequency volatility) and daily refreshes for macro data (slower update cadence).  
   Command: `python3 scripts/run_timescale_migrations.py --create-aggregate <aggregate_name> --source-table <table_name> --refresh-interval <interval>`  
   Example metrics: OHLCV candles, moving averages, volatility indices.  
   Verify with: `SELECT * FROM timescaledb_information.continuous_aggregates WHERE view_name = '<aggregate_name>';`

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/chucks007) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-16 -->