# RAG Database Design: SQL, Full-Text Search, Vector Search, and Context Retrieval > Source: > Published: 2026-07-23 14:38:14+00:00 Retrieval-augmented generation is often introduced with a compact diagram: ``` php documents -> embeddings -> vector database -> LLM ``` That is a useful starting point, but it can make one part of the architecture look like the whole system. Good RAG database design has to cover more than vector storage. A production RAG application may also need to store original documents, enforce tenant boundaries, find exact product names, filter by date, remember a conversation, invalidate stale content, combine several data sources, and keep the final prompt within a token budget. No database becomes a "RAG database" merely by storing embeddings. Different database models solve different parts of retrieval. This article looks at the roles of relational databases, document databases, caches, full-text search engines, and vector databases in RAG. It then examines a retrieval problem that is harder to express with similarity alone: finding a useful neighborhood of context when the application knows the center, but not the exact boundary of the answer. Among practical AI use cases, this pattern appears in customer support, business-efficiency AI tools for sales, internal knowledge assistants, AI programming automation, and AI agent development. The same retrieval questions also apply to AI assistant development with internal data, where access control and source freshness matter as much as the generated answer. RAG retrieves external information when a request is made, then places selected evidence into the model's context. Fine-tuning changes model weights. The practical RAG vs fine-tuning differences are therefore about when and where knowledge is introduced. RAG is often easier to update, cite, filter, or remove because the knowledge remains outside the model. Fine-tuning can change behavior, style, or learned patterns, but it is not a convenient replacement for a frequently changing document store. The main RAG pros and cons follow from that separation. RAG can use current and private sources without retraining the model, but it adds ingestion, retrieval, permissions, evaluation, latency, and operations work. Understanding how AI generation works is not enough: a fluent model can still answer from weak, stale, or unauthorized evidence. A more complete RAG pipeline looks like this: ``` php source data -> ingestion and normalization -> document and metadata storage -> candidate selection -> lexical or vector ranking -> reranking and context assembly -> LLM -> answer and citations ``` Databases can participate at several stages. | Responsibility | Typical data | |---|---| | Source of truth | Documents, records, revisions, users, permissions | | Ingestion state | Jobs, checksums, parser versions, failures | | Exact filtering | Tenant, category, status, language, date | | Lexical retrieval | Keywords, names, identifiers, error codes | | Semantic retrieval | Embeddings and similarity scores | | Session state | Recent messages, selected sources, cached results | | Context assembly | Related records, projections, token-bounded payloads | One database can perform several of these jobs. A small application does not need six databases just because six rows appear in the table. The point is to identify the retrieval job before choosing the storage engine. A useful database comparison begins with those jobs, not with a feature-count table. When teams research how to build RAG, they often begin with an embedding model or a RAG vector database. A safer order is to identify the source of truth, access rules, update process, exact filters, candidate retrieval method, and evaluation plan first. The vector index is one component of that design. To make the differences concrete, imagine that we are building a travel assistant for Kyoto. It stores information about landmarks, restaurants, cultural facilities, events, transit, opening hours, reservations, and user itineraries. Relational databases are often the least surprising place to keep the authoritative application state around a RAG system. For the travel assistant, a relational database could store: SQL is especially useful when correctness depends on exact relationships. If a private itinerary belongs to one user, or a document must not cross a tenant boundary, that rule should not depend on an embedding score. A relational query can select the allowed document IDs first. The RAG pipeline can then rank only those documents. ``` SELECT document_id FROM travel_documents WHERE city = 'Kyoto' AND language = 'en' AND published = true AND valid_from <= CURRENT_TIMESTAMP AND valid_until > CURRENT_TIMESTAMP; ``` PostgreSQL also has JSON, full-text search, and extension support. The [ pgvector](https://github.com/pgvector/pgvector) extension can keep vectors beside relational data, which is often enough for an early or moderately sized RAG application. The important distinction is that adding a vector column does not remove the need to design permissions, document lifecycle, metadata, candidate selection, and prompt construction. A document database is a natural fit when an ingested item already looks like a JSON object and different item types have different fields. A landmark record might contain history, images, coordinates, and opening hours. A restaurant may have cuisine, price range, reservation rules, and dietary options. An event may have a start time, venue, ticket URL, and weather policy. ``` { "kind": "restaurant", "name": "Example Cafe", "area": "Higashiyama", "cuisine": ["Japanese", "Cafe"], "features": { "indoor": true, "vegetarianOptions": true } } ``` [MongoDB documents](https://www.mongodb.com/docs/manual/core/document/) can represent that shape without forcing every item type into the same set of columns. Metadata and source text can live together, and nested fields can be indexed when their access patterns are known. Flexibility does not eliminate retrieval design, however. The application still has to decide which collection, fields, filters, and records should become candidates for the model. The familiar RDB vs NoSQL differences still matter here. Relational systems center exact relationships and constraints; document databases center flexible record shapes and aggregate-oriented access. For business systems, NoSQL database selection criteria should include consistency, authorization, operational skills, backup, and migration—not only whether incoming data is JSON. Redis is useful around RAG even when it is not the primary document store. Common uses include: If many users ask similar questions about the same attraction, caching a validated retrieval result may be cheaper than running the complete pipeline again. Redis also supports persistence and several data structures, but a cache still needs an invalidation policy. Travel information makes this obvious: an answer about an event, temporary closure, or opening time can become wrong even when the cached text is internally consistent. [Redis documentation](https://redis.io/docs/latest/develop/get-started/) describes it as more than a simple key/value cache, but its role in a RAG architecture should still be chosen explicitly. Embedding search is not a replacement for lexical search. Users search for exact place names, train lines, product codes, legal terms, error messages, and quoted phrases. A model may produce similar embeddings for related text, but an exact identifier often deserves a lexical match. OpenSearch can combine: For the travel assistant, it could answer questions such as: ``` Find pages containing "Kiyomizu-dera" in English, published in the Kyoto corpus, with an opening-hours field updated this month. ``` That query contains exact text, metadata, and freshness requirements. Those signals should not be reduced to one vector distance. The [OpenSearch documentation](https://docs.opensearch.org/latest/about/) covers both lexical and vector-oriented search, making it a possible single-engine choice when those retrieval modes need to work together. Vector databases are designed around a different question: Which stored items are closest to this query in embedding space? That is valuable when the user and document do not share the same words. For example, a visitor might ask: ``` Where can I spend a quiet indoor afternoon learning about local history? ``` A useful museum description may not contain that exact sentence. Semantic retrieval can still bring it into the candidate set. [Qdrant](https://qdrant.tech/documentation/overview/) stores vectors with payload metadata and supports filtered similarity search. A typical request first restricts the eligible points, then ranks the remaining points by vector distance. ``` php metadata filter -> approximate nearest-neighbor search -> top candidates -> optional reranker -> LLM context ``` This is an effective pattern, but it leaves an architectural question: how does the application decide the right filter and search namespace before similarity ranking begins? Vector database similarity search algorithms, index type, filtering behavior, memory use, and update patterns all affect the result. A RAG vector DB comparison among Pinecone, Qdrant, pgvector, or OpenSearch should use the application's own corpus and filters. Lists of recommended vector databases cannot replace workload-specific evaluation. Keyword and semantic retrieval are complementary. A RAG hybrid search pipeline can combine BM25 and vector scores so that exact names and identifiers remain visible while semantically related documents can still be discovered. ``` php metadata and permission filters -> BM25 lexical candidates + vector similarity candidates -> merge and deduplicate -> reranker -> token-bounded context ``` For RAG accuracy improvement, rerank integration is often more useful than simply increasing the first-stage result count. A reranker can inspect a small candidate set more carefully, while the first stage remains optimized for recall. RAG chunk size optimization techniques matter too. Small chunks can improve matching precision but lose surrounding meaning; large chunks preserve context but consume more tokens and may dilute relevance. The right chunk size depends on document structure, retrieval method, and the unit that must be cited. RAG frameworks such as LangChain and LlamaIndex can connect loaders, embeddings, retrievers, and models. A LangChain RAG setup sample can demonstrate the wiring, but a framework cannot decide the correct authorization boundary, freshness rule, or candidate scope for an application. An open-source RAG setup for internal search can combine PostgreSQL, OpenSearch or Qdrant, a local model, and an orchestration framework. Self-hosting changes the operations and privacy boundary, but it does not remove the need for the same retrieval and evaluation decisions. Retrieval quality and answer quality should be measured separately. Useful RAG evaluation metrics include: `k` ;Tools such as Ragas can help automate parts of evaluation, but no RAG evaluation tool or tutorial can define the correct expected answer for every business workflow. A small, reviewed test set from real questions is still valuable. RAG hallucination prevention measures should include more than adding extra documents. Stronger measures include current sources, explicit citations, permission checks, conflict detection, an option to abstain, and tests for questions whose answers are not present in the knowledge base. RAG knowledge base automated updates also need versioning and invalidation. A pipeline should know which source revision produced a chunk, when an embedding must be rebuilt, and how deleted or expired information leaves caches and indexes. RAG security measures begin before retrieval. Tenant isolation, source authorization, secret removal, encryption, audit trails, and defenses against prompt injection in retrieved documents all belong in the design. General database security measures still apply even when the database is described as an AI component. Organizations may also need to align collection and use of data with privacy rules, sector obligations, AI legal regulations, and their own AI ethics guidelines. Retrieval makes data available to a model; it does not create permission to use that data. A complete RAG cost comparison includes ingestion, embedding generation, database storage, cache, network transfer, reranking, LLM input tokens, monitoring, backup, and engineering time. For a small business, the average AI implementation cost cannot be inferred from the model API price alone. RAG cost reduction and token savings are closely related to candidate scope. Passing fewer irrelevant records through retrieval and reranking can reduce database work and LLM input together. For local deployments, local LLM setup and PC requirements are a separate capacity decision, but smaller retrieved contexts can still reduce downstream memory and compute pressure. Many AI implementation failures happen because a demo is evaluated only on a few successful questions. The practical AI implementation benefits appear when the system also handles stale data, missing answers, access boundaries, cost limits, and failure recovery. Consider this question: I am at Kiyomizu-dera. It is raining, and I have two hours. What should I do next? Several retrieval methods can help: The challenge is not that any one of these methods is bad. The challenge is deciding what should enter the candidate set at all. A useful answer may need: The application knows the center: the visitor, Kiyomizu-dera, the current day, and the current situation. It does not yet know the exact boundary of the useful answer. The most semantically similar document is not necessarily the most useful document for the current request. A beautifully written article about another Japanese temple may be close in embedding space but unusable within two hours. A short transit notice may be semantically distant from a tourism question but essential to the answer. A restaurant record may contain almost none of the words in the user's request, yet be relevant because it is nearby, open, and compatible with the itinerary. RAG systems commonly address this with metadata filters: ``` { "city": "Kyoto", "area": "Higashiyama", "openNow": true, "weather": "rain", "categories": ["restaurant", "museum", "landmark"] } ``` This is reasonable. But as the product grows, the filter becomes an application-level retrieval plan. Someone must maintain the rules, namespaces, joins, and fan-out reads that reconstruct the context for every request. If the question is simply "what is within one kilometer?", a geographic index such as PostGIS or OpenSearch geo search is the direct solution. Useful travel context is not always a circle, however. Distance remains an important signal. It is simply not the only relationship that defines the field of view. A conventional stack can solve this problem. It may use SQL joins, geographic indexes, search filters, a graph, application-maintained ID lists, and vector ranking. The repeated work is reconstructing the same locality: ``` php start from the current place -> identify the area -> find allowed categories -> join today's events -> add relevant transit -> add itinerary items -> build candidates -> rank candidates ``` The missing operation here is not another scoring algorithm. It is a reusable way to answer this earlier question: What belongs in the neighborhood of this known center for this kind of request? One design option is to store that neighborhood instead of reconstructing it from scratch for every request. This does not automatically require a new database. An application can model the relationship with SQL junction tables, graph edges, materialized views, search documents, or maintained ID lists. The important change is conceptual: candidate scope becomes data with its own lifecycle, rather than temporary query logic assembled immediately before ranking. For the tourism example, a reusable neighborhood needs a few properties: If this relationship is occasional, application code may be enough. If the same pattern appears across users, places, projects, products, or time windows, it can be useful to make locality a first-class retrieval primitive. [KoutenDB](https://github.com/puffball1567/koutendb) is one database designed around that pattern. It uses coordinate-like `ring` placement for canonical records and `stellar` visibility lenses for reusable views across those coordinates. An importer or application can keep tourism data in canonical rings: ``` landmarks/kyoto/kiyomizudera landmarks/kyoto/yasaka-pagoda restaurants/kyoto/gion culture/kyoto/national-museum events/kyoto/2026-07-23 transit/kyoto/higashiyama ``` It can then attach useful coordinates to an area-centered view: ``` kouten stellar attach \ --stellar=travel/kyoto/higashiyama \ --ring=landmarks/kyoto/kiyomizudera kouten stellar attach \ --stellar=travel/kyoto/higashiyama \ --ring=restaurants/kyoto/gion kouten stellar attach \ --stellar=travel/kyoto/higashiyama \ --ring=culture/kyoto/national-museum kouten stellar attach \ --stellar=travel/kyoto/higashiyama \ --ring=events/kyoto/2026-07-23 ``` The application can read the surrounding context from the known center: ``` kouten get --stellar=travel/kyoto/higashiyama ``` Or narrow the same field of view to one category: ``` kouten get --stellar=travel/kyoto/higashiyama --subring=restaurants kouten get --stellar=travel/kyoto/higashiyama --subring=culture ``` The same restaurant can also be visible from a rainy-day guide, a station-centered guide, or a user's itinerary without copying its canonical payload. KoutenDB does not infer geographic or editorial relevance from a path name. The application, importer, or curation process still decides which coordinates belong in each neighborhood. The database preserves that decision so later reads do not have to reconstruct it from scratch. A ring name deliberately looks like a readable path. Hierarchy is useful when parent and child data naturally belong together. A path prefix alone, however, represents one tree. Real records participate in several views at once: ``` canonical catalog: restaurants/kyoto/gion/example-cafe area guide: travel/kyoto/higashiyama weather guide: travel/kyoto/rainy-day personal plan: travel/users/123/today ``` Copying the restaurant under every path creates synchronization work. Moving it to one path weakens the other views. A stellar lens instead stores a visibility relationship across existing coordinates. The caller supplies a center and a cost boundary. Depth, branch budget, subrings, filters, projections, sorting, and limits control how much context is returned. The caller does not have to enumerate every individual record in the answer. Locality-aware retrieval and vector search are not mutually exclusive. A combined pipeline can look like this: ``` php known center: user + place + time -> retrieve the configured local neighborhood -> apply current constraints -> vector-rank the smaller candidate set if needed -> rerank and enforce a token budget -> LLM ``` The vector database asks which candidates are semantically close. The locality layer asks which part of the data should be considered first. This can reduce the number of payloads loaded, vectors compared, records reranked, bytes transferred, and tokens considered downstream. It does not guarantee that every query becomes faster. The model is useful only when the application can express meaningful locality. The database decision becomes clearer when it starts from the question rather than the current RAG trend. | Retrieval question | Natural starting point | |---|---| | Which exact record or relationship is valid? | Relational query and indexes | | Which JSON documents match known fields? | Document database or SQL/JSON | | Which result can be reused briefly? | Cache or session store | | Which documents contain these words? | Full-text search engine | | Which documents are semantically similar? | Vector search | | Which places are inside a literal radius? | Geographic index | | Which context is useful around this known user, place, project, or time? | Locality-aware neighborhood retrieval | These are not exclusive choices. PostgreSQL may remain the source of truth while OpenSearch handles lexical retrieval. A vector database may rank semantically similar chunks. KoutenDB may be used as a locality-aware document and retrieval store, or as an earlier candidate-selection stage. A smaller system may choose one database that covers enough of these roles. A larger system may separate them. Additional infrastructure is justified only when it removes a measured retrieval, correctness, or operations problem. Practical database design steps for RAG are therefore: RAG architecture often begins by asking which embedding model or vector database to use. Another useful question comes first: What does the application already know before retrieval begins? It may already know the authenticated user, tenant, order, project, document group, current place, time window, or active task. That information can define a much smaller and more relevant starting region than the complete corpus. If the request begins with no meaningful center, global lexical or vector search may be exactly right. If the request begins with a known center but an uncertain context boundary, preserving locality can keep the retrieval problem smaller before ranking and prompt construction begin. The important choice is not SQL versus vectors, or one database product versus another. It is deciding whether the application needs exact truth, lexical matching, semantic similarity, geographic proximity, or a reusable neighborhood of context—and then giving each retrieval stage the data model it actually needs. That is the foundation of a RAG database design that can grow beyond a demo.