AI-200 practice: Model schemas and implement indexing strategies, including designing tables and choosing appropriate data types

Question 1

You are designing a table in Azure Database for PostgreSQL to store product embeddings for similarity search. Which extension must be enabled and which column type used?

Accepted Answer

CREATE EXTENSION vector; column vector(n)

Answer

CREATE EXTENSION postgis; column geometry

Answer

CREATE EXTENSION pg_trgm; column text type

Answer

No extension; use a plain numeric array

Question 2

When modeling a schema in Azure Database for PostgreSQL for a document-chunking pipeline, which data type is most appropriate for storing large blocks of extracted text alongside metadata columns?

Accepted Answer

text/varchar for chunks; typed metadata columns

Answer

Single JSONB blob merging text and metadata

Answer

bytea binary type for searchable chunk text

Answer

vector column used to store raw chunk text

Question 3

You are designing a schema for a vector database in Azure Database for PostgreSQL. Which data type provided by the pgvector extension should you use to store embeddings?

Accepted Answer

vector

Answer

float8

Answer

bytea

Answer

jsonb

Question 4

You switch to an embedding model that outputs 3072-dimension vectors and store them in a `vector(3072)` column. `CREATE INDEX ... USING hnsw (embedding vector_cosine_ops)` fails because the column has more than 2000 dimensions. You want to keep all 3072 dimensions and still use an HNSW index. What should you do?

Accepted Answer

Store the embeddings as halfvec(3072), or index the expression embedding::halfvec(3072) with halfvec_cosine_ops

Answer

Raise maintenance_work_mem so the index can hold more dimensions

Answer

Use an IVFFlat index instead, which has no dimension limit

Answer

Store the embeddings in a float8[] column and index it with GIN

Question 5

RAG queries always filter chunks by `tenant_id` and a `created_at` range. Each source system also attaches a different set of optional attributes (author, page, department, and so on), which are sometimes used for containment filters. Which two schema decisions fit best? Select two.

Accepted Answer

Store tenant_id and created_at as typed columns (uuid, timestamptz) with B-tree indexes

Accepted Answer

Store the variable attributes in a jsonb column with a GIN index

Answer

Put every field, including tenant_id and created_at, inside one JSONB document

Answer

Create a separate table for every source system's attribute set

Answer

Store the optional attributes as one comma-separated text column

Question 6

Each uploaded document is split into 50–2,000 chunks, and each chunk has its own embedding. Deleting a document must remove its chunks, and queries rank individual chunks. Which schema fits best?

Accepted Answer

A documents table plus a chunks table with document_id REFERENCES documents ON DELETE CASCADE, chunk_no, content text, and embedding vector(1536)

Answer

A documents table with a single vector(1536)[] array column that holds every chunk embedding

Answer

A separate table created for each document, named after the document ID

Answer

One wide documents table with columns chunk_1 to chunk_2000

Question 7

You're designing a `conversations` table for a chat app. IDs are generated by several services in different regions, and timestamps from users in many time zones must compare correctly. Which two type choices are best? Select two.

Accepted Answer

timestamptz for created_at

Accepted Answer

uuid for the conversation ID

Answer

text in 'DD/MM/YYYY HH:MI' format for created_at

Answer

varchar(36) for the conversation ID

Answer

timestamp without time zone, with each service's local time

Question 8

You're moving from `text-embedding-ada-002` to `text-embedding-3-small`, and both produce 1536-dimension vectors. Re-embedding 8 million chunks takes days, and search must keep working throughout. How should you change the schema?

Accepted Answer

Add a new column (for example embedding_v2 vector(1536)), backfill and index it, switch queries to it, then drop the old column

Answer

Write the new embeddings into the existing column as they're produced, because both models have 1536 dimensions

Answer

Run ALTER TABLE chunks ALTER COLUMN embedding TYPE vector(1536) USING embedding to convert the vectors in place

Answer

Store the old and new vectors added together in one column so both models stay searchable

Question 9

A hybrid retrieval pipeline also produces learned sparse embeddings: vectors with a 30,000-term vocabulary in which typically fewer than 200 values are non-zero. Which pgvector type should store them?

Accepted Answer

sparsevec(30000)

Answer

vector(30000)

Answer

halfvec(30000)

Answer

bit(30000)

Question 10

You're adding keyword search next to vector search on the `content` column of `chunks`. The keyword representation must always match `content`, even when rows are updated by other services that don't know about it. What should you add?

Accepted Answer

A content_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED column with a GIN index

Answer

A trigger-free tsvector column that the application fills in only when it inserts rows

Answer

A B-tree index on content, used with LIKE '%term%' queries

Answer

A second vector column that holds embeddings of keywords

11 questions