io.github.timescale/pg-aiguide

Official2 tools

by timescale · Python

Comprehensive PostgreSQL documentation and best practices, including ecosystem tools

Comprehensive PostgreSQL documentation and best practices for database development.

Topics

aiai-agentsai-codingclaude-marketplaceclaude-pluginclaude-skillsdocsdocumentationmcpmcp-serverpostgrespostgresqlskillsclaude-code-pluginclaude-code-pluginsclaude-code-plugins-marketplace

Captured live from the server via tools/list.

search_docs

Search documentation with hybrid semantic (vector) and keyword (BM25) search. Use semanticWeight to choose keyword-only (0), semantic-only (1), or a blend; mid values fuse rankings with RRF. Supports Tiger Cloud (TimescaleDB), PostgreSQL, and PostGIS.

Parameters (4)

sourcestringrequired
The documentation source to search. "tiger" for Tiger Cloud and TimescaleDB, "postgres" for PostgreSQL, "postgis" for PostGIS spatial extension. Specific versions provided with _X.X suffixes.
querystringrequired
The search query. Used for BM25 when keyword or hybrid search applies, and for the embedding when semantic or hybrid search applies.
limitanyrequired
The maximum number of matches to return. Defaults to 20.
semanticWeightanyrequired
Controls the balance between semantic and keyword search. 0 = keyword only, 0.5 = equal mix, 1 = semantic only. Default is 0.7 (favor semantic search).

view_skill

Retrieve detailed skills for TimescaleDB operations and best practices. ## Available Skills <available_skills> [10 ]{name description}: design-postgis-tables Comprehensive PostGIS spatial table design reference covering geometry types, coordinate systems, spatial indexing, and performance patterns for location-based applications design-postgres-tables "Use this skill for general PostgreSQL table design.\n\n**Trigger when user asks to:**\n- Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones.\n- Choose data types, constraints, or indexes for PostgreSQL\n- Create user tables, order tables, reference tables, or JSONB schemas\n- Understand PostgreSQL best practices for normalization, constraints, or indexing\n- Design update-heavy, upsert-heavy, or OLTP-style tables\n\n\n**Keywords:** PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security\n\nComprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.\n" find-hypertable-candidates "Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables.\n\n**Trigger when user asks to:**\n- Analyze database tables for hypertable conversion potential\n- Identify time-series or event tables in an existing schema\n- Evaluate if a table would benefit from Timescale/TimescaleDB\n- Audit PostgreSQL tables for migration to Timescale/TimescaleDB/TigerData\n- Score or rank tables for hypertable candidacy\n\n\n**Keywords:** hypertable candidate, table analysis, migration assessment, Timescale, TimescaleDB, time-series detection, insert-heavy tables, event logs, audit tables\n\nProvides SQL queries to analyze table statistics, index patterns, and query patterns. Includes scoring criteria (8+ points = good candidate) and pattern recognition for IoT, events, transactions, and sequential data.\n" ghost-database "Use this skill for persistent PostgreSQL storage in agent workflows — spin up databases on demand, test schema migrations safely on forks, and pass databases as assets between agents using Ghost.\n\n**Trigger when user asks to:**\n- Create a new PostgreSQL database quickly\n- Manage database lifecycle in agent workflows\n- Fork a database to test changes safely\n- Run analytics or heavy queries on a fork without impacting the production database\n- Share a database snapshot so others can spin up their own copy\n- Pass a database as an input or output between agents\n- Create a database for agents\n- Need many databases that are billed only when queried\n- Set up the Ghost MCP server or CLI\n\n**Note:** If the Ghost MCP server is already installed, use its tools directly — this skill is for environments without the MCP or as a reference for what's possible.\n\n**Keywords:** Ghost, ghost.build, database, create database, fork database, PostgreSQL, managed Postgres, MCP, agent database, connection string\n" migrate-postgres-tables-to-hypertables "Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation.\n\n**Trigger when user asks to:**\n- Migrate or convert PostgreSQL tables to hypertables\n- Execute hypertable migration with minimal downtime\n- Plan blue-green migration for large tables\n- Validate hypertable migration success\n- Configure compression after migration\n\n**Prerequisites:** Tables already identified as candidates (use find-hypertable-candidates first if needed)\n\n**Keywords:** migrate to hypertable, convert table, Timescale, TimescaleDB, blue-green migration, in-place conversion, create_hypertable, migration validation, compression setup\n\nStep-by-step migration planning including: partition column selection, chunk interval calculation, PK/constraint handling, migration execution (in-place vs blue-green), and performance validation queries.\n"

Parameters (2)

skill_namestringrequired
The name of the skill to browse, or `.` to list all available skills.
pathstringrequired
A relative path to a file or directory within the skill to view. If empty, will view the `SKILL.md` file by default. Use `.` to list the root directory of the skill.

README.md10 KBView on GitHub

pg-aiguide

AI-optimized PostgreSQL expertise for coding assistants

pg-aiguide helps AI coding tools write dramatically better PostgreSQL code. It provides:

Semantic search across the official PostgreSQL manual (version-aware)
AI-optimized “skills” — curated, opinionated Postgres best practices used automatically by AI agents
Extension ecosystem docs, starting with TimescaleDB, with more coming soon

Use it as:

Agent Skills via npx skills — works with Claude Code, Cursor, Codex, Gemini CLI, and 40+ other agents
a public MCP server that can be used with any AI coding agent, or
a Claude Code plugin optimized for use with Claude's native skill support.

⭐ Why pg-aiguide?

AI coding tools often generate Postgres code that is:

outdated
missing constraints and indexes
unaware of modern PG features
inconsistent with real-world best practices

pg-aiguide fixes that by giving AI agents deep, versioned PostgreSQL knowledge and proven patterns.

See the difference

https://github.com/user-attachments/assets/5a426381-09b5-4635-9050-f55422253a3d

<details> <summary>Video Transcript </summary>

Prompt given to Claude Code:

Please describe the schema you would create for an e-commerce website two times, first with the tiger mcp server disabled, then with the tiger mcp server enabled. For each time, write the schema to its own file in the current working directory. Then compare the two files and let me know which approach generated the better schema, using both qualitative and quantitative reasons. For this example, only use standard Postgres.

Result (summarized):

4× more constraints
55% more indexes (including partial/expression indexes)
PG17-recommended patterns
Modern features (GENERATED ALWAYS AS IDENTITY, NULLS NOT DISTINCT)
Cleaner naming & documentation

Conclusion: pg-aiguide produces more robust, performant, maintainable schemas.

</details>

🚀 Quickstart

Agent Skills

Install curated PostgreSQL best-practice skills for your AI coding agent:

bash

npx skills add timescale/pg-aiguide --skill postgres

Or pick individual skills interactively:

bash

npx skills add timescale/pg-aiguide

Works with Claude Code, Cursor, Codex, Gemini CLI, VS Code, and 40+ other agents.

For even deeper PostgreSQL knowledge, also add the MCP server to give your agent semantic search over the official PostgreSQL, TimescaleDB, and PostGIS manuals.

MCP Server

For semantic search over PostgreSQL, TimescaleDB, and PostGIS documentation, add the public MCP server:

https://mcp.tigerdata.com/docs

<details> <summary>Manual MCP configuration using JSON</summary>

json

{
  "mcpServers": {
    "pg-aiguide": {
      "url": "https://mcp.tigerdata.com/docs"
    }
  }
}

</details>

Or it can be used as a Claude Code Plugin:

bash

claude plugin marketplace add timescale/pg-aiguide
claude plugin install pg@aiguide

Install by environment

<details> <summary>Claude Code</summary>

This repo serves as a claude code marketplace plugin. To install, run:

bash

claude plugin marketplace add timescale/pg-aiguide
claude plugin install pg@aiguide

This plugin uses the skills available in the skills directory as well as our publicly available MCP server endpoint hosted by TigerData for searching PostgreSQL documentation.

</details> <details> <summary> Codex </summary>

Run the following to add the MCP server to codex:

bash

codex mcp add --url "https://mcp.tigerdata.com/docs" pg-aiguide

</details> <details> <summary> Cursor </summary>

One-click install:

Or add the following to .cursor/mcp.json

json

{
  "mcpServers": {
    "pg-aiguide": {
      "url": "https://mcp.tigerdata.com/docs"
    }
  }
}

</details> <details> <summary> Gemini CLI </summary>

Run the following to add the MCP server to Gemini CLI:

bash

gemini mcp add -s user pg-aiguide "https://mcp.tigerdata.com/docs" -t http

</details> <details> <summary> Visual Studio </summary>

Click the button to install:

</details> <details> <summary> VS Code </summary>

Click the button to install:

Alternatively, run the following to add the MCP server to VS Code:

bash

code --add-mcp '{"name":"pg-aiguide","type":"http","url":"https://mcp.tigerdata.com/docs"}'

</details> <details> <summary> VS Code Insiders </summary>

Click the button to install:

Alternatively, run the following to add the MCP server to VS Code Insiders:

bash

code-insiders --add-mcp '{"name":"pg-aiguide","type":"http","url":"https://mcp.tigerdata.com/docs"}'

</details> <details> <summary> Windsurf </summary>

Add the following to ~/.codeium/windsurf/mcp_config.json

json

{
  "mcpServers": {
    "pg-aiguide": {
      "serverUrl": "https://mcp.tigerdata.com/docs"
    }
  }
}

</details>

💡 Your First Prompt

Once installed, pg-aiguide can answer Postgres questions or design schemas.

Simple schema example prompt

Create a Postgres table schema for storing usernames and unique email addresses.

Complex schema example prompt

You are a senior software engineer. You are given a task to generate a Postgres schema for an IoT device company. The devices collect environmental data on a factory floor. The data includes temperature, humidity, pressure, as the main data points as well as other measurements that vary from device to device. Each device has a unique id and a human-readable name. We want to record the time the data was collected as well. Analysis for recent data includes finding outliers and anomalies based on measurements, as well as analyzing the data of particular devices for ad-hoc analysis. Historical data analysis includes analyzing the history of data for one device or getting statistics for all devices over long periods of time.

Features

Documentation Search (MCP Tools)

search_docs Unified search tool supporting semantic (vector similarity) and keyword (BM25) search across multiple documentation sources:
- postgres - Official PostgreSQL manual, scoped by version
- tiger - Tiger Data's documentation (TimescaleDB and ecosystem)
- postgis - PostGIS spatial extension documentation

Skills (AI-Optimized Best Practices)

view_skill
Exposes curated, opinionated PostgreSQL best-practice skills used automatically by AI coding assistants.

These skills provide guidance on:
- Schema design
- Indexing strategies
- Data types
- Data integrity and constraints
- Naming conventions
- Performance tuning
- Modern PostgreSQL features

🔌 Ecosystem Documentation

Supported today:

TimescaleDB (docs + skills)
PostGIS (docs)

Coming soon:

pgvector

We welcome contributions for additional extensions and tools.

🛠 Development

See DEVELOPMENT.md for:

running the MCP server locally
adding new skills
adding new docs

🤝 Contributing

We welcome:

new Postgres best-practice skills
additional documentation corpora
search quality improvements
bug reports and feature ideas

📄 License

Apache 2.0

Install

Configuration

Environment variables

OPENAI_API_KEYrequiredsecret

Your API key for text embeddings via OpenAI

PGHOSTrequiredsecret

PostgreSQL host to connect to

PGPORTrequiredsecret

PostgreSQL port to connect to

PGUSERrequiredsecret

PostgreSQL user to connect as

PGPASSWORDrequiredsecret

PostgreSQL password to connect with

PGDATABASErequiredsecret

PostgreSQL database to connect to

DB_SCHEMAsecret

PostgreSQL database schema to use

claude_desktop_config.json

{
  "mcpServers": {
    "pg-aiguide": {
      "command": "npx",
      "args": [
        "-y",
        "@tigerdata/pg-aiguide@0.5.1"
      ],
      "env": {
        "OPENAI_API_KEY": "<YOUR_OPENAI_API_KEY>",
        "PGHOST": "<YOUR_PGHOST>",
        "PGPORT": "<YOUR_PGPORT>",
        "PGUSER": "<YOUR_PGUSER>",
        "PGPASSWORD": "<YOUR_PGPASSWORD>",
        "PGDATABASE": "<YOUR_PGDATABASE>",
        "DB_SCHEMA": "<YOUR_DB_SCHEMA>"
      }
    }
  }
}