Databases in the age of AI

In an order system, a foreign key that prevents `orders.customer_id` from referencing a nonexistent customer is a stronger shared rule than validation on a single screen. The constraint applies consistently even when admin tools, batch jobs, and new mobile APIs all use the same database. A unique constraint prevents duplicate storage of the same payment identifier, while a check constraint enforces allowed quantity or status values close to the data.

A transaction defines externally visible state transitions rather than merely grouping queries for speed. If order creation fails after inventory is reduced, both changes must roll back. When two users order the last item in stock at the same time, you must understand which outcomes the isolation level and locking strategy permit. Using a transaction alone does not eliminate every concurrency problem.

Schema migration does not end with adding a column. In a rolling deployment where old and new application versions coexist, you may need to add a compatible column, backfill data, switch the read path, and strengthen constraints last. Validation and index builds on large tables can introduce locks and I/O, so measure duration and blocking scope at realistic data volumes.

There are also conditions where a relational model is not the right choice. If most access reads and writes an entire aggregate rather than focusing on relationships and transactions, if the schema changes rapidly, or if cross-region availability is a strong priority, another storage model may be simpler. However, compare query plans, cardinality, consistency requirements, and recovery approaches on the same workload rather than splitting storage based only on an assumption that joins are slow.

A document database stores an object’s structure together in one record, which makes it easy to align the application model with the unit of reading. For services where large-scale key-value access and horizontal distribution matter, it reduces relational joins and lets a partition key predict where a request goes. In exchange, rules that span multiple documents, updates to duplicated data, and new query patterns can become the application’s responsibility.

PostgreSQL's JSON types, indexing, partitioning, and extensions make it possible to manage core data that needs relational constraints and flexible attributes within one operational boundary. That does not mean every workload belongs in one database. Compare polyglot persistence with unified operation, including the costs of failure domains, independent scaling, backup, and team ownership.

An embedding is not an answer key that perfectly stores the meaning of a sentence or image; it is the result of turning the model's learned representation into fixed-dimension numbers. Expressions with the same meaning may land close together, but numbers, negation, recent facts, and domain terms may be placed differently than expected. When the model or preprocessing changes, new vectors may not be directly comparable with existing ones in the same space.

Exact search computes distances to every vector to find the closest results under the current metric, but its cost increases with data size. Approximate indexes such as HNSW and IVFFlat gain speed by exploring only some candidates, trading off recall, memory, build cost, and update cost. Use representative queries and a ground-truth set to measure recall@k, tail latency, and index-build time together so index selection is evidence-based.

RAG is not a single step of putting documents into a vector database and calling a model. It is a sequence of source collection, parsing, cleaning, chunking, embedding, indexing, query transformation, candidate retrieval, permission filtering, reranking, context packing, generation, and citation. When an answer is wrong, you cannot tell a prompt problem from a missing source unless you preserve the inputs and revision of each stage.

For example, if the latest HR policy is missing from an answer, check in order whether ingest read an older revision, the table parser lost a key row, the chunk used different wording from the question, or the ACL filter removed an allowed document. A citation must point to the span supporting the actual answer sentence and its source revision, not merely append a URL string.

When an Agent queries a database in natural language, giving the model raw SQL and administrator credentials can turn query mistakes and prompt injection directly into data exposure or modification. Put allowed views, row-level policies, and parameterized queries behind limited tools, and control result counts and sensitive fields on the server.

Apply authorization filters before generating candidates, not as post-processing that hides results after retrieval. If unauthorized vectors enter reranking or model context and are hidden only on screen, the information has already crossed the processing boundary. Record the tenant, subject, source revision, and policy decision in the retrieval trace, and verify how long revoked access takes to disappear from caches and indexes after a permission change.

A teaching fixture sends two simultaneous requests for seat A3 in session S1. Synchronize them so that both finish their prechecks before starting writes, creating contention that sequential tests do not expose. Distinguish this from running the requests sequentially and having only one succeed by chance. Record evidence that both prechecks saw an empty state to confirm that the contention condition actually occurred.

Verify not only one success and one conflict response, but also one final booking row and a single capacity decrement. Correct response statuses can still conceal duplicate internal changes. Query remaining capacity immediately after transaction failure. Cached UI counts can conceal stored errors, so verification must include authorized source-data reads.

Also verify that seat A3 in session S2 remains bookable. A uniqueness rule on seat number alone would block valid bookings across sessions, so the key must reflect the domain scope. Check rebooking after cancellation against policy. Restricting active reservations differs from restricting all historical records, so define the domain rule before choosing the data model.

Make agent tools use the same storage contract and inspect bypass write paths. Actual constraints and transaction outcomes establish conflict prevention more reliably than natural-language prohibitions. Inventory batch imports and administrative writers to verify they share the constraint. One safe API does not establish consistency when other writers can bypass the rule.

A fictional corpus stores tenants A and B together while an A user requests five documents. First build an exact baseline over authorized A documents to check that five relevant documents actually exist. Preserve expected document IDs and revisions so deleted or changed sources are not still expected. A stale baseline may require evaluation-data updates rather than index changes.

If the approximate path returns only two results, record search effort, filter conditions, and the actual execution plan. Removing the tenant condition to fill five results is an authorization violation, not a performance fix. Check whether the query plan actually used the approximate index. If a different plan was chosen after a settings change, the latency change cannot be attributed to the scan limit alone.

After changing scan settings or partitioning, recompare recall and latency for the same questions. One tenant may improve while another waits longer, so observe shared-index effects separately. Repeat with controlled query order and cache conditions. Running only one candidate warm confounds cache effects with index-design differences.

Keep zero unauthorized returns as an independent evaluation condition. Better recall cannot justify even one cross-tenant document; retrieval quality and access control must both hold. Excluded document IDs must not remain in public answers or suggestions. Hiding bodies can still expose titles or existence, so inspect the full return format.

Use fictional policy document D7 revision 2 as the deletion fixture and freeze the list of its derived chunk IDs. Before deletion, verify that D7 is retrieved for the question so a document that was never present cannot produce a false pass. Also include another valid document containing the same sentences. This counterexample checks that deletion follows source identity rather than bulk removal of matching strings.

Inject successful index deletion but failed cache invalidation. Check that status remains partially failed and identifies the path still returning stale answers to the same question. Test that pending cache deletion survives restart. An in-memory completion list can lose failed targets after interruption and keep serving stale material.

After reprocessing, query the active index, result cache, and replicas separately to confirm zero entries for D7 revision 2. A total document count dropping by one does not prove removal of specific derived data. Record the queried revisions so the zero-count verdict identifies which generation it covers. Do not merge replica results from different points in time into a single completion state.

Keep the new revision 3 as a separate fixture to check that the deletion scope is not too broad. Correct deletion removes old evidence while preserving retrieval and source links for the valid current document. Apply the revocation list even when testing a switch to an older generation for recovery. If a rollback reactivates all past documents, it may resolve the outage while breaking the deletion commitment.