High CPU usage on your instance

What you see

Everything gets slower at once: API requests take seconds, Studio is sluggish, Auth sign-ins time out, and requests that were fast yesterday now hit canceling statement due to statement timeout. Every project on the instance is affected, because they share the VM.

Why it happens

All projects on an instance share its vCPUs (2 on Builder and Pro, 4 on Business). Builder's vCPUs are shared-core, so sustained heavy load is throttled sooner than on the dedicated Pro and Business VMs. The usual causes:

• Slow queries running often. A sequential scan on a large table, an RLS policy that does a subquery per row, or a missing index on a filter column. One such query called on every page view can use a full core.
• Long or stuck queries. A runaway report or a bad join that runs for minutes.
• Password sign-ins. Hashing passwords is deliberately expensive. Measured on SupaHost: about 19 sign-ins per second on Builder, 18 on Pro and 38 on Business. A bot hammering your sign-in or sign-up form can use most of the CPU.
• Postgres Changes on busy tables. Realtime checks each change against the RLS policies of every subscriber.
• Edge Functions doing heavy work, such as image processing or big JSON transforms.

How to fix it

1. See what's running now, across every project on the instance. Run this in Studio's SQL editor:

   select pid, datname, usename, state,
          now() - query_start as running_for,
          wait_event_type,
          left(query, 120) as query
   from pg_stat_activity
   where state <> 'idle' and pid <> pg_backend_pid()
   order by query_start;
   

   datname tells you which project (p_<project>). Rows with a long running_for and no wait_event_type are using CPU.

2. Stop a runaway query. pg_cancel_backend cancels the current statement; pg_terminate_backend ends the session:

   select pg_cancel_backend(12345);
   

   The postgres role can always cancel its own sessions. If you get a permission error for a session owned by another role, cancel it from the client side instead.

3. Find the queries with the highest total cost. Check whether pg_stat_statements is installed:

   select extname, extnamespace::regnamespace as schema
   from pg_extension
   where extname = 'pg_stat_statements';
   

   If it returns a row, list this project's heaviest queries (prefix the view with the schema shown if it isn't found):

   select round(total_exec_time::numeric / 1000, 1) as total_seconds,
          calls,
          round(mean_exec_time::numeric, 1) as mean_ms,
          left(query, 150) as query
   from pg_stat_statements
   where dbid = (select oid from pg_database where datname = current_database())
   order by total_exec_time desc
   limit 15;
   

   If it returns nothing, check select * from pg_available_extensions where name = 'pg_stat_statements';. If you hit a privilege error, see insufficient privilege on pg_stat_statements.

4. Fix the top queries. Usually an index, a rewritten RLS policy or a cached result. See Speeding up slow queries with indexes and RLS performance best practices.

5. Reduce sign-in load. Put a CAPTCHA or your own rate limiting in front of public sign-in and sign-up forms, and keep users signed in with refresh tokens instead of asking them to sign in again.

6. Trim Realtime. Subscribe to Postgres Changes with a filter, on small tables, or use Broadcast for high-volume events.

On SupaHost

• CPU and memory charts on the Instance page are coming soon. Until then, use the SQL above.
• On Business and Enterprise, SSH in and run sudo supahost-status. It shows each container's status, disk, memory and uptime. Load averages that stay above the number of vCPUs (4 on Business) mean the CPU is saturated.
• If the load is legitimate and still too high after tuning, change plan in Dashboard → Usage & billing → Change plan. Changing plan resizes the VM, with about a minute or two of downtime.

Related

• Query performance and EXPLAIN
• Monitoring, health checks and alerts
• Changing plans
• High memory or swap usage