Skip to content

Tiger CLI and MCP cookbook

Copy-paste prompts for Tiger MCP that design a schema, analyze your data, test safely with forks, tune a service, and diagnose slowdowns with metrics

Each recipe below is a prompt you copy into your AI agent. The agent uses Tiger MCP's tools and built-in skills to carry it out: creating and querying services, and reasoning about your schema and query plans. For single-command tasks like creating or forking a service, see common tasks instead; this page is for longer, multi-step workflows.

Prerequisites for this page

To follow these steps, you'll need:

Each prompt assumes a service already exists unless it says otherwise. For guardrails, read best practices first, especially if you plan to point an agent at a production service.

Safety: creates a new service; doesn't touch any existing one.

1. Look at the structure of your CSV files (for example, data.csv and sensors.csv).
2. Create a new Tiger Cloud service called "analytics-poc".
3. Design a hypertable schema for this data: call the readings table sensor_data, partition it by time, segment by the column you filter on most (for example, sensor_id), and add a regular table for the metadata. Save the schema to schema.sql.
4. Apply the schema, then load the CSV data into it.
5. Add a continuous aggregate called sensor_data_hourly that rolls sensor_data up to hourly averages per sensor.

The agent designs the CREATE TABLE ... WITH (tsdb.hypertable, ...) statement, picks an index strategy, loads the data with \copy, and creates the continuous aggregate with CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous). The rest of this page's recipes assume a hypertable named sensor_data and a continuous aggregate named sensor_data_hourly; substitute your own names if you use different ones.

Full recipe on GitHub: see Create Database and Schema.

Setup: assumes the sensor_data hypertable from Design a schema and load your data. Substitute your own table if you're starting from an existing service.

Safety: read-only.

1. List the tables, hypertables, and continuous aggregates in this database.
2. Show the total row count and the row count per sensor.
3. Calculate the min, max, average, and standard deviation for temperature and humidity.
4. Find readings more than 3 standard deviations from the mean, and explain what might cause them.

The agent runs the equivalent of:

SELECT sensor_id, COUNT(*) FROM sensor_data GROUP BY sensor_id ORDER BY COUNT(*) DESC;
WITH stats AS (
SELECT AVG(temperature) AS mean_temp, STDDEV(temperature) AS stddev_temp FROM sensor_data
)
SELECT s.time, s.sensor_id, s.temperature
FROM sensor_data s, stats
WHERE ABS(s.temperature - stats.mean_temp) > 3 * stats.stddev_temp
ORDER BY s.time DESC;

For more patterns like this, see Query data.

Full recipe on GitHub: see Data Analysis.

Compare performance with and without a continuous aggregate

Section titled “Compare performance with and without a continuous aggregate”

Setup: assumes the sensor_data hypertable and sensor_data_hourly continuous aggregate from Design a schema and load your data.

Safety: read-only.

1. Run a query that computes hourly averages using my continuous aggregate.
2. Run the same aggregation directly against the raw hypertable.
3. Compare execution time and rows scanned with EXPLAIN ANALYZE, and explain the difference.

This surfaces the difference between querying pre-aggregated data and scanning raw rows, for example:

EXPLAIN ANALYZE
SELECT bucket, sensor_id, avg_temp FROM sensor_data_hourly
WHERE bucket > now() - INTERVAL '7 days';
EXPLAIN ANALYZE
SELECT time_bucket('1 hour', time) AS bucket, sensor_id, AVG(temperature) AS avg_temp
FROM sensor_data
WHERE time > now() - INTERVAL '7 days'
GROUP BY bucket, sensor_id;

Full recipe on GitHub: see Performance Analysis.

Setup: assumes the analytics-poc service from Design a schema and load your data. Substitute your own service name if you're starting from an existing one.

Safety: destructive steps run only on an isolated fork; the source service is never modified.

1. Fork my "analytics-poc" service so I can test a retention policy without risking production data.
2. On the fork only, add a retention policy that drops data older than 30 days, and wait for the job to run.
3. Compare row counts on the fork before and after.
4. Confirm the original service's row count is unchanged.
5. When I'm done, remind me to delete the fork.

A fork is an independent, writable copy of a service. Nothing you do on it reaches the source. See tiger service fork for the fork command; by default it forks at the current database state, or fork from an earlier point with --last-snapshot or --to-timestamp. This same fork-first pattern applies to any risky change: schema migrations, configuration tuning, or bulk deletes.

Full recipe on GitHub: see Safe Experimentation with Forks.

Setup: assumes the sensor_data hypertable from Design a schema and load your data.

Safety: two-step. Step 1 only analyzes and writes a file; nothing changes until you review it and run step 2.

Step 1 (analyze):
1. Check the chunk interval, segmentby column, and columnstore status for my hypertables.
2. Identify optimization opportunities: chunk sizing, segmentby columns, missing indexes, or columnstore policies.
3. Write specific SQL commands and the reasoning behind them to optimization-recommendations.md. Don't run anything that changes the database yet.
Step 2 (apply, after I've reviewed the file):
I've reviewed optimization-recommendations.md and approve it. Apply the commands one at a time, verify each one, and log what you did to optimization-applied.log. Stop and report if anything fails.

The agent inspects timescaledb_information.hypertables, timescaledb_information.hypertable_columnstore_settings, and timescaledb_information.chunks, and its recommendations typically look like:

-- A hypertable created with CREATE TABLE ... WITH (tsdb.hypertable, ...) already has a default
-- columnstore policy, so changing segmentby means replacing it rather than adding a new one.
CALL remove_columnstore_policy('sensor_data');
ALTER TABLE sensor_data SET (
timescaledb.enable_columnstore,
timescaledb.segmentby = 'sensor_id'
);
CALL add_columnstore_policy('sensor_data', after => INTERVAL '7 days');
-- Add an index for a common access pattern
CREATE INDEX idx_sensor_data_sensor_time ON sensor_data (sensor_id, time DESC);

segmentby can be added to an existing hypertable without recreating it. See Set up hypercore for the full conversion workflow.

Full recipe on GitHub: see Database Optimization.

Setup: works on any service that has been running your workload long enough to have query history. Nothing from the earlier recipes is required.

Safety: two-step, same analyze-then-apply pattern as above. Step 1 uses EXPLAIN (not EXPLAIN ANALYZE) so it doesn't re-run slow queries.

Step 1 (analyze):
1. Enable pg_stat_statements if it isn't already, and list the 10 queries with the highest total execution time.
2. For the top 3, show the EXPLAIN plan (not EXPLAIN ANALYZE) and identify the bottleneck (sequential scans, missing indexes, poor join order).
3. Write the recommended CREATE INDEX or query changes, with the reasoning, to query-optimization-recommendations.md. Don't run anything that changes the database yet.
Step 2 (apply, after I've reviewed the file):
I've reviewed query-optimization-recommendations.md and approve it. Apply each change, then use EXPLAIN ANALYZE to confirm the improvement, and log the before/after comparison to query-optimization-applied.log.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

A sequential scan in the EXPLAIN output on a large hypertable is the most common finding, usually fixed with an index on the filtered or joined column. See Improve hypertable performance for more on interpreting query plans.

Full recipe on GitHub: see Advanced Performance Analysis.

Setup: works on any service. The metrics come from the Tiger Cloud platform rather than from SQL, so the agent can investigate even when the database itself is too loaded to answer queries, and can see history from before it was connected.

Safety: read-only. The metrics tools never touch your data, and they stay available when the agent is restricted to read-only.

1. My app slowed down this afternoon. For service "analytics-poc", pull CPU, memory, and disk I/O usage for the last 24 hours and compare each against the service's total capacity.
2. Pull transaction throughput and connection counts over the same window, and tell me whether the slowdown lines up with a traffic change.
3. If a resource is saturated, name it and tell me when the saturation started.
4. Cross-check inside the database: what do pg_stat_activity and pg_stat_statements say was running around that time?
5. Summarize the most likely cause and recommend a fix (for example, resize, connection pooling, or query changes). Don't change anything.

The agent discovers metric names with service_metrics_available, then fetches series such as timescale_cloud_system_cpu_usage_millicores against timescale_cloud_system_cpu_total_millicores, timescale_cloud_system_memory_usage_bytes, pg_stat_database_xact_commit_total, and pg_stat_activity_count. The CLI equivalent of step 1 is:

Terminal window
tiger service metrics series analytics-poc --metric timescale_cloud_system_cpu_usage_millicores --role PRIMARY
tiger service metrics series analytics-poc --metric timescale_cloud_system_memory_usage_bytes --role PRIMARY

The available metrics also cover the pg_stat_* families, so the same investigation extends to locks, replication lag, and per-database activity. Metrics tell you which resource is constrained and when it started; if the answer turns out to be a specific query, continue with Find and fix slow queries, and if the workload has simply outgrown the compute, resize with tiger service resize.