▸case-01 I need to inspect execution statistics for a slow query on PostgreSQL 15, specifically measuring shared block hits and disk read counts. I was thinking of just running standard EXPLAIN ANALYZE. What exact SQL command clause should I add to get I/O buffer metrics? | pass→pass | 5,234 | 4,802 | -8% | 1 | 1 | 0% | 972 | 1,003 | +3% | 0 | 0 | — |
▸case-02 We store arbitrary JSON payloads in a 'metadata' column of type jsonb. We frequently run queries looking for exact key-value matches like metadata @> '{"status": "active"}'. A team member suggested building a default B-Tree index on the column. What index type and configuration should be used instead for optimal performance? | pass→pass | 10,351 | 8,366 | -19% | 1 | 1 | 0% | 1,976 | 1,438 | -27% | 0 | 0 | — |
▸case-03 Autovacuum is causing massive I/O spikes during peak traffic hours on our PostgreSQL cluster. Someone proposed turning off autovacuum entirely during the day. How should we configure autovacuum cost settings to smooth out its I/O footprint without disabling it? | pass→pass | 19,264 | 19,209 | -0% | 1 | 1 | 0% | 3,508 | 3,565 | +2% | 0 | 0 | — |
▸case-04 We need to filter rows in a PostgreSQL table where a jsonb column 'attributes' contains a specific top-level key and string value, such as {"role": "admin"}. Is it best to unnest the JSON object using jsonb_each_text() in a subquery, or is there a dedicated operator? | pass→pass | 7,839 | 6,557 | -16% | 1 | 1 | 0% | 1,585 | 1,371 | -14% | 0 | 0 | — |
▸case-05 We are configuring logical replication streaming from a primary PostgreSQL database to an external consumer. We want to ensure WAL logs are retained for the subscription without arbitrarily inflating wal_keep_size. What feature should be provisioned on the publisher? | fail→pass | 5,472 | 6,474 | +18% | 1 | 1 | 0% | 892 | 1,200 | +35% | 0 | 0 | — |
▸case-06 We are building an automated query performance monitoring pipeline that parses PostgreSQL execution plans programmatically. What parameter format should be passed to EXPLAIN so the output is returned as a structured JSON object? | pass→pass | 8,339 | 7,126 | -15% | 1 | 1 | 0% | 1,506 | 1,544 | +3% | 0 | 0 | — |
▸case-07 After releasing a database optimization patch on PostgreSQL, we want to clear the accumulated execution statistics in pg_stat_statements without restarting the database server. What function should be called? | pass→pass | 4,304 | 3,041 | -29% | 1 | 1 | 0% | 699 | 673 | -4% | 0 | 0 | — |
▸case-08 We have a 500-million row historical table in PostgreSQL where global autovacuum settings rarely trigger maintenance because the 20% default threshold is too high. How can we force autovacuum to trigger after 50,000 row modifications on this specific table? | pass→pass | 12,096 | 8,630 | -29% | 1 | 1 | 0% | 2,264 | 1,791 | -21% | 0 | 0 | — |
▸case-09 On PostgreSQL 14, we need to execute SQL/JSON path expressions against a jsonb document to extract all matching array elements into separate result rows. What standard PostgreSQL JSON path function should be used? | pass→pass | 4,735 | 5,059 | +7% | 1 | 1 | 0% | 914 | 1,186 | +30% | 0 | 0 | — |
▸case-10 A heavy-traffic index on our orders table has severe bloat. Running REINDEX INDEX directly locks out concurrent writes. What keyword option allows rebuilding the index in PostgreSQL without taking write locks on the table? | pass→pass | 3,122 | 3,578 | +15% | 1 | 1 | 0% | 511 | 707 | +38% | 0 | 0 | — |
▸case-11 We have an UPDATE-heavy workload in PostgreSQL where updates keep creating new index entries and causing bloat. We want to maximize Heap-Only Tuple (HOT) updates by leaving space on table pages. What storage parameter should be lowered on the table? | pass→pass | 5,789 | 5,892 | +2% | 1 | 1 | 0% | 807 | 1,112 | +38% | 0 | 0 | — |
▸case-12 A complex analytical query with large ORDER BY and HashAggregate steps is spilling temporary files to disk on PostgreSQL. Increasing shared_buffers globally did not help. How should memory allocation be adjusted specifically for this session or query? | pass→pass | 11,150 | 9,581 | -14% | 1 | 1 | 0% | 2,042 | 1,843 | -10% | 0 | 0 | — |
▸case-13 When attempting to set up a publication for PostgreSQL logical replication, the server returns an error stating that WAL level is insufficient. What setting must be applied in postgresql.conf? | pass→pass | 4,306 | 4,166 | -3% | 1 | 1 | 0% | 606 | 721 | +19% | 0 | 0 | — |
▸case-14 We need to optimize LIKE and ILIKE wildcard searches matching substring patterns like '%acme%' on a text column in PostgreSQL. Standard B-Tree indexes are not being used. What extension and index operator class should be enabled? | pass→pass | 5,949 | 5,777 | -3% | 1 | 1 | 0% | 1,115 | 1,194 | +7% | 0 | 0 | — |
▸case-15 Our application uses soft deletes with a boolean column deleted_at IS NOT NULL. Queries regularly search only active rows where deleted_at IS NULL. How should the index be defined to keep index size small? | pass→pass | 11,818 | 7,189 | -39% | 1 | 1 | 0% | 2,101 | 1,348 | -36% | 0 | 0 | — |
▸case-16 We need to perform a database backup of a 500GB PostgreSQL database using pg_dump with 8 parallel worker jobs (-j 8). What output format flag (-F) must be specified to support parallel dumping? | pass→pass | 4,240 | 4,223 | -0% | 1 | 1 | 0% | 772 | 877 | +14% | 0 | 0 | — |
▸case-17 We want to enforce a maximum execution time limit of 30 seconds for all queries executed by a reporting user role 'analytics_user', without affecting application database connections. What SQL command applies this limit to the role? | pass→pass | 5,019 | 2,814 | -44% | 1 | 1 | 0% | 884 | 777 | -12% | 0 | 0 | — |
▸case-18 On PostgreSQL 13+, we want to verify or configure B-Tree index deduplication for a high-cardinality index. What storage parameter controls whether B-Tree deduplication is active on the index? | pass→pass | 4,403 | 5,229 | +19% | 1 | 1 | 0% | 807 | 1,078 | +34% | 0 | 0 | — |
▸case-19 A long-running reporting query is currently blocking an ALTER TABLE migration in PostgreSQL. We want to stop the query's execution gracefully without abruptly severing the user's TCP database connection. What administration function should be called with the query's PID? | pass→pass | 3,057 | 3,685 | +21% | 1 | 1 | 0% | 473 | 770 | +63% | 0 | 0 | — |
▸case-20 We are defining a new table in MySQL 8.0 for storing user accounts. How do we create an integer primary key column that automatically increments on each insert in MySQL syntax? | pass→pass | 6,524 | 4,737 | -27% | 1 | 1 | 0% | 1,304 | 1,037 | -20% | 0 | 0 | — |
▸case-21 We are writing a schema.prisma file for a Node.js application. How do we define a one-to-many relation between a User model and a Post model in Prisma ORM syntax? | pass→pass | 7,470 | 5,387 | -28% | 1 | 1 | 0% | 1,387 | 1,204 | -13% | 0 | 0 | — |
▸case-22 We are setting up a Redis instance as an LRU key-value cache. What configuration directive in redis.conf specifies the eviction policy when maximum memory is reached? | pass→pass | 3,003 | 3,448 | +15% | 1 | 1 | 0% | 485 | 768 | +58% | 0 | 0 | — |