Databases Overview

Every AI system needs somewhere to put data — and the honest answer, for almost any real system, is three different somewheres, not one. A production RAG application typically runs a relational database for users, sessions, and experiment metadata; a vector database for the embeddings retrieval depends on; and increasingly a graph database for the relationships flat retrieval can't see. None of the three is a strictly better general-purpose database — each optimizes for a genuinely different query shape, and picking wrong doesn't just cost performance, it can make a query structurally impossible to express efficiently at all.

Database Fundamentals, in Plain English

Before comparing engines, five terms worth being precise about — everything below this section uses them constantly, and they're easy to blur together:

• Database: a collection of organized, related data — the actual information itself (a company's customers, a chat app's messages, a RAG pipeline's document chunks).
• DBMS (Database Management System): the software that manages a database for you — handles storing it on disk, retrieving it efficiently, letting multiple things read/write it at once safely, and keeping it durable if the power goes out. Postgres, MySQL, MongoDB, Neo4j, and every other engine on this page is a DBMS. The alternative — writing your own data to plain files by hand — technically works but forces you to reinvent concurrency control, crash recovery, and fast lookup yourself, which is exactly the hard, easy-to-get-wrong work a DBMS exists to do once, correctly, so nobody else has to.
• Schema: the structure a database's data follows — which tables exist, what columns each one has, what type each column holds, which values are required or must be unique. A schema is the blueprint, not the data itself: it's the difference between "there's a users table with an email column that must be unique" (schema) and "the actual list of users and their actual email addresses" (data).
• Table: a collection of records that all share the same structure — think a spreadsheet, where every row follows the same set of columns. A users table might have one row per person who signed up.
• Column: one single typed field within a table's structure, present on every row — email (text), signup_date (a timestamp), is_active (true/false) would each be one column of a users table.

Three Different Questions, Three Different Engines

The three families covered in this section aren't competing solutions to the same problem — they're built to answer three different kinds of question well, and each is a poor fit for the other two's question:

• Relational (MySQL, Postgres) answers "give me rows matching these exact conditions, with guarantees" — structured records, transactions, referential integrity.
• Vector Databases answer "what's semantically similar to this?" — embeddings and approximate nearest-neighbor search, the retrieval backbone of RAG.
• Graph Databases (Neo4j) answer "how are these things connected, possibly several hops away?" — relationship traversal, multi-hop reasoning, GraphRAG.

Picking the Right Store for a Query

The practical skill isn't memorizing three sets of features — it's recognizing, from the shape of a query itself, which engine can actually answer it efficiently:

"Find all orders for user 4471" is a relational lookup. "Find the 10 chunks most semantically similar to this question" is a vector search. "Find every document connected to this one through a chain of citations, up to 3 hops away" is a graph traversal — and notably, that last query gets slower with every additional join in a relational database (each hop is another join), while a graph database treats it as a fast, local walk regardless of overall graph size, because relationships are stored as first-class structures instead of reconstructed from foreign keys every time. Same underlying question, structurally different cost depending on which engine answers it.

Why Real Systems Run All Three at Once

This isn't a theoretical menu to choose one item from — it's normal for a single AI application to run all three stores simultaneously, each owning the part of the data it's actually good at. This pattern (sometimes called polyglot persistence) is the default shape of a real RAG or agent system, not an exception:

A concrete walk-through: a user asks a question → the app looks up their session and permissions in the relational store → embeds the question and retrieves the most relevant chunks from the vector store → for a multi-hop question, traverses the graph store to connect facts across documents (GraphRAG) → writes the interaction back to the relational store for logging and analytics. Three stores, three jobs, one request.

What Each Engine Trades Away

Every one of these engines buys its specialty by giving something up elsewhere — worth naming explicitly, since the tradeoffs are exactly why you can't just pick one and use it for everything:

Relational databases give you exact, strongly-consistent answers (ACID transactions — a query today returns the same correct result it would have a millisecond ago) but pay for it with join cost that grows with relationship depth. Vector databases give up exactness entirely — Approximate Nearest Neighbor (ANN) search trades a small, tunable chance of missing the true closest match for speed that stays roughly flat as the collection grows into the billions. Graph databases keep exactness but restructure storage so relationship-heavy queries stay fast regardless of depth, at the cost of being a worse fit for simple flat lookups than a relational table.

Indexing: The Common Thread

Different as they are, all three engines lean on the same underlying idea — precompute a structure that avoids scanning everything at query time — just specialized to what "nearby" means for that engine:

A relational B-tree index narrows an exact-match or range query from a full table scan to a logarithmic lookup. A vector index (HNSW, IVF) narrows a similarity search from comparing against every stored vector to a small, well-chosen candidate set. A graph engine's native adjacency storage means a "neighbors of this node" query never needs an index at all in the traditional sense — the relationships are the index. Same goal, three different structures, because "what makes two things close" means something different in each engine.

Search Infrastructure: Elasticsearch and OpenSearch

What is it, and how is it different from what's already on this page? A specialized engine built around one job — full-text search over documents at scale — distinct from both vector search (semantic similarity over embeddings) and a NoSQL document store (flexible storage and lookup, not search-optimized). Under the hood, both Elasticsearch and OpenSearch (an Apache-2.0 fork of Elasticsearch that AWS and others created in 2021 after Elastic moved its own licensing away from a fully open-source model — Elasticsearch itself re-added an open-source AGPLv3 option in 2024, but the two projects have diverged since the fork) are built on Apache Lucene, using an inverted index — for every term, a list of which documents contain it — the same structural idea BM25 scoring is built on top of, at real production scale with real infrastructure (sharding, replication, aggregations) around it.

Why is it useful? Relevance-ranked full-text search (typo tolerance, stemming, faceted filtering, aggregations over huge document volumes) is a genuinely different workload than either a database's exact-match lookups or a vector database's similarity search — log analytics and observability platforms (see Observability), e-commerce product search, and general application search all reach for this specifically because neither a relational LIKE query nor a vector index is built for it at this scale.

Limitation: Running Elasticsearch/OpenSearch well in production is real operational overhead — cluster sizing, shard management, and reindexing strategy are their own ongoing discipline, not a "deploy and forget" system, and neither is a good fit for the transactional, strongly-consistent guarantees a relational database provides.

A Fourth Branch: NoSQL

The three engines above are framed around a specific question — which query shape does an AI system's data actually need? — but they don't cover the full database landscape on their own. NoSQL databases are a real fourth branch: document stores (MongoDB), key-value stores (Redis, DynamoDB), and wide-column stores (Cassandra), each trading away part of the relational model (joins, strict schema, or multi-row transactions) for horizontal scale or a data shape that can evolve without a migration. Relevant to AI systems the same way relational is — application state, caching, session storage — just optimized for different scale and flexibility tradeoffs than any of the three engines above.

Reading Path Through This Section

Start with Relational Databases for the model most ML metadata and application state still lives in, then its deep-dive companion PostgreSQL for schemas, indexing, and Postgres-specific features like JSONB and pgvector. Move to Vector Databases for the engine every RAG pipeline depends on, Graph Databases for when relationships matter more than similarity, and NoSQL Databases for the document/key-value/wide-column branch outside all three. See the roadmap for the full checklist-style path through the section.