Azure PostgreSQL相同查询时而秒级完成，突然超时无响应，求排查方案

Hey Doug, let's walk through a structured troubleshooting checklist tailored to your Azure PostgreSQL scenario, and break down why that normally fast SELECT query suddenly started timing out.

Step 1: Verify If Execution Plans Changed (Stats or Cache Issues) #

Even if your total data volume hasn't changed, stale statistics or lost cache can wreck query performance:

• Run EXPLAIN ANALYZE your_query; (if you can let it run long enough) or EXPLAIN your_query; to compare the current execution plan to what you'd expect (e.g., is it suddenly doing a full table scan instead of using an index?).
• Force an update of table statistics with ANALYZE your_target_table; (or ANALYZE; for all tables). Autovacuum usually handles this, but if it's been dormant, stats can get outdated and lead to bad plan choices.
• Check cache hit ratio with this query:

  SELECT 
    round((sum(heap_blks_hit)::numeric / (sum(heap_blks_hit) + sum(heap_blks_read))) * 100, 2) AS cache_hit_ratio
  FROM pg_statio_user_tables;
  

  A drop below 90% means your query is hitting disk far more than usual—this could happen if the server restarted, failed over, or cache was evicted.

Step 2: Dig Into Azure-Specific Storage & IO Metrics #

Since this is Azure-hosted PostgreSQL, storage constraints are a common hidden culprit:

• Navigate to your Azure PostgreSQL server's Metrics tab (right next to Overview). Look for these key metrics:
  • Disk Queue Depth: A value consistently above 2 indicates IO saturation—your query is waiting on disk operations.
  • Data IO Read/Write Bytes/sec: Compare these to your SKU's baseline and burst IOPS limits (you can find these in your server's Pricing tier settings). If you've exhausted burst IOPS, queries will throttle to baseline speeds.
• Even with 40% storage used, check if individual tables have bloated (due to unvacuumed dead tuples) — this makes tables/indexes larger than necessary, increasing IO load.

Step 3: Check for Locks & Waiting Events #

Your pg_stat_activity check for active queries is good, but we need to dig deeper into waits:

• Run this to see what your query is actually waiting on:

  SELECT pid, query, state, wait_event_type, wait_event 
  FROM pg_stat_activity 
  WHERE state != 'idle' AND query LIKE '%your_query_keyword%';
  

  Common waits to watch for: IO (disk bottleneck), Lock (waiting on a table/row lock), or BufferPin (contention for memory buffers).
• Check for pending locks with:

  SELECT * FROM pg_locks WHERE NOT granted;
  

  Even an idle-in-transaction process (from a forgotten connection) can hold locks that block your query.

Step 4: Audit Autovacuum Health (Since You Suspect This) #

Your autovacuum stats show it hasn't run since Feb 2—let's confirm why and fix it:

• First, check if autovacuum is enabled and configured correctly:

  SHOW autovacuum;
  SHOW autovacuum_vacuum_threshold;
  SHOW autovacuum_analyze_threshold;
  

  If autovacuum is off, that's a problem. If thresholds are set too high (e.g., only trigger after 10k dead tuples), it might not run even if there's bloat.
• Check dead tuple counts per table:

  SELECT schemaname, relname, n_dead_tup, last_autovacuum 
  FROM pg_stat_user_tables;
  

  High n_dead_tup values mean your tables have bloated—this makes scans slower and can even break index efficiency.
• Check Azure's Server logs tab for autovacuum errors (search for "autovacuum" keywords). Sometimes Azure's resource limits can block autovacuum from running.

Step 5: Rule Out Azure Maintenance or Configuration Changes #

• Head to your server's Activity log and filter for the last 24 hours. Look for:
  • Planned maintenance events (Azure occasionally patches servers, which can restart the instance or clear cache).
  • Failover events (if your server is in a high availability group, failovers can reset cache and cause initial query slowness).
  • Any unexpected configuration changes (even a small tweak to work_mem or shared_buffers can impact performance).

Why Your Query Suddenly Timed Out: Likely Root Causes #

Putting this all together, the most probable reasons are:

1. Stale Statistics & Bad Execution Plans: Without autovacuum updating stats, PostgreSQL might have switched from an efficient index scan to a slow full table scan.
2. Table/Index Bloat: Unvacuumed dead tuples have bloated your tables, forcing the query to scan far more data than necessary.
3. IO Bottlenecks: Exhausted Azure storage burst IOPS, or temporary disk contention, made the query wait on disk operations.
4. Cache Invalidation: A server restart, failover, or cache eviction meant the query had to read everything from disk instead of using cached data.

内容的提问来源于stack exchange，提问作者Doug Fir