调试与监控

数据库性能是一个很大的话题，很多因素都可能影响。一些最常见的性能差的原因包括：

🌐 Database performance is a large topic and many factors can contribute. Some of the most common causes of poor performance include:

设计得很低效的模式
设计效率低下的查询
缺少索引导致在大表上的查询比预期慢
未使用的索引导致 INSERT、UPDATE 和 DELETE 操作变慢
计算资源不足，比如内存，导致你的数据库经常需要访问磁盘来获取结果
多个查询在高频使用的表上操作导致的锁竞争
你的表里有大量膨胀，导致查询计划很差

你可以使用 Supabase CLI 或 SQL 检查你的数据库和查询是否存在这些问题。

🌐 You can examine your database and queries for these issues using either the Supabase CLI or SQL.

使用命令行 #

🌐 Using the CLI

Supabase CLI 配备了一系列工具，帮助你检查 Postgres 实例是否存在潜在问题。CLI 从 Postgres 内部机制获取信息。因此，大多数提供的工具都兼容任何 Postgres 数据库，不管它们是否属于 Supabase 项目。

你可以在这里找到 Supabase CLI 的安装说明。

`inspect db`#

🌐 The inspect db command

你可以通过 inspect db 命令访问你的 Postgres 数据库的检查工具。你可以运行 supabase inspect db help 来获取所有可用命令的完整列表。

🌐 The inspection tools for your Postgres database are under the inspect db command. You can get a full list of available commands by running supabase inspect db help.

1
$ supabase inspect db help
2
Tools to inspect your Supabase database
3
4
Usage:
5
  supabase inspect db [command]
6
7
Available Commands:
8
  bloat                Estimates space allocated to a relation that is full of dead tuples
9
  blocking             Show queries that are holding locks and the queries that are waiting for them to be released
10
  cache-hit            Show cache hit rates for tables and indices
11
12
...

连接到任何 Postgres 数据库 #

🌐 Connect to any Postgres database

大多数检查命令对 Postgres 是通用的。即使不是 Supabase 项目，你也可以通过 --db-url 提供连接字符串，在任何 Postgres 数据库上运行检查程序。

🌐 Most inspection commands are Postgres agnostic. You can run inspection routines on any Postgres database even if it is not a Supabase project by providing a connection string via --db-url.

例如，你可以连接到你本地的 Postgres 实例：

🌐 For example you can connect to your local Postgres instance:

1
supabase inspect db bloat --db-url postgresql://postgres:postgres@localhost:5432/postgres

连接到 Supabase 实例 #

🌐 Connect to a Supabase instance

使用 Supabase 时，你可以将 Supabase CLI 与你的项目关联起来：

🌐 Working with Supabase, you can link the Supabase CLI with your project:

1
supabase link --project-ref <project-id>

然后每当你在项目文件夹里时，CLI 会自动连接到你的 Supabase 项目，你就不需要再提供 —db-url 了。

🌐 Then the CLI will automatically connect to your Supabase project whenever you are in the project folder and you no longer need to provide —db-url.

检查命令 #

🌐 Inspection commands

下面是提供的 db 检查命令，按不同的使用场景分组。

🌐 Below are the db inspection commands provided, grouped by different use cases.

有些命令可能需要启用 pg_stat_statements 或使用特定版本的 Postgres。

🌐 Some commands might require pg_stat_statements to be enabled or a specific Postgres version to be used.

磁盘存储 #

🌐 Disk storage

如果你的磁盘空间快不够用了，这些命令很有用：

🌐 These commands are handy if you are running low on disk storage:

bloat - 估计浪费空间的数量
vacuum-stats - 提供垃圾收集的相关信息
table-record-counts - 估计每个表的记录数
table-sizes - 显示表格的大小
index-sizes - 显示各个索引的大小
table-index-sizes - 显示每个表的索引大小

查询性能 #

🌐 Query performance

下面的命令在你的 Postgres 数据库占用大量资源（如 CPU、内存或磁盘 IO）时很有用。你也可以用它们来调查慢查询。

🌐 The commands below are useful if your Postgres database consumes a lot of resources like CPU, RAM or Disk IO. You can also use them to investigate slow queries.

缓存命中 - 显示你的缓存使用效率整体如何
unused-indexes - 显示低索引扫描的索引
index-usage - 显示关于索引效率的信息
seq-scans - 显示所有表记录的顺序扫描次数
long-running-queries - 显示当前正在执行的长时间运行查询
outliers - 显示执行时间长但调用次数少的查询，以及在同步 I/O 上花费执行时间比例高的查询

锁 #

🌐 Locks

locks - 显示已对某个关系加独占锁的语句
blocking - 显示正在等待锁释放的语句

连接 #

🌐 Connections

role-connections - 显示所有数据库角色的活跃连接数（Supabase 专用命令）
replication-slots - 显示数据库上复制槽的信息

`pg_stat_statements`#

🌐 Notes on pg_stat_statements

以下命令需要启用 pg_stat_statements：calls、locks、cache-hit、blocking、unused-indexes、index-usage、bloat、outliers、table-record-counts、replication-slots、seq-scans、vacuum-stats、long-running-queries。

🌐 Following commands require pg_stat_statements to be enabled: calls, locks, cache-hit, blocking, unused-indexes, index-usage, bloat, outliers, table-record-counts, replication-slots, seq-scans, vacuum-stats, long-running-queries.

使用 pg_stat_statements 时也要注意，它只会存储最近的 5,000 条语句。此外，在通过运行 select pg_stat_statements_reset(); 优化任何查询后，考虑重置分析。

🌐 When using pg_stat_statements also take note that it only stores the latest 5,000 statements. Moreover, consider resetting the analysis after optimizing any queries by running select pg_stat_statements_reset();

在这里了解更多关于 pg_stats 的信息 here。

🌐 Learn more about pg_stats here.

使用 SQL #

🌐 Using SQL

如果你在从仪表板查看查询性能页面时看到 insufficient privilege 错误，运行这个命令：

🌐 If you're seeing an insufficient privilege error when viewing the Query Performance page from the dashboard, run this command:

1
$ grant pg_read_all_stats to postgres;

Postgres 累积统计系统 #

🌐 Postgres cumulative statistics system

Postgres 会使用累积统计系统收集自身操作的数据。除此之外，每个 Supabase 项目默认都启用了pg_stat_statements 扩展。这个扩展会记录查询执行的性能细节，是找出低效查询的最佳方式。这些信息可以结合 Postgres 查询计划分析器来开发更高效的查询。

🌐 Postgres collects data about its own operations using the cumulative statistics system. In addition to this, every Supabase project has the pg_stat_statements extension enabled by default. This extension records query execution performance details and is the best way to find inefficient queries. This information can be combined with the Postgres query plan analyzer to develop more efficient queries.

这里有一些示例查询可以帮你入门。

🌐 Here are some example queries to get you started.

最常被调用的查询 #

🌐 Most frequently called queries

1
select
2
  auth.rolname,
3
  statements.query,
4
  statements.calls,
5
  -- -- Postgres 13, 14, 15
6
  statements.total_exec_time + statements.total_plan_time as total_time,
7
  statements.min_exec_time + statements.min_plan_time as min_time,
8
  statements.max_exec_time + statements.max_plan_time as max_time,
9
  statements.mean_exec_time + statements.mean_plan_time as mean_time,
10
  -- -- Postgres <= 12
11
  -- total_time,
12
  -- min_time,
13
  -- max_time,
14
  -- mean_time,
15
  statements.rows / statements.calls as avg_rows
16
from
17
  pg_stat_statements as statements
18
  inner join pg_authid as auth on statements.userid = auth.oid
19
order by statements.calls desc
20
limit 100;

这个查询显示：

🌐 This query shows:

查询统计，按每个查询执行的次数排序
运行查询的角色
它被调用的次数
返回的平均行数
查询运行的累计总时间
最小、最大和平均查询时间。

这提供了关于你最常运行的查询的有用信息。那些 max_time 或 mean_time 时间很高且被频繁调用的查询，可能是优化的好候选对象。

🌐 This provides useful information about the queries you run most frequently. Queries that have high max_time or mean_time times and are being called often can be good candidates for optimization.

按执行时间排序的最慢查询 #

🌐 Slowest queries by execution time

1
select
2
  auth.rolname,
3
  statements.query,
4
  statements.calls,
5
  -- -- Postgres 13, 14, 15
6
  statements.total_exec_time + statements.total_plan_time as total_time,
7
  statements.min_exec_time + statements.min_plan_time as min_time,
8
  statements.max_exec_time + statements.max_plan_time as max_time,
9
  statements.mean_exec_time + statements.mean_plan_time as mean_time,
10
  -- -- Postgres <= 12
11
  -- total_time,
12
  -- min_time,
13
  -- max_time,
14
  -- mean_time,
15
  statements.rows / statements.calls as avg_rows
16
from
17
  pg_stat_statements as statements
18
  inner join pg_authid as auth on statements.userid = auth.oid
19
order by max_time desc
20
limit 100;

这个查询会显示按最大执行时间排序的查询统计信息。它类似于上面按调用次数排序的查询，但这个查询会突出那些可能有高执行时间的异常值。执行时间高或平均执行时间高的查询是优化的好候选对象。

🌐 This query will show you statistics about queries ordered by the maximum execution time. It is similar to the query above ordered by calls, but this one highlights outliers that may have high executions times. Queries which have high or mean execution times are good candidates for optimization.

最耗时间的查询 #

🌐 Most time consuming queries

1
select
2
  auth.rolname,
3
  statements.query,
4
  statements.calls,
5
  statements.total_exec_time + statements.total_plan_time as total_time,
6
  to_char(
7
    (
8
      (statements.total_exec_time + statements.total_plan_time) / sum(
9
        statements.total_exec_time + statements.total_plan_time
10
      ) over ()
11
    ) * 100,
12
    'FM90D0'
13
  ) || '%' as prop_total_time
14
from
15
  pg_stat_statements as statements
16
  inner join pg_authid as auth on statements.userid = auth.oid
17
order by total_time desc
18
limit 100;

这个查询会显示按累计总执行时间排序的查询统计信息。它显示了查询运行的总时间以及该查询占总执行时间的比例。

🌐 This query will show you statistics about queries ordered by the cumulative total execution time. It shows the total time the query has spent running as well as the proportion of total execution time the query has taken up.

耗时最长的查询不一定就是糟糕的，你可能有一些非常高效且经常运行的查询，但它们最终占用了很大一部分总时间，不过它还是有助于发现那些占用时间比应该更多的查询。

🌐 Queries which are the most time consuming are not necessarily bad, you may have a very efficient and frequently ran queries that end up taking a large total % time, but it can be useful to help spot queries that are taking up more time than they should.

命中率 #

🌐 Hit rate

通常，对于大多数应用，少量数据的访问频率会比其他数据更高。为了确保经常访问的数据可用，Postgres 会跟踪你的数据访问模式，并将其保存在它的 shared_buffers 缓存中。

🌐 Generally for most applications a small percentage of data is accessed more regularly than the rest. To make sure that your regularly accessed data is available, Postgres tracks your data access patterns and keeps this in its shared_buffers cache.

缓存命中率较低的应用通常表现更差，因为它们必须访问磁盘来获取结果，而不是从内存中提供结果。非常低的命中率还可能导致你超出［磁盘 IO 限制］(/docs/guides/platform/compute-and-disk#disk)，从而引发严重的性能问题。

🌐 Applications with lower cache hit rates generally perform more poorly since they have to hit the disk to get results rather than serving them from memory. Very poor hit rates can also cause you to burst past your Disk IO limits causing significant performance issues.

你可以通过执行以下查询来查看你的缓存和索引命中率：

🌐 You can view your cache and index hit rate by executing the following query:

1
select
2
  'index hit rate' as name,
3
  (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) * 100 as ratio
4
from pg_statio_user_indexes
5
union all
6
select
7
  'table hit rate' as name,
8
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100 as ratio
9
from pg_statio_user_tables;

这显示了从 Postgres shared_buffers 缓存获取的数据块与从磁盘/操作系统缓存读取的数据块的比例。

🌐 This shows the ratio of data blocks fetched from the Postgres shared_buffers cache against the data blocks that were read from disk/OS cache.

如果你的任一索引或表格命中率低于99%，这可能表明你的计算计划对于当前工作负载来说太小，你会从更多内存中受益。升级你的计算可以在你的项目仪表板中完成。

🌐 If either of your index or table hit rate are < 99% then this can indicate your compute plan is too small for your current workload and you would benefit from more memory. Upgrading your compute can be done from your project dashboard.

优化表现不佳的查询 #

🌐 Optimizing poor performing queries

Postgres内置了工具，帮助你优化表现不佳的查询。你可以对任何你识别的昂贵查询使用[查询计划分析器]（https://www.postgresql.org/docs/current/sql-explain.html）：

🌐 Postgres has built in tooling to help you optimize poorly performing queries. You can use the query plan analyzer on any expensive queries that you have identified:

1
explain analyze <query-statement-here>;

当你在 explain 语句中包含 analyze 时，数据库会尝试执行查询，并提供详细的查询计划以及实际执行时间。所以，在使用 explain analyze 与 insert/update/delete 查询时要小心，因为查询会被执行，并且可能产生意想不到的副作用。

🌐 When you include analyze in the explain statement, the database attempts to execute the query and provides a detailed query plan along with actual execution times. So, be careful using explain analyze with insert/update/delete queries, because the query will run, and could have unintended side-effects.

如果你运行 explain 而不带 analyze 关键字，数据库只会进行查询计划，而不会执行查询。当你想查看查询计划而不影响数据库，或者遇到查询超时时，这种方法会很有用。

🌐 If you run explain without the analyze keyword, the database will only perform query planning without executing the query. This approach can be beneficial when you want to inspect the query plan without affecting the database or if you encounter timeouts in your queries.

使用查询计划分析器来优化你的查询是一个大话题，有很多在线资源可用：

🌐 Using the query plan analyzer to optimize your queries is a large topic, with a number of online resources available:

你可以将来自 pg_stat_statements 的信息与通过你的指标端点获取的详细系统指标结合起来，以更好地了解你的数据库行为以及你执行的查询。

🌐 You can pair the information available from pg_stat_statements with the detailed system metrics available via your metrics endpoint to better understand the behavior of your DB and the queries you're executing against it.