Once you've wired an LLM into an internal tool — answering questions against a knowledge base, a set of Acumatica help docs, or a pile of PDFs a client dropped in a shared drive — the first hard problem isn't the model, it's retrieval. You need to find the handful of paragraphs that actually answer the question out of thousands of documents, and pass only those to the model. That's what a vector database is for. Here's what actually matters when you build one of these, and where teams get it wrong.
What an embedding actually is
An embedding model turns a chunk of text into a fixed-length vector of floats — typically 384, 768, or 1536 dimensions depending on the model. The property that makes this useful is that semantically similar text ends up close together in that vector space, measured by cosine similarity or dot product. "Cancel a purchase order" and "void a PO" land near each other even though they share almost no words. That's the whole value proposition: it's semantic similarity, not keyword matching.
It's also the source of most surprises. Embeddings don't understand negation well ("orders that are not overdue" can retrieve overdue-order content), they conflate topically similar but factually different things, and they have no idea what's true — they only know what's *near*. If your retrieval keeps surfacing the wrong fiscal year or the superseded version of a policy, that's not a bug in the vector database, it's a property of similarity search you have to design around.
Chunking is where quality is won or lost
Before anything gets embedded, you have to split source documents into chunks, and this decision affects retrieval quality more than which vector database you pick. Chunk too large (a full page) and you dilute the vector with unrelated content, so a specific question matches a vaguely-relevant blob. Chunk too small (one sentence) and you lose the surrounding context the model needs to actually answer — "the fee is 2%" is useless without knowing which fee.
A reasonable starting point for prose documentation is 300-500 tokens per chunk with roughly 15-20% overlap between consecutive chunks, so a sentence that got cut off at a boundary still shows up whole in the next chunk. For structured content — API references, tables, code samples — chunk along natural boundaries (one function, one table, one config block) rather than a fixed token count. Whatever you pick, store the source document ID and section heading alongside each chunk; when retrieval goes wrong, that metadata is how you debug it.
Do you actually need a dedicated vector database?
For a lot of ERP-adjacent teams, the answer is no. If you're already running Postgres — which most Acumatica and Laravel shops are, one way or another — the pgvector extension gives you a vector column type and approximate nearest-neighbor indexes (IVFFlat or HNSW) in the same database that holds your actual records. No new service to deploy, back up, monitor, or explain to ops. For document sets in the tens of thousands to low millions of chunks, pgvector's HNSW index is fast enough that users won't notice the difference versus a dedicated store.
Where a dedicated vector database (Pinecone, Qdrant, Weaviate, Chroma) earns its keep is at real scale — tens of millions of vectors with tight latency SLAs — or when you need features pgvector doesn't have out of the box, like multi-tenant namespace isolation at scale, built-in hybrid search scoring, or horizontal scaling independent of your primary database. If you're not at that scale, adding a second data store just to hold embeddings is infrastructure you now have to run, patch, and back up for no measurable retrieval improvement.
If the document set is under a few hundred thousand chunks and you already run Postgres, add pgvector before you evaluate a dedicated vector database. Migrate later if you actually hit a scaling wall — most teams never do.
A minimal pgvector retrieval query
The pattern is: embed the incoming question with the same model used to embed the documents, then search for the nearest chunks by distance.
-- schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE doc_chunks (
id bigserial PRIMARY KEY,
document_id text NOT NULL,
section text,
content text NOT NULL,
embedding vector(1536),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON doc_chunks USING hnsw (embedding vector_cosine_ops);
-- retrieval: nearest 8 chunks to the query embedding
SELECT document_id, section, content,
1 - (embedding <=> $1) AS similarity
FROM doc_chunks
ORDER BY embedding <=> $1
LIMIT 8;The $1 parameter is the query embedding, generated at request time by calling the same embedding model against the user's question. The <=> operator is pgvector's cosine distance operator — smaller means more similar, which is why the similarity score subtracts it from 1.
Hybrid search: vector alone is not enough
Pure vector search struggles with exact matches — part numbers, invoice IDs, error codes, config keys — because embeddings compress away exact token identity in favor of semantic meaning. A question like "what does error E-4021 mean" often retrieves conceptually-related error handling text without ever surfacing the chunk containing "E-4021" verbatim.
The fix is hybrid search: run a traditional keyword search (Postgres full-text search with tsvector, or BM25 in a search engine) alongside the vector search, then combine the two ranked lists — commonly with reciprocal rank fusion — before picking the top chunks to send to the model. This is a few extra lines of SQL if you're already on Postgres, and it meaningfully reduces the "the answer is in the document but retrieval never found it" failure mode, which is usually the single biggest source of bad RAG answers in practice.
Keeping embeddings fresh
Documents change — a policy gets updated, an invoice template changes, a procedure gets revised — and a stale embedding pointing at superseded content is worse than no retrieval at all, because it looks confident and is wrong. The embedding itself doesn't know its source document changed; nothing invalidates it automatically. You need a re-embedding trigger tied to whatever already tracks document updates (a modified_at column, a webhook from your CMS, a cron job that diffs checksums), and it needs to delete-and-reinsert affected chunks rather than update them in place, since a content change usually shifts chunk boundaries too.
Nothing errors when an embedding goes stale — the query still returns a result, it's just the wrong one. Track embedding age against source document age explicitly, and alert when the gap grows past whatever your update cadence should be.
| Approach | Best for |
|---|---|
| In-memory (list of vectors, linear scan) | Prototypes, under ~10k chunks, no persistence needed between runs. |
| pgvector on existing Postgres | Most production cases up to low millions of chunks, especially if you already run Postgres and want one less service. |
| Dedicated vector DB (Pinecone, Qdrant, Weaviate) | Tens of millions+ vectors, strict latency SLAs, multi-tenant isolation at scale. |
None of this replaces getting the basics right first: chunk sensibly, store the metadata you'll need to debug bad answers, and don't reach for a new database service before you've actually measured that pgvector can't keep up. Most retrieval problems people blame on "the vector database" turn out to be chunking or staleness issues that no amount of infrastructure fixes.
Independent software engineer in Nairobi specialising in Acumatica customisations, Laravel backends, and tax fiscalisation integrations across East and Southern Africa.