▸case-01 Our Django application is hitting severe latency spikes on our PostgreSQL instance during high-traffic events, and we suspect inefficient JOINs along with N+1 query patterns across several API endpoints. Could you help us systematically profile the database traffic, pinpoint the main bottlenecks, develop a refactoring strategy for queries and indexing, and outline a caching and monitoring setup to prevent regressions? | pass→pass | 25,033 | 19,152 | -23% | 1 | 1 | 0% | 4,226 | 5,315 | +26% | 0 | 0 | — |
▸case-02 We are planning to scale our MySQL database to support a 10x surge in write-heavy workloads for an e-commerce platform. Can you guide us through analyzing current execution plans, identifying schema and index bottlenecks, designing a horizontal scaling or sharding approach, and setting up benchmarks to validate the throughput improvements? | fail→pass | 37,142 | 26,673 | -28% | 1 | 1 | 0% | 6,204 | 5,269 | -15% | 0 | 0 | — |
▸case-03 Our GraphQL API is experiencing delayed response times caused by unoptimized ORM data fetching on nested fields. Please walk us through analyzing query execution behavior, diagnosing the core performance bottlenecks, outlining a DataLoader batching and caching architecture, and defining testing procedures to verify performance gains. | pass→pass | 22,946 | 23,111 | +1% | 1 | 1 | 0% | 3,874 | 6,024 | +55% | 0 | 0 | — |
▸case-04 We need to create PostgreSQL user roles and GRANT permissions so that our reporting team only has read access to the analytics schema while preventing access to PII tables. Please write the SQL security scripts for this permission setup. | pass→pass | 15,677 | 12,819 | -18% | 1 | 1 | 0% | 2,741 | 4,001 | +46% | 0 | 0 | — |
▸case-05 Write a simple Python script using pandas and psycopg2 to parse a daily inventory CSV file and insert the rows into a PostgreSQL staging table. | pass→fail | 10,681 | 8,822 | -17% | 1 | 1 | 0% | 2,110 | 3,523 | +67% | 0 | 0 | — |
▸case-06 Draft an operational procedure for backing up our MySQL database daily to AWS S3 and verifying backup checksums for SOC2 compliance auditing. | pass→pass | 21,570 | 21,741 | +1% | 1 | 1 | 0% | 3,952 | 5,703 | +44% | 0 | 0 | — |
▸case-07 We store unstructured event payload logs in a PostgreSQL JSONB column called `payload`. Our queries frequently filter by specific key-value pairs deep inside the JSON document using the `@>` containment operator. The queries currently trigger a full sequential table scan. Which index type should we create on the JSONB column? | pass→pass | 8,118 | 8,557 | +5% | 1 | 1 | 0% | 1,459 | 3,505 | +140% | 0 | 0 | — |
▸case-08 We ran `EXPLAIN ANALYZE` on a query scanning a 50-million row `orders` table in PostgreSQL. The output shows `Seq Scan on orders` taking 4200ms with `Filter: (status = 'PENDING'::text)`. The `PENDING` status accounts for only 0.5% of total rows in the table. What specific indexing optimization minimizes storage overhead while targeting this query? | pass→pass | 8,996 | 9,316 | +4% | 1 | 1 | 0% | 1,638 | 3,576 | +118% | 0 | 0 | — |
▸case-09 In our FastAPI application using SQLAlchemy, accessing `user.orders` inside a loop over 500 users executes 501 separate SQL queries. A developer proposed adding an index to `orders.user_id` to fix this. Will an index solve the N+1 latency, and what is the proper application ORM solution? | pass→pass | 11,542 | 10,157 | -12% | 1 | 1 | 0% | 2,020 | 3,692 | +83% | 0 | 0 | — |
▸case-10 Our web service queries product details from MySQL on every page request. Product details change infrequently, but when updated by admins, immediate consistency on the next read is required. We want to add Redis caching. Which caching pattern ensures updated items are immediately visible while shielding DB reads? | fail→fail | 12,498 | 11,746 | -6% | 1 | 1 | 0% | 2,025 | 3,654 | +80% | 0 | 0 | — |
▸case-11 We have a MySQL table `user_events` and a frequent query: `SELECT * FROM user_events WHERE tenant_id = ? AND event_type = ? AND created_at >= ? ORDER BY created_at DESC`. `tenant_id` has 100 unique values, `event_type` has 5 unique values, and `created_at` is a timestamp. What is the optimal column ordering for a composite index on these fields? | pass→pass | 11,031 | 8,863 | -20% | 1 | 1 | 0% | 1,852 | 3,321 | +79% | 0 | 0 | — |
▸case-12 We have a 200GB PostgreSQL table storing append-only sensor telemetry sorted strictly by timestamp. Standard B-Tree indexes on `created_at` consume 30GB of RAM and degrade write throughput. What lightweight index type in PostgreSQL is designed for naturally ordered, large append-only datasets? | pass→pass | 8,093 | 8,995 | +11% | 1 | 1 | 0% | 1,482 | 3,301 | +123% | 0 | 0 | — |
▸case-17 An engineer is fetching filtered user records in DynamoDB using `docClient.scan()` with a `FilterExpression`. Latency increases as table size grows, and consumed read capacity units (RCUs) are spiking. Why does this happen and what operation replaces Scan? | pass→pass | 10,925 | 11,133 | +2% | 1 | 1 | 0% | 1,874 | 3,736 | +99% | 0 | 0 | — |
▸case-13 In MongoDB, our aggregation query performs a `$match` stage, a `$lookup` stage, and then another `$match` stage on fields from the joined collection. It takes 12 seconds to run. How should we restructure the pipeline order to optimize performance? | pass→pass | 14,590 | 10,295 | -29% | 1 | 1 | 0% | 2,470 | 3,535 | +43% | 0 | 0 | — |
▸case-14 High update and delete volumes on a PostgreSQL 14 table have caused severe dead tuple table bloat. Running `VACUUM FULL` will take exclusive write locks for hours, which production cannot tolerate. What utility or strategy rebuilds the table bloat concurrently without long locks? | pass→pass | 12,991 | 11,894 | -8% | 1 | 1 | 0% | 2,158 | 3,664 | +70% | 0 | 0 | — |
▸case-15 Our serverless Lambda functions connect directly to PostgreSQL. During peak traffic spikes with 1,000 concurrent Lambda executions, the database fails with 'fatal: sorry, too many clients already'. Should we increase PostgreSQL's `max_connections` parameter to 2000, or implement connection pooling? | pass→pass | 13,404 | 13,976 | +4% | 1 | 1 | 0% | 2,050 | 4,098 | +100% | 0 | 0 | — |
▸case-16 We need to rename a column `user_email` to `email` in a table with 100 million rows in PostgreSQL without causing API downtime or locking reads/writes in production. What migration pattern avoids table-locking issues? | pass→pass | 17,039 | 12,358 | -27% | 1 | 1 | 0% | 2,755 | 4,055 | +47% | 0 | 0 | — |
▸case-18 In SQL Server, concurrent background workers experience frequent deadlocks when updating `orders` and `order_items` tables. Worker A updates `orders` then `order_items`, while Worker B updates `order_items` then `orders`. What is the primary architectural fix to prevent deadlocks? | pass→pass | 6,683 | 9,165 | +37% | 1 | 1 | 0% | 1,053 | 3,322 | +215% | 0 | 0 | — |
▸case-19 In Oracle Database, an analytical query using `WHERE id IN (SELECT ...)` is executing slowly because the subquery evaluates for every outer row. What query optimization technique addresses correlated subquery evaluation? | pass→pass | 11,331 | 11,068 | -2% | 1 | 1 | 0% | 1,954 | 3,824 | +96% | 0 | 0 | — |
▸case-20 We are designing a ClickHouse table using the MergeTree engine for storing clickstream logs. Queries almost exclusively aggregate events by `tenant_id` and filter by `event_date`. How should the sorting key be defined in the `ORDER BY` clause? | pass→pass | 14,192 | 10,678 | -25% | 1 | 1 | 0% | 2,458 | 3,616 | +47% | 0 | 0 | — |
▸case-21 Our search service running on Elasticsearch paginates through millions of document results using deep `from` and `size` parameters (e.g. `from: 50000, size: 50`). Node memory usage spikes and requests fail with `Result window is too large`. What Elasticsearch feature replaces deep offset pagination? | pass→pass | 11,234 | 10,413 | -7% | 1 | 1 | 0% | 1,868 | 3,617 | +94% | 0 | 0 | — |
▸case-22 Our AWS Aurora PostgreSQL cluster experiences 95% CPU utilization on the writer instance due to heavy analytical reporting queries running alongside transactional traffic. How should we offload this read-heavy traffic using Aurora cluster endpoints? | fail→pass | 14,952 | 11,505 | -23% | 1 | 1 | 0% | 2,399 | 3,629 | +51% | 0 | 0 | — |