# pgvector MCP

> pgvector runs vector similarity searches and manages embedding tables right inside your existing PostgreSQL database. It lets you build complex AI retrieval pipelines without writing custom API endpoints or deploying new microservices. Simply connect it to your agent, ask questions about your data structure, and watch the agent handle everything from creating HNSW indexes to running nearest-neighbor queries across millions of vectors.

## Overview
- **Category:** loved-by-devs
- **Price:** Free
- **Tags:** embeddings, similarity-search, postgresql, nearest-neighbor, vector-index

## Description

Look, you're building complex AI retrieval pipelines, and you don't wanna write custom API endpoints or deploy a whole bunch of new microservices just to run vector searches. This pgvector setup lets your agent handle the heavy lifting right inside your existing PostgreSQL database. You connect it once, and suddenly you can ask questions about your data structure, and your agent manages everything from building indexes to running nearest-neighbor queries across millions of embeddings.

If you need to know what vector data lives in your system, run `list_tables`. This tool shows every single table in the database that has a dedicated vector column, giving you a clear overview of all your current knowledge sources. If none of those tables are set up right, call `create_table`. That sets up a brand new table specifically structured to hold high-dimensional embeddings and any associated metadata you need. Think of it as building the perfect digital filing cabinet for your data.

Once the structure's ready, you gotta get data in there. Use `insert_vector` to programmatically add new vectors along with their corresponding descriptive metadata into an existing embedding table. If a source document changes or gets archived, don't leave old junk lying around—you can use `delete_vector` to remove specific vector entries when the original source data is outdated or incorrect.

The main event is finding stuff. When you need to find vectors most similar to a query—say, a user prompt or another embedding—run `search_vectors`. This tool performs a nearest-neighbor query, letting you use specific distance metrics like cosine, L2, or inner product against massive sets of data. But running those searches across millions of vectors without killing your performance? That's where optimization comes in.

If the search speed isn't fast enough, execute `create_index`. This builds highly efficient performance indexes—you can specify HNSW or IVFFlat—right on your vector columns. It dramatically speeds up those approximate nearest-neighbor (ANN) queries. You run this once, and suddenly searching feels instantaneous.

Your agent handles the entire workflow: it checks what tables exist, uses `create_table` if they don't, populates them with `insert_vector`, optimizes them with `create_index`, and then runs a lightning-fast search using `search_vectors`. You just ask your AI client to find information based on context, and the agent handles all the database commands required.

## Tools

### create_index
Builds performance indexes (like HNSW) on vector columns to speed up similarity searches dramatically.

### create_table
Creates a new table specifically structured to hold high-dimensional embeddings and associated metadata.

### delete_vector
Removes specific vector entries from a table when the source data is outdated or incorrect.

### insert_vector
Adds new vectors and their corresponding metadata into an existing embedding table.

### list_tables
Shows all tables in the database that currently contain vector columns, helping you map out your data sources.

### search_vectors
Runs a nearest-neighbor query to find vectors most similar to a given search query or embedding.

## Prompt Examples

**Prompt:** 
```
Show me all tables with vector columns in my database.
```

**Response:** 
```
Found 2 tables with vector columns: `document_chunks` (1536 dimensions, 48,200 rows, HNSW index active) and `product_embeddings` (768 dimensions, 12,500 rows, no index). Want me to create an IVFFlat index on `product_embeddings`?
```

**Prompt:** 
```
Search for the 5 most similar documents to this query in the document_chunks table.
```

**Response:** 
```
Top 5 results from `document_chunks` (cosine distance):
1. "API Authentication Guide" — distance: 0.12
2. "OAuth 2.0 Setup" — distance: 0.18
3. "Token Refresh Flow" — distance: 0.23
4. "Security Best Practices" — distance: 0.31
5. "Rate Limiting" — distance: 0.35
Want me to retrieve the full content of the top result?
```

**Prompt:** 
```
Create a new table called 'support_tickets' with 1536-dimension vectors and an HNSW index.
```

**Response:** 
```
Done. Created table `support_tickets` with columns: `id TEXT PRIMARY KEY`, `embedding vector(1536)`, `metadata JSONB`. Then created HNSW index using `vector_cosine_ops`. The table is ready for inserts.
```

## Capabilities

### Run similarity searches
Use `search_vectors` to perform nearest-neighbor queries across large sets of embeddings using specific distance metrics.

### Define vector structures
Call `create_table` to set up new tables specifically designed to hold high-dimensional vector data and associated metadata.

### Insert and manage vectors
Use `insert_vector` or `delete_vector` to programmatically add, modify, or remove individual vector entries from existing tables.

### List available schemas
Run `list_tables` to get a clear overview of all tables in the database that contain vector columns.

### Optimize query speed
Execute `create_index` to build performance indexes (HNSW or IVFFlat) on your vector columns, making searches much faster.

## Use Cases

### Building a Knowledge Base Index
A data architect needs to index 10 million document chunks. They first use `list_tables` to check existing schemas, then ask the agent to `create_table` with the proper vector dimension. Finally, they trigger bulk inserts using `insert_vector`, and immediately call `create_index` so the system is fast on day one.

### Retrieving Context for Chatbot Answers
A fullstack dev wants a chatbot to answer questions based on internal documents. The user asks the agent, 'What's the policy on PTO?' The agent runs `search_vectors` using cosine distance against the document chunks table and returns the top 5 relevant passages for the LLM.

### Updating Outdated Product Embeddings
A product manager updates a product catalog. They don't want to overwrite old data, so they first use `delete_vector` on the outdated record IDs and then run targeted batches of `insert_vector` for the new embeddings.

### Auditing Database Readiness
An AI engineer suspects some tables are missing vector capabilities. They simply ask the agent to 'Show me all tables with vectors.' The agent runs `list_tables`, providing a clear audit trail of every available embedding source.

## Benefits

- **Cut out the middleware.** Instead of building and maintaining a separate vector service layer, you keep all your embeddings right where they belong: Postgres. You just call `search_vectors` through the agent.
- **Speed up searches with `create_index`.** Building HNSW or IVFFlat indexes on your vector columns dramatically reduces query latency, making real-time RAG possible without complex optimization passes.
- **Manage data structure in one place.** Use `list_tables` to audit your entire schema and `create_table` when you need a new embedding source. It’s all conversational setup.
- **Maintain clean knowledge bases.** If content changes, use `delete_vector` followed by targeted `insert_vector` calls. You keep the data current without manual database scripts.
- **Works with existing apps.** Because everything stays in Postgres, you can add semantic search to your current stack simply by letting the agent call `search_vectors`, zero new microservices required.

## How It Works

The bottom line is that you talk to your AI client like a database administrator. It translates your intent into precise PostgreSQL commands, handles the execution, and gives you the clean answer.

1. First, connect the agent by providing the PostgreSQL connection string. This links the conversational layer to your database.
2. Next, ask the agent to execute a foundational command—like `list_tables` or `create_table`—to map out the required data structure.
3. Finally, give it the task: 'Find the top 5 documents similar to X query.' The agent then runs the necessary sequence of calls (`search_vectors`) and returns the results.

## Frequently Asked Questions

**How do I start using pgvector with search_vectors?**
You must first ensure your table exists and has vectors. Start by running `list_tables` to confirm, then use the agent to construct a query calling `search_vectors` against the target table.

**Is pgvector MCP Server better than using an external vector database?**
It’s simpler. By keeping everything in Postgres, you cut out the latency and complexity of cross-database calls. You manage one connection string instead of two or three.

**What is the difference between create_table and list_tables?**
`list_tables` shows you what's already there, like an inventory check. `create_table` builds a brand new container for vectors when you need to ingest data into a fresh source.

**How often should I use create_index?**
Whenever your dataset grows substantially or if search performance degrades, run `create_index`. It turns slow linear scans into rapid approximate nearest-neighbor lookups.

**What permissions are needed to successfully run `create_table`?**
You need full write access, specifically CREATE TABLE rights. While the agent handles the syntax, your underlying database user must have permissions to create schema objects and enable the pgvector extension first.

**What happens if I use `insert_vector` with incorrect dimensions?**
The tool will immediately throw a dimension mismatch error. You must ensure that every vector you send via `insert_vector` matches the exact dimensionality of the target column defined in your table's schema.

**Should I use `create_index` or rely on PostgreSQL for searching vectors?**
You must run `create_index`. Relying only on default database indexing causes performance to degrade dramatically as your dataset grows past a few thousand records. The index is what makes similarity search fast.

**How can I verify if my tables are ready for high-volume searching before running `search_vectors`?**
First, use `list_tables` to check the schema and current index status. If your vector column doesn't show an active HNSW or IVFFlat index, run `create_index` immediately before attempting complex searches.

**Does the agent connect directly to my database?**
Yes. Your connection string is encrypted at rest and injected into an isolated runtime. The agent connects directly to your PostgreSQL instance — no intermediate proxies, no data copies, no third-party storage.

**What vector dimensions are supported?**
Any dimension supported by pgvector — from small 128-d vectors to large 3072-d embeddings (e.g., OpenAI text-embedding-3-large). Specify the dimension when creating a table and the agent handles the rest.

**Which distance metrics can I use for similarity search?**
pgvector supports three operators: `` (L2/Euclidean distance), `` (cosine distance), and `` (negative inner product). The agent uses cosine distance by default, which works best for normalized embeddings like those from OpenAI.