Database Administration Flashcards
7 cards from real CAIC practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Database Administration flashcards as text
A company stores AI training datasets in a relational database. Which indexing strategy best accelerates similarity searches on high-dimensional embedding vectors?
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.
When an AI pipeline writes predictions continuously to a PostgreSQL table, which technique prevents table bloat caused by frequent UPDATEs triggering MVCC dead tuples?
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.
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?
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.
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?
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.
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?
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.
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?
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.
When designing a time-series database for storing AI model performance metrics, which partitioning strategy most effectively manages data retention and query performance?
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.