Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Data export and archival procedures move database contents into files stored in Cloud Storage for portability, long-term retention, and reuse in other environments. An export creates an external file in Cloud Storage that can recreate data, copy a single database or table, and remain available even if the source instance is deleted. Automated exports combine native database export tools with schedulers and tiered storage classes to meet compliance and synchronization needs without interrupting production work.
Data protection in Cloud SQL (opens in a new tab) uses three complementary methods that guard against different failures. Backups provide a lightweight restore point for an instance, point-in-time recovery restores to a specific moment, and exports create portable files in Cloud Storage. The methods differ in what they create, how granular they are, and whether they survive deletion of the source.
| Method | What it creates | Survives instance deletion | Granularity and restore target |
|---|---|---|---|
| Backups | Lightweight restore point on the instance | No, backups are deleted with the instance | Whole instance only; cannot restore if the instance region is unavailable |
| Point-in-time recovery | Recovery to a specific point in time | Creates a new instance during recovery | Always creates a new instance; cannot recover to an existing instance |
| Exports | External file in Cloud Storage | Yes, unaffected if the instance is deleted | Single database or even a single table, depending on export format; takes longer to create |
Exports are the choice when data must be portable, selectively restorable, or retained after cleanup, while backups and point-in-time recovery are the choice for fast operational restore.
Native export paths send data from the database to Cloud Storage without manual file handling. Serverless exports in Cloud SQL offload the export operation to a temporary instance, so the primary instance continues to serve queries at its usual performance, and the temporary instance is automatically deleted when the export completes. Exports intended for reuse in Cloud SQL must use the proper export procedure and use the exact same SQL mode for import as for export, and small instances can be temporarily increased in CPU and RAM to improve performance when importing large datasets.
Other Google Cloud databases use their own export operators to Cloud Storage. Bigtable tables can be exported as a series of Hadoop sequence files that are stored in Cloud Storage or imported back into another Bigtable instance. For portability across tools, choose services that support exporting data to an open format like CSV, Parquet, and Avro.
Automated exports use schedulers and APIs to run exports on a regular schedule for archival compliance and cross-environment synchronization. Cloud Scheduler with the REST API automates Cloud SQL export management, and Cloud Scheduler with Cloud Run functions supports more advanced automation scenarios. Managed Airflow, a managed version of Apache Airflow, schedules regular backups of multiple databases with a directed acyclic graph (DAG) that runs daily to copy data to another project, dataset, or table or to export data to Cloud Storage.
Managed Airflow performs exports with Cloud Platform operators that move data from the source database to Cloud Storage:
A simpler scheduled-task pattern copies files to Cloud Storage with the gcloud storage command or a Cloud Storage client library. Data flows from the on-premises source directory to the destination bucket, and access to the bucket is controlled by a service account and an Identity and Access Management (IAM) policy:
Exam tip: Exports survive deletion of the source instance and can target a single database or table, while backups are deleted with the instance and cannot target a single database or table.
Archival storage in Cloud Storage lowers cost by moving infrequently accessed export files to lower-cost tiers. The tiered storage pattern migrates data from higher-cost storage to Nearline, Coldline, and Archive classes as the likelihood of access decreases. Nearline is designed for backup scenarios where access is at most once a month, which supports regular recovery testing while keeping costs low.
Lower storage cost comes with tradeoffs that matter for archival planning. Nearline, Coldline, and Archive charge minimum storage durations and add costs for retrieving data or metadata earlier than the minimum duration. Unneeded tables and objects can be removed from the database and stored in a full backup or an archival Cloud Storage bucket to reduce database cost. To guard archived exports, Soft Delete preserves deleted and overwritten objects for a specified period and Object holds prevent deletion or updates to objects, while IAM policies restrict who can access the bucket.
Database health and maintenance automation is the practice of keeping PostgreSQL-compatible databases fast and available through routine upkeep and controlled update windows. Cloud SQL (opens in a new tab) and AlloyDB for PostgreSQL divide this work: the database engine needs regular cleanup such as vacuuming, while the managed platform needs periodic software patches applied during scheduled windows. Customers configure when and how that work happens, and Google supplies the infrastructure, patch versions, and scheduling controls.
PostgreSQL vacuum operations reclaim dead tuples, freeze transaction IDs, and keep tables healthy without long outages when configured correctly. Standard VACUUM runs in parallel with production work, so SELECT, INSERT, UPDATE, and DELETE continue normally, but ALTER TABLE and other definition changes are blocked while a table is vacuumed. VACUUM FULL reclaims more disk space but exclusively locks the table and runs slowly, so standard VACUUM is preferred for routine automation.
Tuning focuses on reducing vacuum duration and write-ahead log (WAL) pressure:
maintenance_work_mem to batch more tuples per iteration, while keeping autovacuum_max_workers in mind because autovacuum can allocate up to that many multiples of the memory.VACUUM generates many WAL records that slow completion.vacuum_freeze_min_age toward its maximum of 1,000,000,000 from the default of 50,000,000 when a table nears the two-billion transaction ID limit, which can cut frozen tuples by up to half.INDEX_CLEANUP OFF on PostgreSQL 12.0 and later to clean and freeze table data quickly without a full index scan, since index cleaning time grows with index size and count.Larger indexes and active replicas both extend vacuum time, while higher memory and skipped index cleanup shorten it. Monitoring with Cloud Monitoring dashboards and Query Insights helps identify slow queries and constrained memory or CPU before vacuum problems affect availability.
Cloud SQL maintenance timing and windows control when disruptive software updates occur so brief downtime lands during low traffic. Maintenance timing selects the week of the rollout after notification, the maintenance window selects a one-hour day-and-time slot, and the deny maintenance period blocks all scheduled maintenance for a set of days. Together they let staging receive an update first for testing, followed by production at least seven days later.
| Timing option | Update occurs after notification | Choose when |
|---|---|---|
| Any | Any time, typically within Week 1 | No timing preference is configured |
| Week 1, also called preview or canary | 7 to 14 days | Early testing in staging or non-critical instances is wanted |
| Week 2, also called production or stable | 15 to 21 days | Production stability with time to validate staging is wanted |
| Week 5 | 35 to 42 days | Maximum deferral before the next quarterly rollout is wanted |
If no window is set, Cloud SQL uses default windows in the instance time zone: 10 PM to 6 AM Monday through Friday on weekdays, and Friday 10 PM to Monday 6 AM on weekends. A deny maintenance period can last up to 90 days, for example November 1 through January 15 for holiday shopping peaks. Changes made while maintenance is scheduled or in progress apply only to future events.
Cloud SQL maintenance applies updates by moving the instance to an updated virtual machine (VM) while preserving data, network identity, and location. The workflow preserves all transactions written before maintenance on a common data disk, keeps the same static IP address and zone, and clears the database cache because the cache leaves with the original VM.
The update follows an ordered sequence:
Notifications, rescheduling, and self-service give control around that sequence. Notifications are opt-in per project, send one email per instance at least one week before maintenance, and cover upcoming, started, completed, and canceled events, but they are not sent for read replicas. Rescheduling supports IMMEDIATE, NEXT_AVAILABLE_WINDOW, and SPECIFIC_TIME, and self-service maintenance installs a new maintenance version on demand when an update is needed sooner or was previously skipped, updating all read replicas of a primary instance first before the primary.
Exam tip: Week 1 or Week 2 timing can be rescheduled up to 28 days after the original date, while Week 5 timing can be rescheduled only up to 7 days, and any reschedule must occur at least 24 hours before the event.
Reducing downtime impact means making applications resilient to dropped connections and failed in-flight transactions during the maintenance switchover. Transient errors are unavoidable in the cloud, and maintenance causes the same symptoms, so retry logic, smaller transactions, and connection handling protect both planned maintenance and unplanned interruptions.
Practical controls include:
Readiness also depends on capacity and operational discipline. Configure storage with at least 20% free space for maintenance operations and create a metrics-based alerting policy on disk utilization, and do not start a new administrative operation before the previous operation completes. For data protection maintenance, use serverless exports to offload exports to a temporary instance that is deleted afterward, and automate recurring exports with Cloud Scheduler with the REST API, adding Cloud Run functions for advanced scenarios.
Index optimization and fragmentation management is routine maintenance that keeps indexes fast for query execution and compact for storage as tables are inserted, updated, and deleted. In Cloud SQL for SQL Server and SQL Server on Google Cloud, the work centers on measuring fragmentation and reorganizing fragmented indexes on a schedule. In Spanner (opens in a new tab), the work centers on iterative testing where schema and index changes are validated with load tests and performance metrics.
Measuring fragmentation in SQL Server environments means querying index physical statistics to decide which indexes need attention. The query joins sys.dm_db_index_physical_stats to sys.indexes and returns avg_fragmentation_in_percent for each named index in a database. Heavily modified tables usually show the highest values, so a regular schedule should focus on those tables first. For example, results may show ORDERS_I1 at 0% while PK_STOCK is at about 95.5%, which makes PK_STOCK the clear priority for maintenance.
Reorganizing indexes in SQL Server environments uses ALTER INDEX ... REORGANIZE to reduce fragmentation without recreating the full index structure. Administrators can generate the statements from INFORMATION_SCHEMA.TABLES with a query that builds ALTER INDEX ALL ON table_name REORGANIZE for each base table, then select the tables with the highest fragmentation and execute those statements incrementally. The generated work should be saved and run as a regular maintenance job so fragmentation does not accumulate. When a database is restored from another location to Google Cloud, the database should first rebuild, or at least reorganize, its indexes and then run DBCC UPDATEUSAGE to correct usage counts.
Exam tip: After a restore to Google Cloud, rebuild or at least reorganize indexes first, then run DBCC UPDATEUSAGE for the restored database.
Aligning disk allocation with SQL Server storage units improves the I/O performance that index scans and maintenance depend on. SQL Server stores data in extents that are 64 KB in size, made of eight contiguous 8 KB pages. Formatting secondary disks with a 64 KB allocation unit lets SQL Server read and write whole extents more efficiently. The documented practice is to initialize new raw disks as GPT, create a maximum-size partition, and format the volume as NTFS with AllocationUnitSize 65536.
Tuning schema and indexes in Spanner is an iterative cycle of measuring CPU utilization and latency, changing the schema or application, and testing again. Changes can include adding or changing an index, changing a primary key, batching writes, or merging and modifying queries. Tuning is most reliable when the test setup is close to live production traffic throughput and data sizes, especially before production traffic begins. The documented workflow follows an ordered sequence:
Suboptimal query performance in Spanner often traces to the same small set of causes. Inefficient queries such as full scans of large tables, cross-joins over large tables, or predicates on non-key columns increase rows and bytes read. High CPU utilization above the healthy threshold increases request latency, while transaction locking causes competing transactions to wait for the same resources. Inefficient schema design limits what query optimization can fix, and hotspots limit write throughput for high-QPS workloads and can be investigated with Key Visualizer statistics in the Google Cloud console.
Last updated