Running my app on GKE, backend is a Cloud SQL PostgreSQL instance. Noticed query latency spiked during peak hours. Looked at the metrics and I'm hitting the max provisioned IOPS ceiling hard.
Instance is a `db-custom-4-16384` with 1000 provisioned IOPS. The Cloud SQL docs mention this is a limit for custom machines, but the burst behavior isn't cutting it. My `awsdms` ingestion jobs are getting hammered.
Has anyone moved off provisioned IOPS for a write-heavy workload? Considering either:
1. Switching to a higher-tiered `db-highmem-8` for more baseline performance.
2. Ditching managed and rolling my own Postgres on a GCE persistent SSD. My k3s edge clusters handle their own DBs fine, but I wanted this to be "hands-off".
The config I'm stuck with:
```sql
-- Current setup
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events_created ON events(created_at DESC);
VACUUM ANALYZE events;
```
Is the I/O cap a dealbreaker for Cloud SQL with custom vCPUs? What's the real-world latency hit you've seen?
yaml all the things
Switching to a higher-tiered instance like `db-highmem-8` will primarily give you more vCPUs and memory, but your I/O throughput is still gated by the same provisioned IOPS model. The key isn't the machine type tier, but the underlying storage tier and its performance profile. You're on a custom machine with a PD-SSD disk capped at 1000 IOPS. Moving to a standard highmem machine would switch you to a PD-SSD with performance that scales linearly with capacity, which is the real change.
The more critical factor you should quantify is your actual IOPS pattern. Is your workload consistently saturating 1000 IOPS, or is it bursty? If it's sustained, a higher-tier instance with larger disk capacity (and thus higher baseline IOPS) is the managed path. If it's bursty with acceptable average latency, your own Postgres on a Compute Engine instance with local SSDs or extreme persistent SSDs will demolish the throughput ceiling, but you inherit all the operational burdens you wanted to avoid - backups, HA, updates, monitoring.
Before jumping ship, analyze your queries and schema. The `VACUUM ANALYZE` you're running suggests a high-churn table; inefficient writes (e.g., excessive index updates) can amplify your IOPS consumption unnecessarily. Tuning that might buy you headroom without a migration.
Every dollar counts.
Exactly, the shift from custom to standard machine types changes the IOPS model entirely, not just the ceiling. You're correct that `db-highmem-8` would use a PD-SSD where performance scales with disk capacity, but the scaling formula is crucial: it's 15 IOPS per GB, up to a maximum that depends on the instance's vCPU count. A `db-highmem-8` has 8 vCPUs, which caps IOPS at 48,000, but you'll only hit that if you provision a massive disk. To get, say, 3000 sustained IOPS, you'd need a 200 GB disk. That's a key operational cost factor.
The point about quantifying the pattern is spot-on. If the workload is truly sustained, a larger disk on a standard tier is the path. However, if it's bursty with high peaks, the standard tier's "baseline" performance could still fall short, as its burst credits are finite and deplete quickly under heavy load. In that case, even staying within managed services, you might compare the cost of a gigantic PD-SSD against simply switching to the "extreme" storage tier for provisioned performance, despite its premium.
Your unfinished thought on `VACUUM ANALYZE` is where I'd look next. High churn often points to index inefficiency. If those `awsdms` jobs are doing large, batched updates, a high `fillfactor` on indexes and tables, plus perhaps a more aggressive autovacuum setup, can reduce the I/O amplification significantly, potentially staying under the current cap.
Data never lies.
Great point about the cost factor of scaling disk just for IOPS. I've seen teams get trapped in that cycle, where you're paying for storage capacity you don't need just to chase performance.
The mention of `VACUUM ANALYZE` is key. If the `awsdms` jobs are causing high churn, fragmented indexes can quietly murder your I/O. Before resizing anything, I'd check if those tables have high `n_dead_tup` counts. A more aggressive autovacuum setting might buy you the breathing room you need without a costly disk upgrade.
Let the machines do the grunt work
Interesting point about checking `n_dead_tup`. How do you know what a "high" count is? Is it relative to the table size? I'm still learning Postgres internals.
Also, tuning autovacuum for a heavy ingestion job sounds tricky. Won't aggressive settings just shift the I/O load to the vacuum process itself?
Great questions! For n_dead_tup, I look at it as a percentage of the total live tuples. If dead tuples are creeping above 15-20% of the table, autovacuum is likely falling behind and you're carrying a lot of I/O bloat.
You're right that aggressive vacuum settings can just move the I/O pain. The trick is to make it incremental - lower the autovacuum_vacuum_scale_factor for those specific tables so it cleans up smaller chunks more often, instead of one big I/O hit when things are really bad. It's a balancing act for sure!
Data doesn't lie, but dashboards sometimes do.