AgentSkillsCN

timescaledb-management

指导如何管理 TimescaleDB 超表、连续聚合，以及数据迁移。适用于新建数据表，或修改时序数据 Schema 时使用。

SKILL.md
--- frontmatter
name: timescaledb-management
description: Guidelines for managing TimescaleDB hypertables, continuous aggregates, and migrations. Use this when creating new tables or modifying time-series data schemas.

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>';