mirror of
https://github.com/supabase/supabase.git
synced 2026-10-18 23:59:08 +03:00
136 lines
7.5 KiB
Plaintext
136 lines
7.5 KiB
Plaintext
---
|
|
id: 'connection-management'
|
|
title: 'Connection management'
|
|
description: 'Managing connections'
|
|
subtitle: 'Using your connections resourcefully'
|
|
---
|
|
|
|
### Connecting to your database
|
|
|
|
Beyond the [Supabase client-libraries](https://supabase.com/docs/guides/api/rest/client-libs), Supabase offers three connection strings for direct database access, compatible with database libraries like [Prisma](/docs/guides/database/prisma) and [Drizzle](/docs/guides/database/drizzle), as well as tools like [PSQL](/docs/guides/database/psql) and [pgAdmin](/docs/guides/database/pgadmin).
|
|
You can find these connection strings in your [Database Settings](https://supabase.com/dashboard/project/_/settings/database).
|
|
|
|
### Direct connection string:
|
|
|
|
It connects directly to your Postgres instance. It is ideal for persistent servers (AWS EC2, Fly.io VMs, DigitalOcean Droplets...).
|
|
|
|
<Admonition type="caution">
|
|
Direct connections use IPv6 addresses by default. If your environment isn't compatible or you're
|
|
unsure, check this [guide](https://github.com/orgs/supabase/discussions/27034).
|
|
</Admonition>
|
|
|
|
```md
|
|
// Example String
|
|
postgresql://postgres:[YOUR-PASSWORD]@db.apbkobhfnmcqqzqeeqss.supabase.co:5432/postgres
|
|
```
|
|
|
|
#### Supavisor session mode (port 5432):
|
|
|
|
Session mode is not uses the Supavisor pooler to proxy connections to your database. Because it works in IPv4 environments, it can act as an IPv4 proxy alternative for direct connections.
|
|
|
|
```md
|
|
// Example String
|
|
postgres://postgres.apbkobhfnmcqqzqeeqss:[YOUR-PASSWORD]@aws-0-ca-central-1.pooler.supabase.com:5432/postgres
|
|
```
|
|
|
|
#### Transaction mode (port 6543):
|
|
|
|
Supavisor in transaction mode forms hot connections with the database and only allows clients to access them connection when a query is pending. Transaction mode has amazing scaling capacity, and is best used with temporary servers, such as serverless/Edge Functions or auto scaling servers.
|
|
|
|
```md
|
|
// Example String
|
|
postgres://postgres.apbkobhfnmcqqzqeeqss:[YOUR-PASSWORD]@aws-0-ca-central-1.pooler.supabase.com:6543/postgres
|
|
```
|
|
|
|
## What problems do poolers solve?
|
|
|
|
A database can only sustain a limited amount of connections. Postgres connections are like websockets, once established, they are preserved until the client (application servers) disconnects. A server might only make a single 10 ms query, but needlessly reserve its database connection for seconds or longer.
|
|
|
|
Serverside-poolers, such as Supavisor, sit between clients and the database and can be thought of as load balancers for Postgres connections. They maintain hot connections with the database and intelligently share them with clients only when needed. They prevent idle or greedy clients from reserving all connections, while also maximizing the amount of queries a single connection can service.
|
|
|
|
Application-side poolers are built into connection libraries and API servers, such as Prisma, SQLAlchemy, and PostgREST. They form several hot connections with Postgres or a serverside-pooler and exist to minimize the overhead of reforming new connections with the database between queries.
|
|
|
|
### When to use a pooler
|
|
|
|
#### Stationary clients
|
|
|
|
External connection libraries, such as Prisma, SQLAlchemy, and Postgres.js, all have built in poolers that manage connections for you. They will claim a certain amount of hot connections based on your library's settings, and queue queries for them as necessary. When deploying your application to stationary servers, often times, the built-in poolers are optimal.
|
|
|
|
#### Serverless and auto-scaling clients
|
|
|
|
Temporary or autos-scaling environments, such as AWS Lambda, Vercel, or Supabase Edge Functions, connect/disconnect rapidly and scale unpredictably. Even when using a library with an internal pooler, an external pooler, such as Supabase's Supavisor in transaction mode, is required.
|
|
|
|
### Allocating Supavisor-connections
|
|
|
|
"Pool size" refers to the maximum number of direct connections the pooler can manage on behalf of your database per `user+db+mode` combination.
|
|
|
|
You can modify Supavisor's pool size in the [Database Settings](https://supabase.com/dashboard/project/_/settings/database):
|
|
|
|
|
|
|
|
### What is the "user+db+mode" Combination?
|
|
|
|
Postgres is not technically a database. It is a Relational Database Management System (RDMS). Within it, you can spawn Postgres databases. In Supabase, it is a common pattern to just use the default database called `postgres`, but you could create more:
|
|
|
|
```sql
|
|
CREATE DATABASE postgres;
|
|
CREATE DATABASE another_database;
|
|
```
|
|
|
|
Similarly, a database can have many database users, but most people just rely on the default user, also named "postgres".
|
|
|
|
```sql
|
|
CREATE USER postgres WITH PASSWORD 'super-secret-password';
|
|
CREATE USER some_new_user WITH PASSWORD 'password';
|
|
```
|
|
|
|
The modes are transaction (port 6543) and session (port 5432) mode.
|
|
|
|
The "user+database+mode" combinations are formed from the above variables and are used within the connection string:
|
|
|
|
```md
|
|
postgres://[USER].shfmmplnqscentnakbkl:[password]@aws-0-ca-central-1.pooler.supabase.com:[MODE]/[DATABASE]
|
|
```
|
|
|
|
### Over-allocating connections
|
|
|
|
In session mode, Supavisor will commission a new direct connection as long as the pool size permits. In transaction mode, the pooler will only create a new direct connection when there are more pending queries than available direct connections.
|
|
|
|
In all cases, if the pooler and other servers collectively try to claim more direct connections than your database supports, the database will reject the newer connection with a `53300` "too_many_connections" error. Postgres displays the errors with one of two messages:
|
|
|
|
- `sorry, too many clients already`
|
|
- `remaining connection slots are reserved for non-replication superuser connections`
|
|
|
|
### Configuring your Supavisor pool size
|
|
|
|
If your Supavisor pooler uses most of the available connections, you risk depriving other servers, such as Auth, from accessing your database. Still, as much as possible, you want to give your pooler freedom to grow its pool as needed to service demand.
|
|
|
|
As a rule of thumb, if you're heavily using the Supabase Postgres API, try to keep the pooler's usage under 40% of available connections. Otherwise, you can increase usage to around 80%. This leaves room for other services, such as auth, realtime, and even the Dashboard.
|
|
|
|
These percentages are flexible and depend on your application's usage and setup. Monitor connection usage to determine the optimal allocation without depriving other servers of necessary connections.
|
|
|
|
### How to monitor connections?
|
|
|
|
For viewing active connections, you can utilize [pg_stat_activity](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW). It is a Postgres `view` that keeps track of live processes run by your database, including connections. It's particularly useful for determining if idle clients are monopolizing connection slots.
|
|
|
|
This is a query you can use to observe the clients currently connected 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;
|
|
```
|
|
|
|
Connection usage over time can be monitored with a [Supabase Grafana Dashboard](/docs/guides/platform/supabase-grafana). It provides realtime visibility of over 200 database metrics, such as graphs of CPU, EBS, and active direct/pooler connections. It can be extremely useful for monitoring and debugging instances.
|