Building a Local PostgreSQL Internals Assistant - Zero API Costs

If you work deep in PostgreSQL internals, you know the problem: the source is ~1.5 million lines of C spread across hundreds of files. You want to understand how ReadBuffer relates to StrategyGetBuffer, or trace the WAL flush path from XLogInsert to disk - and you end up either grep-ing blindly or burning through API credits asking a hosted LLM that may not even have the right source version in context.

I wanted something better: a setup where I could ask natural language questions about PostgreSQL source code and get answers grounded in the actual C files on my machine - not from some LLM's training data. And I wanted it free, local, and integrated into my editor.

This post walks through exactly that.

What We're Building

A three-layer stack:

ctags + cscope          → deterministic symbol navigation
pgvector on PG 18       → semantic search over indexed source chunks
Ollama (local LLM)      → natural language reasoning over retrieved context
MCP server              → exposes all of this as tools inside Zed / VSCode

The key insight: these layers answer different types of questions.

• ctags/cscope: "Where is ReadBuffer defined? Who calls it?" - deterministic, instant

• pgvector: "Find code related to buffer eviction clock sweep" - semantic, fuzzy

• LLM: "Explain what this function does in context of buffer management" - reasoning

No single tool does all three well. Together, they cover the full range of questions you ask when reading unfamiliar source code.

Prerequisites

• PostgreSQL running locally with pgvector extension

• PostgreSQL Source code

• Zed or VSCode with GitHub Copilot / Agent Panel

Step 1: Install Ollama

# Install via official installer (not Homebrew — the brew version
# may be missing the llama-server binary)
curl -fsSL https://ollama.com/install.sh | sh

Pull two models — one for embeddings, one for chat:

# Lightweight code reasoning model (~4.5GB, runs well on 16GB M-series)
ollama pull qwen2.5-coder:7b

# Embedding model (~500MB)
ollama pull nomic-embed-text

A note on RAM: qwen2.5-coder:7b uses ~4.5GB when loaded. Models only load into RAM when a request comes in and unload after ~5 minutes of idle. So the server itself (ollama serve) is just ~50MB - start it when you need it, kill it when done.

# Start manually
ollama serve

# Stop (Ctrl+C, or from another terminal)
pkill ollama

Step 2: Set Up pgvector

brew install pgvector
(or) 
make PG_CONFIG=$pg_config install

Connect to your PostgreSQL cluster and set up the schema:

CREATE DATABASE pg_source_index;
\c pg_source_index

CREATE EXTENSION vector;

CREATE TABLE code_chunks (
    id            SERIAL PRIMARY KEY,
    file_path     TEXT NOT NULL,
    function_name TEXT,
    chunk_text    TEXT NOT NULL,
    embedding     vector(768),
    start_line    INT,
    end_line      INT,
    subsystem     TEXT
);

-- HNSW index for fast cosine similarity search
CREATE INDEX ON code_chunks
USING hnsw (embedding vector_cosine_ops);

I set it up on PostgreSQL 18, the table ends up at ~297MB for the full PostgreSQL source (33,734 chunks).

Step 3: Build cscope + ctags Index

brew install cscope universal-ctags

From your PostgreSQL source root:

# ctags — jump-to-definition, call hierarchies
ctags -R --languages=C --fields=+iaS --extras=+q .

# cscope — cross-reference index (who calls what, where defined)
find . -name "*.c" -o -name "*.h" | xargs ls 2>/dev/null > cscope.files
cscope -b -q -k

You'll get a tags file and three cscope.out files. These are deterministic and fast — no LLM involved.

Note: You may see warnings about missing generated files (fmgroids.h, nodetags.h, etc.). These are build-time generated files. The index still builds correctly for all existing source files.

Step 4: Index PG Source into pgvector

Create a Python virtual environment:

python3 -m venv ~/pg-tools-env
source ~/pg-tools-env/bin/activate
pip install psycopg2-binary requests mcp

Save this as index_pg_source.py:

import os
import re
import psycopg2
import requests

PG_CONN   = "dbname=pg_source_index"
PG_SRC    = "/path/to/postgresql"       # your PG source root
OLLAMA    = "http://localhost:11434"
EMB_MODEL = "nomic-embed-text"

SUBSYSTEMS = {
    "storage/buffer": "buffer",
    "storage/smgr":   "storage",
    "access/heap":    "heap",
    "executor":       "executor",
    "storage/lmgr":   "locking",
    "access/transam": "mvcc",
    "replication/walreceiver": "wal",
    "access/rmgrdesc": "wal",
}

def get_subsystem(path):
    for pattern, name in SUBSYSTEMS.items():
        if pattern in path:
            return name
    return "other"

def extract_functions(content):
    chunks = []
    lines = content.split('\n')
    current_chunk = []
    current_start = 1
    brace_depth = 0
    in_function = False

    for i, line in enumerate(lines, 1):
        current_chunk.append(line)
        brace_depth += line.count('{') - line.count('}')
        if brace_depth == 0 and in_function:
            chunk_text = '\n'.join(current_chunk)
            if len(chunk_text.strip()) > 50:
                chunks.append((chunk_text, current_start, i))
            current_chunk = []
            current_start = i + 1
            in_function = False
        elif brace_depth > 0:
            in_function = True

    return chunks

def embed(text):
    resp = requests.post(f"{OLLAMA}/api/embeddings", json={
        "model": EMB_MODEL,
        "prompt": text[:2000]
    })
    return resp.json()["embedding"]

def index_file(cur, filepath):
    with open(filepath, 'r', errors='ignore') as f:
        content = f.read()

    subsystem = get_subsystem(filepath)
    chunks = extract_functions(content)

    for chunk_text, start, end in chunks:
        match = re.search(r'^(\w+)\s*\(', chunk_text, re.MULTILINE)
        fn_name = match.group(1) if match else None
        embedding = embed(chunk_text)
        cur.execute("""
            INSERT INTO code_chunks
                (file_path, function_name, chunk_text, embedding,
                 start_line, end_line, subsystem)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
        """, (filepath, fn_name, chunk_text, embedding, start, end, subsystem))

def main():
    conn = psycopg2.connect(PG_CONN)
    conn.autocommit = True
    cur = conn.cursor()

    c_files = []
    for root, _, files in os.walk(PG_SRC):
        for f in files:
            if f.endswith('.c') or f.endswith('.h'):
                c_files.append(os.path.join(root, f))

    print(f"Indexing {len(c_files)} files...")
    for i, fp in enumerate(c_files):
        print(f"[{i+1}/{len(c_files)}] {fp}")
        try:
            index_file(cur, fp)
        except Exception as e:
            print(f"  ERROR: {e}")

    print("Done.")

if __name__ == "__main__":
    main()

Run it (takes 15–30 minutes, one-time):

ollama serve &
python index_pg_source.py

Verify:

SELECT COUNT(*) FROM code_chunks;

Step 5: The MCP Server

This is where it comes together. Instead of switching to a terminal to query the index, we expose it as MCP tools directly inside the editor.

Save as pg_mcp_server.py:

import subprocess
import psycopg2
import requests
from mcp.server.fastmcp import FastMCP

PG_CONN     = "dbname=pg_source_index"
OLLAMA      = "http://localhost:11434"
EMB_MODEL   = "nomic-embed-text"
CHAT_MODEL  = "qwen2.5-coder:7b"
TOP_K       = 5
PG_SRC      = "/path/to/postgresql"     # where cscope.out lives

mcp = FastMCP("pg-source-explorer")

def embed(text):
    resp = requests.post(f"{OLLAMA}/api/embeddings", json={
        "model": EMB_MODEL, "prompt": text[:2000]
    })
    return resp.json()["embedding"]

def vector_search(query, subsystem=None, top_k=TOP_K):
    conn = psycopg2.connect(PG_CONN)
    cur  = conn.cursor()
    q_embed = str(embed(query))
    if subsystem:
        cur.execute("""
            SELECT file_path, function_name, chunk_text, subsystem,
                   1 - (embedding <=> %s::vector) AS similarity
            FROM code_chunks WHERE subsystem = %s
            ORDER BY embedding <=> %s::vector LIMIT %s
        """, (q_embed, subsystem, q_embed, top_k))
    else:
        cur.execute("""
            SELECT file_path, function_name, chunk_text, subsystem,
                   1 - (embedding <=> %s::vector) AS similarity
            FROM code_chunks
            ORDER BY embedding <=> %s::vector LIMIT %s
        """, (q_embed, q_embed, top_k))
    rows = cur.fetchall()
    cur.close(); conn.close()
    return rows

@mcp.tool()
def ask_pg(question: str, subsystem: str = "") -> str:
    """
    Ask a natural language question about PostgreSQL internals.
    Searches indexed PG source and returns an LLM answer grounded
    in actual source context.

    Args:
        question  : e.g. "How does PostgreSQL evict dirty buffers?"
        subsystem : Optional filter — buffer, wal, executor, mvcc,
                    heap, storage, locking. Leave empty for all.
    """
    results = vector_search(question, subsystem=subsystem.strip() or None)
    if not results:
        return "No relevant chunks found. Is the index built?"

    context = "\n\n---\n\n".join([
        f"File: {r[0]}\nFunction: {r[1]}\nSubsystem: {r[3]}\n\n{r[2]}"
        for r in results
    ])
    prompt = f"""You are an expert in PostgreSQL internals at C source level.
Use the source code context below to answer the question.
Always reference specific function names and file paths.

CONTEXT:
{context}

QUESTION: {question}
Answer:"""

    resp = requests.post(f"{OLLAMA}/api/generate", json={
        "model": CHAT_MODEL, "prompt": prompt, "stream": False
    })
    return resp.json()["response"]

@mcp.tool()
def search_pg(query: str, subsystem: str = "", top_k: int = 5) -> str:
    """
    Semantic search over indexed PostgreSQL source.
    Returns top matching code chunks with file paths and similarity scores.

    Args:
        query     : e.g. "buffer eviction clock sweep"
        subsystem : Optional filter (buffer, wal, executor, etc.)
        top_k     : Number of results (default 5, max 10)
    """
    results = vector_search(query, subsystem=subsystem.strip() or None,
                            top_k=min(top_k, 10))
    if not results:
        return "No results found."

    output = []
    for i, r in enumerate(results, 1):
        output.append(
            f"── Result {i} ──\n"
            f"File: {r[0]}  Function: {r[1]}  "
            f"Subsystem: {r[3]}  Similarity: {r[4]:.3f}\n\n"
            f"{r[2][:800]}{'...' if len(r[2]) > 800 else ''}"
        )
    return "\n\n".join(output)

@mcp.tool()
def search_symbol(symbol: str, search_type: str = "callers") -> str:
    """
    Look up a C symbol in PostgreSQL source using cscope.

    Args:
        symbol      : e.g. "ReadBuffer", "BufMgrLock"
        search_type : callers | definition | references | callees
    """
    type_map = {"callers": "3", "definition": "1",
                "references": "0", "callees": "2"}
    flag = type_map.get(search_type, "3")

    try:
        result = subprocess.run(
            ["cscope", "-d", "-f", f"{PG_SRC}/cscope.out",
             "-L", f"-{flag}", symbol],
            capture_output=True, text=True, timeout=10
        )
        if not result.stdout.strip():
            return f"No {search_type} found for '{symbol}'."

        lines = result.stdout.strip().split('\n')
        output = [f"cscope {search_type} for '{symbol}':\n"]
        for line in lines[:30]:
            parts = line.split()
            if len(parts) >= 3:
                output.append(f"  {parts[0]}:{parts[2]}  (in {parts[1]})")
            else:
                output.append(f"  {line}")
        if len(lines) > 30:
            output.append(f"\n  ... and {len(lines) - 30} more")
        return "\n".join(output)

    except FileNotFoundError:
        return "cscope not found. Run: brew install cscope"
    except subprocess.TimeoutExpired:
        return "cscope timed out."

if __name__ == "__main__":
    print("Starting pg-source-explorer MCP server...")
    print(f"  Chat model : {CHAT_MODEL}")
    print(f"  Embed model: {EMB_MODEL}")
    mcp.run(transport="stdio")

Step 6: Wire into Zed

In Zed settings.json (Cmd+Shift+P → open settings):

{
  "context_servers": {
    "pg-source-explorer": {
      "source": "custom",
      "command": {
        "path": "/Users/yourname/pg-tools-env/bin/python",
        "args": ["/path/to/pg_mcp_server.py"]
      }
    }
  }
}

Restart Zed. Check the Agent Panel for a green dot next to pg-source-explorer.

Step 6 (alt) — Wire into VSCode

Create ~/.vscode/mcp.json for global access:

{
  "servers": {
    "pg-source-explorer": {
      "type": "stdio",
      "command": "/Users/yourname/pg-tools-env/bin/python",
      "args": ["/path/to/pg_mcp_server.py"],
      "env": {}
    }
  }
}

Open the file in VSCode — click the Start CodeLens button that appears above the server entry. Then switch Copilot Chat to Agent mode and enable the tools.

Using It

Always start your PostgreSQL cluster and Ollama first:

bin/pg_ctl -D /path/to/cluster start
source ~/pg-tools-env/bin/activate
ollama serve

Then in the Agent Panel:

# Full RAG answer grounded in source
Use the ask_pg tool to explain how PostgreSQL evicts dirty buffers

# Raw source chunks
Use search_pg to find code related to clock sweep

# Call graph
Use search_symbol to find who calls ReadBuffer

Does It Actually Help?

Honest answer: yes, with caveats.

The biggest value is cscope + ctags — deterministic, instant, no LLM needed.

The RAG layer (ask_pg) is genuinely useful when you want to understand what a function does in context, not just navigate to it. The answer is grounded in your local source, not the LLM's training data - so it references the exact version of PG you're working with.

Here's a concrete example. When I asked about MemoryContext, the RAG answer surfaced the callback mechanism (MemoryContextCallback) - a detail the hosted LLM skipped entirely. That came directly from mcxt.h chunks in the index.

The LLM layer (qwen2.5-coder:7b) is a 7B model - fine for code explanation, not as strong as Claude for architecture reasoning. I use local LLM for source navigation, hosted Claude for design decisions.

Comfortable even on 16GB. Models unload after 5 minutes idle, so you're not paying the RAM cost the whole session.

The Full Stack

Zed / VSCode Agent Panel
        ↓
pg-source-explorer MCP (3 tools)
        ↓
ask_pg → pgvector similarity (HNSW) → qwen2.5-coder:7b
search_pg → pgvector similarity
search_symbol → cscope
        ↓
33,734 chunks of PostgreSQL 18 source — on your machine, for free

The code is on GitHub: https://github.com/samsiva-dev/pg_mcp_server