▸case-23 We need to write an Ansible playbook to apply minor OS kernel security updates to our Ubuntu PostgreSQL 15 database host servers and adjust systemd unit file memory limits. We do not need schema design, technology selection, or data modeling. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-11 In a PostgreSQL database, we store arbitrary user preferences in a jsonb column named settings. We frequently run queries looking for exact top-level key/value matches like WHERE settings @> '{"theme": "dark"}'. The developer attached a standard B-tree index to the settings column, but PostgreSQL refuses to use it for key-value containment queries. Which index type should be used? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-14 We offloaded read queries in a web application to asynchronous PostgreSQL read replicas. Users complain that immediately after creating a new post, refreshing their dashboard shows the post missing due to 500ms replication lag. How should the database connection routing architecture be modified to solve this? | fail→pass | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-18 We are re-architecting a monolithic legacy Oracle SQL database into microservice-owned databases. The business cannot afford any maintenance window or downtime, and running a single cutover migration weekend is deemed too risky. What architectural migration pattern should be used to incrementally migrate data and traffic domain-by-domain? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-21 We have an existing slow PostgreSQL SQL query SELECT * FROM orders JOIN items ON orders.id = items.order_id WHERE orders.status = 'PENDING' running on a fixed production schema where modifying tables, indexes, or infrastructure is strictly prohibited. Provide the EXPLAIN ANALYZE tuning advice for this specific query. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-07 Our e-commerce product catalog page experiences massive read spikes when popular items go on sale. When a high-traffic cache key expires in Redis, database CPU spikes to 100% as hundreds of concurrent requests simultaneously hit PostgreSQL to recompute the same product data. What caching architecture technique should be implemented to prevent this? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-16 We are designing a fraud detection network that needs to execute 6-hop deep relationship traversals (e.g., finding if User A shares a bank account, IP address, or device fingerprint with a known fraudster User B through interconnected paths). Relational SQL recursive joins are timing out after 30 seconds. What database model should be selected for this access pattern? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-08 In our data warehouse, customer addresses change over time. Operations needs to report sales based on the customer's address at the exact time an order was placed 3 years ago, while shipping needs the current address. The current model simply overwrites address in the customers dimension table whenever it changes. What dimensional modeling pattern solves this historical tracking requirement? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-06 We are designing a sharded PostgreSQL architecture for a social network. The primary query pattern is loading a user's personal profile and recent posts. The initial proposal is to shard the database using auto-incrementing post_id as the shard key so data distributes evenly. Evaluate this choice and recommend the correct shard key. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-20 Our PostgreSQL database storage cost is exploding because 95% of our 500 million rows are historical transactions over 2 years old that are rarely accessed but must be retained for 7 years for compliance. Active transactions need sub-10ms response times. What database lifecycle and tiered storage strategy should be designed? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-22 Design the RESTful HTTP API endpoint signatures, request payload schemas, and response JSON formats for a new user notification preference feature in our Node.js Express application. Do not include database schema designs or storage layer architecture. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-13 We have a query in MySQL: SELECT * FROM orders WHERE status = 'COMPLETED' AND created_at >= '2023-01-01' ORDER BY created_at DESC;. The status column has low cardinality, while created_at has high cardinality timestamps. We need a composite index on (status, created_at). Which column should come first in the composite index key definition and why? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-09 We are modeling a deeply nested organizational hierarchy (up to 15 levels deep) where we frequently need to fetch an entire employee subtree or find all ancestors of a node in a single fast query. The current schema uses a simple parent_id adjacency list, which causes recursive N+1 queries. Recommend an alternative relational physical design pattern for fast tree reads. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-17 We are designing a high-volume logging service requiring constant write throughput of 500,000 writes/sec with zero read-side join requirements and high availability across 3 availability zones without a single point of failure master node. A team member recommends MongoDB with a single primary replica set. Why is a peer-to-peer distributed wide-column store a better fit? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-15 We have a 2 Terabyte PostgreSQL log table partitioned by month (created_at). However, queries filtering by user_id are performing full scans across all 24 monthly partitions, ignoring partitioning benefits. Why is partition pruning failing, and what partition key or indexing adjustments are required? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-19 We are building an event ticketing system where 10,000 users attempt to purchase 100 flash-sale seat tickets simultaneously within a 5-second window. The developer proposed using application-level optimistic concurrency control (checking version numbers before commit). What will happen under high contention and what transaction concurrency control strategy should be used at the database level? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-10 We are designing a MongoDB e-commerce schema. An order contains line items, and each line item refers to a product. The catalog has 10,000 products whose titles and prices change monthly. A developer wants to embed the full product object inside the order's line items array, but is worried that updating a product price in the catalog won't automatically update past orders. Analyze this concern. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-12 We are deploying a multi-region active-active relational database across US-East and EU-West to reduce latency. Both regions issue concurrent writes to the same tables using standard auto-incrementing integer primary keys (1, 2, 3...). What primary key collision issue will occur and how should the primary key generation strategy be altered? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-03 We need an architecture for an enterprise order management system where complete historical audit trails of every status change are required, and read patterns (complex reporting aggregations) heavily conflict with high-speed write patterns (order placement). The team proposes running heavy analytical reporting queries directly on the primary transactional SQL table during peak operational hours. Recommend a design pattern to separate these concerns. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-05 We need to rename a widely used column usr_email to email in a PostgreSQL table with 50 million rows and constant 24/7 API traffic. The team plans to issue a single SQL command ALTER TABLE users RENAME COLUMN usr_email TO email; directly in production during application deployment. What is the danger of this approach and what multi-phase migration pattern should be followed? | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-02 We are building a core banking double-entry transaction ledger where absolute ACID compliance, strict foreign key constraints, and zero risk of orphaned balances are mandatory. A team member suggested storing ledger entries as unconstrained MongoDB documents to get high write throughput. Evaluate this choice and recommend the correct database architecture class. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-04 We are designing a multi-tenant B2B SaaS platform expected to serve both small SMB tenants and large enterprise tenants with strict HIPAA data isolation rules. A developer suggests putting all tenants in a single shared table with just a tenant_id column for isolation. Analyze the risks and recommend a schema multi-tenancy model for enterprise clients. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |
▸case-01 We are ingesting 1,000,000 telemetry events per second from smart electricity meters. Each event has meter_id, timestamp, active_power, and voltage. Our team wants to store this in standard MySQL tables with auto-incrementing primary keys and B-tree indexes because they already know MySQL. Recommend an appropriate data storage architecture pattern or engine class and explain why the proposed MySQL design will fail under scale. | fail→fail | — | — | — | — | — | — | — | — | — | — | — | — |