Postgres and SQLite Vector Store Without a Vendor

A vector store is a table of ids, metadata, and float32 blobs plus a way to score a query vector. You do not need a hosted vector database for a private corpus on one VPS. SQLite with a BLOB column, or Postgres with bytea or a pgvector column you compile yourself, is enough for tens of thousands of chunks. This post is the schema, the scan, and the backup story. Netbay does not sell a managed vector product. You own the file.

Start with the access pattern, not a logo

Most RAG indexes on a single node are under 100k rows. A 384-d float32 vector is 1536 bytes. One hundred thousand rows is about 150 MB of vectors plus metadata. Linear scan with a NumPy or numpy-frombuffer loop on Intel Xeon Platinum is a few milliseconds to a few tens of milliseconds. You do not need HNSW until brute force is slow in your own benchmark.

Write path: upsert by a stable primary key such as repo, path, chunk index, and git sha. Read path: take the top 50 by cosine, then maybe rerank. Do not build a microservice around the store until those two paths are boring. Keep the database on High-Speed SSD, not on a tmpfs that dies on reboot.

Lucknow DC01 is one datacenter. Put the database on the same VPS as the embedder. Crossing the public internet to a vendor index adds latency and a second bill. L3/L4 DDoS filtering in front of the VPS does not replace bind-to-localhost for Postgres.

SQLite: one file, WAL, a BLOB of float32

SQLite is the right default when one process owns writes. Enable WAL. Set a busy timeout. Store the vector as a BLOB of little-endian float32. Store model_name and dim so a future MiniLM-to-E5 migration is a new table, not silent garbage.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;

CREATE TABLE IF NOT EXISTS chunks (
  id INTEGER PRIMARY KEY,
  path TEXT NOT NULL,
  chunk_index INTEGER NOT NULL,
  git_sha TEXT NOT NULL,
  model TEXT NOT NULL,
  dim INTEGER NOT NULL,
  content TEXT NOT NULL,
  embedding BLOB NOT NULL,
  UNIQUE (path, chunk_index, model)
);

CREATE INDEX IF NOT EXISTS chunks_path ON chunks(path);

Load candidates in Python, frombuffer each blob, dot with the query. If you later want sqlite-vec, it is still a library in your process, not a cloud. Until then, a tight loop is honest and easy to test.

import sqlite3
import numpy as np

def top_k(conn, query, k=20):
    q = query.astype(np.float32)
    q /= np.linalg.norm(q) + 1e-12
    rows = conn.execute(
        "SELECT id, path, content, embedding FROM chunks WHERE model = ?",
        ("all-MiniLM-L6-v2",),
    ).fetchall()
    scored = []
    for row_id, path, content, blob in rows:
        vec = np.frombuffer(blob, dtype=np.float32)
        scored.append((float(vec.dot(q)), row_id, path, content))
    scored.sort(reverse=True)
    return scored[:k]

Cap the table you scan. If you have multiple models, filter on model. If you have multiple repos, filter on path prefix. Brute force is only cheap if you do not scan yesterday's experiments.

Postgres when several workers must write

If ingest, the API, and a nightly job all write, SQLite lock contention will show up as busy timeouts. Postgres on the same VPS is the next step. Bind listen_addresses to localhost or a private IP. Do not put 5432 on the public interface.

Two storage options: bytea plus an application scan, or pgvector if you install the extension yourself. bytea keeps the dependency list small. pgvector gives you an operator and later an IVFFlat or HNSW index when row counts justify it. Either way, the backup is pg_dump or a filesystem snapshot you copy off box. There is no vendor console.

Keep metadata columns you will filter on: path, language, git_sha, updated_at. ANN indexes do not replace WHERE path LIKE a docs prefix. Hybrid filters happen in SQL. Vectors happen after.

A practical schema is chunks (id, path, chunk_index, git_sha, model, dim, content, embedding bytea) with a unique constraint on (path, chunk_index, model). Upsert with ON CONFLICT DO UPDATE so a re-embed of one file does not duplicate rows.

Backup, dim checks, and what not to buy

Restore is a file copy for SQLite and a dump for Postgres. Test it. A vector table you cannot restore is a search demo. Store dim on every row and reject writes where len(blob) != dim * 4. That one check saves a week of "search got weird after I tried another model."

Do not rent a hosted vector database for a corpus that fits in RAM. The VPS disk is the product. Keep models, caches, and the database on paths you can du and rsync.

When brute force crosses 50 ms at p95, measure before you shard. Often the scan includes rows you should have filtered. Sometimes you need a simple inverted index for keywords and only embed the FTS shortlist. That hybrid is a later post. The store stays a table.

Takeaway

Treat embeddings as rows you can dump and restore. SQLite until writers collide, then Postgres on localhost. Scan until you measure a need for an ANN index. You can spin up an Ubuntu 24.04 instance on Netbay in Lucknow in under 60 seconds and keep the whole index on that disk — netbayhosts.in.