Loading plugin...

pg-aiguide · Flowy

README

Read 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

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.

🚀 Quickstart

Agent Skills

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

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

Or pick individual skills interactively:

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

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

Or it can be used as a Claude Code Plugin:

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

Install by environment

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

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.

Run the following to add the MCP server to codex:

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

One-click install:

Or add the following to .cursor/mcp.json

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

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

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

Click the button to install:

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

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

Click the button to install:

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

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

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

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

💡 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

File tree

106

timescale/pg-aiguideView on GitHub ↗

.claude-plugin/
  marketplace.json
.cursor-plugin/
  plugin.json
.dockerignore
.env.sample
.github/
  workflows/
    build-and-deploy-on-merge.yaml
    build-on-feature-branch.yaml
    deploy-feature-branch.yaml
    ingest-postgis.yaml
    ingest-postgres-docs.yaml
    ingest-tiger-docs.yaml
    lint.yml
    publish.yml
    test.yml
.gitignore
.vscode/
  launch.json
  settings.json
  tasks.json
API.md
assets/
  logo.svg
biome.json
bun
bun.lock
check
CLAUDE.md
DEVELOPMENT.md
docker/
docker-compose.yml
  tsdb/
    100_setup_db.sql
Dockerfile
generate-server.json.ts
ingest/
  .python-version
  constants.py
  document_importer.py
  encoder.py
  postgis_docs.py
  postgres_docs.py
  pyproject.toml
  README.md
  tiger_docs_config.toml
  tiger_docs.py
  types.py
  utils/
    __init__.py
    __pycache__/
      __init__.cpython-313.pyc
      db.cpython-313.pyc
    beautiful_soup.py
    chunking.py
    db.py
  uv.lock
LICENSE
mcp.json
migrations/
  1756387543053-initial.js
  1759241172003-add-hnsw-indexes.js
  1759241361471-add-version-index.js
  1759851009030-add-tiger-indexes.js
  1767150354320-add-postgis-tables.js
  1767990776052-add-bm25-indexes.js
  1769649893644-add-postgis-bm25-index.js
NOTICE
package.json
README.md
release.ts
rules/
  postgres-best-practices.mdc
scripts/
  validate-skills.ts
skills/
skills.yaml
  design-postgis-tables/
    SKILL.md
  design-postgres-tables/
    SKILL.md
  find-hypertable-candidates/
    SKILL.md
  ghost-database/
    SKILL.md
  migrate-postgres-tables-to-hypertables/
    SKILL.md
  pgvector-semantic-search/
    SKILL.md
  postgres/
  postgres-database-migration/
    references/
      backfill-strategies.md
      complete-example.md
      validation-queries.md
    SKILL.md
  postgres-hybrid-text-search/
    SKILL.md
    references/
      backfill-strategies.md
      complete-example.md
      design-postgis-tables.md
      design-postgres-tables.md
      find-hypertable-candidates.md
      ghost-database.md
      migrate-postgres-tables-to-hypertables.md
      pgvector-semantic-search.md
      postgres-database-migration.md
      postgres-hybrid-text-search.md
      setup-timescaledb-hypertables.md
      validation-queries.md
    SKILL.md
  setup-timescaledb-hypertables/
    SKILL.md
src/
  apis/
    index.ts
    searchDocs.test.ts
    searchDocs.ts
  config.ts
  httpServer.ts
  index.ts
  prompts/
    index.ts
  serverInfo.ts
  stdio.ts
  types.ts
  util/
    featureFlags.test.ts
    featureFlags.ts
    getDocChunkRows.test.ts
    getDocChunkRows.ts
    rrf.test.ts
    rrf.ts
tsconfig.build.json
tsconfig.json