Vector Database

CodePilot stores and queries vector embeddings using PostgreSQL with the pgvector extension. This avoids the need for a separate vector database like Pinecone or Weaviate — everything lives in a single PostgreSQL instance.

Why pgvector?

Since CodePilot already uses PostgreSQL for application data (users, repositories, sessions), adding pgvector keeps the architecture simple. All data lives in one database with one backup strategy.

Setup

pgvector is included in the Docker image pgvector/pgvector:pg18:

services:
  postgres:
    image: pgvector/pgvector:pg18
    container_name: codepilot_postgres
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "${POSTGRES_PORT:-5432}:5432"
    volumes:
      - postgres_data:/var/lib/postgresql

The extension is enabled via a Prisma migration:

CREATE EXTENSION IF NOT EXISTS vector;

Schema

Embeddings are stored in the CodeEmbedding table:

model CodeEmbedding {
  id            String     @id @default(cuid())
  chunkId       String
  chunk         CodeChunk  @relation(fields: [chunkId], references: [id])
  embedding     Unsupported("vector(768)")
  repositoryId  String
  repository    Repository @relation(fields: [repositoryId], references: [id])
  createdAt     DateTime   @default(now())
}

Indexing

CodePilot uses HNSW (Hierarchical Navigable Small World) indexing for fast approximate nearest-neighbor search:

CREATE INDEX idx_code_embedding_hnsw
  ON "CodeEmbedding"
  USING hnsw ("embedding" vector_cosine_ops);

HNSW vs. IVFFlat

HNSW is preferred because it provides better recall and query speed, which directly impacts the quality of RAG responses.

Similarity Search

Queries are performed using cosine distance (<=>) operator:

SELECT
  ce."id",
  cc."content",
  cc."filePath",
  cc."startLine",
  cc."endLine",
  ce."embedding" <=> $1::vector AS distance
FROM "CodeEmbedding" ce
JOIN "CodeChunk" cc ON cc."id" = ce."chunkId"
WHERE ce."repositoryId" = $2
ORDER BY ce."embedding" <=> $1::vector
LIMIT 10;

The $1 parameter is the query embedding vector, and $2 is the repository ID. Results are ordered by cosine distance (lower = more similar).

Performance

For a repository with ~5,000 code chunks:

pgvector with HNSW handles repositories with tens of thousands of chunks efficiently. For larger codebases, the index parameters (m and ef_construction) can be tuned.