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 healthcare AI application must satisfy HIPAA requirements for database audit trails. Which database feature natively supports this requirement?
Answer: Database audit logging capturing all SELECT, INSERT, UPDATE, and DELETE operations per user
Audit logging records who accessed or modified data and when, providing the access trail required by HIPAA's Technical Safeguard standards.
When replicating a PostgreSQL database that stores AI model weights (large BYTEA objects) to a standby for disaster recovery, which replication method minimizes network bandwidth?
Answer: Streaming physical replication with WAL compression enabled
WAL compression reduces the bytes transferred during streaming replication by compressing write-ahead log records before sending them to the standby.
An AI inference service experiences connection storms when 500 model servers simultaneously query PostgreSQL. What infrastructure component resolves this without changing application code?
Answer: Deploying PgBouncer in transaction-mode pooling in front of PostgreSQL
PgBouncer in transaction-mode multiplexes thousands of application connections onto a small pool of real PostgreSQL connections, eliminating connection storms.
A data engineer notices that a JSONB column storing AI model metadata is frequently queried with the filter metadata->>'model_version' = '3.2'. How should this be optimized?
Answer: Create a functional index on (metadata->>'model_version')
A functional index on the specific JSONB expression allows the query planner to use an index scan for that extraction, far more selective than a full GIN index on all keys.
An AI platform uses database sequences to assign unique IDs to training runs. What problem arises when using a standard sequence across 8 parallel worker nodes?
Answer: Sequence fetching becomes a serialization bottleneck, reducing throughput
Centralizing ID generation through a single sequence forces all workers to serialize on sequence fetch calls, creating a throughput ceiling at high concurrency.
Which data modeling anti-pattern is most harmful when storing ML experiment results in a relational database?
Answer: Storing all hyperparameters as a single serialized JSON blob with no indexed sub-fields
Serializing all hyperparameters into an opaque blob prevents efficient filtering, indexing, and comparison across experiments, making hyperparameter search queries extremely slow.
A company wants to implement Change Data Capture (CDC) from PostgreSQL to feed real-time feature updates into an AI feature store. Which PostgreSQL capability enables this?
Answer: Logical replication slots with WAL decoding (e.g., pgoutput or Debezium)
Logical replication slots expose decoded WAL events as structured row-level changes that CDC tools like Debezium can consume and forward to downstream feature stores.