▸case-08 We are creating a standard SQL user table that will hold around 5,000 records. Write a standard SQL CREATE TABLE query for a `users` table with `id` as primary key, `email`, and `created_at`. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-19 We want to stress-test our PostgreSQL instance before migrating to a larger cloud instance to simulate 2,000 concurrent OLTP transactions per second. What standard command-line benchmarking utility is built for PostgreSQL load testing? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-20 Our DynamoDB workload receives steady, predictable web traffic 24/7 with minimal traffic spikes. We are currently using On-Demand capacity mode and receiving high monthly bills. What capacity mode switch will lower monthly AWS costs? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-17 We need to delete or archive data older than 3 years from a 500 million row PostgreSQL audit table every month. Running `DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL '3 years'` causes lock escalation and WAL generation spikes. What schema partitioning design simplifies this cleanup? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-11 Our SQL Server database experiences frequent deadlocks between heavy batch update processes and concurrent user READ queries under READ COMMITTED isolation level. What isolation level or database setting option should we enable to prevent readers from blocking writers? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-04 We are adding Redis to our stack to cache user profiles. Should we implement write-through caching where the application writes to Redis and the database synchronously, or cache-aside where the database is written first and the cache key is invalidated? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-09 We are building an order processing system on Amazon DynamoDB. A developer suggested using the customer creation timestamp as the Partition Key (HASH) so orders are sorted naturally. Is this an optimal partition key choice? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-05 We store arbitrary user metadata in a JSONB column named `attributes` in PostgreSQL. Queries filtering by `attributes->>'department'` are doing sequential table scans. What index type should we create to optimize queries on specific JSONB key-value paths? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-16 Our relational database handles heavy full-text search queries using `%LIKE%` wildcards, causing CPU usage to hit 100%. What strategy offloads these search workloads while keeping data synchronized? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-06 In MongoDB, our aggregation pipeline performs a `$lookup` join across two collections with millions of documents, followed by `$match` and `$sort`. The query is timing out. What structural changes should we make to the pipeline order? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-07 We have a reference lookup table containing 50 static country code records. A developer proposed setting up Redis caching, database sharding, and read replicas for this table. Is this infrastructure necessary? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-10 We have a 2 TB PostgreSQL event log table where timestamped records are continuously appended and older data is never updated. B-tree indexes on `created_at` are taking up 150 GB of RAM. What index strategy should we use to drastically lower RAM usage while maintaining range scan performance? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-18 In a microservices architecture using CQRS, read models are suffering from high latency because read-side queries compute aggregations on raw domain events on the fly. How should we optimize the read database design? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-21 We query 50 billion row log datasets for aggregate daily counts in PostgreSQL, taking 45 seconds per query. Developers want to move to ClickHouse. What key structural feature of ClickHouse makes analytical aggregations faster than traditional row-based engines? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-12 We run a daily batch job that updates a single row in a system settings table in PostgreSQL. A teammate suggests migrating this settings table to Apache Cassandra to improve write performance. Is this migration advisable? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-02 Our PostgreSQL 15 analytics endpoint running a 5-way JOIN across tables with tens of millions of rows takes 14 seconds. Developers suggest adding B-tree indexes on every foreign key column immediately to fix it. How should we approach diagnosing and optimizing this query? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-14 Our Aurora PostgreSQL Serverless v2 database experiences connection spikes during flash sales, causing high memory usage due to backend processes spawning 500 direct database connections. How should we manage connections at scale? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-03 Our GraphQL service causes severe database load because fetching a list of 100 posts executes 101 distinct SQL SELECT queries to fetch author details. What is the standard pattern to resolve this without manually writing raw complex SQL for every resolver? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-01 We need to alter the schema of a multi-terabyte production table in MySQL without incurring service downtime or blocking active writes. Please supply a phase-by-phase migration blueprint that includes dual-writing patterns, background data backfilling, validation checks, and a safe rollback mechanism if issues arise. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-13 We ingest sensor telemetry metrics into TimescaleDB on PostgreSQL. Our queries aggregate metrics over 1-hour time windows. Should we partition hypertables primarily by sensor UUID or by time intervals? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-15 A high-churn PostgreSQL table's B-tree index has bloated to twice the size of the table data itself due to frequent UPDATE queries. How can we rebuild this index without locking writes on the table in production? | fail→pass | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-22 We deployed a dedicated MySQL 8.0 server with 64 GB of RAM. The default `innodb_buffer_pool_size` is causing severe disk I/O bottlenecks under load because active working data isn't fitting in memory. What recommended value should `innodb_buffer_pool_size` be set to on a dedicated database server? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-23 We run a query to fetch the latest 3 orders for each customer across 10 million customers. A subquery with correlated SELECTs is running for minutes. What SQL syntax feature optimizes this top-N per group pattern? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |