An AI product is never one kind of data. It has account and billing records that must be correct, a registry of models and prompts that changes rarely, chat transcripts that are append-heavy, event logs that are enormous, features that must be served in milliseconds, embeddings that are searched by similarity, and sometimes a knowledge graph. Interviewers probe this because "just use Postgres" and "just use MongoDB" are both lazy answers: the strong answer starts from the access pattern and the consistency requirement of each workload, then admits what it costs to run more than one store. For a Backend or AI engineer the practical skill is choosing the smallest set of stores that covers the workloads, and knowing how the copies stay in step. The general SQL-versus-NoSQL taxonomy is covered in SQL vs NoSQL; this page applies it to AI workloads.
A relational database (like Postgres or MySQL) stores information in strict, structured tables with rows and columns, similar to a well-organized spreadsheet where every row must follow the same set of columns. It is built to guarantee that related pieces of data (a customer, their order, their payment) stay correct and connected even when many people are reading and writing at once.
NoSQL is an umbrella term for databases that skip the rigid table-and-row structure in favor of more flexible formats, like documents, simple key-value pairs, or wide tables spread across many machines. This flexibility makes them well suited to huge volumes of fast-changing or unevenly shaped data, at the cost of some of the strict correctness guarantees a relational database provides.
Think of a public library. A relational database is like the library's official card catalog: every book has a precise record with a fixed set of fields (title, author, ISBN, shelf location), cross-referenced so the system always knows exactly what exists and where. NoSQL is more like a warehouse of moving boxes, each one loosely labeled and packed differently depending on what is inside — perfect for handling enormous, constantly shifting piles of donated books quickly, but you sacrifice the precise, guaranteed lookup the card catalog gives you.
Workload Dominant access pattern Consistency need Usual fit (not a rule)
---------------------- --------------------------------- ---------------------- ---------------------------------
Users, billing, quotas point lookup + joins + transactions strong (ACID) relational (Postgres/MySQL)
Model / prompt registry small, relational, versioned strong, low write rate relational
Chat history append; read last N by conv id per-conversation order relational, or wide-column/KV/doc
Feature / online store key lookup by entity id, low ms read-your-write nice, key-value / in-memory / DynamoDB-like
staleness usually OK
Event / request logs huge append, scan by time range eventual is fine object storage + columnar files,
or a log/OLAP store
Embeddings nearest-neighbour by vector eventual vs source vector index (pgvector, dedicated
vector DB, search engine)
Knowledge graph multi-hop traversal depends graph DB, or relational with
recursive queries at small scaleThe table is a starting heuristic, not a lookup. What actually decides is the questions you must answer quickly: "give me the last 10 messages for conversation X" is a key-plus-range read; "how many tokens did tenant Y spend per model last month" is an aggregation across many rows; "which 5 chunks are closest to this vector" is a similarity search. A store is good at the questions its physical layout was built for. Relational engines keep rows in a B-tree and are strong at joins, constraints and multi-row transactions; key-value and wide-column stores partition by key and scale writes by adding nodes but generally expect you to know your queries in advance; a vector index is built only for nearest-neighbour lookups (see Vector Databases and pgvector).
Where it breaks at scale: teams often pick the store by data size ("it is big, so NoSQL") when the deciding factor is the query. A 200 GB table with a good index and single-key lookups is fine in a relational database; a 5 GB dataset that needs ad-hoc joins is painful in a key-value store. Conversely a relational primary that must absorb hundreds of thousands of small writes per second eventually needs sharding (see Replication & Sharding), and at that point some of the relational conveniences (cross-shard joins, global transactions) are gone anyway.
DOCUMENT model: one document per conversation, messages embedded in an array
conversations: { _id: 7, user_id: 3, messages: [ {seq:0,role:"user",...}, {seq:1,...}, ... ] }
+ one read gets the whole conversation, no join
- append = rewrite (or patch) a growing document; documents have size limits
(e.g. MongoDB 16 MB, DynamoDB 400 KB per item -- check current limits)
- "all assistant messages > 500 tokens" needs to look inside every document
RELATIONAL model: messages are rows
conv(id, user_id) msg(conv_id, seq, role, content, tokens) PRIMARY KEY (conv_id, seq)
+ append = one small INSERT; last-N is an index range scan; aggregations are GROUP BY
+ can enforce foreign keys, per-row retention, per-row deletes (privacy requests)
- reading a whole conversation is N rows, not one documentThe same data can be modelled either way, and the correct choice follows the dominant query. If the app always loads the entire small conversation and never analyses messages, embedding them in one document is simple and fast. If the prompt builder needs only the last few messages of a long conversation, or you need per-message analytics and deletion, per-message rows (or, in a wide-column store, one partition per conversation with messages ordered by a clustering key) avoid reading and rewriting the whole history. The demo below measures exactly this difference in bytes. Note that real document databases can often project or slice an array server-side, so the honest comparison is "rewrite cost and unbounded growth", not "documents cannot do it".
The "bucket" pattern is the usual compromise: one document per conversation per N messages (or per day), which bounds document size while keeping a few messages per read. It shows the general rule of NoSQL modelling: you denormalize deliberately for the reads you know about, and you pay for it with duplicated data and harder ad-hoc questions (see Normalization & Denormalization).
Needs a real transaction (multi-row, must not double-apply):
- credit balance / quota decrement + usage ledger entry
- "create fine-tune job" + reserve GPU quota + write audit row
- model registry: promote version N to "production" and demote the old one atomically
Fine with eventual / single-item atomicity:
- chat message append (order within one conversation matters, across conversations does not)
- request log lines, metrics, click events
- embeddings and search indexes (rebuildable from the source of truth)
- online features (a few seconds stale is usually acceptable; verify per model)Ask of each data type: "what is the damage if two writers race or a write is lost?" A billing ledger that double-charges or a registry with two "production" versions is a correctness incident, so it belongs in a store with multi-row ACID transactions and constraints (see Transactions & ACID). Many NoSQL systems offer single-item atomicity and some offer limited multi-item transactions, but the guarantees and performance vary by product, so check the specific system rather than assuming. A lost log line or a slightly stale feature usually is not an incident, which is why those workloads can use cheaper, horizontally scalable, eventually consistent stores (see CAP & Consistency Models).
The failure mode to name in an interview is putting a money-like counter in a store that only guarantees eventual consistency, then reconciling forever; or the opposite, forcing terabytes of append-only telemetry through the transactional primary and starving the customer-facing queries that share its I/O and connection budget.
writes derived copies (rebuildable)
app ----> PRIMARY (Postgres) --CDC/outbox--> vector index
system of record --> search index
--> online feature store
--> analytics files (Parquet in object storage)
Costs of each extra store:
ops : backups, upgrades, capacity, on-call runbooks, security review, another failure mode
sync : the copy lags or drifts (dual-write failure, missed deletes, schema change)
consistency: a read from the derived store may not yet reflect a write to the primary
cognitive : N query languages, N client libraries, N ways to do access controlPolyglot persistence means using several specialised stores. It is legitimate when a workload has a shape one engine handles badly, but every added store is a permanent operational cost, and the hardest part is keeping copies consistent. The naive approach, writing to the primary and then calling the second store from the application, loses updates whenever the second call fails after the first commit. The standard fixes are a transactional outbox (write the change and an "event to publish" row in the same local transaction, then a relay retries), or change-data-capture from the primary's log (see Change Data Capture), both of which give at-least-once delivery, so the consumer must be idempotent (see Idempotency). Designate one store the system of record and treat everything else as derived and rebuildable; deletes and privacy requests must then propagate to every derived copy.
Before adding a store, check whether the incumbent can cover the workload at your scale: Postgres with an index extension can serve modest vector search, JSON columns, and full-text search, and often that is the correct first step. Add the specialised store when measured load, latency, or index size says the incumbent cannot cope, not when it merely looks fashionable.
ILLUSTRATIVE assumptions: multi-tenant chat assistant, RAG over customer documents,
1M requests/day, ~10 messages per conversation, ~2 KB per message.
Data Store choice Why
-------------------------- --------------------------------- --------------------------------------------
tenants, users, billing, Postgres (system of record) joins, constraints, ACID for money and quotas
quotas, API keys
model + prompt registry same Postgres (separate schema) tiny, relational, needs atomic promotion
chat history Postgres table at first; append + last-N by conv id; move to a
partition/shard or wide-column key-partitioned store only if write volume
store if volume demands outgrows one primary
document chunks + pgvector first; dedicated vector same DB keeps chunk metadata and vectors
embeddings DB when index size / QPS demands consistent; scale out when measured
online features / session Redis or DynamoDB-style KV key lookup in low ms, TTL, rebuildable
request + token logs object storage as Parquet, or an append-only, scanned by time; cheap per GB
OLAP/log store (see the object-storage page)
knowledge graph (optional) Postgres recursive CTEs if small; add a graph DB only if multi-hop traversal
graph DB if traversals dominate is a core, measured need
Rough size: 1M req/day x ~2 messages logged = ~2M rows/day; ~2 KB each = ~4 GB/day, ~1.5 TB/year
of chat text alone (ILLUSTRATIVE, before indexes and replicas). That is manageable in one
partitioned Postgres for a while; the log firehose is what should leave the primary first.The design principle is to start with one strong relational system of record and peel workloads away only when a specific access pattern or volume demands it, in the order of the highest pain: usually the high-volume append-only logs first (they need no transactions and are cheap in object storage), then the online lookups that need single-digit-millisecond latency, then vector search at scale. The word ILLUSTRATIVE matters: real numbers depend on token counts, retention, replication, and compression, so state assumptions aloud in the interview and show the arithmetic.
# Python 3 (standard library only): chat history as one JSON document vs one row per message.
# Sizes are bytes; no timings, so the output is deterministic for the seed.
import sqlite3, json, random
random.seed(11)
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE conv_doc (id INTEGER PRIMARY KEY, user_id INT, doc TEXT);
CREATE TABLE conv (id INTEGER PRIMARY KEY, user_id INT);
CREATE TABLE msg (conv_id INT, seq INT, role TEXT, content TEXT, tokens INT,
PRIMARY KEY (conv_id, seq));
CREATE INDEX idx_conv_user ON conv(user_id);
CREATE INDEX idx_doc_user ON conv_doc(user_id);
""")
WORDS = "the model returns a short answer about retrieval latency cache index token cost".split()
def text(n): return " ".join(random.choice(WORDS) for _ in range(n))
N_CONV, MSGS = 200, 60
for cid in range(1, N_CONV + 1):
uid = cid % 20
msgs = [{"seq": s, "role": "user" if s % 2 == 0 else "assistant",
"content": text(random.randint(8, 40)), "tokens": random.randint(10, 60)}
for s in range(MSGS)]
db.execute("INSERT INTO conv_doc VALUES (?,?,?)", (cid, uid, json.dumps({"messages": msgs})))
db.execute("INSERT INTO conv VALUES (?,?)", (cid, uid))
db.executemany("INSERT INTO msg VALUES (?,?,?,?,?)",
[(cid, m["seq"], m["role"], m["content"], m["tokens"]) for m in msgs])
CID, LAST = 7, 10
print("== Q1: load the last 10 messages of one conversation (to build the prompt)")
doc = db.execute("SELECT doc FROM conv_doc WHERE id=?", (CID,)).fetchone()[0]
last_doc = json.loads(doc)["messages"][-LAST:]
rows = db.execute("SELECT seq, role, content, tokens FROM msg WHERE conv_id=? "
"ORDER BY seq DESC LIMIT ?", (CID, LAST)).fetchall()
row_bytes = sum(len(r[2]) + len(r[1]) + 16 for r in rows)
print("document model : fetched %6d bytes, parsed %d messages, kept %d" % (len(doc), MSGS, len(last_doc)))
print("relational : fetched %6d bytes (approx), %d rows" % (row_bytes, len(rows)))
print("== Q2: append one message")
new = {"seq": MSGS, "role": "user", "content": text(20), "tokens": 30}
d = json.loads(doc); d["messages"].append(new); newdoc = json.dumps(d)
print("document model : rewrite whole doc -> %d bytes written (read-modify-write)" % len(newdoc))
print("relational : INSERT one row -> ~%d bytes written" % (len(new["content"]) + len(new["role"]) + 16))
print("== Q3: total tokens per role across ALL conversations")
rel = db.execute("SELECT role, SUM(tokens) FROM msg GROUP BY role ORDER BY role").fetchall()
tot = {}
scanned = 0
for (d,) in db.execute("SELECT doc FROM conv_doc"):
scanned += len(d)
for m in json.loads(d)["messages"]:
tot[m["role"]] = tot.get(m["role"], 0) + m["tokens"]
print("relational GROUP BY :", rel)
print("document, app-side :", sorted(tot.items()), " (parsed %d bytes of JSON)" % scanned)
js = db.execute("SELECT j.value ->> 'role', SUM(j.value ->> 'tokens') FROM conv_doc, "
"json_each(conv_doc.doc, '$.messages') j GROUP BY 1 ORDER BY 1").fetchall()
print("document, json_each :", js)
print("== Query plans")
for label, q in [("doc by user_id", "SELECT doc FROM conv_doc WHERE user_id=3"),
("rel messages by conv", "SELECT * FROM msg WHERE conv_id=7 ORDER BY seq DESC LIMIT 10"),
("rel role search ", "SELECT * FROM msg WHERE role='user'"),
("doc role search ", "SELECT * FROM conv_doc, json_each(doc,'$.messages') j WHERE j.value->>'role'='user'")]:
plan = db.execute("EXPLAIN QUERY PLAN " + q).fetchall()
print(label, "->", " | ".join(p[3] for p in plan))
print("sqlite version", sqlite3.sqlite_version, "| total doc bytes:",
db.execute("SELECT SUM(LENGTH(doc)) FROM conv_doc").fetchone()[0])
# Real output from python3 (sqlite 3.50.4; sizes are bytes, so deterministic for this seed):
# == Q1: load the last 10 messages of one conversation (to build the prompt)
# document model : fetched 12596 bytes, parsed 60 messages, kept 10
# relational : fetched 1554 bytes (approx), 10 rows
# == Q2: append one message
# document model : rewrite whole doc -> 12771 bytes written (read-modify-write)
# relational : INSERT one row -> ~137 bytes written
# == Q3: total tokens per role across ALL conversations
# relational GROUP BY : [('assistant', 210936), ('user', 210053)]
# document, app-side : [('assistant', 210936), ('user', 210053)] (parsed 2486196 bytes of JSON)
# document, json_each : [('assistant', 210936), ('user', 210053)]
# == Query plans
# doc by user_id -> SEARCH conv_doc USING INDEX idx_doc_user (user_id=?)
# rel messages by conv -> SEARCH msg USING INDEX sqlite_autoindex_msg_1 (conv_id=?)
# rel role search -> SCAN msg
# doc role search -> SCAN conv_doc | SCAN j VIRTUAL TABLE INDEX 3:
# sqlite version 3.50.4 | total doc bytes: 2486196
# Python 3: why naive dual writes lose updates, and how an outbox repairs it.
import sqlite3, random
random.seed(3)
# Polyglot persistence: system of record (SQLite) + derived index (a dict standing in for a search/vector store)
def run(use_outbox, n=1000, fail_rate=0.05):
db = sqlite3.connect(":memory:")
db.executescript("CREATE TABLE docs(id INT PRIMARY KEY, body TEXT);"
"CREATE TABLE outbox(id INTEGER PRIMARY KEY AUTOINCREMENT, doc_id INT, done INT DEFAULT 0);")
index = {} # derived store
def index_write(doc_id): # may fail, like a network call
if random.random() < fail_rate: raise ConnectionError
index[doc_id] = "indexed"
for i in range(n):
if not use_outbox: # dual write: DB commit, then index call
db.execute("INSERT INTO docs VALUES (?,?)", (i, "x")); db.commit()
try: index_write(i)
except ConnectionError: pass # lost forever: nobody retries
else: # outbox: one local transaction records intent
with db:
db.execute("INSERT INTO docs VALUES (?,?)", (i, "x"))
db.execute("INSERT INTO outbox(doc_id) VALUES (?)", (i,))
if use_outbox: # relay: retry until every row is done (at-least-once)
rounds = 0
while db.execute("SELECT COUNT(*) FROM outbox WHERE done=0").fetchone()[0]:
rounds += 1
for oid, did in db.execute("SELECT id, doc_id FROM outbox WHERE done=0").fetchall():
try: index_write(did)
except ConnectionError: continue
db.execute("UPDATE outbox SET done=1 WHERE id=?", (oid,)); db.commit()
print(" relay rounds needed:", rounds)
return n - len(index)
print("dual write, no retry : docs missing from index =", run(False))
print("transactional outbox : docs missing from index =", run(True))
# Real output from python3:
# dual write, no retry : docs missing from index = 50
# relay rounds needed: 3
# transactional outbox : docs missing from index = 0
# (the exact counts depend on the seed; the point is that the dual write silently loses ~fail_rate of updates)Q: How do you decide between a relational database and a NoSQL store for a new AI workload?
A: I start from the queries and the cost of a wrong write. If the workload needs joins, constraints, or multi-row transactions (billing, quotas, registries), I use a relational database. If it is key-based access at a volume one node cannot handle (chat at very high write rate, online features), a partitioned key-value or wide-column store is a fit, and I accept that I must model for known queries. Data size alone is a weak reason.
Q: Where would you store chat history?
A: The access pattern is append, and read the last N messages by conversation id. Per-message rows keyed by (conversation_id, sequence) in Postgres work well until write volume outgrows one primary, and the same key design maps directly to a wide-column or DynamoDB-style partition. Embedding the whole conversation in one document is fine for short chats but rewrites a growing blob on every message and runs into document size limits.
Q: Which parts of an LLM app truly need ACID transactions?
A: Anything where a lost or doubled write is a correctness incident: credit and quota decrements with a ledger row, job creation that reserves resources, and registry promotion where exactly one version must be "production". Chat messages, logs and embeddings generally tolerate weaker guarantees. I would keep the transactional set small and in one relational store so those invariants can be enforced with constraints.
Q: What is the hidden cost of polyglot persistence?
A: Operations (backups, upgrades, on-call for each system), a synchronisation path that can lag or drift, and a consistency window where a derived store lags the primary. A dual write can fail halfway and silently leave the copies different; an outbox or CDC pipeline fixes that with at-least-once delivery, which then requires idempotent consumers. So each store must justify itself against measured need.
Q: Could Postgres alone serve an LLM app? When would you leave it?
A: For a long time, yes: relational tables, JSON columns, full-text search, and a vector extension such as pgvector cover many early workloads and keep everything transactionally consistent. I would move a workload out when measurement shows a limit: the append-only log volume crowding the primary, vector index size or query rate beyond what one node serves, or latency targets that need an in-memory tier. Logs go first because they need no transactions.
Q: Why not put event logs in the same database as user accounts?
A: They have opposite profiles: logs are huge, append-only, rarely updated and scanned by time, while accounts are small, updated, and queried by key with correctness requirements. Mixing them makes the log volume consume I/O, cache, and storage that latency-sensitive transactional queries need, and it makes backups and vacuum-style maintenance heavier. Columnar files in object storage are far cheaper per GB for the log data.
Q: When is a graph database justified?
A: When multi-hop relationships (friends of friends, entity links in a knowledge graph, dependency chains) are the core query and depth is variable, so relational joins or recursive CTEs become expensive or unwieldy. For shallow, small graphs a relational schema or recursive CTE is usually enough and avoids another system. I would prototype on the incumbent and measure the traversal cost before adding a graph store.
Follow-up: Your search index is missing some documents that exist in the primary. How do you debug and prevent it?
A: The likely cause is a dual write where the second call failed and nobody retried, or a consumer that dropped events. Reconcile by comparing primary IDs with index IDs (or version numbers) and re-index the differences, then prevent recurrence with an outbox or CDC feed, idempotent index writes keyed by document ID and version, a dead-letter queue, and a lag/count-drift alert. Deletes need the same treatment or the index will serve removed data.
Follow-up: A single tenant's chat volume is 100x others and hot-spots one partition. What do you do?
A: Partition by a higher-cardinality key such as conversation_id (not tenant_id), or add a bucket suffix to spread one tenant's conversations, accepting that "all of a tenant's messages" becomes a scatter-gather or needs a secondary index. You could also isolate the large tenant on its own shard. The cost is more complex routing and cross-partition queries, which is the usual trade for write scalability.
Follow-up: A user invokes a right-to-erasure request. What does polyglot persistence do to that?
A: The deletion must reach every copy: the primary, chat history, the vector index, caches, search indexes, derived Parquet files, and backups within their retention policy. With derived stores that means deletes must flow through the same outbox/CDC pipeline and be verified, and immutable columnar files may need to be rewritten or compacted. This is a real argument for keeping fewer stores and for partitioning data so a user's records are easy to locate.
In a design interview for an LLM product (a support assistant, a copilot, a RAG search), the storage slide should name one system of record and list derived stores with a reason and a feeding mechanism for each. A defensible ILLUSTRATIVE sketch: Postgres for tenants, billing and the model registry; conversation messages in Postgres or a key-partitioned store depending on write rate (assume 1M requests/day is only ~12 requests per second on average and perhaps 10 times that at peak, well within one primary, so do not shard prematurely); chunk embeddings in pgvector until the index no longer fits comfortably in memory, then a dedicated vector service; Redis for sessions, rate limits and cached embeddings; and request and token logs shipped as Parquet to object storage for analytics and cost attribution (see Object Storage & Data Lakes and Feature Stores). Then say how derived stores are fed (outbox or CDC), how lag is monitored, and how a store is rebuilt if lost. Interviewers listen for two things: estimation with stated assumptions rather than reflex, and an explicit answer to "what happens when the vector index and the primary disagree".
Consolidating on one relational database buys transactional simplicity, one operational surface and easy joins, at the cost of a single scaling limit and workloads interfering with each other; splitting into specialised stores buys each workload the shape it needs and horizontal scale, at the cost of synchronisation, more operations and weaker cross-store consistency. NoSQL modelling is fast for the queries you designed and awkward for the ones you did not, so requirements that keep changing favour a relational schema with indexes. Polyglot persistence is the wrong choice for a small team with modest data, where the added operations outweigh any benefit; a single-store design is the wrong choice once one workload's volume or latency (high-rate logs, sub-millisecond lookups, large vector indexes) demonstrably harms the others. Choose the simplest option that passes measured requirements, and revisit when the numbers change, not before.