TigerCLI and pg-aiguide Cookbook

Practical examples for using AI agents with PostgreSQL, TimescaleDB, and TigerData through Model Context Protocol (MCP)

This cookbook provides hands-on recipes demonstrating how to leverage AI coding assistants (Claude Code, Gemini CLI, etc.) with TigerData and TimescaleDB. Each recipe is designed as a copy-paste prompt that guides AI agents through complex database operations.

What You'll Learn

• 🤖 AI-Assisted Database Management - Use natural language to create, configure, and optimize databases
• 📊 Time-Series Data Analysis - Analyze sensor data, identify patterns, and detect anomalies
• ⚡ Performance Optimization - Benchmark queries, tune configurations, and implement best practices
• 🔍 Query Performance Debugging - Use pg_stat_statements and EXPLAIN ANALYZE to find bottlenecks
• 🗄️ Data Lifecycle Management - Implement retention policies and compression strategies
• 🔀 Safe Experimentation - Use database forks to test changes without risk

Prerequisites

• Tiger Cloud account - Sign up at https://tigerdata.com
• AI coding assistant - Claude Code (recommended), Gemini CLI, Cursor, or others
• Python 3.x - Optional, for data generation
• psql - PostgreSQL client (recommended)

Quick Start

1. Install Tiger CLI

Tiger CLI is an open-source command-line interface and MCP server for database management with AI assistance.

# Install
curl -fsSL https://cli.tigerdata.com | sh

# Verify
tiger version

2. Authenticate

tiger auth login

This opens your browser to complete authentication with Tiger Cloud.

3. Install MCP Integration

tiger mcp install

The installer automatically detects and configures compatible AI tools:

• Claude Code (recommended)
• Codeium
• Cursor
• Gemini CLI (generous free tier)
• Google Antigravity
• Kiro CLI
• VS Code with Copilot
• Windsurf

Note: Most tools require paid subscriptions. Gemini CLI offers a free tier sufficient for these recipes.

4. Verify Setup

In your AI assistant, check for these available MCP tools:

• mcp__tiger__search_docs - Search Tiger/Postgres documentation
• mcp__tiger__service_list - List Tiger services
• mcp__tiger__service_create - Create new services
• mcp__tiger__service_fork - Fork existing services
• mcp__tiger__db_execute_query - Execute database queries
• mcp__tiger__view_skill - Access pg-aiguide skills

Recipes

Recipes are numbered to indicate the recommended learning order. Start with Recipe 00 for service management basics.

Beginner

Recipe 00: Tiger Service Management ⭐ Start here!

• List, create, start, stop, and resize database services
• View service logs for debugging
• Master complete database lifecycle operations
• Manage default service for your session

Recipe 01: Create Database and Schema

• Create a TimescaleDB service with optimized schema
• Learn about hypertables, continuous aggregates, and time partitioning
• Load sample data from CSV files

Recipe 02: Data Analysis

• Explore database structure and data distribution
• Calculate statistics and identify outliers
• Understand time-series patterns

Intermediate

Recipe 03: Performance Analysis

• Compare query performance with and without continuous aggregates
• Measure execution times and resource usage
• Understand materialized view benefits

Recipe 04: Safe Experimentation with Forks

• Learn database forking for risk-free testing
• Test destructive operations without production impact
• Use multiple fork strategies (NOW, LAST_SNAPSHOT, PITR)

Advanced

Recipe 05: Database Optimization

• Analyze hypertable chunk sizes and partitioning
• Configure compression and indexes
• Get specific optimization recommendations

Recipe 06: Advanced Performance Analysis

• Use pg_stat_statements to identify slow queries
• Master EXPLAIN ANALYZE for query plan interpretation
• Optimize queries based on execution analysis

Common Recipe Workflows

These workflows show how to combine recipes for common use cases:

Complete Beginner Path

Perfect for first-time users learning the basics:

1. Recipe 00 (sections 1-6) - Learn service management basics
   • List services, get service details, view logs
   • Understand service lifecycle operations
2. Recipe 01 - Create your first database with optimized schema
   • Set up hypertables and continuous aggregates
   • Load sample sensor data
3. Recipe 02 - Analyze the data you just loaded
   • Understand data distribution and patterns
   • Identify outliers and anomalies
4. Recipe 03 - Understand query performance
   • See the benefits of continuous aggregates
   • Learn about query execution times

Database Optimization Path

For optimizing existing databases and queries:

1. Recipe 02 - Understand your data first
   • Analyze data volume, distribution, and patterns
   • Identify which queries you run most often
2. Recipe 06 - Identify slow queries
   • Use pg_stat_statements to find bottlenecks
   • Analyze query execution plans with EXPLAIN
3. Recipe 05 - Optimize database configuration
   • Adjust chunk intervals and compression
   • Add appropriate indexes
4. Recipe 04 - Test optimizations safely
   • Fork database to test changes
   • Verify improvements before production

Safe Production Changes

For making changes to production databases without risk:

1. Recipe 04 - Create a fork of your production database
   • Use NOW, LAST_SNAPSHOT, or PITR strategy
   • Verify fork is an exact copy
2. Apply changes to the fork - Test your changes in isolation
   • Schema modifications, new indexes, retention policies
   • Configuration tuning, query optimizations
3. Verify results on fork - Ensure changes work as expected
   • Run performance tests
   • Check data integrity
4. Apply to production - Once verified on fork
   • Document what worked
   • Apply the same changes to production
   • Delete fork to avoid ongoing costs

Quick Experimentation

For rapid testing of database features:

1. Recipe 00 (section 2) - Create a new test service
   • Use natural language: "Create a new 'my-experiment' Tiger service"
   • Minimal resources for cost efficiency
2. Recipe 01 - Set up schema on test service
   • Load sample data for realistic testing
3. Experiment freely - Try whatever you want
   • Test new features, queries, configurations
   • No risk to production data
4. Recipe 00 (section 10) - Delete when done
   • Clean up test service to avoid costs
   • "Delete 'my-experiment' using bash command 'tiger service delete'"

Safety-First Approach

This cookbook prioritizes safety:

• ✅ Read-only recipes - Analysis recipes (02, 03) only query data
• ✅ New database creation - Recipe 01 creates new databases, doesn't modify existing
• ✅ Fork-based testing - Recipe 04 uses isolated database copies for experiments
• ✅ Two-step modifications - Recipes 05 and 06 use analyze-then-apply workflow:
  • Step 1: Analyze and save recommendations to file (safe, read-only)
  • Step 2: Apply changes after human review (requires explicit approval)

Human-in-the-loop: No destructive operations occur without your explicit review and approval.

How to Use a Recipe

Each recipe follows this structure:

1. Safety Note - Clearly indicates if recipe is read-only, creates new resources, or modifies existing
2. Description - What you'll learn and accomplish
3. Prerequisites - Required setup and dependencies
4. Prompt - Copy-paste text for your AI assistant (split into steps for modifications)
5. What to Expect - Detailed explanation of AI actions
6. Verification - SQL queries to check results
7. Troubleshooting - Common issues and solutions

Simply copy the prompt from a recipe and paste it into your AI assistant. The AI will use Tiger MCP tools to complete the task.

For recipes with modifications (05, 06):

1. Run Step 1 prompt to analyze and generate recommendations
2. Review the generated recommendations file
3. Run Step 2 prompt to apply approved changes

Repository Structure

cookbook/
├── README.md                    # This file
├── CLAUDE.md                    # Guide for Claude Code instances
├── Recipe-Template.md           # Template for creating new recipes
├── spec.md                      # Repository specification
├── recipes/                     # Individual recipe files
│   ├── 00-tiger-service-management.md
│   ├── 01-create-database-schema.md
│   ├── 02-data-analysis.md
│   ├── 03-performance-analysis.md
│   ├── 04-safe-experimentation-with-forks.md
│   ├── 05-database-optimization.md
│   └── 06-advanced-performance-analysis.md
└── test-data/                   # Sample data files for recipes
    └── README.md                # Info about test data

Troubleshooting

MCP Tool Access Issues

# Verify installation
tiger version

# Re-authenticate
tiger auth login

# Reinstall MCP integration
tiger mcp install

# Restart your AI assistant tool

Database Connection Problems

# Check authentication status
tiger auth status

# List your services
tiger service list

# Or check the web console
open https://console.tigerdata.com

Script or Command Failures

• Confirm Python 3.x: python3 --version
• Check directory write permissions
• Review error messages from the AI assistant
• Verify CSV data files are present

Contributing

Have a useful recipe or improvement? Contributions are welcome!

1. Fork this repository
2. Create your recipe using Recipe-Template.md
3. Submit a pull request

Resources

• TimescaleDB Documentation: https://www.tigerdata.com/docs
• Tiger CLI GitHub: https://github.com/timescale/tiger-cli
• pg-aiguide: https://github.com/timescale/pg-aiguide
• TigerData Console: https://console.tigerdata.com
• MCP Specification: https://modelcontextprotocol.io

Related Projects

• TigerData Workshops - Full workshop materials
• Tiger CLI - Command-line interface for TigerData
• pg-aiguide - PostgreSQL and TimescaleDB best practices MCP server

License

MIT License

Acknowledgments

Based on the Agentic Postgres Workshop created by Anton Umnikov/Tiger Data.