CAIC Database Administration 3 — Questions and Answers
Question 1: An AI system must maintain a consistent snapshot of training data while a bulk import job is running. Which PostgreSQL transaction isolation level provides this guarantee without blocking the import?
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ (Correct answer)
- SERIALIZABLE
Correct answer: REPEATABLE READ
REPEATABLE READ ensures the transaction sees a consistent snapshot taken at the start of the transaction, preventing non-repeatable reads without blocking concurrent writes.
Question 2: A vector database stores 1 billion 768-dimensional embeddings. Which storage optimization reduces disk usage while maintaining acceptable recall for approximate nearest neighbor search?
- Storing embeddings as VARCHAR strings
- Applying product quantization (PQ) to compress vectors (Correct answer)
- Creating a separate table for each 100-dimensional segment
- Encoding vectors as JSON arrays
Correct answer: Applying product quantization (PQ) to compress vectors
Product quantization divides vectors into sub-vectors and quantizes each, achieving 4x–32x compression with minimal recall loss compared to storing full-precision floats.
Question 3: An AI consultant discovers that a nightly ETL pipeline runs 47 sequential INSERT statements instead of a bulk load. What is the most impactful fix?
- Increase the database connection pool size
- Replace sequential INSERTs with a single COPY or bulk insert operation (Correct answer)
- Add an index on the destination table's primary key
- Wrap each INSERT in its own transaction
Correct answer: Replace sequential INSERTs with a single COPY or bulk insert operation
Bulk load operations like COPY bypass per-row overhead and transaction commits, loading data orders of magnitude faster than sequential individual INSERTs.
Question 4: A feature store uses a key-value cache (Redis) in front of PostgreSQL. After a model update changes feature semantics, which cache strategy prevents stale features from reaching the model?
- Increase Redis TTL to 24 hours
- Cache-aside pattern with write-through invalidation on feature update (Correct answer)
- Read-through caching with no expiry
- LRU eviction with a very large maxmemory setting
Correct answer: Cache-aside pattern with write-through invalidation on feature update
Write-through invalidation explicitly deletes or overwrites cache entries when the underlying feature data changes, ensuring the model always receives current values.
Question 5: Which database concept ensures that if an AI training job crashes mid-batch, the database returns to the last consistent state without manual intervention?
- Referential integrity constraints
- Atomicity enforced by transaction rollback (Correct answer)
- Check constraints on feature columns
- Foreign key cascades
Correct answer: Atomicity enforced by transaction rollback
Atomicity guarantees that a transaction is either fully committed or fully rolled back, so a crash mid-batch leaves the database at the last successful commit.
Question 6: A multi-tenant AI SaaS product stores customer datasets in the same PostgreSQL cluster. What is the recommended isolation approach that balances security and resource efficiency?
- One database cluster per tenant
- Separate schemas per tenant with RLS enforced at the application layer
- A single shared schema with a tenant_id column and RLS policies (Correct answer)
- Separate servers per tenant
Correct answer: A single shared schema with a tenant_id column and RLS policies
A single schema with tenant_id and Row-Level Security policies provides strong logical isolation while sharing infrastructure, avoiding the operational cost of per-tenant clusters.
Question 7: An AI consultant is evaluating whether to use a graph database vs. a relational database for storing knowledge graph data used by an LLM RAG pipeline. What is the primary advantage of a graph database for this use case?
- Graph databases support ACID transactions, unlike relational databases
- Graph databases traverse multi-hop relationships in constant time regardless of graph size
- Graph databases natively express and traverse entity relationships without expensive JOIN chains (Correct answer)
- Graph databases store data in columnar format for faster reads
Correct answer: Graph databases natively express and traverse entity relationships without expensive JOIN chains
Graph databases store relationships as first-class citizens, enabling efficient multi-hop traversals that would require many costly JOINs in a relational schema.
CAIC Database Administration 3
An AI system must maintain a consistent snapshot of training data while a bulk import job is running.
Which PostgreSQL transaction isolation level provides this guarantee without blocking the import?