From Rows to Meaning: How Vector Databases Work, and How to Build or Add One

Vector databases come up in almost every conversation about AI apps, and most explanations start with the maths. This one starts with something you already know, an ordinary database, and adds one idea at a time until you can build a vector database yourself, or turn the database you already run into one.

You don't need a maths background. You don't need to take notes either: every section ends with a one-line Remember, and there's a nine-line summary at the end.

The guide has five parts. If you only want one thing, jump straight to it:

• Part 1 · Understand it: from a plain table to vector indexes, sections 1–8.
• Part 2 · The mental model: the five parts of every vector database, section 9.
• Part 3 · Build one: a tiny vector database in about 60 lines of Python, section 10.
• Part 4 · Upgrade one: add vector search to Postgres, step by step, section 11.
• Part 5 · Use it well: choosing, and the mistakes to avoid, sections 12–13.

1. What a database is for

A database has one main job: keep your data safe and let you find it again later.

Picture a help centre with a few support articles. In a database they live in a table. Each row is one article and each column is one piece of information about it.

Diagramtable: articles

SELECT title FROM articles WHERE id = 42;

2. Indexes make finding fast

With four rows, the database can look at every row. That's a full scan. With a million rows, checking every one for each question is slow.

The fix is the one a book uses. A book index lists topics in alphabetical order with page numbers. A database index keeps a column's values in sorted order, each pointing to its row.

Sorted order is the whole trick. Most databases store an index as a B-tree: a small tree where each box says "smaller values go left, bigger go right". Finding 42 takes three hops:

Diagramthree hops to row 42

Interactivefull scan vs index

Rows in the table1,000,000

Full scan

1,000,000

rows checked, worst case

Sorted index

≈ 20

comparisons

Each time the table grows 10×, the index needs only about 3 more comparisons.

3. Searching text

People don't search by ID. They type words. Databases handle that with full-text search, and under the hood it builds an inverted index: a list of every word and the rows that contain it, like the index at the back of a book.

Diagramhow full-text search finds rows

PostgreSQL, MySQL and SQL Server all have full-text search built in, and you should keep using it. But the picture shows its blind spot: it matches words. Different words, no match, even when the meaning is the same.

4. The meaning gap

The customer who typed "my parcel hasn't arrived" needed the article "Where is my order?". They share no useful words. People describe the same problem in many ways, and word search can't follow them.

We need a search that answers a different question:

Computers measure numbers. So the next step is to turn meaning into numbers.

5. Embeddings: a position for every meaning

An embedding model reads a piece of text and returns a list of numbers. That list is the embedding. Think of it as coordinates: a position on a huge map where texts with similar meanings are placed close together.

Diagramtext → numbers → a point on a map

Try the map below. Pick a question and see which articles sit closest to it, and what word search would have found.

Interactivea toy map of meaning

“My parcel hasn't arrived”“I forgot my login”“Charge me less each month”“Refund policy”

your question: “My parcel hasn't arrived” · dashed lines go to its three nearest articles

Closest by meaning

• Delivery times
• Where is my order?
• Shipping costs

Word search finds

• Nothing. No shared words.

• You don't pick the numbers. The model does. You only choose which model.
• Real maps have many more directions. The map above uses 2 numbers. Real embeddings use hundreds or thousands (384, 768, 1,536 and 3,072 are common sizes). You can't picture that many directions, and you don't need to. "Close means similar" works the same way.
• One model for everything. Your rows and your users' questions must be embedded by the same model. Two models draw two different maps.

A list of numbers like this is also called a vector. That's the "vector" in vector database.

6. Measuring closeness

Draw an arrow from the centre of the map to each point. If two arrows point the same way, the meanings are similar. The measure of "how much the same way" is called cosine similarity: 1 means the same direction, 0 means unrelated.

Diagramcosine similarity

This is called nearest neighbour search, and it's the one job every vector database is built around.

7. Brute force: compare with everything

The simplest way to find the nearest points is to measure the question against every stored embedding and keep the best few. It's exact, and for a few thousand items it's perfectly fine.

Diagramexact search

Here's what it costs as you grow, with embeddings of 1,536 numbers (4 bytes each):

Stored embeddings	Multiplications per question	Raw storage
10,000	≈ 15 million	≈ 61 MB
1,000,000	≈ 1.5 billion	≈ 6.1 GB
100,000,000	≈ 154 billion	≈ 614 GB

Why not use a B-tree, like section 2? A B-tree needs values that sit in one order on a line. Meaning has hundreds of directions at once, so there's no single order to sort by. The phone-book trick doesn't work here, and we need new tricks.

8. Vector indexes: smart shortcuts

Vector indexes make one deal: only check a small, promising part of the data, and accept that you'll occasionally miss a true neighbour. This is approximate nearest neighbour search (ANN). Done well, the results are almost identical to exact search, for a fraction of the work.

Shortcut 1: neighbourhoods (IVF)

Before any search, group the points into neighbourhoods, each with a centre point. A question first finds the nearest centres, then searches only inside those neighbourhoods.

InteractiveIVF · neighbourhoods

Neighbourhoods to search1 of 4 · 25% checked

Shortcut 2: motorways and local roads (HNSW)

Driving to a friend in another city, you take the motorway, then main roads, then local streets. You never drive down every street in the country. HNSW builds the same thing out of your data: every point is linked to a few nearby points, plus sparse layers on top with long-distance links. A search starts at the top with big jumps and drops down layer by layer, taking smaller and smaller steps.

InteractiveHNSW · layered graph

Play the searchResetThe full path. Press play to watch it step by step.

HNSW is the most common choice today. Its accuracy dial in pgvector is hnsw.ef_search: how many candidates the search keeps in mind. Higher is more accurate and a little slower.

You measure accuracy as recall. If the true 10 nearest neighbours are known and the index returns 9 of them, recall is 90%. You'll measure recall yourself in Part 3.

9. Five parts of every vector database

Every vector database, from a 60-line Python script to a managed cloud service, is made of the same five parts. They work along two paths: a write path when your data changes, and a read path when someone searches.

The mental modelfive parts · two paths

Part	Its job	Where you met it
Embed	Turn text into a position on the map of meaning	Section 5
Store	Keep each vector with its id, metadata and original text	Section 1, with a new column
Index	Shortcuts so a search doesn't check everything	Section 8
Query	Find the nearest k, apply filters, return results	Sections 6–7
Sync	When data changes, re-embed it so the map stays true	New: easy to forget, painful to miss

Products differ in who runs which part. pgvector puts Store, Index and Query inside Postgres, and you bring Embed and Sync. A dedicated vector database runs Store, Index and Query as its own service. Some managed search services handle Embed for you as well. Same five parts, different owners.

10. A tiny vector database in Python

You need Python 3.9+ and NumPy (pip install numpy). At its core, our database is three lists kept side by side: ids, a big table of numbers with one row per vector, and metadata.

Diagraminside TinyVectorDB

We scale every vector to length 1 as it comes in. Then cosine similarity becomes a plain multiply-and-add (a "dot product"), which NumPy does very fast.

# tiny_vector_db.py
import numpy as np


def unit(v):
    # Scale each vector to length 1, so dot product = cosine similarity
    v = np.asarray(v, dtype=np.float32)
    return v / np.linalg.norm(v, axis=-1, keepdims=True)


class TinyVectorDB:
    def __init__(self, dim):
        self.dim = dim
        self.ids = []
        self.meta = []
        self.vectors = np.empty((0, dim), dtype=np.float32)
        self.index = None  # built later by build_index()

    def add_many(self, ids, vectors, metas=None):
        V = unit(vectors)
        assert V.shape[1] == self.dim, "vector size doesn't match"
        self.vectors = np.vstack([self.vectors, V])
        self.ids += list(ids)
        self.meta += metas or [{} for _ in ids]
        self.index = None  # new data makes the old index stale

One line does the real work: self.vectors @ q compares the question with every stored vector at once. That's brute force from section 7. The optional where filter removes rows whose metadata doesn't match before ranking.

    # inside class TinyVectorDB
    def search(self, query, k=3, where=None):
        q = unit(query)
        scores = self.vectors @ q  # cosine similarity with every vector
        if where:
            keep = np.array([all(m.get(f) == v for f, v in where.items())
                             for m in self.meta])
            scores = np.where(keep, scores, -np.inf)
        top = np.argsort(-scores)[:k]
        return [(self.ids[i], float(scores[i])) for i in top
                if scores[i] > -np.inf]

This is the neighbourhood shortcut from section 8. build_index finds neighbourhood centres with a simple k-means loop: put each vector with its nearest centre, move each centre to the middle of its group, repeat. search_fast then checks only the n_probe nearest neighbourhoods.

    # inside class TinyVectorDB
    def build_index(self, n_lists=16, iters=10, seed=0):
        X = self.vectors
        rng = np.random.default_rng(seed)
        centroids = X[rng.choice(len(X), n_lists, replace=False)]
        for _ in range(iters):  # k-means: group, then re-centre
            nearest = np.argmax(X @ centroids.T, axis=1)
            for c in range(n_lists):
                members = X[nearest == c]
                if len(members):
                    centroids[c] = unit(members.mean(axis=0))
        nearest = np.argmax(X @ centroids.T, axis=1)
        self.index = {
            "centroids": centroids,
            "lists": [np.where(nearest == c)[0] for c in range(n_lists)],
        }

    def search_fast(self, query, k=3, n_probe=2):
        if self.index is None:
            self.build_index()
        q = unit(query)
        closest = np.argsort(-(self.index["centroids"] @ q))[:n_probe]
        candidates = np.concatenate([self.index["lists"][c] for c in closest])
        scores = self.vectors[candidates] @ q  # only these get checked
        top = candidates[np.argsort(-scores)[:k]]
        return [(self.ids[i], float(self.vectors[i] @ q)) for i in top]

Now test the deal the index makes. We create 20,000 made-up vectors that form natural groups (real embeddings do too), then ask: of the true top 10 from exact search, how many did the fast search find?

# try_recall.py
import numpy as np
from tiny_vector_db import TinyVectorDB

rng = np.random.default_rng(42)
centers = rng.normal(size=(50, 64))
X = centers[rng.integers(0, 50, 20_000)] + 1.5 * rng.normal(size=(20_000, 64))

db = TinyVectorDB(dim=64)
db.add_many(range(len(X)), X)
db.build_index(n_lists=16)

# 100 new questions, drawn from the same groups
queries = centers[rng.integers(0, 50, 100)] + 1.5 * rng.normal(size=(100, 64))

def recall(n_probe):
    found = 0
    for q in queries:
        exact = {i for i, _ in db.search(q, k=10)}
        fast = {i for i, _ in db.search_fast(q, k=10, n_probe=n_probe)}
        found += len(exact & fast)
    return found / (10 * len(queries))

for n_probe in (1, 2, 4, 8, 16):
    print(f"n_probe={n_probe:2}  checks ~{n_probe / 16:.0%} of data  recall={recall(n_probe):.2f}")

When I ran it, this came out:

n_probe= 1  checks ~6% of data  recall=0.74
n_probe= 2  checks ~12% of data  recall=0.85
n_probe= 4  checks ~25% of data  recall=0.93
n_probe= 8  checks ~50% of data  recall=0.98
n_probe=16  checks ~100% of data  recall=1.00

Diagram20,000 vectors · 16 neighbourhoods · one run

That trade between work and accuracy is the dial every real vector database gives you, and now you've built it. Exact numbers vary with your NumPy version and data; the shape of the curve is the point.

Swap the made-up vectors for real embeddings. pip install sentence-transformers gives you small models that run on a laptop. all-MiniLM-L6-v2 returns 384 numbers per text.

# try_text.py
from sentence_transformers import SentenceTransformer
from tiny_vector_db import TinyVectorDB

model = SentenceTransformer("all-MiniLM-L6-v2")  # downloads once, ~90 MB

docs = [
    ("Refund policy", "payments"),
    ("Get my money back", "payments"),
    ("Reset your password", "account"),
    ("Can't sign in", "account"),
    ("Where is my order?", "shipping"),
    ("Delivery times", "shipping"),
]

db = TinyVectorDB(dim=384)
db.add_many(range(len(docs)), model.encode([t for t, _ in docs]),
            [{"category": c} for _, c in docs])

question = model.encode("my parcel hasn't arrived")
for i, score in db.search(question, k=3):
    print(f"{score:.3f}  {docs[i][0]}")

print("--- only payments ---")
for i, score in db.search(question, k=2, where={"category": "payments"}):
    print(f"{score:.3f}  {docs[i][0]}")

Look at what comes back first. You've now built four of the five parts: Embed, Store, Index and Query. The fifth, Sync, keeps things fresh as data changes, and that's exactly what the next part handles inside a real database.

11. Turn Postgres into a vector database

Here's what changes, mapped onto the five parts. The table gets one new column, a small background job fills it, and a trigger keeps it honest.

Diagramone column, one job, one index, one trigger

To follow along you need Docker and Python. If you already have a Postgres database you're allowed to change, use that instead. On managed services, check that pgvector is available for your version first. Azure Database for PostgreSQL needs the extension allow-listed in its server parameters; Amazon RDS and Aurora PostgreSQL support it on recent versions.

docker run --name pgvec -e POSTGRES_PASSWORD=secret -p 5432:5432 -d pgvector/pgvector:pg17
docker exec -it pgvec psql -U postgres

CREATE TABLE articles (
  id       bigserial PRIMARY KEY,
  title    text NOT NULL,
  body     text,
  category text
);

INSERT INTO articles (title, body, category) VALUES
  ('Refund policy',       'Refunds are paid to the original card within 5 days.', 'payments'),
  ('Get my money back',   'How to request a refund for a damaged item.',           'payments'),
  ('Reset your password', 'Use the forgot password link on the sign-in page.',    'account'),
  ('Can''t sign in',      'Check two-factor codes and browser cookies.',          'account'),
  ('Where is my order?',  'Track a parcel with the link in your confirmation.',   'shipping'),
  ('Delivery times',      'Standard delivery takes 3 to 5 working days.',         'shipping');

The number in vector(384) must match your embedding model's output size. The column starts empty (NULL) for every row, and that's fine.

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE articles ADD COLUMN embedding vector(384);

This script finds rows with no embedding, embeds them 100 at a time and writes them back. It's safe to stop and re-run, because it only picks up rows that are still empty. Install with pip install "psycopg[binary]" pgvector sentence-transformers.

# embed_job.py
import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("all-MiniLM-L6-v2")  # 384 numbers per text
DB = "postgresql://postgres:secret@localhost:5432/postgres"

with psycopg.connect(DB) as conn:
    register_vector(conn)  # lets us pass NumPy arrays as vectors
    while True:
        rows = conn.execute(
            "SELECT id, title || '. ' || coalesce(body, '') FROM articles "
            "WHERE embedding IS NULL ORDER BY id LIMIT 100"
        ).fetchall()
        if not rows:
            break
        vectors = model.encode([text for _, text in rows])
        with conn.cursor() as cur:
            cur.executemany(
                "UPDATE articles SET embedding = %s WHERE id = %s",
                [(vec, row_id) for (row_id, _), vec in zip(rows, vectors)],
            )
        conn.commit()
        print(f"embedded {len(rows)} rows")

With six rows Postgres will simply check them all, but on a real table this is what makes search fast. CONCURRENTLY builds the index without blocking writes, which matters on a live table. The operator class must match the distance you query with: vector_cosine_ops goes with the <=> operator.

CREATE INDEX CONCURRENTLY articles_embedding_idx
  ON articles USING hnsw (embedding vector_cosine_ops);

Your app embeds the question with the same model, then runs an ordinary query. Filters, joins and permissions all work as usual.

# search.py
import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("all-MiniLM-L6-v2")
q = model.encode("my parcel hasn't arrived")

with psycopg.connect("postgresql://postgres:secret@localhost:5432/postgres") as conn:
    register_vector(conn)
    rows = conn.execute(
        """
        SELECT title, category, embedding <=> %s AS distance
        FROM articles
        WHERE embedding IS NOT NULL
        ORDER BY distance
        LIMIT 3
        """,
        (q,),
    ).fetchall()
    for title, category, distance in rows:
        print(f"{distance:.3f}  {title}  ({category})")

To narrow by metadata, add a normal condition such as AND category = 'shipping'. On big tables with a selective filter, read section 13 about filters and approximate indexes.

If someone edits an article, its old embedding still describes the old text. The simplest reliable pattern: a trigger empties the embedding whenever the text changes, and the job from step 2 (run on a schedule or as a worker) refills it. New rows start empty, so they get picked up too.

CREATE OR REPLACE FUNCTION clear_embedding() RETURNS trigger AS $$
BEGIN
  IF NEW.title IS DISTINCT FROM OLD.title
     OR NEW.body IS DISTINCT FROM OLD.body THEN
    NEW.embedding := NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_clear_embedding
BEFORE UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION clear_embedding();

The trade-off: an edited row drops out of vector search until the job runs again. For most apps that's fine. If it isn't, run the job more often, or embed in the same request that saves the edit.

Embeddings are weak on exact terms like error codes and product IDs, and full-text search is strong there. Run both and merge them. A simple, popular way to merge is reciprocal rank fusion: each list gives a row 1 / (60 + its rank) points, and you add the points up. Rows that rank well in both lists rise to the top.

-- $1 = question embedding, $2 = question text
WITH semantic AS (
  SELECT id, row_number() OVER (ORDER BY dist) AS rank
  FROM (SELECT id, embedding <=> $1 AS dist
        FROM articles ORDER BY dist LIMIT 20) s
),
keyword AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank_cd(doc, q) DESC) AS rank
  FROM (SELECT id, to_tsvector('english', title || ' ' || coalesce(body, '')) AS doc
        FROM articles) a,
       plainto_tsquery('english', $2) AS q
  WHERE doc @@ q
  ORDER BY rank
  LIMIT 20
)
SELECT a.title, sum(1.0 / (60 + r.rank)) AS score
FROM (SELECT * FROM semantic UNION ALL SELECT * FROM keyword) r
JOIN articles a USING (id)
GROUP BY a.id, a.title
ORDER BY score DESC
LIMIT 5;

Two details. plainto_tsquery only matches rows that contain all the question's words, so a long question can leave the keyword list empty; that's fine, because the semantic list still answers. And on a large table you'd store the tsvector in its own column with a GIN index instead of computing it on every query.

12. Which one should you use?

Start from what you already have, not from what's popular.

If…	Use	Why
You're learning	The tiny one from Part 3	An afternoon, and the concepts stick
You already run Postgres	pgvector, as in Part 4	One system, with your existing joins, permissions and backups
Vectors are your core workload at large scale	A dedicated vector database (Pinecone, Qdrant, Weaviate, Milvus and others)	Store, Index and Query as a separate service you scale on its own
You want managed search with vectors	A search engine (Elasticsearch, OpenSearch, Azure AI Search)	Keyword and vector search together, often with ranking built in

Move away from the database you already run when a measured need tells you to: search is too slow at your real size and traffic, recall is too low at the speed you need, or there's a feature you'd otherwise have to build yourself. Measure p95 latency, recall against exact search, index build time and memory before deciding.

13. What trips people up

• Embed Mixing models. Rows embedded with one model and questions with another live on different maps, so results look random. If you change models, re-embed everything and store which model made each vector.
• Embed Chunk size. Long documents are split into chunks before embedding. Too small cuts answers in half; too big blurs several topics into one position. Test a few sizes on your own questions.
• Query Filters that shrink results. With an approximate index, a selective filter like WHERE category = 'payments' can quietly return fewer rows than you asked for, because the filter is applied to a limited set of candidates. pgvector 0.8 and later add iterative index scans to handle this; partial indexes or partitioning also help. Check the docs for your version.
• Query Expecting exact matches. Embeddings are weak on codes, IDs and names. Use hybrid search.
• Store Size limits. Bigger embeddings cost more storage and memory. pgvector's vector type can be indexed up to 2,000 dimensions, so 3,072-dimension embeddings need a workaround, such as indexing them as halfvec.
• Sync Stale embeddings. The text was edited but the vector wasn't, so search quietly returns the old meaning. Part 4, step 5 fixes this.
• Index Never measuring. Write 20–30 real questions with known right answers and check whether search finds them. Measure recall when you tune the index. It's the cheapest way to know whether a change helped.

The whole guide in nine lines

1. A database stores rows and answers exact questions. An index is fast because values can be sorted.
2. Text search looks words up in an inverted index. It matches words, not meaning.
3. An embedding model turns text into a list of numbers: a position for its meaning. Similar meanings sit close together.
4. Search becomes "find the nearest points". Checking every point is exact but slow at scale.
5. Meaning can't be sorted, so vector indexes use shortcuts: neighbourhoods (IVF) or layered roads (HNSW). Measure the trade as recall.
6. Every vector database has five parts: Embed · Store · Index · Query · Sync.
7. Build one: a table of numbers, a dot product and a k-means index, in about 60 lines.
8. Upgrade Postgres: one column, one embedding job, one HNSW index, one trigger.
9. Keep keyword search for exact terms, and merge the two (hybrid search).

---

Checked against the pgvector documentation and a pgvector 0.8.7 container as of October 2026. Details such as index limits and filtering behaviour change between versions, so check the docs for the version you run.