---
title: "Building a Local PostgreSQL Internals Assistant - Zero API Costs"
description: "Local PostgreSQL internals assistant — semantic search over PG source via pgvector + Ollama, exposed as MCP tools in Zed and VSCode. Free, offline, zero API cos"
author: "Samba Siva"
published: 2026-06-05T16:36:36.535Z
updated: 2026-11-06T14:05:21.816Z
tags:
  - "Technical"
  - "Artificial Intelligence"
  - "LLM"
url: "https://sambasivareddy.in/blog/building-a-local-postgresql-internals-assistant-zero-api-costs"
---

# 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

```bash
# 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:

```bash
# 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.

```bash
# Start manually
ollama serve

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

* * *

## Step 2: Set Up pgvector

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

Connect to your PostgreSQL cluster and set up the schema:

```sql
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

```bash
brew install cscope universal-ctags
```

From your PostgreSQL source root:

```bash
# 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:

```bash
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`:

```python
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):

```bash
ollama serve &
python index_pg_source.py
```

Verify:

```sql
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`:

```python
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):

```json
{
  "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:

```json
{
  "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:

```bash
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.

| Component | RAM |
| --- | --- |
| Ollama (idle) | ~50MB |
| nomic-embed-text loaded | ~550MB |
| qwen2.5-coder:7b loaded | ~4.5GB |
| PG 18 cluster | ~200MB |
| Zed + browser | ~2–3GB |
| **Total** | **~7.5GB** |

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](https://github.com/samsiva-dev/pg_mcp_server)

* * *