supabase/apps/docs/content/troubleshooting/monitor-supavisor-postgres-connections.mdx

---
title = "How to monitor Postgres and Supavisor connections"
github_url = "https://github.com/orgs/supabase/discussions/27141"
topics = [ "database", "supavisor" ]
keywords = [ "Grafana", "connections", "performance", "pooler" ]
date_created = "2024-06-09"
database_id = "bdd39473-ae02-41f5-b75d-877a79f361f6"
---

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](https://github.com/supabase/supabase-grafana) that displays real-time metrics of your database. Although the [Observability Dashboard](/dashboard/project/_/observability) 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_

![image](https://github.com/supabase/supabase/assets/91111415/18ed2c88-332e-4e66-b9b4-c37e99a39104)

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 ](/docs/guides/observability/metrics/grafana-self-hosted)

## Observing connections

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

![image](https://github.com/supabase/supabase/assets/91111415/141b8e01-9aae-4544-818c-acb47371ef3b)

- **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:

```sql
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](https://gist.github.com/TheOtherBrian1/d6e862a65e03049eb4f102f6ca809401)

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

```sql
  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](/dashboard/project/_/database/settings):

![image](https://github.com/supabase/supabase/assets/91111415/8e0cc80a-4d46-44b5-915b-8d7549f352d3).

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](https://github.com/orgs/supabase/discussions/27003)
- [Memory](https://github.com/orgs/supabase/discussions/27021)
- [CPU](https://github.com/orgs/supabase/discussions/27022)

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](https://github.com/orgs/supabase/discussions/21566)
- [Using SQLAlchemy with Supabase](https://github.com/orgs/supabase/discussions/27071)
- [Supabase and IPv4/IPv6 compatibility ](https://github.com/orgs/supabase/discussions/27034)
- [Addressing Max Client Errors](https://github.com/orgs/supabase/discussions/22305)
- [Connecting to your database](/docs/guides/database/connecting-to-postgres#quickstarts)
- [How to Change Max Database Connections](https://github.com/orgs/supabase/discussions/27197)