GrepAI Storage with PostgreSQL

This skill covers using PostgreSQL with the pgvector extension as the storage backend for GrepAI.

When to Use This Skill

• Team environments with shared index

• Large codebases (10K+ files)

• Need concurrent access

• Integration with existing PostgreSQL infrastructure

Prerequisites

• PostgreSQL 14+ with pgvector extension

• Database user with create table permissions

• Network access to PostgreSQL server

Advantages

Benefit Description

👥 Team sharing Multiple users can access same index

📏 Scalable Handles large codebases

🔄 Concurrent Multiple simultaneous searches

💾 Persistent Data survives machine restarts

🔧 Familiar Standard database tooling

Setting Up PostgreSQL with pgvector

Option 1: Docker (Recommended for Development)

Run PostgreSQL with pgvector

docker run -d
--name grepai-postgres
-e POSTGRES_USER=grepai
-e POSTGRES_PASSWORD=grepai
-e POSTGRES_DB=grepai
-p 5432:5432
pgvector/pgvector:pg16

Option 2: Install on Existing PostgreSQL

Install pgvector extension (Ubuntu/Debian)

sudo apt install postgresql-16-pgvector

Or compile from source

git clone https://github.com/pgvector/pgvector.git cd pgvector make sudo make install

Then enable the extension:

-- Connect to your database CREATE EXTENSION IF NOT EXISTS vector;

Option 3: Managed Services

• Supabase: pgvector included by default

• Neon: pgvector available

• AWS RDS: Install pgvector extension

• Azure Database: pgvector available

Configuration

Basic Configuration

.grepai/config.yaml

store: backend: postgres postgres: dsn: postgres://user:password@localhost:5432/grepai

With Environment Variable

store: backend: postgres postgres: dsn: ${DATABASE_URL}

Set the environment variable:

export DATABASE_URL="postgres://user:password@localhost:5432/grepai"

Full DSN Options

store: backend: postgres postgres: dsn: postgres://user:password@host:5432/database?sslmode=require

DSN components:

• user : Database username

• password : Database password

• host : Server hostname or IP

• 5432 : Port (default: 5432)

• database : Database name

• sslmode : SSL mode (disable, require, verify-full)

SSL Modes

Mode Description Use Case

disable

No SSL Local development

require

SSL required Production

verify-full

SSL + verify certificate High security

Production with SSL

store: backend: postgres postgres: dsn: postgres://user:pass@prod.db.com:5432/grepai?sslmode=require

Database Schema

GrepAI automatically creates these tables:

-- Vector embeddings table CREATE TABLE IF NOT EXISTS embeddings ( id SERIAL PRIMARY KEY, file_path TEXT NOT NULL, chunk_index INTEGER NOT NULL, content TEXT NOT NULL, start_line INTEGER, end_line INTEGER, embedding vector(768), -- Dimension matches your model created_at TIMESTAMP DEFAULT NOW(), UNIQUE(file_path, chunk_index) );

-- Index for vector similarity search CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops);

Verifying Setup

Check pgvector Extension

-- Connect to database psql -U grepai -d grepai

-- Check extension is installed SELECT * FROM pg_extension WHERE extname = 'vector';

-- Check GrepAI tables exist (after first grepai watch) \dt

Test Connection from GrepAI

Check status

grepai status

Should show PostgreSQL backend info

Performance Tuning

PostgreSQL Configuration

For better vector search performance:

-- Increase work memory for vector operations SET work_mem = '256MB';

-- Adjust for your hardware SET effective_cache_size = '4GB'; SET shared_buffers = '1GB';

Index Tuning

For large indices, tune the IVFFlat index:

-- More lists = faster search, more memory CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); -- Adjust based on row count

Rule of thumb: lists = sqrt(rows)

Concurrent Access

PostgreSQL handles concurrent access automatically:

• Multiple grepai search commands work simultaneously

• One grepai watch daemon per codebase

• Many users can share the same index

Team Setup

Shared Database

All team members point to the same database:

Each developer's .grepai/config.yaml

store: backend: postgres postgres: dsn: postgres://team:secret@shared-db.company.com:5432/grepai

Per-Project Databases

For isolated projects, use separate databases:

Create databases

createdb -U postgres grepai_projecta createdb -U postgres grepai_projectb

Project A config

store: backend: postgres postgres: dsn: postgres://user:pass@localhost:5432/grepai_projecta

Backup and Restore

Backup

pg_dump -U grepai -d grepai > grepai_backup.sql

Restore

psql -U grepai -d grepai < grepai_backup.sql

Migrating from GOB

• Set up PostgreSQL with pgvector

• Update configuration:

store: backend: postgres postgres: dsn: postgres://user:pass@localhost:5432/grepai

• Delete old index:

rm .grepai/index.gob

• Re-index:

grepai watch

Common Issues

❌ Problem: FATAL: password authentication failed

✅ Solution: Check DSN credentials and pg_hba.conf

❌ Problem: ERROR: extension "vector" is not available

✅ Solution: Install pgvector:

sudo apt install postgresql-16-pgvector

Then: CREATE EXTENSION vector;

❌ Problem: ERROR: type "vector" does not exist

✅ Solution: Enable extension in the database:

CREATE EXTENSION IF NOT EXISTS vector;

❌ Problem: Connection refused ✅ Solution:

• Check PostgreSQL is running

• Verify host and port

• Check firewall rules

❌ Problem: Slow searches ✅ Solution:

• Add IVFFlat index

• Increase work_mem

• Vacuum and analyze tables

Best Practices

• Use environment variables: Don't commit credentials

• Enable SSL: For remote databases

• Regular backups: pg_dump before major changes

• Monitor performance: Check query times

• Index maintenance: Regular VACUUM ANALYZE

Output Format

PostgreSQL storage status:

✅ PostgreSQL Storage Configured

Backend: PostgreSQL + pgvector Host: localhost:5432 Database: grepai SSL: disabled

Contents:

• Files: 2,450
• Chunks: 12,340
• Vector dimension: 768

Performance:

• Connection: OK
• IVFFlat index: Yes
• Search latency: ~50ms