Fixing PostgreSQL “Batch Query Latency Spike from 20ms to 1s” – Default Fillfactor Breaking HOT Updates under Heavy UPSERT

Environment: Google Cloud SQL (PostgreSQL), OLTP workload with ~1k UPSERTs/sec, 50M rows (scaling to 1B).

1. The Incident

Our Slack monitoring channel exploded at 02:00 AM. A critical batch SELECT on a core table, which usually hums along at 20ms, was suddenly clocking in at 500ms to 1 second, trending upward. No complex JOINs, no aggregations—just a range scan on the leading column of the composite PK.

We SSH into the primary container and immediately ran our standard top-level check:

top -p $(pgrep -d',' postgres)

CPU was idle (~15%). iostat -x 1 showed minimal await times on the disk. The bottleneck wasn’t CPU or raw storage bandwidth—it was logical I/O fragmentation.

2. Root Cause: The Heap Scatter

The schema is deceptively simple:

CREATE TABLE high_update_table (
  pk_part1 bigint,
  pk_part2 bigint,
  value text,
  updated_at timestamptz,
  PRIMARY KEY (pk_part1, pk_part2)
);

The workload issues ~1,000 INSERT ... ON CONFLICT statements per second, updating value and updated_at. The read pattern is:

SELECT * FROM high_update_table WHERE pk_part1 = ? AND pk_part2 BETWEEN ? AND ?;

Our first concrete: We exec into the container and queried pg_stat_user_tables to sanity-check the heap efficiency.

SELECT heap_blks_hit, heap_blks_read, heap_hot_updates, n_tup_upd 
FROM pg_stat_user_tables 
WHERE relname = 'high_update_table';

We noticed heap_blks_read was astronomically high compared to heap_blks_hit (ratio ~0.3), and heap_hot_updates accounted for only 30% of total updates. We initially suspected a faulty SSD cache, but the disk metrics said otherwise.

Second concrete: We manually ran a parameterized query with EXPLAIN (ANALYZE, BUFFERS) against our test partition to force a cold read:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM high_update_table 
WHERE pk_part1 = 10000001 AND pk_part2 BETWEEN 5000 AND 6000;

The output revealed the killer:
Buffers: shared hit=120 read=850
For a logical range of ~1,000 rows, we were hitting 850 disk pages. The table was physically fragmented. Because fillfactor defaulted to 100%, every UPDATE forced the new tuple version onto a new page, scattering logically adjacent rows across the heap.

3. Phase 1: Fillfactor & Heap-Only Tuples (HOT)

We knew we had to enable HOT updates. The conditions were already satisfied (we don’t update indexed columns), but the pages had no room.

Third concrete: Before committing, we ran a VACUUM VERBOSE simulation in a staging environment to estimate the space reclaim, but we hit a dead end: VACUUM FULL blocks reads. We pivoted to pg_repack. We ran a test dry-run:

pg_repack --host=<host> --dbname=<db> --table=high_update_table --no-kill-backend --dry-run

We also monitored the progress in production using:

SELECT * FROM pg_stat_progress_cluster;

We applied the change without a full lock:

ALTER TABLE high_update_table SET (fillfactor = 80);

We then triggered a maintenance window using pg_repack (instead of VACUUM FULL) to reorganize the physical order without long-term locks.

The immediate result: We ran the pg_stat_user_tables check again. HOT ratio skyrocketed from 30% to 99.5%. Cold query latency dropped from 200–1000ms to 30–60ms. Hot cache reads dropped from 20–50ms to <2ms.

We later raised fillfactor to 90 to reduce space waste, as we observed 100% HOT efficiency.

4. Phase 2: Autovacuum Overhaul

Even with HOT, we couldn’t risk dead tuple buildup from the occasional index update or abort. The default autovacuum settings were far too lazy for this throughput.

We applied table-level tweaks to override the global slowness:

ALTER TABLE high_update_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000
);

On the instance, we modified postgresql.conf (adjusted via GCP flags since we are on Cloud SQL):

autovacuum_naptime = 15s
autovacuum_max_workers = 6

Monitoring output: We added a daily巡检 query to ensure dead tuples stayed low:

SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  (n_dead_tup::float / GREATEST(n_live_tup, 1)) * 100 as dead_pct
FROM pg_stat_user_tables 
WHERE relname = 'high_update_table';

After tuning, dead_pct consistently remained under 0.5%, rendering autovacuum almost idle.

5. Phase 3: Partitioning for Scale

We are projecting 1B rows. To keep the B-tree indexes lean and the HOT cache local, we implemented range partitioning on pk_part1:

CREATE TABLE high_update_table_partitioned (
  LIKE high_update_table INCLUDING DEFAULTS
) PARTITION BY RANGE (pk_part1);

CREATE TABLE high_update_table_p1 PARTITION OF high_update_table_partitioned
  FOR VALUES FROM (MINVALUE) TO (100000000);

We pointed the application to the parent table. Partition pruning ensures each query only hits a 50M–100M row segment, further reducing random I/O churn.

6. The MySQL Consideration (and Why We Stayed)

There was internal debate about migrating to MySQL for its clustered primary key (InnoDB) to avoid the heap fetch entirely. We provisioned a MySQL replica and ran the same UPSERT workload.

Fourth concrete排查动作: We monitored SHOW ENGINE INNODB STATUS and vmstat during the stress test.
We observed the secondary index maintenance overhead spiked CPU usage to 90% (compared to PG’s 40%), specifically due to the mandatory back-reference updates in the clustered index. The “free lunch” of the clustered PK wasn’t worth the write amplification. We dropped the migration plan.

7. Cloud-Specific Layer (GCP Cloud SQL)

We are on Google Cloud SQL. We noticed that even with HOT optimized, cold reads remained around 60ms. We traced this to network-attached persistent disks (PD-SSD).

We executed three infrastructure changes:

1. Resized disk: 500GB → 1.5TB (GCP scales IOPS linearly with capacity).
2. Networking: Enabled Private IP to reduce network hops between the application and the DB.
3. Tier upgrade: Moved to the Cloud SQL “Plus” tier, which adds a local SSD cache layer.
   Cold read latency dropped below 30ms post these adjustments.

8. Final Configuration & Production Checklist

The final architecture isn’t a magic bullet but a combination:

• Table configuration:sqlALTER TABLE high_update_table SET (fillfactor = 90);
• Application logic: Added a pre-check (compare old vs new value) to avoid meaningless UPDATE rewrites, ensuring HOT eligibility.
• Index strategy: No covering indexes (avoided write amplification); relying solely on the PK for range scans.
• Buffer pool: shared_buffers = 25% of total RAM; effective_cache_size = 75% of RAM.

Production Go-Live Checklist (what we signed off on):

• □ Confirm zero long-running transactions (SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction' AND age(now(), state_change) > interval '5 minutes').
• □ Validate HOT ratio baseline (>95%) using pg_stat_user_tables.
• □ pg_repack installed and tested with --no-kill-backend.
• □ Partitions pre-created for the next 6 months.
• □ Monitoring alarms set for dead_pct > 5%.

Two-Week Result:

• HOT update rate: 99.2%
• Table bloat: <5% (confirmed via pg_total_relation_size vs actual data size).
• P95 Cold read: <50ms; P95 Hot read: <2ms.