Autovacuum Stalled Due to Inactive Replication Slot

如果你发现 supabase inspect db vacuum-stats 报告你的表显示“Expect autovacuum? yes”，但自动清理（autovacuum）活动已经长时间没有运行，导致数据库 RAM 使用量不断增加，这通常表明自动清理进程被卡住了。自动清理被卡住的原因之一是复制槽（replication slot）不活跃，本指南中对此有说明。

🌐 If you observe that supabase inspect db vacuum-stats reports "Expect autovacuum? yes" for your tables, but autovacuum activity has been inactive for an extended period, leading to increasing database RAM usage, this typically indicates a stalled autovacuum process. One of the reasons for autovacuum to get stalled is an inactive replication slot for which this guide talks about.

这为什么会发生？ #

🌐 Why does this happen?

复制槽（逻辑或物理）会告诉 Postgres“不要在这个点之前删除 WAL 或较旧的事务状态”，因为消费者或副本可能仍然需要那些 WAL 记录或可见性信息。这意味着自动清理（autovacuum）会变慢、做更多的工作，或者看起来停滞不前，因为它无法通过槽所锚定的旧快照继续进行。非活动的逻辑复制槽可能会阻止自动清理有效运行。这种停滞会导致死元组无法清理，从而引起数据库膨胀和资源消耗增加。

🌐 Replication slots (logical or physical) tell Postgres “don’t remove WAL or older transaction state before this point” because a consumer/replica might still need those WAL records or visibility information. That means autovacuum will get slower, do more work, or appear to be stalled because it can't progress past the older snapshot anchored by the slot. Inactive logical replication slots can prevent the autovacuum process from running effectively. This stall prevents the cleanup of dead tuples, leading to database bloat and increased resource consumption.

如何解决这个问题 #

🌐 How to resolve this issue

识别不活跃的复制槽： 在你的SQL 编辑器中执行以下查询，以列出所有复制槽及其活动状态：
```
1
select slot_name, slot_type, active, active_pid from pg_replication_slots where active is false;
```
删除不活动的插槽： 对于每个被标记为 active = f（不活动）的 slot_name，执行以下命令。将 'slot_name' 替换为不活动插槽的实际名称（例如，'example_slot'）：
```
1
select pg_drop_replication_slot('slot_name');
```
确认移除: 重新运行步骤1中的识别查询，以确认不活跃的插槽已经成功移除。移除后，自动清理应恢复正常运行。