Part 5: Build a Local pgVector Tool

The easiest way to stop treating vector databases like magic is to build a small one-purpose tool with them.

In this final post, we will build a local command-line search tool backed by Postgres and pgvector. It will index a few notes, store their embeddings, and return the closest matches for a natural-language query.

The point is not to create a production RAG stack. The point is to make the moving parts visible:

a normal relational database
one vector column
one embedding model
one nearest-neighbor query
one tiny interface you can run locally

What We Are Building

The tool will support two commands:

npm run index
npm run search -- "what should I read before choosing a vector database?"

index reads local notes, embeds them, and stores them in Postgres.

search embeds the query and asks Postgres for the nearest note chunks using the pgvector distance operator.

Start Postgres with pgVector

Create a project folder:

mkdir local-pgvector-search
cd local-pgvector-search
npm init -y
npm install pg dotenv openai

Then add a docker-compose.yml:

services:
  postgres:
    image: pgvector/pgvector:pg16
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: vectors
    ports:
      - "5432:5432"
    volumes:
      - pgvector-data:/var/lib/postgresql/data
 
volumes:
  pgvector-data:

Start it:

docker compose up -d

Add .env:

DATABASE_URL=postgres://app:app@localhost:5432/vectors
OPENAI_API_KEY=your_api_key_here

Tip

If you want a completely offline version, replace the embedding call with a local embedding model such as Nomic Embed Text through Ollama. The database code stays almost exactly the same.

Create the Schema

Add schema.sql:

create extension if not exists vector;
 
create table if not exists notes (
  id bigserial primary key,
  source text not null,
  content text not null,
  embedding vector(1536) not null,
  created_at timestamptz not null default now()
);
 
create index if not exists notes_embedding_hnsw_idx
on notes
using hnsw (embedding vector_cosine_ops);

The only unusual line is embedding vector(1536). That number must match the embedding model dimension. For text-embedding-3-small, the default dimension is 1536.

Run the schema:

psql postgres://app:app@localhost:5432/vectors -f schema.sql

If psql is not installed on your machine, run it inside the container:

docker compose exec postgres psql -U app -d vectors -f /dev/stdin < schema.sql

Add Some Notes

Create a notes folder:

mkdir notes

Add notes/choosing-a-vector-database.md:

Choose a vector database based on operational fit first. If the team already runs Postgres well, pgVector can be the simplest path. If workloads require huge-scale approximate search, dedicated vector systems may fit better.

Add notes/chunking.md:

Chunking controls retrieval quality. Chunks should be large enough to preserve meaning and small enough to avoid mixing unrelated ideas. Start simple, evaluate results, then tune.

Add notes/metadata.md:

Metadata filters are often more important than vector search itself. Tenant, permission, document type, source, and freshness filters keep retrieval useful and safe.

Write the Database Helper

Add db.js:

import "dotenv/config";
import pg from "pg";
 
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});
 
export async function closeDb() {
  await pool.end();
}

Update package.json so Node treats files as ES modules:

{
  "type": "module",
  "scripts": {
    "index": "node index.js",
    "search": "node search.js"
  }
}

Keep the other fields that npm init created.

Create the Embedding Function

Add embed.js:

import OpenAI from "openai";
 
const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});
 
export async function embed(text) {
  const response = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: text,
  });
 
  return response.data[0].embedding;
}
 
export function toVectorLiteral(values) {
  return `[${values.join(",")}]`;
}

The database driver does not need to understand vectors as a special JavaScript type. We send the vector as a Postgres vector literal like [0.012,-0.019,...].

Index the Notes

Add index.js:

import fs from "node:fs/promises";
import path from "node:path";
import { pool, closeDb } from "./db.js";
import { embed, toVectorLiteral } from "./embed.js";
 
const notesDir = path.join(process.cwd(), "notes");
 
async function main() {
  const files = await fs.readdir(notesDir);
 
  await pool.query("truncate table notes restart identity");
 
  for (const file of files) {
    if (!file.endsWith(".md")) continue;
 
    const source = path.join("notes", file);
    const content = await fs.readFile(path.join(notesDir, file), "utf-8");
    const embedding = await embed(content);
 
    await pool.query(
      "insert into notes (source, content, embedding) values ($1, $2, $3::vector)",
      [source, content.trim(), toVectorLiteral(embedding)]
    );
 
    console.log(`Indexed ${source}`);
  }
}
 
main()
  .catch((error) => {
    console.error(error);
    process.exitCode = 1;
  })
  .finally(closeDb);

Run it:

npm run index

Search by Meaning

Add search.js:

import { pool, closeDb } from "./db.js";
import { embed, toVectorLiteral } from "./embed.js";
 
const query = process.argv.slice(2).join(" ");
 
if (!query) {
  console.error('Usage: npm run search "your question"');
  process.exit(1);
}
 
async function main() {
  const queryEmbedding = await embed(query);
 
  const result = await pool.query(
    `
    select
      source,
      content,
      1 - (embedding <=> $1::vector) as similarity
    from notes
    order by embedding <=> $1::vector
    limit 3
    `,
    [toVectorLiteral(queryEmbedding)]
  );
 
  for (const row of result.rows) {
    console.log(`\n${row.source}`);
    console.log(`similarity: ${Number(row.similarity).toFixed(4)}`);
    console.log(row.content);
  }
}
 
main()
  .catch((error) => {
    console.error(error);
    process.exitCode = 1;
  })
  .finally(closeDb);

Try a query:

npm run search -- "how do I keep retrieval scoped to the right users and documents?"

You should see the metadata note near the top, even though the query does not say "metadata" directly.

That is the core trick: the query and the notes are both mapped into the same embedding space, then Postgres sorts by vector distance.

Add Metadata Filters

Most useful vector search is not pure vector search. It is vector search inside a narrower slice of data.

For example, add a topic column:

alter table notes add column if not exists topic text;

Then your search can combine semantic similarity with normal SQL filters:

select source, content
from notes
where topic = 'retrieval'
order by embedding <=> $1::vector
limit 3;

This is why pgvector is such a friendly first vector database. You do not leave SQL behind. You add vector search to data modeling habits you already understand.

What This Teaches

This tiny tool gives you the mental model for larger systems:

Embeddings are data: Store them next to the text and metadata they represent.
Vector search is ranking: The database returns nearest rows, not guaranteed truth.
Filters matter: Permissions, tenants, freshness, and document type usually belong in SQL.
Indexes are workload choices: HNSW helps larger collections, but exact search is fine for small local experiments.
Evaluation is the real product work: If the top results are not useful, tune chunking, metadata, prompts, or the corpus before blaming the database.

Where to Take It Next

Once the local version works, the natural upgrades are straightforward:

Split long documents into chunks and store document_id, chunk_index, and heading.
Add metadata filters for tenant, role, source, and freshness.
Store the original document separately from chunks.
Add a small API route around search.
Measure retrieval quality with a handful of real questions and expected sources.

That is the practical shape of befriending vector databases: start with one table, one embedding column, and one query you can explain without hand-waving.