Operational & Query-Planning Considerations Quiz

Q1. Which consistency level provides the most up-to-date (strongest) read guarantees at the potential cost of higher latency or reduced availability? Eventual consistency
Strong consistency
Causal consistency
Timeline consistency

Q2. In a cluster with a replication factor of 5, how many replicas must acknowledge a write for the operation to succeed at the “quorum” consistency level? 3
5
4
2

Q3. What is a potential effect of replication lag on queries using a secondary index on a read replica? It will have no effect; the query returns the latest data.
It may cause the database to error or crash.
It might return stale or out-of-date results due to the lagging index.
It triggers an automatic redirect of the query to the primary node.

Q4. Which scenario could cause a query planner to perform a full table scan despite an index being available on the table? The database server is under heavy CPU load at query time.
The query's filter matches most of the table's rows.
The table has multiple indexes defined on it.
The index is on an integer column instead of a text column.

Q5. Which of the following describes a “ghost table” approach to performing an online schema change? Create a new table with the desired schema, copy data gradually into it, then swap it in as the live table with minimal downtime.
Lock the original table and apply the schema changes directly, blocking writes until the operation completes.
Take the database offline, drop the original table, and recreate it with the new schema during a maintenance window.
Apply the schema change on a standby replica, then promote that replica to primary once the change is complete.

Q6. What does the “p99 latency” metric represent in database performance monitoring? The average latency of the slowest 1% of queries.
The time it takes to execute 99 queries in a row.
The 99th percentile query latency (the time within which 99% of queries finish).
The percentage of queries that complete within 99 milliseconds.

Q7. Which AWS service provides an interactive dashboard for database performance (showing load, top SQL queries, and wait events)? AWS X-Ray
Amazon CloudWatch
AWS CloudTrail
Amazon RDS Performance Insights

Q8. What is the purpose of DynamoDB’s “adaptive capacity” feature? To automatically boost throughput capacity for partitions that receive disproportionate traffic.
To replicate data across multiple regions for disaster recovery.
To automatically split a partition when its data volume exceeds a threshold.
To compress infrequently accessed data to reduce storage costs.

Q9. In a high-availability database cluster, which measure can help prevent a “split-brain” scenario during failover? Rely solely on updating DNS entries to redirect clients to the new primary.
Permit both the old primary and new primary to accept writes briefly to avoid downtime.
Assume the original primary will automatically shut down if it loses connectivity.
Use a quorum-based consensus mechanism to elect a single primary leader.

Q10. Which is an advantage of point-in-time recovery (PITR) compared to relying solely on periodic full backups? It eliminates the need to take any full backups of the database.
It allows restoring the database to a specific moment between full backups, minimizing potential data loss.
It significantly reduces the time required to restore a large database from scratch.
It requires no additional storage space beyond the normal backup files.

Q11. Which of the following is NOT typically a factor when planning database capacity? Storage IOPS (input/output operations per second) capacity
Maximum number of concurrent connections
Programming language used for the application
Available memory for the database working set

Q12. In a distributed database, what does “timeline consistency” ensure? All replicas apply every write in exactly the same order at the same time.
It is basically the same as eventual consistency, allowing some stale reads.
Any replica can serve reads with no guarantee of ordering between updates.
Once a client has seen a new write, it will never see an earlier state of the data afterward.

Q13. In the context of an online schema migration, what does the “dual-write backfill” strategy entail? Writing to both the old schema (or table) and the new schema concurrently while backfilling data to keep them in sync.
Using a two-phase commit to ensure each write is durably stored in multiple locations.
Running two separate database systems in parallel and merging the data after migration.
Duplicating all read queries against both the old and new schema to compare results for consistency.

Q14. What is the purpose of a database slow query log? To log every query that runs on the database for auditing.
To record queries that take longer than a defined threshold to execute, for performance analysis.
To automatically identify and optimize queries that are running slowly.
To keep a record of all errors encountered during query execution.

Q15. Amazon Aurora is designed for high availability. Which aspect of Aurora’s architecture enables a rapid failover when the primary instance fails? It keeps a fully synchronized standby database instance in a different region at all times.
It runs on a serverless platform that can restart instantly on failure.
It offloads writes to the application tier during failover to avoid downtime.
Its compute nodes share a distributed storage layer, allowing a new instance to quickly take over using the same data.

system-design