Supabase and Supavisor - Postgres and Poolers

managing_connections.md

Supabase and Supavisor - Postgres and Poolers

There may come a time where you want more insight into your database; to understand who is doing what...and when. You might already be familiar with some of Postgres' system functions and views and, in this short guide, we will take a look at the catchy Cumulative Statistics System, providing insights into replication slots, connections, the WAL and more (that you can read about here)

Viewing Your Connections

All connections can be viewed using pg_stat_activity and you can simply start with:

SELECT * FROM pg_stat_activity;

This gives you everything but you will really want some key columns, let's focus on these:

• pid - Process ID of the connection
• datname - The database that this connection is connected to
• usename - The username of the connection
• application_name - Name of the application
• client_addr - IP of the client connected
• client_port - The port that the connection is using
• query- The most recent query run by the connection
• state - State of the connection (typically idle or active)
• backend_type - This is good information about the purpose/type of the connection, with options like: client backend, background writer, walsender

With this in mind, let's use this new knowledge to run some more in-depth queries:

Get All Active Connections

SELECT * FROM pg_stat_activity WHERE state = 'active';

Get All PGBouncer Connections

SELECT * FROM pg_stat_activity WHERE usename = 'pgbouncer';

Note: This is an initial response for pgbuncer.get_auth and will not list all pgbouncer connections as pgbouncer will connect with the user specified in the connection string.

Get All Supavisor Connections

SELECT * FROM pg_stat_activity WHERE application_name ilike '%Supavisor%';

---

Managing Your Connections

Viewing is a great start, the queries in the previous section enable us to get insights, either as a one off or continuously. How about killing connections we do not want or need? This is where we use pg_terminate_backend:

Killing A Connection By PID

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = '1211'; -- Replace 12345 with the actual process ID

Conditionally Killing All Connections (i.e. by Application Name or User)

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'pgbouncer';

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'Supavisor';