AI-200: PostgreSQL, Redis, and RAG

An AI assistant answering questions about customer contracts has to do two things well: find the right source material and deliver it quickly enough to be useful. Those demands can lead to a combination of Azure Database for PostgreSQL and Azure Managed Redis, but the services play different roles. PostgreSQL can hold authoritative application data and vector-search structures; Redis can support fast-changing caches and selected vector-retrieval use cases. Both require deliberate data modeling and performance choices. The AI-200 syllabus includes PostgreSQL indexing, pgvector, vector workloads, connection optimization and Azure Managed Redis operations because retrieval reliability depends on the underlying systems.

Within Azure-focused Microsoft certifications, AI-200 is unusually attentive to the operational details of data-driven AI services: connection management, query cost, vector indexing and the behavior of application caches. These details determine whether a retrieval feature remains useful when its dataset grows and its documents change. The task is not to declare a universal winner between relational and in-memory technology. It is to decide which records must be durable, which operations need fresh results, and which latency improvements justify additional operational complexity.

Model the authoritative records before adding vectors

Suppose a company stores contracts, amendments, customers, departments and access permissions. PostgreSQL is attractive when those entities have relationships, transactions and structured queries that the application must maintain. Tables can preserve the normalized records while retrieval-oriented tables hold chunks, embeddings and references to the source versions. That division prevents a vector index from becoming the only copy of legally significant content. A semantically similar chunk is not the same as an authoritative contract clause.

Schema decisions affect both accuracy and maintenance. A retrieval chunk should retain a stable source identifier, version, effective date, tenant, permitted audience and the embedding model used to create the vector. Foreign-key relationships or other application-enforced constraints can help prevent orphaned references. If an amendment supersedes a paragraph, the application needs a reliable way to exclude the old version. A query that retrieves old and current clauses together might be technically correct vector search but dangerously wrong contract guidance.

The first design question is whether the retrieval path can establish which source version is valid before the model sees the text. A clean schema often matters more than finding the fastest approximate-nearest-neighbor method on day one.

Pgvector adds search capabilities without erasing SQL fundamentals

The pgvector extension supports vector storage and similarity operations in PostgreSQL environments that provide the relevant capability. An application can associate an embedding with a row and use vector comparisons to rank candidates. Depending on requirements and supported index options, approximate indexing may reduce query cost for larger collections at the expense of recall characteristics. The exact choice should come from measured query latency, candidate volume and the relevance threshold the application needs.

A legal search illustrates the tension. Searching the top ten semantically related clauses might retrieve a similarly worded document from the wrong jurisdiction. SQL filters for customer, locale, active version and document type should narrow the eligible population. Sometimes those filters are selective enough that a straightforward query performs adequately; sometimes vector indexing and relational indexing need to work together. Blindly adding a specialized vector index cannot compensate for poor tenant isolation or a slow join across an unindexed metadata field.

The important distinction is between search candidate generation and final answer grounding. A retrieval result is evidence to evaluate, not an instruction to the model to answer with certainty. Build evaluation queries with known relevant documents and known distractors so that changes to embedding models or indexes can be tested systematically.

Workload sizing follows concurrent queries and vector dimensions

Vector workloads consume memory, compute and storage in ways that differ from a simple key lookup. Large embeddings increase storage per record and may affect index size, cache efficiency and comparison cost. More concurrent requests can saturate CPU or memory before disk usage looks alarming. AI-200 explicitly expects an understanding of configuring PostgreSQL compute, memory and storage for vector workloads, together with optimizing index choice and query latency.

A useful exercise starts with a measured dataset: number of chunks, vector dimension, average metadata size, candidate filters and expected requests per second. Record p50 and p95 query latency while increasing concurrency. Examine the query plan instead of assuming vector computation is the only cost. If the application performs a database round trip for every retrieved chunk, network latency or connection setup may dominate. If a single query scans too many records, the schema and indexes deserve attention before increasing instance size.

Connection management is another hidden bottleneck. A serverless API may create many short-lived connections during a traffic surge. Pooling where supported and correct, bounded concurrency and sane timeouts can preserve database health. A long model call should not unnecessarily hold an idle database transaction open. SQL transaction lifetime and model-inference latency belong to different stages of the pipeline.

Redis is a performance tool with data-lifecycle responsibilities

Azure Managed Redis can accelerate supported caching and data operations, including vector indexing scenarios covered by the AI-200 guide. A cache may hold frequently requested retrieval results, expensive computed features or short-lived application state. The improvement comes from avoiding repeated slow work, but the invalidation problem arrives immediately. A cached contract answer generated yesterday might be fast and wrong after a critical amendment. Each cached object therefore needs an explicit key design, expiration rule and invalidation path that correspond to the source’s freshness requirements.

For a retrieval service, a cache key might reflect tenant, query normalization, data-set version, model version and permission context. If authorization context is missing, the cache can return results intended for another user. If the corpus version is missing, stale source content may persist until expiration even when the underlying database is correct. The role of Redis in web applications is a useful foundation, but AI workloads add prompt and embedding version concerns to the familiar cache-coherence problem.

Redis can also act as a vector-search component in suitable designs. That does not make it a drop-in replacement for a transactional system of record. Evaluate persistence, durability requirements, supported features, memory pricing, security and failure recovery separately from query speed.

RAG is a pipeline whose weak point can move

Retrieval-augmented generation takes a user’s question, retrieves candidate evidence and supplies selected context to a generative model. The model then composes an answer using that material. Each stage can degrade quality: text extraction can corrupt tables, chunking can sever references, embeddings can blur numeric distinctions, filtering can admit the wrong tenant and prompt construction can remove the crucial clause. Fast storage is helpful but cannot rescue a pipeline that retrieves irrelevant sources.

For contract analysis, provenance should remain visible. A good response path associates a cited sentence with document ID, version and section rather than relying on an unsupported free-form assertion. When the search yields insufficient evidence, the application should be allowed to say that it cannot determine the answer. Retrieval scores are not calibrated probabilities of factual correctness; a top-ranked hit can still be wrong or outdated. Evaluate grounded answer quality as well as retrieval recall.

A model update requires coordinated testing. If embeddings are regenerated with a new model, cached vectors and index records may need version-aware rebuilding. If the language model changes but embeddings do not, answer style and faithfulness can still shift. These are different changes; track them independently rather than blaming the database for every regression in generated text.

Control security, cost and operational failure paths

Database credentials, Redis access and network connections need clear security boundaries. Managed identity or other supported secure authentication patterns should be considered rather than embedding reusable secrets in source code. The application must enforce tenant and document permissions before relevant context leaves the retrieval service. A row or cache key accidentally shared across customers is an architectural security incident, even if every network packet was encrypted in transit.

Cost analysis includes database capacity, vector-index storage, memory allocation, request volume and model inference. Caching can reduce database queries but can also increase infrastructure cost or deliver little benefit if queries are highly unique. A warm cache can make a benchmark look excellent while real production traffic contains diverse, nonrepeating questions. Measure hit rate under representative workloads, not a synthetic loop issuing one identical request.

When latency rises, break down the stages. Is PostgreSQL scanning a broad candidate set? Is Redis returning cache misses? Is connection pooling exhausted? Is the embedding service slow? Is the generative model taking longer to decode its response? Distributed traces and query metrics should make that answer possible. Specialized AI-300 operations practices can extend this work into model and GenAIOps observability, but AI-200 owns the backend behavior that those metrics describe.

Test retrieval under a meaningful change

Build a small corpus of contract clauses with similar wording, two permission groups and one superseded amendment. Keep the authoritative metadata in PostgreSQL. Create a retrieval index and compare the results with and without selective metadata filters. Add a cache for one measured expensive operation, then update the source and determine when the correct answer becomes visible. If an old answer remains in Redis, document exactly which invalidation or versioning rule failed.

Repeat the test under concurrency. Watch database connections, memory pressure, query plans, cache hit rate and total response latency. Inject a database timeout and decide whether the application should retry within a bounded budget, degrade gracefully or return a clear temporary failure. A retrieval system earns trust by giving accurate, authorized answers as its data changes—not simply by producing the lowest latency in a benchmark. That is the architectural connection the AI-200 PostgreSQL and Redis objectives are designed to reveal.