- Home
- Skills
- Data & Databases
- Timescaledb Retention Policy Manager
Works with the AI Agents you already use
Timescaledb Retention Policy Manager
Optimize TimescaleDB with hypertables, continuous aggregates, and automated retention policies.
$15
Timescaledb Retention Policy Manager
Example session with this skill installed
Convert my 'sensor_readings' table into a hypertable with 1-day chunks. Add a 30-day retention policy and a continuous aggregate for hourly temperature averages.
- Read your context and instructions
- Compiled the timescaledb retention policy
I have generated the TimescaleDB configuration for your sensor_readings table, including hypertable conversion, hourly rollups, and automated data retention.
TimescaleDB Configuration Script
-- 1. Convert to Hypertable
-- Partitions data by 'time' column into 1-day chunks
SELECT create_hypertable('sensor_readings', 'time', chunk_time_interval => INTERVAL '1 day');
-- 2. Create Continuous Aggregate for Hourly Averages
-- Enables sub-second rollups for temperature data
CREATE MATERIALIZED VIEW sensor_readings_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
sensor_id,
avg(temperature) as avg_temp,
max(temperature) as max_temp
FROM sensor_readings
GROUP BY bucket, sensor_id;
-- 3. Define Refresh Policy for Aggregate
-- Refreshes the hourly view every 30 minutes
SELECT add_continuous_aggregate_policy('sensor_readings_hourly',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '30 minutes');
-- 4. Enable Compression
-- Compress chunks older than 7 days to save ~90% disk space
ALTER TABLE sensor_readings SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'sensor_id'
);
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');
-- 5. Establish Data Retention Policy
-- Automatically drops raw data older than 30 days
SELECT add_retention_policy('sensor_readings', INTERVAL '30 days');
Next steps
- Run the script in your PostgreSQL console to apply the changes.
- Monitor the
timescaledb_information.hypertable_statsview to verify chunk sizes fit in RAM. - Verify the continuous aggregate refresh status using
timescaledb_information.continuous_aggregate_stats.
Connects securely to your tools. The creator never sees your data.
About this skill
Managing high-throughput time-series data often leads to bloated tables, sluggish queries, and RAM exhaustion. This skill provides the precise TimescaleDB SQL configurations needed to partition data, automate rollups, and enforce retention policies.
What it does
- Hypertable conversion translates standard PostgreSQL tables into distributed hypertables using
create_hypertable. - Chunk optimization calculates and sets optimal chunk time intervals to ensure active datasets fit within available RAM.
- Continuous aggregation builds materialized views for sub-second rollups on massive IoT or financial datasets.
- Retention management establishes automated data TTL policies and columnstore compression settings.
How it works
- Analyze schema to identify time-series candidates for hypertable conversion.
- Define intervals based on data volume to set
chunk_time_interval(e.g., 1 day or 7 days). - Generate rollups by creating materialized continuous aggregates for common query patterns.
- Apply policies to automate data deletion and compression to save disk space.
Frameworks & tools
Specifically designed for TimescaleDB 2.0+ running on PostgreSQL. It targets time-series workloads common in IoT, financial market data, and systems monitoring.
Why this beats prompting it yourself
Writing TimescaleDB policies manually often results in sub-optimal chunk sizes that spill out of RAM or broken continuous aggregate refresh loops. This skill uses a deterministic workflow to ensure all mandatory optimization steps, from compression to retention, are included in the generated SQL.
Use cases
- IoT sensor telemetry where data older than 30 days must be compressed and data older than 90 days deleted.
- Financial market data requiring sub-second rollups for OHLC (Open, High, Low, Close) price bars.
- Systems monitoring where high-write throughput requires partitioning by daily chunks to maintain insert performance.
Known limitations
Outputs are restricted to TimescaleDB SQL syntax and do not include application-level ORM logic. Does not perform live database migrations or execute commands against a running instance.
Produces TimescaleDB 2.0+ SQL only. No live DB execution or ORM-specific code generation. Requires existing PostgreSQL table structures as input.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 2 days ago
Requires
Permissions
Target Use Cases: - Configure a TimescaleDB hypertable for time-series data. - Create automated continuous aggregates in TimescaleDB. - Set up chunk compression and retention policy in TimescaleDB.