6.2 Schema Design, Data Types & Indexing Strategies

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

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:

1. B-tree (or partial) index on tenant_id
2. Vector index on embedding
3. 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.