ApsaraDB RDS:Troubleshoot high CPU utilization of ApsaraDB RDS for PostgreSQL

Method 1: Use the pg_stat_statements extension

The pg_stat_statements extension tracks cumulative execution statistics for all SQL statements. Because it accumulates data over time, reset its counters before troubleshooting so the results reflect only recent activity.

1. If the extension is not already installed, create it and reset the counters:

   CREATE EXTENSION pg_stat_statements;
   SELECT pg_stat_reset();
   SELECT pg_stat_statements_reset();

2. Wait about one minute for the counters to accumulate data.

3. Find the queries that consume the most total execution time:

   SELECT *
   FROM pg_stat_statements
   ORDER BY total_time DESC
   LIMIT 5;

4. Find the queries that read the most shared buffer blocks. High buffer reads typically indicate missing indexes — the database scans far more data than needed:

   SELECT *
   FROM pg_stat_statements
   ORDER BY shared_blks_hit + shared_blks_read DESC
   LIMIT 5;

To find queries that are slow per individual call (rather than in aggregate), also sort by mean_exec_time DESC. This surfaces queries with a high average runtime that may not rank near the top by total time.

After identifying the problematic queries, go to Step 3 to resolve the issue.

Method 2: Use the pg_stat_activity view

The pg_stat_activity view shows all current database sessions and their queries. Run the following query to find queries that have been running for an unusually long time:

SELECT datname,
       usename,
       client_addr,
       application_name,
       state,
       backend_start,
       xact_start,
       xact_stay,
       query_start,
       query_stay,
       replace(query, chr(10), ' ') AS query
FROM (
    SELECT pgsa.datname AS datname,
           pgsa.usename AS usename,
           pgsa.client_addr AS client_addr,
           pgsa.application_name AS application_name,
           pgsa.state AS state,
           pgsa.backend_start AS backend_start,
           pgsa.xact_start AS xact_start,
           extract(epoch FROM (now() - pgsa.xact_start)) AS xact_stay,
           pgsa.query_start AS query_start,
           extract(epoch FROM (now() - pgsa.query_start)) AS query_stay,
           pgsa.query AS query
    FROM pg_stat_activity AS pgsa
    WHERE pgsa.state != 'idle'
      AND pgsa.state != 'idle in transaction'
      AND pgsa.state != 'idle in transaction (aborted)'
) idleconnections
ORDER BY query_stay DESC
LIMIT 5;

After identifying the problematic queries, go to Step 3 to resolve the issue.

Method 3: Find tables with missing indexes

Without indexes, the database performs a sequential scan and reads every row to find matching results. For example, if the database has 8 GB of memory and 6 GB of hot data resides in memory without indexes, sequential scans process large amounts of irrelevant data and drive up CPU usage. For tables with more than 100,000 rows, a single sequential scan can consume nearly all available CPU — and the problem compounds when hundreds of connections run such scans at the same time.

1. Find the tables with the most sequential scans:

   SELECT *
   FROM pg_stat_user_tables
   WHERE n_live_tup > 100000
     AND seq_scan > 0
   ORDER BY seq_tup_read DESC
   LIMIT 10;

2. Check for slow queries currently accessing those tables:

   SELECT *
   FROM pg_stat_activity
   WHERE query ILIKE '%<table name>%'
     AND now() - query_start > interval '10 seconds';

SELECT *
FROM pg_stat_statements
WHERE query ILIKE '%<table>%'
ORDER BY shared_blks_hit + shared_blks_read DESC
LIMIT 3;

After identifying the problematic queries, go to Step 3 to resolve the issue.

Terminate slow queries

After identifying the problematic queries in Step 2, terminate them to restore normal service. PostgreSQL provides two functions for this:

• pg_cancel_backend(): Gracefully cancels a query without closing the connection.

• pg_terminate_backend(): Forcibly terminates the entire backend process.

-- Gracefully cancel matching queries
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%<query text>%'
  AND pid != pg_backend_pid();

-- Forcibly terminate matching backend processes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%<query text>%'
  AND pid != pg_backend_pid();

Optimize slow queries

If the slow queries are necessary for your business, optimize them to prevent the issue from recurring.

1. Update table statistics. Run ANALYZE or VACUUM ANALYZE on the tables involved in the slow queries. Outdated statistics cause the query planner to choose inefficient execution plans. Run these commands during off-peak hours to minimize impact.

   Note

   Replace [$Table] with the name of the table related to the slow queries.

   ANALYZE [$Table];
   VACUUM ANALYZE [$Table];

2. Review the execution plan. Use EXPLAIN to examine how the database executes a query and identify whether it performs sequential scans on large tables that would benefit from an index.

   • View the execution plan without running the query:

     EXPLAIN [$Query_Text];

   • Run the query and view detailed execution statistics:

     EXPLAIN (BUFFERS TRUE, ANALYZE TRUE, VERBOSE TRUE) [$Query_Text];

   Note

   Replace [$Query_Text] with the SQL query you want to analyze.

   If the execution plan shows sequential scans on large tables, add indexes on columns used in WHERE clauses, JOIN conditions, or ORDER BY clauses.

3. Rewrite inefficient queries. Remove unnecessary subqueries from slow queries. To further improve performance, consider rewriting UNION ALL operations and using JOIN clauses for fixed join sequences.