7.1 Configure Compute, Memory & Storage for Vector Workloads

7.1 Configure Compute, Memory & Storage for Vector Workloads

Quick Answer: For production RAG and embedding search on Azure Database for PostgreSQL Flexible Server, prefer a Memory Optimized compute tier with enough RAM to keep vector indexes warm, set storage to Premium SSD with headroom for index builds, and plan PostgreSQL memory parameters (shared_buffers, work_mem, maintenance_work_mem) conceptually so caches and sorts do not thrash under concurrent similarity queries.

When you store embeddings for semantic retrieval, the database stops behaving like a typical OLTP system. Rows are wider, indexes are larger, and queries scan neighborhoods of high-dimensional vectors instead of looking up a few keys. The AI-200 exam expects you to configure compute, memory, and storage resources to support vector workloads—not just enable the vector extension and hope default SKUs keep up.

Why Vector Workloads Stress Resources Differently

An embedding is typically hundreds or thousands of floating-point dimensions. A million 1,536-dimension vectors already consumes multiple gigabytes of raw table data before indexes. Approximate nearest neighbor (ANN) structures such as HNSW (Hierarchical Navigable Small World) and IVFFlat add graph or list metadata that multiplies storage and memory demand.

During ingest, the server must write embedding rows, update secondary metadata indexes, and often rebuild or expand the vector index. During query, each similarity search may touch many index pages and heap pages to return the top-k neighbors. Concurrent RAG applications multiply that cost across sessions. Undersized compute shows up as high CPU wait; undersized memory shows up as cache misses and disk thrashing; undersized storage IOPS show up as long CREATE INDEX times and latency spikes when the working set spills.

Azure Database for PostgreSQL Flexible Server SKU Choices

Azure Database for PostgreSQL Flexible Server offers compute tiers that map cleanly to embedding scenarios:

For exam and architecture purposes, treat Memory Optimized as the default production recommendation whenever the vector index must stay largely in memory for predictable p95 latency. Burstable SKUs can pass a lab demo and fail under a realistic document corpus because CPU credits deplete during bulk embedding load or index creation.

Sizing for Embedding Workloads

SKU sizing for embedding workloads is a capacity plan, not a guess:

1. Estimate raw vector storage — rows × dimensions × 4 bytes (float4) plus row overhead and TOAST behavior for related text columns.
2. Add ANN index overhead — HNSW graphs are commonly multiple times the raw vector size depending on m and ef_construction; IVFFlat lists also add non-trivial space.
3. Add working set for hot queries — concurrent top-k searches need cached index pages plus filtered metadata pages.
4. Size vCores for concurrency — bulk COPY/insert of embeddings and parallel search sessions compete for CPU; ingestion jobs often need a temporary scale-up.
5. Leave headroom — plan for corpus growth, re-embedding after model upgrades, and index rebuild windows.

A practical rule used in Azure AI solutions is to size RAM so the active vector index plus frequently filtered metadata can remain cached under expected concurrency. If the working set cannot fit, latency becomes I/O-bound regardless of how clever the distance operator is.

Memory Parameters at a Conceptual Level

You do not need to memorize every PostgreSQL GUC for the exam, but you must understand how the major memory knobs interact with vector work.

shared_buffers

shared_buffers is PostgreSQL’s primary page cache for table and index blocks. For vector workloads, keeping hot HNSW/IVFFlat pages and embedding heap pages in shared_buffers reduces repeated reads from Azure Premium SSD. Conceptually, architects often target a substantial fraction of instance RAM for shared_buffers (commonly discussed around roughly a quarter of RAM on dedicated servers, with Azure managed defaults and guidance applied per SKU). The exam point is not a magic number—it is that vector indexes are cache-sensitive, so memory-optimized SKUs plus adequate shared_buffers planning beat CPU-only scale-ups when the bottleneck is random page reads.

work_mem

work_mem bounds memory for per-operation sorts, hashes, and some query node work. Similarity queries that combine metadata filters, joins, and ordering can spill to disk temporary files if work_mem is too small under concurrency. Raising work_mem helps individual queries but multiplies across parallel sessions, so you size it relative to concurrent RAG traffic rather than maximizing a single session.

maintenance_work_mem

maintenance_work_mem matters when building or reindexing vector indexes. CREATE INDEX for HNSW or IVFFlat is a maintenance operation: more maintenance memory generally shortens build time and reduces temp I/O. Teams often temporarily increase maintenance memory (and sometimes scale compute) for index builds, then return to steady-state settings.

Storage: Capacity, IOPS, and Index Builds

Azure Database for PostgreSQL Flexible Server uses managed storage (Premium SSD class characteristics in production designs). Vector workloads need:

• Capacity headroom for embeddings, text chunks, metadata, WAL growth during bulk load, and index copies during rebuild.
• IOPS/throughput headroom for random reads during ANN search and sequential/random writes during ingest and CREATE INDEX.
• Growth planning when documents are re-chunked or re-embedded with a higher-dimension model.

Index creation is often the storage stress test. An HNSW build can saturate disk before it saturates CPU. If storage is undersized on IOPS, builds stretch into hours and online query latency degrades while the build competes for the same disks. Production designs therefore provision storage for the build peak, not only the steady-state query average.

Compute Versus Inference: Separate Bottlenecks

A common architecture mistake is to size the PostgreSQL SKU as if it were the embedding model. In Azure AI solutions:

• Model inference (Azure OpenAI or other embedding endpoints) consumes tokens/throughput quotas.
• PostgreSQL consumes vCores, memory, and IOPS to store vectors and run ANN search.

You can have plenty of embedding API capacity and still miss RAG latency SLOs if the database cannot serve filtered vector search. Conversely, an oversized database cannot fix an embedding pipeline that is rate-limited. Exam scenarios often ask which resource to scale when symptoms point to DB cache misses, index build failures, or connection/CPU saturation on Flexible Server—not the model deployment.

Operational Checklist Before Go-Live

Use this checklist when validating compute, memory, and storage for vector workloads:

1. Confirm the Flexible Server tier (prefer Memory Optimized for large ANN indexes).
2. Estimate embedding + index footprint and leave growth margin.
3. Validate Premium SSD size/IOPS against a representative index build.
4. Review conceptual memory settings: shared_buffers for cache residency, work_mem for concurrent filtered queries, maintenance_work_mem for builds.
5. Load-test concurrent top-k searches with realistic metadata filters.
6. Monitor CPU, memory, storage throughput, temp files, and index build duration.
7. Plan scale-up windows for re-embedding and index rebuilds after model changes.

Exam Scenario Pattern

Expect items that describe slow semantic search after a corpus grows, long CREATE INDEX operations, or memory pressure alerts on General Purpose / Burstable SKUs. The corrective action is usually move to Memory Optimized compute, increase storage IOPS/capacity, and align memory parameters with vector cache and maintenance needs—not switching distance metrics alone.

Master this section by connecting symptoms to resources: cache misses → memory/SKU; build timeouts → storage IOPS/maintenance_work_mem/temporary scale-up; steady CPU pegging under concurrent RAG → more vCores or reduced fan-out; Burstable credit exhaustion → leave Burstable for non-production.