---
title: Tiger CLI and MCP cookbook | Tiger Data Docs
description: Copy-paste prompts for Tiger MCP that design a schema, analyze your data, compare performance, test safely with forks, and tune a service
---

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](/build/tiger-cli-mcp/common-tasks/index.md) instead; this page is for longer, multi-step workflows.

## Prerequisites for this page

To follow these steps, you'll need:

- Tiger CLI installed and your AI agent connected through Tiger MCP. See [Integrate Tiger Cloud with your AI agent](/get-started/quickstart/mcp-cli/index.md).

Each prompt assumes a service already exists unless it says otherwise. For guardrails, read [best practices](/build/tiger-cli-mcp/agent-best-practices/index.md) first, especially if you plan to point an agent at a production service.

## Design a schema and load your data

**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](https://github.com/timescale/pg-ai-cookbook/blob/main/recipes/01-create-database-schema.md).

## Analyze your data

**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](/build/data-management/query-data/select/index.md).

**Full recipe on GitHub**: see [Data Analysis](https://github.com/timescale/pg-ai-cookbook/blob/main/recipes/02-data-analysis.md).

## Compare performance with and without a continuous aggregate

**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](https://github.com/timescale/pg-ai-cookbook/blob/main/recipes/03-performance-analysis.md).

## Test a change safely on a fork

**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. Delete the fork when I'm done.
```

A fork is an independent, writable copy of a service. Nothing you do on it reaches the source. See [Manage your services](/build/tiger-cli-mcp/common-tasks#manage-your-services/index.md) for the fork command and its strategies (`--now`, `--last-snapshot`, `--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](https://github.com/timescale/pg-ai-cookbook/blob/main/recipes/04-safe-experimentation-with-forks.md).

## Optimize a hypertable's configuration

**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](/build/columnar-storage/setup-hypercore/index.md) for the full conversion workflow.

**Full recipe on GitHub**: see [Database Optimization](https://github.com/timescale/pg-ai-cookbook/blob/main/recipes/05-database-optimization.md).

## Find and fix slow queries

**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](/build/performance-optimization/improve-hypertable-performance/index.md) for more on interpreting query plans.

**Full recipe on GitHub**: see [Advanced Performance Analysis](https://github.com/timescale/pg-ai-cookbook/blob/main/recipes/06-advanced-performance-analysis.md).

## Next steps

- [Common tasks with Tiger CLI and Tiger MCP](/build/tiger-cli-mcp/common-tasks/index.md): The single-command version of service management and everyday SQL.
- [Best practices for AI agents](/build/tiger-cli-mcp/agent-best-practices/index.md): Read-only mode, forks, and guardrails for safe agent use.
- [Tiger MCP reference](/reference/tiger-cloud/tiger-mcp/index.md): Every MCP tool and its parameters.