A larger cache can reduce physical reads; it cannot repair a missing index, remove a single-writer ceiling, or make an asynchronous replica strongly consistent. Cloud SQL performance work starts by identifying the constrained resource.

Situation

A PostgreSQL workload has rising read latency as its active data set grows. The team can increase memory, use Cloud SQL Enterprise Plus with Data Cache, add read replicas, or move analytical work elsewhere. All four choices can be reasonable, but they address different bottlenecks.

The first task is measurement: query plans and latency distributions, buffer activity, storage latency and throughput, CPU, memory pressure, connections, locks, and replica lag. “The database is slow” is not a capacity model.

The Problem

Rules such as “upgrade when cache hit ratio falls below 90%” are unsafe. PostgreSQL cache-hit ratios differ by workload, and sequential scans can be efficient. Separate PostgreSQL shared-buffer observations from Cloud SQL Data Cache evidence: SQL statistics such as pg_statio_user_tables describe PostgreSQL buffer activity, while Cloud Monitoring exposes the GA database/postgresql/data_cache/hit_ratio metric for the local-SSD cache.

Adding replicas also does not fix a slow query. It creates more read capacity only when traffic can be routed to read-only endpoints and tolerate asynchronous visibility. The core question is: is the workload limited by execution, memory, storage reads, concurrency, or write serialization?

A Performance Decision Model

flowchart TD
    Slow[Latency regression] --> Plans[Compare query plans and waits]
    Plans --> CPU{CPU or execution bound}
    Plans --> IO{Storage read bound}
    Plans --> Conn{Connection or lock bound}
    CPU --> Tune[Tune SQL and indexes]
    IO --> Cache[Evaluate memory or Data Cache]
    Conn --> Pool[Bound pools and reduce contention]
    Tune --> Replica[Add replicas only for separable reads]
    Cache --> Replica

Enterprise Plus is a Cloud SQL edition with different machine families and availability features. For eligible Enterprise Plus configurations, Data Cache uses local SSD as an additional read cache for PostgreSQL data pages. Google reports up to four times read-performance improvement for its tested workloads. “Up to” is neither a latency guarantee nor evidence that a particular application will benefit.

Data Cache helps when repeat reads miss memory but have useful locality in the local SSD tier. It is less useful when queries scan data once, CPU dominates, indexes are absent, connections are saturated, or the write path is the constraint. Local cache contents are not durable state; cache warm-up and lifecycle events must be included in testing.

In Practice

Use a before-and-after protocol:

  1. Capture a representative workload, not only one favorable query.
  2. Record EXPLAIN (ANALYZE, BUFFERS) for high-impact statements.
  3. Measure p50, p95, and p99 latency, CPU, storage reads, locks, and throughput.
  4. Record the Cloud Monitoring Data Cache hit-ratio, hit-count, and miss-count metrics after the cache has warmed; use PostgreSQL statistics separately for shared-buffer behavior.
  5. Repeat after documented cache-erasing events such as planned maintenance, an unexpected shutdown, a major-version upgrade, or a machine-type change.
  6. Compare total cost and application SLOs, not only database IOPS.

An explicit Terraform configuration can record intent:

resource "google_sql_database_instance" "postgres" {
  name             = "production-postgres"
  database_version = "POSTGRES_15"
  region           = "us-central1"

  settings {
    edition = "ENTERPRISE_PLUS"
    tier    = "db-perf-optimized-N-8"

    data_cache_config {
      data_cache_enabled = true
    }
  }
}

Validate the provider schema, tier availability, edition-conversion path, downtime, and storage changes when the plan is executed. Data Cache availability and defaults depend on edition, machine type, and current service rules; keep the configuration explicit and verify the generated plan.

Read replicas are a separate lever. Route only read-only requests that accept replica lag. Define a primary fallback policy, monitor lag, and test replay conflicts for long PostgreSQL queries. A new or restarted replica may be cold, so introduce traffic gradually and measure it rather than asserting a fixed warm-up time.

Preserve a control case for every upgrade. Test the same workload on a right-sized Enterprise instance, an Enterprise Plus instance with Data Cache disabled, and the intended configuration with Data Cache enabled. Without those controls, a machine-family or memory change can be mistaken for a cache improvement. Hold connection count, data snapshot, client placement, and warm-up procedure constant, and report confidence intervals across repeated runs rather than selecting the fastest sample.

Include write-side regression checks even when the goal is read performance. A configuration that improves dashboard latency but increases transaction commit latency, replica lag, or maintenance recovery time has shifted the cost rather than removed it.

Where It Breaks

Failure modeWhyResponse
Full scans evict useful pagesQuery or index design dominates cache capacityFix access paths before buying another cache tier.
Cache improves averages but not tail latencyCold reads or lock contention dominate p99Measure waits and cold-cache events separately.
Replica returns stale stateReplication is asynchronousKeep correctness-sensitive reads on the primary or design an explicit consistency token.
Replica query is canceledReplay conflicts with a long-running readBound query duration or isolate analytics outside the replica path.
More connections erase the gainPer-session work and memory saturate computeUse bounded pooling and admission control.

What to Do Next

  • Problem: Scaling choices are being made from a single cache ratio or vendor benchmark.
  • Solution: Classify the bottleneck, then test Data Cache, larger memory, replicas, or query changes against the same workload.
  • Proof: Google describes Data Cache as a local-SSD read cache and publishes bounded, workload-dependent results rather than a universal multiplier.
  • Action: Produce a performance report with baseline plans, latency percentiles, resource saturation, Data Cache measurements, replica lag, and total cost.

Sources to Verify