How to Monitor Postgres and Supavisor Connections

[TheOtherBrian1]

This is a copy of a troubleshooting article on Supabase's docs site. It may be missing some details from the original. View the original article.

This guide explains how connections impact your Supabase database's performance and how to optimize them for better resource utilization.

Installing Supabase Grafana

Supabase has an open-source Grafana Repo that displays real-time metrics of your database. Although the Observability Dashboard provides similar metrics, it averages the data by the hour or day. Having visuals of your connection usage can help you better allocate resources.

Visual of Grafana Dashboard

It can be run locally within Docker. Alternatively, you can deploy it to fly.io or Grafana Cloud, which are better for long-term data collection.

Installation instructions can be found in it the metrics docs

Observing connections

In Supabase Grafana, the "Client Connections" graph shows connections to both Supavisor and Postgres

• Yellow: The yellow line represents the number of actively querying and idle connections to the Supavisor Pooler.
• Green: The green line represents the total number of actively querying and idle direct connections to the database.

Investigating connection sources

pg_stat_activity is a VIEW that keeps track of processes being run by your database, including connections. It's particularly useful for determining if idle clients are hogging connection slots.

This is a query you can use to observe the database roles and servers connecting to your database:

SELECT pg_stat_activity.pid, ssl AS ssl_connection, datname AS database, usename AS connected_role, application_name, client_addr, query, query_start, state, backend_start FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;

Interpreting the query:

Column	Description
pid	connection id
ssl	Indicates if SSL is in use
datname	Name of the connected database (usually postgres)
usename	Role of the connected user
application_name	Name of the connecting application
client_addr	IP address of the connecting server
query	Last query executed by the connection
query_start	Time when the last query was executed
state	Querying state: active or idle
backend_start	Timestamp of the connection's establishment

• Note: If you are unfamiliar with the Supabase database roles, check this reference

If you believe a connection should be killed, you can do so by running the following query:

 select pg_terminate_backend(pid) from pg_stat_activity where pid = <connection_id>;

Managing the Supavisor pooler:

The Supavisor Pooler is an intermediary between your clients (application servers) and the database. In transaction mode (port 6543), it can enable Postgres to share single connections with many clients, only allowing access when a query is pending. This prevents idle clients from hogging a direct connection and allows for more throughput.

In cases where you see significantly more pooler connections than direct connections, if you can, you should consider increasing how many direct connections the pooler is allowed to manage in the Dashboard's Database Settings:

.

The general rule is that if you are using the PostgREST database API, you should avoid raising your pool size past 40%. Otherwise, you can commit 80% to the pool. This leaves adequate room for the Authentication server and other utilities.

These numbers are generalizations and assume a certain level of activity from all connected servers. The actual values depend on your concurrent peak connection usage. For instance, if you were only using 80 connections in a week period and your database could support 500 connections, then realistically you could allocate the remaining 420 (minus a reasonable buffer) to service more demand.

Secondary issues:

When managing Postgres, outside of connections, there are generally 3 likely bottlenecks (links to address each):

• Disk/IO
• Memory
• CPU

They're all intertwined to some extent. If IO, CPU, or Memory are constrained, this can cause queries to slow down. Your application servers and Supavisor may compensate by creating more database connections or letting queries wait longer in their respective queues. Sometimes, by addressing or optimizing other factors of the database, you can better address connection issues.

Other helpful resources:

• Supavisor FAQ
• Using SQLAlchemy with Supabase
• Supabase and IPv4/IPv6 compatibility
• Addressing Max Client Errors
• Connecting to your database
• How to Change Max Database Connections