TimescaleDB

What This Skill Does

The TimescaleDB skill allows OpenClaw agents to manage, optimize, and query time-series data using the power of TimescaleDB hypertables. It bridges the gap between raw PostgreSQL data and high-performance time-series analytics, enabling features like automatic data partitioning (hypertables), significant storage savings through native compression, and efficient data aggregation using time_bucket. This skill enables your agent to perform complex temporal analysis, manage data retention policies, and maintain high write performance for logging and sensor metrics.

Installation

To integrate this skill into your environment, execute the following command in your terminal: clawhub install openclaw/skills/skills/ivangdavila/timescaledb

Use Cases

• IoT & Sensor Networks: Efficiently store millions of rows of temperature or power usage data and query them by specific hourly or daily intervals.
• Application Monitoring: Track API response times and error rates over long periods with automatic retention policies that drop outdated metrics.
• Financial Analysis: Use continuous aggregates to calculate moving averages or candle-stick charts for high-frequency trading data without impacting query latency.

Example Prompts

1. "Initialize the 'metrics' table as a hypertable partitioned by the 'time' column."
2. "Create a continuous aggregate view named 'hourly_temp_stats' to average sensor data grouped by 1-hour buckets."
3. "Set up a retention policy to automatically drop all chunks older than 180 days to reclaim storage space."

Tips & Limitations

• Chunk Management: Aim for chunks that occupy approximately 25% of your available memory. Oversized chunks degrade query performance, while undersized chunks add excessive metadata overhead.
• Compression Strategy: Always use compression for historical data. Remember that compressed chunks are read-only; you must explicitly decompress a chunk if you need to perform updates or deletions on historic records.
• Insertion Best Practices: Avoid single-row INSERT statements. For maximum throughput, use COPY or multi-value INSERT batches. Out-of-order data ingestion is supported but significantly slower than chronological insertion.
• Indexing: Leverage the built-in time index. Avoid redundant indexes, but prioritize composite indexes on filtering columns (e.g., device_id) combined with time to allow for efficient chunk exclusion during query execution.