Manage a solution that can span multiple database technologies

Database connectivity and access management for Cloud SQL (opens in a new tab) and AlloyDB (opens in a new tab) combine identity proof, encrypted transport, private network paths, and least-privilege authorization. Traffic flows from the application through an encrypted proxy or connector to the database instance, while Identity and Access Management (IAM) controls both reaching the instance and logging in as a database user. Private networking and audit logging add boundaries and visibility around those paths.

1.1Database Authentication Models for Cloud SQL and AlloyDB

Database authentication models define how a client proves identity before connecting to AlloyDB or Cloud SQL. Built-in authentication uses a username and password stored inside the database, with access controlled by GRANT and REVOKE. IAM database authentication uses Google identities such as users, groups, and service accounts, with IAM roles plus database privileges controlling access. IAM authentication reduces password sprawl because users sign in with existing Google credentials and use short-lived OAuth 2.0 tokens valid for one hour. Centralized IAM control also provides consistent policies across projects and automatic account creation on first login when group authentication is used.

1.2Encrypted Tunnels with Auth Proxies and Language Connectors

Encrypted tunnels with Auth Proxies and connectors protect traffic between the application and the database and upgrade it to modern encryption. The AlloyDB Auth Proxy is a client-side proxy process that upgrades non-TLS connections to TLS 1.3 connections. AlloyDB Language Connectors are in-application client libraries that connect from the application to a proxy server on the AlloyDB cluster. The Cloud SQL Auth Proxy and Cloud SQL connectors for Go, Java, and Python follow the same pattern by calling APIs through sqladmin.googleapis.com.

AlloyDB automatically generates the server certificate for TLS 1.3. Mutual authentication with client certificates requires an AlloyDB Language Connector with mutual TLS (mTLS) or the AlloyDB Auth Proxy. Automatic IAM authentication is the most reliable choice for long-lived processes and pooling because the connector requests and refreshes tokens without operator action. Manual authentication requires signing in to Google Cloud, requesting a token with the Cloud SQL Admin API scope, and passing email as username and token as password, which expires after one hour.

1.3Private Network Paths and Security Perimeters

Private network paths keep database traffic inside Google Cloud and off public networks. Private services access and Private Service Connect connect an application in another Virtual Private Cloud (VPC) to AlloyDB using its private IP address, and Private Service Connect must be configured when the AlloyDB cluster is created. Serverless VPC Access connects serverless workloads such as Cloud Run to AlloyDB, so traffic flows from the serverless environment through the VPC connector to the private database endpoint. Cloud VPN or Cloud Interconnect provides a secure path between a corporate network and Google Cloud for hybrid applications, with the corporate network on one side and Google Cloud on the other.

Broader controls add boundaries around those paths. VPC Service Controls defines a service perimeter around database resources to control data movement with ingress and egress rules and prevent exfiltration. Shared VPC provides centralized network configuration while keeping environments separated. If public IP is enabled, access should be limited with AlloyDB Language Connectors and a custom organization policy that restricts fields such as enable public IP address.

1.4IAM Permissions, Principals, and Least-Privilege Access

IAM permissions for connecting and logging in separate the right to reach the instance from the right to log in as a database user. For Cloud SQL, roles/cloudsql.instanceUser contains cloudsql.instances.login required to log in, while roles/cloudsql.client contains cloudsql.instances.connect required to connect through the proxy. An identity using the proxy therefore needs both roles, and a new database user has no database privileges until privileges are granted inside the engine. For AlloyDB, IAM controls which workloads can connect when the proxy or connectors are used, while IAM database authentication controls which principals can log in. Outbound firewall rules must allow egress TCP to port 443 for API calls and port 3307 for Cloud SQL connector data traffic.

An IAM policy binds a principal to a role on a resource, and the policy applies from the point where it is granted downward. A project-level grant applies to all instances in that project, while an organization-level grant applies across all projects. IAM policies use four principal types:

• Users: individual human identities authenticated through Cloud Identity, Google Workspace, or a federated provider.
• Groups: collections of users that let administrators grant and audit access once for many people.
• Domains: all users in an organization domain, which is rarely appropriate for databases.
• Service accounts: application identities, including service agents that Google creates to let a service access resources.

Database roles package permissions at different precision. Basic Owner, Editor, and Viewer roles grant broad permissions and must never be used for routine production database work. Predefined roles match a specific job such as viewing or operating a database. Custom roles minimize blast radius when no predefined role fits. Workload access follows a fixed order: first create the service account, then grant the least-privilege resource role to it, then delegate use permission such as Service Account User to the administrator, and only then attach and run the workload.

1.5Conditional Access and Control-Plane Audit Logging

Conditional access and audit logging combine time-bound grants, context checks, and complete logs to enforce least privilege. Time-bound access works only for a limited period or for a specific resource instead of standing access. A common pattern holds admin power in a privileged service account and grants users only temporary impersonation rights through group membership, so removing the user from the group ends privilege. Access Context Manager adds a context check on top of an IAM role, such as requiring users to be on the corporate network for privileged access. Strong authentication uses single sign-on, 2-step verification, and Fast IDentity Online (FIDO) keys for highly privileged accounts.

Cloud Audit Logs records administrative activity, data reads, data writes, and IAM policy changes. Administrators enable data access audit configuration with ADMIN_READ, DATA_READ, and DATA_WRITE and review use in Logs Explorer. Filtering for SetIamPolicy shows who changed permissions, while data access logs show who read or wrote data. Reviewing both verifies that time-bound access actually expired.

Database monitoring and troubleshooting for Cloud SQL (opens in a new tab) connects latency, resource, quota, and log signals to root cause and response. Telemetry flows from built-in agents and the Ops Agent into Cloud Monitoring, where CPU, memory, disk, and Query Insights views support dashboards and alerts. The same signals drive rightsizing, quota increases, and incident triage.

2.1Assessing Slow Queries, Locking, and Missing Indexes

Assessing slow queries, locking, and missing indexes traces why reads are slow and whether contention or a missing index is the cause. Slow reads increase input/output (I/O), raise latency, and can overload a primary instance and its replicas. Work flows from latency measurement to query review to lock and storage checks. In Firestore, a query that needs a composite index returns an error with a link to create the required index. A Firestore composite index names a collectionGroup, a queryScope of COLLECTION, and an ordered list of fields with fieldPath and order of ASCENDING or DESCENDING. The field order must match the query filter and sort, including nested paths such as price.currency. New definitions are added to the indexes array and deployed with firebase deploy --only firestore:indexes.

Latency isolation determines whether delay comes from the instance, client, or network. For Memorystore for Valkey, the test runs from a Compute Engine virtual machine in the same region and VPC as the instance. The tool pings the server for a few minutes, and an average of 1 millisecond or less means the instance is healthy. If the instance is fast but the application is slow, the delay sits outside in cross-region distance, high client CPU or memory, exhausted connection pools, or application logic. On the instance, replace operations that block the single Valkey main thread, use SCAN instead of KEYS, use read replicas when slightly stale data is acceptable, and set expiration times.

2.2Concurrency Controls and Storage Checks for Contention

Data contention in Firestore occurs when a transaction needs documents it read to stay consistent while another operation updates the same documents. Firestore delays or fails one operation and automatically retries transactions, then returns ABORTED: Too much contention after finite retries. Firestore guarantees serializable isolation by commit time, so transactions behave as if they ran in series ordered by commit time.

Server libraries follow the database setting, with Standard edition defaulting to PESSIMISTIC and Enterprise edition defaulting to OPTIMISTIC. Mobile and web software development kits always emulate optimistic concurrency. Within a transaction, reads must come before writes. The mode can be viewed with gcloud firestore databases describe and changed with gcloud firestore databases update --concurrency-mode. When slowness remains, confirm disk and memory pressure with slow-log entries, high p99 disk timings, memory near 80%, and rejected-connection counts.

2.3Tracking CPU, Memory, Throughput, and Capacity

Tracking CPU and memory finds bottlenecks before they cause outages. CPU Utilization is percent of virtual CPU used, and Memory Utilization is percent of memory used excluding disk caches and, on Linux, kernel memory. Sustained high values mean the workload presses against capacity, while consistently low values mean overprovisioning. High CPU should trigger analysis with Query Insights, pg_stat_activity, and checks for poorly written queries, long transactions, and many connections. A rising number of reads served from disk suggests the working set no longer fits in cache.

Storage performance is judged from throughput, input/output operations per second (IOPS), I/O size, queue depth, and disk-space utilization. Small 4 to 16 KiB random operations are usually limited by IOPS, while large 256 KiB to 1 MiB sequential operations are usually limited by throughput. For Cloud SQL, disk performance depends on disk size and virtual CPU count, while for Hyperdisk it depends on provisioned IOPS, throughput, size, and machine type. Choose solid-state drive (SSD) for consistent performance and hard disk drive (HDD) only for very large 10 TB or larger, infrequently accessed datasets. Keep at least 20% free disk space, enable automatic storage increases, audit over-provisioned disks, and archive cold tables to Cloud Storage.

2.4Monitoring Quotas and Requesting Increases

Monitoring quotas keeps multi-technology solutions running by matching limits to demand. A quota sets the maximum amount of a resource a project, folder, or organization can use. Allocation quotas limit how much is held at one time and free only when resources are deleted. Rate quotas limit operations per time window and refill when the window resets, with per-day resets at midnight Pacific Time. Concurrent quotas limit simultaneous long-running operations. Scope adds project, folder, and organization levels, while location adds global, regional, and zonal levels. A quota sets a maximum that can be created if capacity exists, but it does not guarantee capacity.

A quota increase can be requested through the console, a support case, or an account team. The console path needs Project Owner or Editor, Quota Administrator, or a custom role with serviceusage.quotas.update. The ordered console flow is:

1. Open Quotas page: Go to Quotas and System Limits and filter for the quota.
2. Select quotas: Select one or more quotas to change.
3. Start edit: Click Edit quotas and enter contact details.
4. Describe need: Enter requested value and justification with consumption proof.
5. Submit: Submit for review and track the request.

Most requests take 24 to 48 hours, while large increases can take 10 business days. Strong justification shows quota exceeded errors, growth patterns, and region spread. Before and after requesting, optimize with exponential backoff, client-side rate limiting, and steady patterns instead of bursts. The quota adjuster can proactively raise values by 10 to 20 percent when peak use nears the limit, but it only increases values and covers only some services. Cloud SQL Admin API rate quotas control Admin API calls, refill over 60-second intervals, and return HTTP 429 with rateLimitExceeded when exceeded.

2.5Investigating Hotspots, Locks, and System Bottlenecks

Investigating resource contention finds where concurrent work competes for data, connections, CPU, memory, or disk. A distributed hotspot is an unbalanced partition that receives disproportionate reads or writes. Poor keys such as monotonically increasing keys concentrate work on few splits. Cloud Spanner distributes compute with one server task per zone up to 1000 processing units and storage of 1024.0 GiB per 100 PUs below 1 node and 10 TiB per node at larger sizes. Detection starts with the Query insights dashboard for CPU seconds per minute and TopN table, then uses SPANNER_SYS.SPLIT_STATS_TOP_MINUTE ordered by CPU_USAGE_SCORE to rank the hottest splits.

Locking appears as blocking sessions, higher latency, and timeouts that flatten throughput instead of rising with clients. Diagnosis searches Cloud Logging for operation_too_costly, reviews slow queries for missing indexes, and checks pg_stat_activity by state. Fixes shorten lock hold time with small bundles, separate independent resources, add supporting indexes, and perform vacuum and statistics maintenance. System bottlenecks differ by signal: CPU constraint shows CPU and CPU Wait above capacity, I/O constraint shows IO Wait dominating, and memory constraint shows process usage near 90% or cache plus free below 10%. Connection exhaustion can hide metrics and cause timeouts, so bounded pooling matters.

2.6Setting Alerts for Errors and Performance Metrics

Setting alerts connects database signals to people and tools for timely response. Log-based alerting watches the log stream for a matching filter and opens an alert when an error appears. Log data flows from Cloud Logging to a Cloud Monitoring alerting policy, which forwards notifications to channels such as email, SMS, or PagerDuty. Multiple channels should be configured so one failure does not block notice. A policy is limited to 2 new alerts per minute and 20 alerts and 20 notifications per day. Filters must use correct Logging query-language names, and the query should be validated in Logs Explorer before saving.

Metric-based policies watch numeric Cloud SQL signals such as CPU, memory usage and total_usage, disk utilization, and throughput. Key thresholds include keeping at least 20% disk space free and keeping memory usage under 90%. A condition combines metric, threshold, and duration so brief spikes do not page on-call staff. Incidents move in order from Open when the condition is met, to Acknowledged when marked under investigation, to Closed when manually or automatically closed after the auto-close period, which defaults to 7 days. Cloud Audit Logs support investigations: Admin Activity logs are always written and cannot be disabled, while Data Access logs must be explicitly enabled. Longer history requires a log sink that exports copies to BigQuery.

Geographic and automated backup planning stores restorable copies in separate locations on a schedule so data survives zonal failure, regional failure, and human error. In Google Cloud, Cloud Storage bucket locations, managed database replication, and schedulers such as Cloud Scheduler provide the foundation. The design must balance availability against compliance, residency rules, recovery point objective (RPO), and recovery time objective (RTO).

3.1Geographic Storage Options and Scheduled Exports

Geographic storage options keep backup objects in two or more separate areas. A dual-region bucket stores data in two chosen regions, while a multi-region bucket stores data across data centers in the multi-region. Synchronization happens asynchronously, so recovery depends on replication finishing before failure. Turbo replication strengthens a dual-region by replicating to both regions with an RPO of 15 minutes. When strict RPO and RTO require proof that data reached the second region, use two single-region buckets with dual-write from the application or a Cloud Storage copy from the first bucket to the second.

Scheduled cross-region backups create restorable copies without manual work. For BigQuery, the most cost-effective backup is an export to Cloud Storage scheduled by Cloud Scheduler or Managed Service for Apache Airflow. Export formats include Avro with SNAPPY, JSON with GZIP, and columnar Parquet and ORC that provide high compression. If multi-region storage is not allowed, the BigQuery Data Transfer Service copies a dataset to a different region. The Storage Transfer Service moves data to, from, or between Cloud Storage, Amazon S3, and on-premises systems. The Backup and DR (opens in a new tab) Service centralizes backup for Compute Engine virtual machines, including Persistent Disk snapshots stored in Cloud Storage.

3.2Managed Database Resilience and Residency Controls

Managed database resilience is determined by where each service stores copies and whether it replicates automatically. Bigtable production instances require a minimum of two clusters in unique zones in the same region, and Google automatically manages replication that creates an independent copy in each zone. Incoming traffic can fail over between clusters in the same instance, which protects against zonal failure but not regional loss. BigQuery datasets can be regional or multi-regional but do not automatically back up to another region, so survival of regional loss requires cross-region replication or managed disaster recovery (opens in a new tab) with slots in a secondary region. Short-term human errors are covered by time travel for the last seven days plus a fail-safe period that retains deleted data for an additional seven days. Firestore in a multi-region location operates in a multi-zone and multi-region replicated configuration and can withstand loss of entire regions.

Balancing redundancy with residency enforces where data at rest can be created. The Organization Policy Service provides centralized control through location restrictions that allow or deny multi-regions, regions such as us-east1, or zones. When residency restricts design to a single region, data flows to a second platform in the same area such as Amazon S3, on-premises MinIO or Ceph, or Filestore. Cloud External Key Manager (Cloud EKM) encrypts BigQuery and Compute Engine data with keys outside Google infrastructure, while customer-supplied encryption keys protect Cloud Storage and Compute Engine data with per-operation keys.

3.3Native Automated Backups and RPO and RTO Alignment

Native automated backup features create recoverable copies without custom code. Cloud SQL uses automated backups plus binary logging to support point-in-time recovery (PITR), which restores to a fresh instance. Cloud SQL can also use high availability (HA) and cross-region replicas, and Enterprise Plus edition uses advanced disaster recovery with zero data loss after cross-regional failover. Spanner Backup and Restore provides consistent backups retained up to 1 year, and restore mounts the backup without copying data, giving lower RTO than export. Spanner also provides PITR up to 7 days in the past. BigQuery long-term storage drops table storage price by 50 percent if the table is not edited for 90 consecutive days.

RPO is maximum acceptable data loss in time, so more frequent backups produce smaller RPO. RTO is maximum acceptable time to restore service, so restore path and standby capacity determine recovery speed. AlloyDB for PostgreSQL provides continuous backup by default for near-zero RPO, with PITR at microsecond granularity in a window of 1 to 35 days and default of 14 days. AlloyDB creates one daily incremental backup and restores to a new cluster in the same region. Discrete on-demand full backups and scheduled incremental backups are immutable and retained up to 1 year, with default 14 days for automated backups.

3.4Defining Targets and Validating Recovery Strategies

Defining RTO and RPO targets starts with grouping applications by criticality and outage scope. Tier 1 systems such as real-time payments need RTO zero and RPO zero for zone and region outages. Tier 2 systems such as regional CRM need about 15 minutes for zone outages and 1 hour for region outages. Tier 3 systems such as back-office tools tolerate 1 hour for zone outages and 12 hours for region outages. Business-critical operations must depend only on data plane components that continuously process operations, not on management-plane APIs that may be unavailable during recovery.

High availability survives zone failures within one region with synchronous replication and zero or near-zero RTO and RPO. Disaster recovery survives regional outages across two or more regions with typically asynchronous replication, higher RTO and RPO, and manual or semi-automated failover. Validation regularly proves backups restore within targets. A complete drill tests backup schedules, data transfer completion, restore speed and correctness, security controls in the recovery environment, and network routing including DNS and load balancing. Safe testing restores in an isolated non-production environment in order: first run consistency checks after backup, then restore isolated from production, then bring up services and measure RTO and RPO. Backup frequency must be equal to or more frequent than RPO.

3.5Implementing PITR and Retention Controls

Implementing PITR enables continuous change logs so a database restores to a specific timestamp between backups. In all cases PITR depends on uninterrupted log retention, and restore creates a new instance or cluster rather than overwriting the source. Cloud SQL PITR replays logs on top of a backup to create a new clone and requires automated backups, binary logging, a daily backup start time in UTC, and a Days of logs setting. Spanner PITR recovers data up to 7 days in the past for logical errors such as bad writes. AlloyDB PITR restores an active cluster from any point in the recovery window into a new cluster, and is blocked until the first new backup exists if continuous backup was disabled and re-enabled.

Transaction log retention in Cloud SQL is controlled by transactionLogRetentionDays, with 1 to 7 days and default 7 days for Enterprise edition and 1 to 35 days and default 14 days for Enterprise Plus edition. When retention is increased, the window grows by one day per day until it reaches the new value. Effective on-disk retention is the minimum of transactionLogRetentionDays and the expire_logs_days or binlog_expire_logs_seconds flag. Retention governance for BigQuery uses Knowledge Catalog tags for retention_period and expiration_action of archive or purge. Purge creates a snapshot for short recovery then expires the source, while archive exports to Cloud Storage in Parquet as a BigLake table then drops the source.

3.6Engine-Specific Export, Import, and Transfer Permissions

Engine-specific import and export matches each database to its utility and file format. Cloud Spanner uses Dataflow jobs that read from and write to Cloud Storage with Avro or CSV plus JSON manifests. A Spanner Avro export folder contains spanner-export.json, per-table manifests, Avro data files, and change-stream manifests. To import, the operator selects the export folder, enters a new database name that does not already exist, and chooses GoogleSQL or PostgreSQL dialect. CSV import requires pre-created tables and a manifest mapping tables to files, while non-Spanner Avro import preserves more types and requires a spanner-export.json pointing to destination tables.

Firestore managed export and import authorize as the Firestore service agent and stage files in Cloud Storage. A same-project bucket works by default, while a cross-project bucket requires an explicit grant. An export of all entities without an entity filter cannot be used for selective restore or BigQuery loading. To keep BigQuery compatibility, the export must include kind names, and each kind becomes a table schema where each property becomes a column. BigQuery allows a maximum of 10,000 columns per table. Dataflow jobs require Cloud Spanner Viewer, Dataflow Worker, Storage Admin, Spanner Database Reader, and for imports Spanner Database Admin on the worker service account. Recommended Compute Engine quotas start at 200 CPUs, 200 IP addresses, and 50 TB of disk.

Optimizing cost and performance matches capacity to actual use, finds waste in spending trends, and chooses pricing that fits stable versus variable demand. Scaling up enlarges one instance, scaling out adds replicas or distributed copies, and monitoring connects forecasts to scaling actions. Together rightsizing, autoscaling, and commitments protect performance while removing idle cost.

4.1Vertical Scaling and Instance Sizing in Cloud SQL

Vertical scaling in Cloud SQL (opens in a new tab) increases vCPUs, memory, disk size, throughput, and IOPS on one primary instance. Correct sizing matches resources to peak demand, because an undersized instance shows slow inserts, updates, deletes, and high memory pressure. Storage performance depends on disk size plus vCPU count working together. A larger disk provides higher throughput and IOPS, while more vCPUs let the instance use that performance, so CPU and storage must be scaled together with at least 20% free disk space for maintenance.

Table counts also limit upgrades, so keep below 500 databases per instance and fewer than 50,000 tables, or 500,000 tables only with 32 or more cores and 200 GB or more memory. For performance-intensive workloads, provide at least 60 GB of memory. Changing memory size requires a restart that drops connections, so applications need pooling, exponential backoff, small transactions, a maintenance window, and a disk-utilization alert. AlloyDB for PostgreSQL raises vertical limits with larger machine types and a data cache that improves reads.

4.2Horizontal Scaling with Replicas and Distributed Copies

Horizontal scaling moves read pressure off a single primary onto separate capacity. In Cloud SQL, application writes flow to the primary while reads flow to read replicas, so heavy reads no longer compete with writes. A cross-region replica can be promoted to become the new primary after a disaster, replicas can be configured for HA in advance, and cascading replicas protect the middle replica after promotion. Database sharding splits one database across instances by customer or subject to scale writes and reads, but it requires re-architecture and an even shard key to avoid hotspots.

A Spanner regional configuration maintains three read-write replicas in three zones, each holding a full copy. A Bigtable production instance requires two clusters in unique zones. Spanner scales reads, writes, and storage together by adding nodes and processing units, where 1000 PUs equals one node and storage allotment is 1024.0 GiB per 100 PUs below 1 node and 10 TiB per node at larger sizes. Choose Cloud SQL replicas to isolate reads from one relational primary, Spanner for distributed read-write capacity, and Bigtable for zonal clusters with managed copies.

4.3Regional Trade-offs and Replication Operations

Regional versus multi-regional deployment trades lower cost and latency against survival of region loss. A regional deployment places primary and standby in different zones of one region, so traffic stays inside with low latency and no cross-region cost. A multi-regional deployment spreads tiers across at least two regions, so the system withstands zone and region outages but pays for extra capacity, replication, and networking. High availability inside one region typically uses synchronous replication with zero or near-zero RTO and RPO and automatic failover. Disaster recovery across regions typically uses asynchronous replication with higher RTO, nonzero RPO, and promote-and-failover steps.

Cross-region replication in AlloyDB links a primary cluster to a secondary cluster through asynchronous WAL streaming without waiting for secondary confirmation. The secondary normally lags slightly, and that lag determines RPO and affects RTO. A new secondary starts with one instance and no read pools, and later primary changes including read pools must be added manually. WAL retention is controlled by wal_keep_size with default 100MB and max_wal_size with default 1504MB, where max_wal_size must be set higher than wal_keep_size. If a secondary falls behind by more than wal_keep_size, the primary may remove a needed segment and terminate replication. Promotion converts the secondary to an independent primary when the primary region is unavailable, while switchover moves writes in a controlled order when both regions are healthy.

4.4Proactive Monitoring and Peak Capacity Planning

Proactive monitoring keeps capacity ahead of demand by turning history into forecasts and scaling policies. Forecasting starts with user count and request rate, then adds historical trends, seasonal variation, special-event spikes, and business growth. That forecast flows into a capacity estimate shaped by managed instance group size, machine type, disk count and size, plus N+1 redundancy. Cloud Monitoring collects CPU, memory, disk, connections, replication lag, and transaction rates and provides Cloud SQL monitoring and Query Insights dashboards. Benchmarking validates estimates with load tests under normal and peak conditions.

Automated scaling acts on those signals. Enabling automatic storage increases lets Cloud SQL grow as data grows, while stateless managed instance group autoscaling adds virtual machines when traffic rises. Stateful groups cannot be autoscaled and depend on manual planning. For AlloyDB, transactions per second is the primary sizing metric rather than IOPS, with a target of 60-70% steady-state CPU to leave margin for spikes. During AlloyDB scale operations, the primary has sub-second downtime and read pool nodes have zero downtime. Only the first Cloud SQL scale-down in a three-hour period gets near-zero downtime.

4.5Cost Visibility, Pricing Models, and Query Efficiency

Cost visibility turns raw spend into trends by owner and workload. Cloud Billing reports show usage and invoice details for every project, and billing exports to BigQuery enable custom analysis by project, service, label, or time. Recommender and Active Assist suggest smaller types, flag idle resources, and flag low-utilization virtual machines at no cost. Labels as key-value pairs for team, environment, or cost center allow filtering and chargeback. Cloud Billing budgets notify when spend crosses thresholds but do not stop resources.

Pricing models change the rate without changing resources consumed. Cloud SQL committed use discounts are spend-based commitments for vCPUs and memory in one region, billed monthly for the full term even if use drops. A 1-year commitment gives 25% discount and a 3-year commitment gives 52% discount. They apply to MySQL, PostgreSQL, and SQL Server including Enterprise and Enterprise Plus, but never to shared-CPU types, storage, backups, IP addresses, transfer, or licensing. Query Insights measures query load as CPU seconds per minute and surfaces expensive fingerprints in a TopN table limited to 100 entries. Strategic indexing lowers I/O by avoiding full scans, while removing unused indexes reduces write cost. Partitioning prunes scans, routing reads to replicas offloads the primary, and vertical scaling relieves urgency but does not reduce per-query scan cost.

Automating common database tasks uses schedulers, serverless functions, and managed maintenance to keep databases healthy without manual work. Data flows from a scheduler trigger to an export, vacuum, upgrade, or monitoring workflow, then to storage or a new instance state. Automation preserves performance by offloading work and applying changes during low-traffic windows.

5.1How Exports Differ from Backups and Maintenance

Data export and archival in Cloud SQL (opens in a new tab) moves database contents into portable files in Cloud Storage for reuse, copying, and long-term retention. An export creates an external file that can recreate data, copy a single database or table, and remain available even if the source instance is deleted. Backups and point-in-time recovery instead support fast operational restore of the instance itself. Exports are therefore the choice when data must be portable, selective, or retained after cleanup.

Database health automation keeps PostgreSQL-compatible databases fast through engine cleanup and controlled platform updates. Standard VACUUM reclaims dead tuples while running in parallel with SELECT, INSERT, UPDATE, and DELETE, but it blocks definition changes. VACUUM FULL reclaims more space but exclusively locks the table and runs slowly, so standard VACUUM is preferred for routine automation. Tuning increases maintenance_work_mem, reduces write-ahead log volume, raises vacuum_freeze_min_age toward 1,000,000,000 when nearing the transaction ID limit, and manages index cleanup. In Cloud SQL for SQL Server, maintenance measures fragmentation with sys.dm_db_index_physical_stats and runs ALTER INDEX ... REORGANIZE, while SQL Server stores data in 64 KB extents of eight 8 KB pages.

5.2Scheduled Export Architecture and Failure Handling

Scheduled exports use Cloud Scheduler to start work on a timetable, Pub/Sub to deliver the trigger, and Cloud Run functions to run export logic. Data flows from Cloud Scheduler to a Pub/Sub topic to a function, then from the database to a Cloud Storage bucket. The same pattern is recommended for Firestore in Datastore mode and suggested for Cloud SQL, which does not provide built-in automated exports. The project must have billing enabled, the destination bucket must exist near the database location, and a Requester Pays bucket cannot be used. Serverless exports in Cloud SQL offload the export to a temporary instance that is deleted when the export completes, so the primary keeps serving queries.

The end-to-end flow preserves efficiency because no server runs between exports:

1. Schedule fires: Cloud Scheduler publishes the configured payload to the Pub/Sub topic at the cron time.
2. Trigger delivers: Pub/Sub delivers the message to the export function.
3. Export starts: The function decodes the payload and calls the export operation with bucket and filters.
4. Data lands: The database service writes timestamp-organized output files to the Cloud Storage bucket.

Export monitoring tracks each job by dataExportId in order from IN_QUEUE to PROCESSING to either FINISHED_SUCCESS or FINISHED_FAILURE. Verification must continue through function logs, long-running operations, and destination files because scheduler success only confirms delivery to Pub/Sub. Storage configuration controls cost and protection through lifecycle rules, retention policies, versioning, uniform bucket-level access, and blocked public access. Archival tiers move files from higher-cost storage to Nearline, Coldline, and Archive as access likelihood decreases.

5.3Upgrades, Maintenance Timing, and Validation

A valid upgrade target is a major version that Cloud SQL lists as upgradable for the specific instance. Operators run gcloud sql instances describe and locate upgradableDatabaseVersions. Cloud SQL for MySQL does not support skipping major versions, so an instance must move one major at a time, while AlloyDB for PostgreSQL can upgrade directly from POSTGRES_14 to POSTGRES_15 through POSTGRES_18. Pre-upgrade checks review release notes for breaking changes, verify settings such as PostgreSQL LC_COLLATE of en_US.UTF8, drop unsupported extensions, and validate on a clone during low activity.

In-place upgrades update the existing instance without creating a new instance, so name, IP address, and settings are preserved. When a single-instance upgrade starts, each step depends on the prior step:

1. Check configuration: Cloud SQL verifies compatibility and fails fast if not ready.
2. Make unavailable: Cloud SQL makes the instance unavailable to connections.
3. Pre-upgrade backup: Cloud SQL takes an on-demand backup of the prior state.
4. Perform upgrade: Cloud SQL upgrades binaries and system objects in place.
5. Make available: Cloud SQL makes the instance available for new writes.
6. Post-upgrade backup: Cloud SQL takes an on-demand backup after writes are allowed.

An upgrade with replicas upgrades the primary first, restarts replication, then upgrades all replicas. Maintenance timing controls when updates land so downtime hits low traffic. Timing selects the rollout week after notification, the window selects a one-hour slot, and the deny period blocks maintenance for up to 90 days. If no window is set, Cloud SQL uses 10 PM to 6 AM on weekdays and Friday 10 PM to Monday 6 AM on weekends. Week 1 updates occur 7 to 14 days after notification, Week 2 at 15 to 21 days, and Week 5 at 35 to 42 days. Post-upgrade validation runs acceptance tests, verifies reconnects and transactions, and reviews CPU, memory, connections, and replication lag.

5.4Defining SLIs, SLOs, and Alerting Coverage

A service-level indicator measures service performance from the user's point of view, a service-level objective is the target value over a compliance period, and the error budget starts at 1 minus the objective and shrinks as performance misses the goal. In Google Cloud, Cloud Monitoring collects metrics that become indicators, and up to 500 objectives can be created per service. An availability indicator is successful responses divided by all responses, while a latency indicator is calls below a threshold divided by all calls. An objective combines indicator, goal such as 99% availability, and period such as calendar WEEK or rolling 259200 seconds. The highest objective that can be set is 99.9%.

Request-based indicators count individual successful requests out of all requests and work well for high-volume work. Windows-based indicators count time windows where performance meets a threshold and work better for low-traffic or bursty workloads. Database indicators build on latency, traffic, error rate, and saturation. Alerting watches burn rate and notifies channels before a service-level agreement breach, with calendar periods resetting at each boundary and rolling periods continuously dropping old points. SLA coverage for Cloud SQL excludes outages outside Google's control and requires high availability with at least one dedicated CPU, so shared-core and single-zone instances are not covered. Coverage is also removed by storage-full conditions, sustained high CPU or memory, transaction ID wraparound, and risky flags. Root cause analysis correlates trends with Cloud Logging, Cloud Trace, and Cloud Profiler.

Key Facts

• IAM database authentication for AlloyDB and Cloud SQL uses short-lived OAuth 2.0 tokens valid for one hour and requires an SSL or TLS 1.3 connection, with automatic authentication refreshing tokens for long-lived processes.
• A Cloud SQL instance supports a maximum of 200 IAM groups, Cloud Identity membership changes take about 15 minutes to propagate, and using the Cloud SQL Auth Proxy requires both cloudsql.instances.connect and cloudsql.instances.login.
• Admin Activity audit logs are always written and cannot be disabled, while Data Access audit logs must be explicitly enabled, and engine logs such as pgAudit capture SQL operations inside the database.
• AlloyDB continuous backup provides near-zero RPO with point-in-time recovery from 1 to 35 days, Spanner Backup and Restore retains backups up to 1 year, and Cloud SQL Enterprise Plus allows 1 to 35 days of transaction log retention.
• High availability uses synchronous replication within one region with zero or near-zero RTO and RPO, while cross-region disaster recovery typically uses asynchronous replication with promotion and higher RTO and RPO.