Database Administration Flashcards

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?

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.

A vector database stores 1 billion 768-dimensional embeddings. Which storage optimization reduces disk usage while maintaining acceptable recall for approximate nearest neighbor search?

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.

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?

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.

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?

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.

Which database concept ensures that if an AI training job crashes mid-batch, the database returns to the last consistent state without manual intervention?

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.

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?

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.

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?

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.