Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
An e-commerce application stores order history in a 90-million-row orders table on AlloyDB for PostgreSQL. During peak traffic, checkout latency spikes and monitoring shows high disk read throughput.
Using Query Insights, you identify the top normalized query contributing to database load:
SELECT total_amount FROM orders WHERE customer_id = $1 AND status = $2;
Running EXPLAIN (ANALYZE, BUFFERS) for this query shows:
Seq Scan on orders discarding millions of rowsBuffers: shared read values indicating heavy disk I/OWhich action should you take to eliminate the sequential scan, minimize disk I/O, and avoid unnecessary write overhead?
Creating individual secondary indexes on every column of a large table builds a separate B-tree structure for each attribute. While this gives the query planner multiple single-column index candidates, it is an anti-pattern known as over-indexing.
INSERT, UPDATE, or DELETE on the orders table must update every index on the affected row, violating the requirement to avoid unnecessary write overhead.WHERE clause (customer_id = $1 AND status = $2), separate single-column indexes require either scanning one index and filtering heap rows or performing a BitmapAnd combination across two indexes—both of which incur more I/O than a single composite covering index.total_amount alongside the filter keys, so the engine must still fetch table heap pages from disk.No scenario warrants blindly indexing every column on a transactional table; instead, indexes should be designed selectively around actual workload predicates identified in Query Insights and Index Advisor.
work_mem and temp_buffers doIn PostgreSQL and AlloyDB, work_mem specifies the base maximum memory allocated per query operation—such as Sort, Hash Join, or Hash Aggregate—before writing intermediate data to temporary disk files. temp_buffers controls the maximum memory each database session uses for temporary tables.
EXPLAIN (ANALYZE, BUFFERS) output shows high Buffers: shared read values from a Seq Scan on a permanent table, not disk spills (temp read/temp written or temp_files).work_mem or temp_buffers will not cause the query planner to skip reading all 90 million rows of orders when no suitable index exists.work_mem too high can cause severe memory pressure or out-of-memory failures when hundreds of concurrent checkout queries execute simultaneously.work_mem is allocated per sort or hash operator per connection, not globally for the cluster.temp_buffers applies strictly to session-local temporary tables and has no effect on permanent table scans.pg_stat_database (temp_files and temp_bytes) or EXPLAIN (ANALYZE, BUFFERS) temp block counters, rather than shared read on a sequential scan.The EXPLAIN (ANALYZE, BUFFERS) output showed a Hash Join, Hash Anti Join, or Sort node spilling large intermediate datasets to disk (External Merge Disk or growing temp_files in pg_stat_database), rather than a Seq Scan missing an index.
Creating a secondary index solely on status organizes pointers to rows in the orders table by order state (such as pending, shipped, or completed). While it covers one predicate in the WHERE clause, it ignores both customer_id and the projected total_amount column.
status typically has very few distinct values across 90 million rows. An index scan on status = $2 would still match tens of millions of entries and discard nearly all of them once customer_id = $1 is checked.Seq Scan.status cannot cover total_amount, forcing heavy random disk I/O to read table heap rows.customer_id) should lead composite indexes to narrow the search space immediately.Buffers: shared read.The query filtered exclusively on a highly selective single column where only a tiny fraction of rows matched, and latency from heap lookups for non-indexed columns was negligible.
A composite covering index in PostgreSQL and AlloyDB combines multiple filter columns into a single B-tree index structure while storing additional non-key payload columns using the INCLUDE clause (for example, CREATE INDEX idx_orders_cust_status ON orders (customer_id, status) INCLUDE (total_amount)). This design allows the database engine to satisfy both the WHERE predicate and the SELECT projection directly from the index pages.
customer_id and status in the index key enables an Index Only Scan or selective Index Scan rather than scanning all 90 million rows.total_amount is included in the leaf pages of the index, the engine avoids extra heap fetches (reading underlying table blocks from disk), directly reducing Buffers: shared read counts.INSERT, UPDATE, and DELETE operations than creating multiple separate indexes.EXPLAIN (ANALYZE, BUFFERS) confirms actual runtime latency, true row counts, and improved buffer cache hit ratios.orders table grows beyond 90 million rows.Unlike memory-tuning changes or low-selectivity single-column indexes, a targeted composite covering index directly resolves the root cause shown in the execution plan—a full table scan—while keeping storage and write amplification minimal.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated