Grepai Storage Postgres

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

1. PostgreSQL 14+ with pgvector extension
2. Database user with create table permissions
3. Network access to PostgreSQL server

Advantages

Setting Up PostgreSQL with pgvector

Option 1: Docker (Recommended for Development)

# Run PostgreSQL with pgvectordocker 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 sourcegit clone https://github.com/pgvector/pgvector.gitcd pgvectormakesudo make install

Then enable the extension:

-- Connect to your databaseCREATE 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.yamlstore:  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

# Production with SSLstore:  backend: postgres  postgres:    dsn: postgres://user:[email protected]:5432/grepai?sslmode=require

Database Schema

GrepAI automatically creates these tables:

-- Vector embeddings tableCREATE 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 searchCREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops);

Verifying Setup

Check pgvector Extension

-- Connect to databasepsql -U grepai -d grepai
-- Check extension is installedSELECT * FROM pg_extension WHERE extname = 'vector';
-- Check GrepAI tables exist (after first grepai watch)\dt

Test Connection from GrepAI

# Check statusgrepai status
# Should show PostgreSQL backend info

Performance Tuning

PostgreSQL Configuration

For better vector search performance:

-- Increase work memory for vector operationsSET work_mem = '256MB';
-- Adjust for your hardwareSET effective_cache_size = '4GB';SET shared_buffers = '1GB';

Index Tuning

For large indices, tune the IVFFlat index:

-- More lists = faster search, more memoryCREATE INDEX ON embeddingsUSING 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.yamlstore:  backend: postgres  postgres:    dsn: postgres://team:[email protected]:5432/grepai

Per-Project Databases

For isolated projects, use separate databases:

# Create databasescreatedb -U postgres grepai_projectacreatedb -U postgres grepai_projectb

# Project A configstore:  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

1. Set up PostgreSQL with pgvector
2. Update configuration:

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

3. Delete old index:

rm .grepai/index.gob

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

1. Use environment variables: Don't commit credentials
2. Enable SSL: For remote databases
3. Regular backups: pg_dump before major changes
4. Monitor performance: Check query times
5. 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