# pg-aiguide MCP server

Comprehensive PostgreSQL documentation and best practices, including ecosystem tools

## Links
- Registry page: https://www.getdrio.com/mcp/io-github-timescale-pg-aiguide
- Repository: https://github.com/timescale/pg-aiguide

## Install
- Command: `npx -y @tigerdata/pg-aiguide`
- Endpoint: https://mcp.tigerdata.com/docs
- Auth: Auth required by registry metadata

## Setup notes
- Package: Npm @tigerdata/pg-aiguide v0.5.1
- Environment variable: OPENAI_API_KEY (required; secret)
- Environment variable: PGHOST (required; secret)
- Environment variable: PGPORT (required; secret)
- Environment variable: PGUSER (required; secret)
- Environment variable: PGPASSWORD (required; secret)
- Environment variable: PGDATABASE (required; secret)
- Environment variable: DB_SCHEMA (secret)
- Package: Oci ghcr.io/timescale/pg-aiguide:0.5.1
- Environment variable: OPENAI_API_KEY (required; secret)
- Environment variable: PGHOST (required; secret)
- Environment variable: PGPORT (required; secret)
- Environment variable: PGUSER (required; secret)
- Environment variable: PGPASSWORD (required; secret)
- Environment variable: PGDATABASE (required; secret)
- Environment variable: DB_SCHEMA (secret)
- The upstream registry signals required auth or secrets.
- Remote endpoint: https://mcp.tigerdata.com/docs

## Tools
- search_docs (Search Documentation) - 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. Endpoint: https://mcp.tigerdata.com/docs
- view_skill (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"
  pgvector-semantic-search	"Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG applications, or semantic search.\n\n**Trigger when user asks to:**\n- Store or search vector embeddings in PostgreSQL\n- Set up semantic search, similarity search, or nearest neighbor search\n- Create HNSW or IVFFlat indexes for vectors\n- Implement RAG (Retrieval Augmented Generation) with PostgreSQL\n- Optimize pgvector performance, recall, or memory usage\n- Use binary quantization for large vector datasets\n\n**Keywords:** pgvector, embeddings, semantic search, vector similarity, HNSW, IVFFlat, halfvec, cosine distance, nearest neighbor, RAG, LLM, AI search\n\nCovers: halfvec storage, HNSW index configuration (m, ef_construction, ef_search), quantization strategies, filtered search, bulk loading, and performance tuning.\n"
  postgres	"Use this skill for any PostgreSQL database work — table design, indexing, data types, constraints, extensions (pgvector, PostGIS, TimescaleDB), search, and migrations.\n\n**Trigger when user asks to:**\n- Design or modify PostgreSQL tables, schemas, or data models\n- Choose data types, constraints, indexes, or partitioning strategies\n- Work with pgvector embeddings, semantic search, or RAG\n- Set up full-text search, hybrid search, or BM25 ranking\n- Use PostGIS for spatial/geographic data\n- Set up TimescaleDB hypertables for time-series data\n- Migrate tables to hypertables or evaluate migration candidates\n- Plan or execute safe schema migrations with zero downtime\n- Create, fork, or manage databases with Ghost\n\n**Keywords:** PostgreSQL, Postgres, SQL, schema, table design, indexes, constraints, pgvector, PostGIS, TimescaleDB, hypertable, semantic search, hybrid search, BM25, time-series, migration, Ghost\n"
  postgres-database-migration	"Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases.\n\n**Trigger when user asks to:**\n- Test a schema migration before applying it to production\n- Add, remove, or rename columns safely on a live table\n- Change a column's data type without downtime\n- Add or drop indexes, constraints, or foreign keys on large tables\n- Understand which ALTER TABLE operations lock the table\n- Roll back a failed migration\n- Plan a zero-downtime migration strategy\n- Fork a database to test a migration safely\n\n**Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, ghost, rollback, backfill, deploy\n\nCovers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.\n"
  postgres-hybrid-text-search	"Use this skill to implement hybrid search combining BM25 keyword search with semantic vector search using Reciprocal Rank Fusion (RRF).\n\n**Trigger when user asks to:**\n- Combine keyword and semantic search\n- Implement hybrid search or multi-modal retrieval\n- Use BM25/pg_textsearch with pgvector together\n- Implement RRF (Reciprocal Rank Fusion) for search\n- Build search that handles both exact terms and meaning\n\n\n**Keywords:** hybrid search, BM25, pg_textsearch, RRF, reciprocal rank fusion, keyword search, full-text search, reranking, cross-encoder\n\nCovers: pg_textsearch BM25 index setup, parallel query patterns, client-side RRF fusion (Python/TypeScript), weighting strategies, and optional ML reranking.\n"
  setup-timescaledb-hypertables	"Use this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table.\n\n**Trigger when user asks to:**\n- Create or design SQL schemas/tables AND Timescale/TimescaleDB/TigerData/Tiger Cloud is available\n- Set up hypertables, compression, retention policies, or continuous aggregates\n- Configure partition columns, segment_by, order_by, or chunk intervals\n- Optimize time-series database performance or storage\n- Create tables for sensors, metrics, telemetry, events, or transaction logs\n\n**Keywords:** CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segment_by, order_by\n\nStep-by-step instructions for hypertable creation, column selection, compression policies, retention, continuous aggregates, and indexes.\n"
</available_skills> Endpoint: https://mcp.tigerdata.com/docs

## Resources
Not captured

## Prompts
- design-postgis-tables - 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 - design-postgres-tables Use this skill for general PostgreSQL table design.

**Trigger when user asks to:**
- Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones.
- Choose data types, constraints, or indexes for PostgreSQL
- Create user tables, order tables, reference tables, or JSONB schemas
- Understand PostgreSQL best practices for normalization, constraints, or indexing
- Design update-heavy, upsert-heavy, or OLTP-style tables


**Keywords:** PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security

Comprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.
- find-hypertable-candidates - find-hypertable-candidates Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables.

**Trigger when user asks to:**
- Analyze database tables for hypertable conversion potential
- Identify time-series or event tables in an existing schema
- Evaluate if a table would benefit from Timescale/TimescaleDB
- Audit PostgreSQL tables for migration to Timescale/TimescaleDB/TigerData
- Score or rank tables for hypertable candidacy


**Keywords:** hypertable candidate, table analysis, migration assessment, Timescale, TimescaleDB, time-series detection, insert-heavy tables, event logs, audit tables

Provides 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.
- ghost-database - 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.

**Trigger when user asks to:**
- Create a new PostgreSQL database quickly
- Manage database lifecycle in agent workflows
- Fork a database to test changes safely
- Run analytics or heavy queries on a fork without impacting the production database
- Share a database snapshot so others can spin up their own copy
- Pass a database as an input or output between agents
- Create a database for agents
- Need many databases that are billed only when queried
- Set up the Ghost MCP server or CLI

**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.

**Keywords:** Ghost, ghost.build, database, create database, fork database, PostgreSQL, managed Postgres, MCP, agent database, connection string
- migrate-postgres-tables-to-hypertables - migrate-postgres-tables-to-hypertables Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation.

**Trigger when user asks to:**
- Migrate or convert PostgreSQL tables to hypertables
- Execute hypertable migration with minimal downtime
- Plan blue-green migration for large tables
- Validate hypertable migration success
- Configure compression after migration

**Prerequisites:** Tables already identified as candidates (use find-hypertable-candidates first if needed)

**Keywords:** migrate to hypertable, convert table, Timescale, TimescaleDB, blue-green migration, in-place conversion, create_hypertable, migration validation, compression setup

Step-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.
- pgvector-semantic-search - pgvector-semantic-search Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG applications, or semantic search.

**Trigger when user asks to:**
- Store or search vector embeddings in PostgreSQL
- Set up semantic search, similarity search, or nearest neighbor search
- Create HNSW or IVFFlat indexes for vectors
- Implement RAG (Retrieval Augmented Generation) with PostgreSQL
- Optimize pgvector performance, recall, or memory usage
- Use binary quantization for large vector datasets

**Keywords:** pgvector, embeddings, semantic search, vector similarity, HNSW, IVFFlat, halfvec, cosine distance, nearest neighbor, RAG, LLM, AI search

Covers: halfvec storage, HNSW index configuration (m, ef_construction, ef_search), quantization strategies, filtered search, bulk loading, and performance tuning.
- postgres - postgres Use this skill for any PostgreSQL database work — table design, indexing, data types, constraints, extensions (pgvector, PostGIS, TimescaleDB), search, and migrations.

**Trigger when user asks to:**
- Design or modify PostgreSQL tables, schemas, or data models
- Choose data types, constraints, indexes, or partitioning strategies
- Work with pgvector embeddings, semantic search, or RAG
- Set up full-text search, hybrid search, or BM25 ranking
- Use PostGIS for spatial/geographic data
- Set up TimescaleDB hypertables for time-series data
- Migrate tables to hypertables or evaluate migration candidates
- Plan or execute safe schema migrations with zero downtime
- Create, fork, or manage databases with Ghost

**Keywords:** PostgreSQL, Postgres, SQL, schema, table design, indexes, constraints, pgvector, PostGIS, TimescaleDB, hypertable, semantic search, hybrid search, BM25, time-series, migration, Ghost
- postgres-database-migration - postgres-database-migration Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases.

**Trigger when user asks to:**
- Test a schema migration before applying it to production
- Add, remove, or rename columns safely on a live table
- Change a column's data type without downtime
- Add or drop indexes, constraints, or foreign keys on large tables
- Understand which ALTER TABLE operations lock the table
- Roll back a failed migration
- Plan a zero-downtime migration strategy
- Fork a database to test a migration safely

**Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, ghost, rollback, backfill, deploy

Covers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.
- postgres-hybrid-text-search - postgres-hybrid-text-search Use this skill to implement hybrid search combining BM25 keyword search with semantic vector search using Reciprocal Rank Fusion (RRF).

**Trigger when user asks to:**
- Combine keyword and semantic search
- Implement hybrid search or multi-modal retrieval
- Use BM25/pg_textsearch with pgvector together
- Implement RRF (Reciprocal Rank Fusion) for search
- Build search that handles both exact terms and meaning


**Keywords:** hybrid search, BM25, pg_textsearch, RRF, reciprocal rank fusion, keyword search, full-text search, reranking, cross-encoder

Covers: pg_textsearch BM25 index setup, parallel query patterns, client-side RRF fusion (Python/TypeScript), weighting strategies, and optional ML reranking.
- setup-timescaledb-hypertables - setup-timescaledb-hypertables Use this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table.

**Trigger when user asks to:**
- Create or design SQL schemas/tables AND Timescale/TimescaleDB/TigerData/Tiger Cloud is available
- Set up hypertables, compression, retention policies, or continuous aggregates
- Configure partition columns, segment_by, order_by, or chunk intervals
- Optimize time-series database performance or storage
- Create tables for sensors, metrics, telemetry, events, or transaction logs

**Keywords:** CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segment_by, order_by

Step-by-step instructions for hypertable creation, column selection, compression policies, retention, continuous aggregates, and indexes.

## Metadata
- Owner: io.github.timescale
- Version: 0.5.1
- Runtime: Npm, Oci
- Transports: STDIO, HTTP
- License: Not captured
- Language: Not captured
- Stars: Not captured
- Updated: Jun 26, 2026
- Source: https://registry.modelcontextprotocol.io