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.
A retail company runs a high-write Cloud SQL for PostgreSQL instance that supports its e-commerce platform. Frequent UPDATE and DELETE operations have caused table bloat and stale query planner statistics, degrading read performance. To prevent disruptions, the database team must meet the following requirements:
Which configuration should the database team implement?
VACUUM operations from the primary instance, and enable maintenance notifications without setting a maintenance window so updates can be rescheduled once they begin.Read replicas asynchronously replicate changes from a primary Cloud SQL instance to offload read-only analytical or application queries. Maintenance notifications send email alerts at least one week before a scheduled Cloud SQL platform update so administrators can plan ahead or reschedule.
VACUUM: Read replicas do not execute VACUUM for the primary instance; in fact, having too many replicas hinders VACUUM operations and increases write-ahead log (WAL) overhead.VACUUM and ANALYZE always execute on the primary instance and generate WAL records replicated downstream.Week 1/Week 2, or 7 days for Week 5).The primary goal was scaling out read-only query traffic across replicas while already having a configured maintenance window and opting into advance email alerts to defer non-critical patches 24+ hours ahead of time.
VACUUM FULL and REINDEX job via Cloud Scheduler during weekdays, and set the Cloud SQL maintenance timing to Any so updates apply immediately.VACUUM FULL and Any Maintenance Timing DoVACUUM FULL rewrites an entire PostgreSQL table into a new disk file to compact free space and return storage to the operating system. Setting Cloud SQL maintenance timing to Any allows managed platform updates to occur without an explicit rollout delay, typically within the first week of a rollout using default off-peak windows.
VACUUM, VACUUM FULL acquires an exclusive lock on the target table and runs slowly, blocking SELECT, INSERT, UPDATE, and DELETE operations until completion.Any without a specific maintenance window and deny maintenance period allows updates to occur in default weekday night windows (10 PM to 6 AM) and does not block updates during the 14-day holiday sale.VACUUM FULL via Cloud Scheduler replaces PostgreSQL's built-in autovacuum daemon with an intrusive, lock-heavy workflow.VACUUM FULL requires substantial temporary disk space to rewrite the table copy.REINDEX without CONCURRENTLY also imposes restrictive locks that stall active transactions.Any does not restrict updates to a specific weekend hour or enforce a blackout period.You had an offline maintenance outage window where a table suffered extreme historical bloat requiring immediate physical disk shrinkage and no concurrent application traffic was running.
For ongoing production health, standard VACUUM paired with tuned maintenance_work_mem reclaims reusable space without halting application queries.
maintenance_work_mem setting, verify ANALYZE runs regularly, and configure a weekend maintenance window alongside a 14-day deny maintenance period.In Cloud SQL for PostgreSQL, internal table hygiene and managed infrastructure patching operate at two complementary layers. Autovacuum combines standard non-blocking VACUUM and ANALYZE processes to reclaim space from dead tuples and refresh table statistics in the pg_statistic catalog. Meanwhile, maintenance windows and deny maintenance periods govern when Google Cloud applies underlying operating system patches and minor PostgreSQL version upgrades.
VACUUM and ANALYZE operations to run concurrently alongside production SELECT, INSERT, UPDATE, and DELETE queries without taking exclusive table locks. Monitoring views such as pg_stat_user_tables (last_autovacuum and last_autoanalyze) confirms tables stay fresh.maintenance_work_mem enables each worker to batch more dead tuples per pass, completing cleanup faster while keeping total memory allocation (autovacuum_max_workers multiplied by maintenance_work_mem) within safe instance RAM limits.pg_statistic metadata.Unlike VACUUM FULL, disabling autovacuum, or relying on invalid deny intervals, this approach balances continuous query optimizer health with strict application availability during critical business windows.
autovacuum to reduce CPU overhead, run manual VACUUM with INDEX_CLEANUP OFF monthly, and configure a 120-day deny maintenance period covering the entire holiday quarter.INDEX_CLEANUP OFF, and Deny Periods DoDisabling autovacuum stops PostgreSQL's background workers from automatically reclaiming dead tuples and updating table statistics. Passing INDEX_CLEANUP OFF to VACUUM skips scanning and cleaning indexes to freeze table pages faster, while a deny maintenance period blocks managed Cloud SQL updates for a defined date range.
autovacuum prevents periodic ANALYZE runs, leaving pg_statistic outdated and causing the query planner to choose inefficient execution plans.VACUUM only monthly on a high-write database lets dead tuples accumulate rapidly, degrading scan performance and risking transaction ID wraparound.INDEX_CLEANUP OFF leaves dead index entries behind, which undermines the goal of resolving index and table bloat.INDEX_CLEANUP OFF is a tactical option for emergency transaction ID freeze operations, not routine bloat management.You needed a short-term emergency intervention (INDEX_CLEANUP OFF) to quickly freeze tuples approaching the two-billion transaction ID limit while scheduling a valid deny period of 90 days or fewer.
Keeping autovacuum active and tuning maintenance_work_mem prevents both bloat and stale statistics continuously without manual intervention.
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