6.2 Schema Design, Data Types & Indexing Strategies

Exam AI-200: Developing AI Cloud Solutions on Azure Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current Exam Facts 1.2 Audience Profile & Study Path

2 Chapter 1: Container Registry & App Service Hosting

1.1 ACR: Build, Store, Version & Manage Images 1.2 ACR Tasks: Build & Run Images 1.3 Deploy Containers to Azure App Service

3 Chapter 2: Azure Container Apps & KEDA Scaling

2.1 Azure Container Apps: Environments, Config & Revisions 2.2 KEDA Event-Driven Scaling in Container Apps

4 Chapter 3: AKS Manifests & Container Troubleshooting

3.1 Deploy & Manage Apps on AKS with Manifests 3.2 Monitor & Troubleshoot AKS and Container Apps

5 Chapter 4: Cosmos DB for NoSQL Fundamentals

4.1 Cosmos DB for NoSQL: SDK Connect & Queries 4.2 Optimize RUs: Indexing Policies & Consistency Levels

6 Chapter 5: Cosmos DB Vectors & Change Feed

5.1 Embeddings & Vector Similarity Search in Cosmos DB 5.2 Change Feed Processor for New/Updated Items

7 Chapter 6: Azure PostgreSQL Foundations for AI

6.1 Azure Database for PostgreSQL: Connect & Query with SDKs 6.2 Schema Design, Data Types & Indexing Strategies 6.3 pgvector Indexing: Latency & Compute Overhead

8 Chapter 7: PostgreSQL Vector Workloads & RAG

7.1 Configure Compute, Memory & Storage for Vector Workloads 7.2 Vector Search, Semantic Retrieval & RAG with Metadata Filters 7.3 Connection Optimization for Throughput & Latency

9 Chapter 8: Azure Managed Redis for AI Solutions

8.1 Azure Managed Redis: Caching, Expiration & Invalidation 8.2 Redis Vector Indexing for Similarity Search

10 Chapter 9: Service Bus & Event Grid for AI Pipelines

9.1 Service Bus Queues & Message Processing 9.2 Topics, Subscriptions & Dead-Letter Queues 9.3 Event Grid: Custom Events & Filters 9.4 Event Grid Retries & Event-Driven Workflows

11 Chapter 10: Azure Functions for Serverless AI Back Ends

10.1 Azure Functions: Triggers & Bindings 10.2 Build Serverless APIs with Functions 10.3 Configure & Deploy Function Apps

12 Chapter 11: Key Vault & App Configuration

11.1 Azure Key Vault: Secret Storage & Retrieval 11.2 Key Vault Secret Rotation Patterns 11.3 Azure App Configuration for App Settings

13 Chapter 12: OpenTelemetry & KQL Observability

12.1 Distributed Tracing with OpenTelemetry SDKs 12.2 Correlating Traces into Azure Monitoring 12.3 Write KQL Queries to Analyze Logs 12.4 KQL for Metrics & Troubleshooting Scenarios

6.2 Schema Design, Data Types & Indexing Strategies

Key Takeaways Explain This Topic AI

Model AI content as relational tables (documents, chunks, tenants) plus a fixed-dimension vector column from pgvector

Choose PostgreSQL data types deliberately: uuid/text for IDs, jsonb for sparse metadata, vector(n) for embeddings, timestamptz for audit fields

B-tree indexes accelerate equality and range filters; GIN indexes accelerate jsonb and full-text; pgvector indexes accelerate similarity search

Normalize tenant and document metadata; store embeddings at chunk granularity for RAG retrieval quality

Match index type to access pattern—do not create an HNSW index hoping it will speed up ordinary WHERE tenant_id = … filters

Last updated: July 2026

6.2 Schema Design, Data Types & Indexing Strategies

Quick Answer: Design tables for documents and chunks, store embeddings in a vector(n) column, keep filters in typed columns or jsonb , then index with B-tree (filters), GIN (jsonb/full-text), and pgvector indexes (similarity). Schema quality determines whether later HNSW/IVFFlat tuning can meet latency goals.

Connecting with an SDK is only half the job. The AI-200 domain expects you to model schemas and choose indexes so retrieval stays fast as data grows.

Enable pgvector first

On Flexible Server, allow and create the extension in the application database: CREATE EXTENSION IF NOT EXISTS vector;

Confirm the Azure portal Server parameters allow the vector extension. Without it, the vector type and distance operators ( <=> , <-> , <#> ) are unavailable.

• Recommended table layout for RAG
• A practical schema separates concerns: CREATE TABLE tenants (
• tenant_id uuid PRIMARY KEY,
• name text NOT NULL
• );

• CREATE TABLE documents (
• document_id uuid PRIMARY KEY,
• tenant_id uuid NOT NULL REFERENCES tenants(tenant_id),
• title text NOT NULL,
• source_uri text,
• metadata jsonb DEFAULT '{}'::jsonb,
• created_at timestamptz NOT NULL DEFAULT now()
• );

• CREATE TABLE chunks (
• chunk_id uuid PRIMARY KEY,
• document_id uuid NOT NULL REFERENCES documents(document_id),
• tenant_id uuid NOT NULL,
• chunk_index int NOT NULL,
• content text NOT NULL,
• embedding vector(1536) NOT NULL,
• token_count int,
• UNIQUE (document_id, chunk_index)
• );

Why this shape works for AI:

Chunk-level embeddings match how RAG retrieves passages, not whole files.

Denormalized tenant_id on chunks lets you filter multi-tenant searches without joining every time.

metadata jsonb holds optional fields (language, product SKU) without constant ALTER TABLE churn.

Fixed vector(1536) matches a common Azure OpenAI embedding dimension—always align n with the model you use.

Choosing data types

• Need Prefer Avoid
• Stable primary keys uuid Sequential int if you shard or merge tenants later
• Human-readable text text varchar(n) unless you have a hard length rule
• Sparse attributes jsonb Dozens of nullable columns you rarely query
• Embeddings vector(n) from pgvector Storing floats in float8[] without operators
• Money / scores numeric or real as appropriate Mixing types in the same filter column
• Event time timestamptz timestamp without time zone for global apps
• Booleans / flags boolean Encoding true/false as text

Dimension discipline matters: if you change embedding models (for example 1536 → 3072), you must migrate the column type and rebuild vector indexes. Plan versioning ( embedding_model text column) so old and new vectors do not collide silently.

Indexing strategies by access pattern

PostgreSQL offers several index families; AI apps usually combine them.

B-tree

B-tree is the default. Use it for:

• Primary keys and foreign keys
• Equality filters: tenant_id = $1
• Ranges: created_at >= $1
• Sorting that matches an ORDER BY on scalar columns
• Example: CREATE INDEX chunks_tenant_idx ON chunks (tenant_id);
• CREATE INDEX documents_created_idx ON documents (created_at DESC);

B-tree does not accelerate cosine/IP/L2 similarity over embeddings. Creating a B-tree on a vector column is the wrong tool for nearest-neighbor search.

GIN

GIN (Generalized Inverted Index) shines for:

• jsonb containment ( metadata @> '{"lang":"en"}' )
• Full-text search ( to_tsvector / @@ )
• Arrays
• Example: CREATE INDEX documents_metadata_gin ON documents USING gin (metadata);
• CREATE INDEX chunks_content_fts ON chunks USING gin (to_tsvector('english', content));

Hybrid retrieval often filters with GIN/B-tree predicates, then ranks with vector distance—or blends lexical and vector scores in the application.

pgvector indexes

For approximate nearest neighbor (ANN) search, pgvector provides HNSW and IVFFlat index types (detailed in section 6.3). Conceptually: CREATE INDEX chunks_embedding_hnsw

ON chunks USING hnsw (embedding vector_cosine_ops);

Pick the operator class to match your distance metric ( vector_cosine_ops , vector_l2_ops , vector_ip_ops ). The metric in SQL must match how you normalized embeddings at write time.

• Composite filters + vectors
• A frequent production query: SELECT chunk_id, content
• FROM chunks
• WHERE tenant_id = $1
• ORDER BY embedding <=> $2
• LIMIT 8;

For this to stay fast:

• B-tree (or partial) index on tenant_id
• Vector index on embedding
• Enough selectivity on the filter so the planner does not scan the entire ANN candidate set across tenants
• Partial indexes help when one tenant or soft-delete flag dominates: CREATE INDEX chunks_active_tenant_idx
• ON chunks (tenant_id)
• WHERE deleted_at IS NULL;
• Modeling pitfalls to avoid

One giant documents table with a single embedding for multi-page PDFs—retrieval becomes coarse and noisy.

Unbounded jsonb used for everything you should have typed (tenant IDs, timestamps)—you lose constraints and efficient B-tree filters.

Mismatched vector dimensions across rows—Postgres rejects inconsistent vector(n) lengths in a typed column, but application bugs show up at insert time.

Indexing everything —each index slows writes and consumes memory; index for measured query patterns.

Design checklist for the exam

When a scenario asks you to “model schemas and implement indexing strategies,” walk this order: entities → data types → filter indexes (B-tree/GIN) → vector column → pgvector ANN index → validate with EXPLAIN ANALYZE. That sequence keeps Azure PostgreSQL both correct and performant for AI solutions.