High memory or swap usage

What you see

Some of these, often together:

FATAL:  terminating connection because of crash of another server process
server closed the connection unexpectedly
ERROR:  53200: out of memory

The instance gets slow, Studio pages fail to load, or an Edge Function stops with a resource limit error. If the instance stops answering health checks for about 3 minutes, you also get an instance down email (when downtime alerts are on).

Why it happens

On SupaHost, everything for every project on the instance shares the VM's memory (4 GB on Builder, 8 GB on Pro, 16 GB on Business). The main consumers:

• Postgres shared memory (shared_buffers), a fixed block it sets aside at start.
• Postgres connections. Each connection is a process with its own memory. Hundreds of idle client connections add up.
• Big queries. Sorts, hash joins and aggregations can each use up to work_mem, and one query can run several of them, in every connection at once. Large select * results also have to be built in memory.
• Studio. Each project's Studio runs only while in use and can use up to 768 MB. It starts when you open it and stops after 30 minutes idle.
• Edge Functions. Each project that has functions runs one functions container (384 MB on Builder, 768 MB on Pro, 1.5 GB on Business), and each function run can use up to 150 MB.

When the VM runs out, the kernel kills a process. If that's a Postgres process, Postgres restarts every connection, which gives the "crash of another server process" message.

How to fix it

1. Look at the Postgres settings that drive memory:

   select name, setting, unit
   from pg_settings
   where name in ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'max_connections');
   

2. Count connections per project and client:

   select datname, usename, application_name, state, count(*)
   from pg_stat_activity
   group by datname, usename, application_name, state
   order by count(*) desc;
   

   Lots of idle postgres connections usually come from an ORM pool. Cap it at around 5 per process. See Too many connections.

3. Find memory-hungry queries. Look at the plan for queries that sort or join large sets:

   explain (analyze, buffers)
   select guest_id, count(*) from public.bookings group by guest_id order by 2 desc;
   

   Sort Method: external merge Disk: ... means the sort didn't fit in work_mem and spilled to disk. That's slower but safe. The risk is many such queries running at once.

4. Return less data. Select only the columns you need and paginate. The Data API already caps responses at 1,000 rows per request; direct connections have no cap.

5. Close Studio tabs you're not using. Each open project's Studio keeps running until it has been idle for 30 minutes.

6. Keep big work out of request paths. Move heavy reports and batch jobs to a cron job at a quiet hour.

On SupaHost

• Memory charts on the Instance page are coming soon.
• On Business and Enterprise, SSH in and run sudo supahost-status. Its Memory: line shows memory used out of the total, along with each container's status and how long they've been up. A container that restarted recently shows a short uptime.
• If memory stays high after cleanup, change plan in Dashboard → Usage & billing → Change plan. Changing plan resizes the VM to one with more memory, with about a minute or two of downtime.

Related

• High CPU usage on your instance
• Instance health alert: services unhealthy
• Edge Function memory limit or worker limit errors
• Changing plans