How to monitor Postgres and Supavisor connections

本指南解释了连接如何影响你的 Supabase 数据库性能，以及如何优化它们以更好地利用资源。

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

安装 Supabase Grafana #

🌐 Installing Supabase Grafana

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.

Grafana 仪表板的可视化

🌐 Visual of Grafana Dashboard

它可以在 Docker 中本地运行。或者，你也可以部署到 fly.io 或 Grafana Cloud，它们更适合长期收集数据。

🌐 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.

安装说明可以在metrics 文档中找到

🌐 Installation instructions can be found in it the metrics docs

观察联系 #

🌐 Observing connections

在 Supabase Grafana 中，“客户端连接”图表显示了到 Supavisor 和 Postgres 的连接情况

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

• 黄色：黄色线表示对 Supavisor Pooler 的活动查询连接数和空闲连接数。
• 绿色：绿色线表示主动查询和空闲的数据库直接连接的总数。

调查连接来源 #

🌐 Investigating connection sources

pg_stat_activity 是一个 VIEW，它用于跟踪数据库正在运行的进程，包括连接。它特别有用，可以帮助判断是否有空闲客户端占用了连接槽位。

这是一个可以用来查看连接到你数据库的数据库角色和服务器的查询:

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

1
SELECT
2
   pg_stat_activity.pid,
3
   ssl AS ssl_connection,
4
   datname AS database,
5
   usename AS connected_role,
6
   application_name,
7
   client_addr,
8
   query,
9
   query_start,
10
   state,
11
   backend_start
12
FROM pg_stat_ssl
13
JOIN pg_stat_activity
14
 ON pg_stat_ssl.pid = pg_stat_activity.pid;

解读这个查询：

🌐 Interpreting the query:

• 注意：如果你对 Supabase 数据库的角色不熟悉，可以查看这个 参考资料

如果你觉得一个连接应该被终止，你可以通过运行以下查询来做到：

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

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

管理Supavisor连接池： #

🌐 Managing the Supavisor pooler:

Supavisor Pooler 是你的客户端（应用服务器）和数据库之间的中介。在事务模式（端口 6543）下，它可以让 Postgres 与多个客户端共享单个连接，只有在有查询挂起时才允许访问。这可以防止空闲客户端占用直接连接，并允许更高的吞吐量。

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

.

一般规则是，如果你在使用 PostgREST 数据库 API，应该避免将连接池大小提高超过 40%。否则，你可以将 80% 的资源分配给连接池。这会为认证服务器和其他工具留出足够的空间。

🌐 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.

这些数字是概括性的，并假设所有连接的服务器都有一定的活跃度。实际数值取决于你同时在线的高峰连接使用情况。例如，如果你一周内只使用了 80 个连接，而你的数据库可以支持 500 个连接，那么实际上你可以把剩下的 420 个连接（减去一个合理的缓冲）分配给更多的需求。

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

在管理 Postgres 时，除了连接问题外，通常有 3 个可能的瓶颈（每个都有相应的解决链接）:

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

• 磁盘/输入输出
• 记忆
• CPU

它们在某种程度上都是互相交织的。如果 IO、CPU 或内存受限，可能会导致查询变慢。你的应用服务器和 Supavisor 可能会通过创建更多的数据库连接或让查询在各自的队列中等待更久来补偿。有时候，通过处理或优化数据库的其他因素，你可以更好地解决连接问题。

🌐 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 常见问题
• 在 Supabase 中使用 SQLAlchemy
• Supabase 和 IPv4/IPv6 兼容性
• 处理最大客户端错误
• 正在连接到你的数据库
• 如何更改最大数据库连接数