CAIC Database Administration 2 — Questions and Answers
Question 1: A company stores AI training datasets in a relational database. Which indexing strategy best accelerates similarity searches on high-dimensional embedding vectors?
- B-tree index on the embedding column
- Approximate Nearest Neighbor (ANN) index such as HNSW (Correct answer)
- Full-text search index on serialized vectors
- Composite primary key across all vector dimensions
Correct answer: Approximate Nearest Neighbor (ANN) index such as HNSW
ANN indexes like HNSW (Hierarchical Navigable Small World) are purpose-built for high-dimensional vector similarity searches, offering sub-linear query time.
Question 2: When an AI pipeline writes predictions continuously to a PostgreSQL table, which technique prevents table bloat caused by frequent UPDATEs triggering MVCC dead tuples?
- Increasing shared_buffers
- Running VACUUM ANALYZE on a frequent schedule (Correct answer)
- Switching to a TEMPORARY TABLE
- Disabling WAL logging for that table
Correct answer: Running VACUUM ANALYZE on a frequent schedule
Regular VACUUM ANALYZE reclaims space from dead tuples created by MVCC and updates planner statistics, preventing bloat in high-update workloads.
Question 3: An AI consultant needs to enforce row-level access control so that each data science team only queries their own labeled datasets. Which database feature provides the most fine-grained solution?
- View-based access control
- Row-Level Security (RLS) policies (Correct answer)
- Schema-level GRANT statements
- Column encryption with role-based keys
Correct answer: Row-Level Security (RLS) policies
Row-Level Security policies attach access predicates directly to tables, transparently filtering rows based on the executing user's role or session variables.
Question 4: A data lakehouse uses Apache Parquet files as the storage layer for ML features. What is the primary advantage of Parquet's columnar format over row-oriented storage for analytical AI workloads?
- Lower write latency for transactional inserts
- Efficient compression and selective column reads, reducing I/O (Correct answer)
- Native support for ACID transactions without extra tooling
- Built-in replication across cloud regions
Correct answer: Efficient compression and selective column reads, reducing I/O
Columnar storage allows queries to read only the required columns and compresses homogeneous data more effectively, drastically reducing I/O for analytical scans.
Question 5: Which database normalization issue arises when an AI model's feature store table contains columns that depend on a non-key attribute rather than the full primary key?
- First Normal Form (1NF) violation
- Partial dependency violating Second Normal Form (2NF) (Correct answer)
- Transitive dependency violating Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF) violation
Correct answer: Partial dependency violating Second Normal Form (2NF)
A partial dependency occurs when a non-key column depends on only part of a composite primary key, violating 2NF.
Question 6: A production AI system requires that database schema changes never block queries on a 50-million-row inference-log table. Which migration approach satisfies this requirement?
- ALTER TABLE with an exclusive lock during off-peak hours
- Online DDL or concurrent index builds that avoid table locks (Correct answer)
- Dropping and recreating the table with the new schema
- Exporting to CSV, modifying, and reimporting
Correct answer: Online DDL or concurrent index builds that avoid table locks
Online DDL operations (e.g., PostgreSQL's CREATE INDEX CONCURRENTLY) allow schema changes to proceed without holding locks that block reads or writes.
Question 7: When designing a time-series database for storing AI model performance metrics, which partitioning strategy most effectively manages data retention and query performance?
- Hash partitioning on the model_id column
- Range partitioning on the timestamp column with automated partition pruning (Correct answer)
- List partitioning on the metric_name column
- Round-robin partitioning across all available nodes
Correct answer: Range partitioning on the timestamp column with automated partition pruning
Range partitioning on timestamps allows the query planner to prune irrelevant partitions and simplifies dropping old partitions for data retention policies.
CAIC Database Administration 2
A company stores AI training datasets in a relational database.
Which indexing strategy best accelerates similarity searches on high-dimensional embedding vectors?