安全地删除数据和丢弃对象

删除行和删除数据库对象是日常操作，但在实时数据库上，这些操作可能会锁表、阻塞查询并导致停机。本指南介绍了保持这些操作安全快速的实用策略。

🌐 Deleting rows and dropping database objects are routine operations, but on a live database they can lock tables, block queries, and cause downtime. This guide covers practical strategies for keeping these operations safe and fast.

准备删除 #

🌐 Preparing to delete

在预发布环境中测试
确保你有最近的备份
确认表的依赖和外键约束
明确删除依赖对象，谨慎使用 CASCADE
选择交通不太拥挤的时间进行操作
在迁移中运行操作
设置超时时间，比如 lock_timeout 和 statement_timeout

识别依赖 #

🌐 Identifying dependencies

系统目录表 pg_class、pg_constraint 和 pg_depend 可以用来识别依赖：

🌐 The system catalog tables pg_class, pg_constraint, and pg_depend can be used to identify dependencies:

1
-- Find tables that depend on a specific table
2
select
3
  d.classid::regclass as dependent_object,
4
  d.objid::regclass as dependent_object_id,
5
  d.refclassid::regclass as referenced_object,
6
  d.refobjid::regclass as referenced_object_id
7
from pg_depend d
8
where d.refobjid = 'public.logs'::regclass;

如果你想删除的对象有依赖，你需要先删除那些依赖，或者使用 CASCADE，它会自动删除所有相关对象。

🌐 If the object you want to delete has dependencies, you'll need to drop those first or use CASCADE which will automatically drop all related objects.

数据删除策略 #

🌐 Data deletion strategies

有几种方法可以从表中删除数据，你选择哪种方法取决于你想删除多少数据。

🌐 There are several ways to delete data from a table and the approach you choose depends on how much you want to delete.

小删除 #

🌐 Small deletes

对于少于几千行的表，DELETE 操作就可以了：

🌐 For tables with less than a few thousand rows, a DELETE operation is fine:

1
delete from logs
2
where created_at < now() - interval '90 days';

这会在表上获取一个 ROW EXCLUSIVE 锁，但仍允许其他 SELECT、INSERT、UPDATE 和 DELETE 语句同时运行。对于行数较少的情况，这个操作几乎没有影响就能完成。

🌐 This acquires a ROW EXCLUSIVE lock on the table, which still allows other SELECT, INSERT, UPDATE, and DELETE statements to run concurrently. For small row counts, the operation completes with minimal impact.

大量删除 #

🌐 Large deletes

用一条语句删除数百万行可能会长时间占用锁，产生 WAL（预写日志）流量，并影响复制。相反，可以分批删除：

🌐 Deleting millions of rows in a single statement can hold locks for a long time, generate WAL (Write-Ahead Log) traffic, and impact replication. Instead, delete in batches:

1
-- Delete 5,000 rows at a time
2
DELETE FROM logs
3
WHERE id IN (
4
  SELECT id
5
  FROM logs
6
  WHERE created_at < now() - interval '90 days'
7
  LIMIT 5000
8
);

这种方法的好处是可以控制它的运行时间，锁定时间更短，并且对其他事务的影响更小。

🌐 This approach has the benefit of controlling when it runs, locking for a shorter period of time and minimising impact on other transactions.

如果你事先知道在数据库的业务周期中必须进行如此大规模的删除，那么你应该认真考虑使用表分区作为管理工具。

🌐 If you know in advance that such large deletes will have to happen in the business cycle of your database, then you should seriously think about using table partitioning as a management tool.

软删除 #

🌐 Soft deletes

如果你需要“删除”数据，但又想保留恢复的选项，可以考虑使用软删除模式：

🌐 If you need to "delete" data but want the option to recover it, consider a soft-delete pattern:

1
alter table orders
2
add column deleted_at timestamptz;
3
4
-- "Delete" a row
5
update orders
6
set deleted_at = now()
7
where id = 42;

然后在查询或视图中排除软删除的行：

🌐 Then exclude soft-deleted rows in your queries or views:

1
create view active_orders as
2
  select * from orders where deleted_at is null;

将软删除与定期的硬删除任务（使用 pg_cron）结合起来，在低流量期间批量永久删除已软删除的旧行。

🌐 Combine soft deletes with a scheduled hard-delete job (using pg_cron) to permanently remove old soft-deleted rows in batches during low-traffic periods.

正在删除所有数据 #

🌐 Deleting all data

如果你需要删除表中的所有数据，可以考虑使用 TRUNCATE 而不是 DELETE：

🌐 If you need to delete all data from a table, consider using TRUNCATE instead of DELETE:

1
truncate table logs;

TRUNCATE 比 DELETE 快得多，因为它不会生成单独的行级 WAL 条目，也不会扫描表。它还会重置任何自增序列。

对象删除策略 #

🌐 Object deletion strategies

删除表 #

🌐 Dropping tables

删除一个表会永久移除它及其所有数据。迁移时总是使用 IF EXISTS 来避免错误：

🌐 Dropping a table removes it and all its data permanently. Always use IF EXISTS to avoid errors in migrations:

1
drop table if exists old_analytics;

DROP TABLE 会获得一个 ACCESS EXCLUSIVE 锁，这会阻塞表上的所有其他操作，包括读取。在繁忙的表上，这可能会排在长时间运行的查询后面。请参见下方的监控锁。

删除列 #

🌐 Dropping columns

在 Postgres 中删除一列只是元数据操作——它不会重写表。不过，它仍然需要一个 ACCESS EXCLUSIVE 锁：

🌐 Dropping a column is a metadata-only operation in Postgres — it doesn't rewrite the table. However, it still requires an ACCESS EXCLUSIVE lock:

1
alter table users
2
drop column if exists legacy_field;

由于这个锁很短（仅限元数据），通常是安全的。但在有很多并发事务的表上，即使是短暂的 ACCESS EXCLUSIVE 锁也可能在长时间运行的查询后排队。使用锁超时可以避免无限等待：

🌐 Since the lock is brief (metadata-only), this is generally safe. But on a table with many concurrent transactions, even a brief ACCESS EXCLUSIVE lock can queue behind long-running queries. Use a lock timeout to avoid waiting indefinitely:

1
set local lock_timeout = '5s';
2
alter table users drop column if exists legacy_field;

如果语句超时，可以在更安静的时候重试。

🌐 If the statement times out, retry during a quieter period.

删除索引 #

🌐 Dropping indexes

删除一个普通索引会在索引上获取一个 ACCESS EXCLUSIVE 锁，但不会锁住表，因此对表的读写操作可以继续进行：

🌐 Dropping a regular index takes an ACCESS EXCLUSIVE lock on the index but not on the table, so reads and writes to the table continue uninterrupted:

1
drop index if exists idx_users_legacy_field;

Supabase CLI 中的 inspect 命令可以帮助你找出未使用的索引：

🌐 The inspect command in the Supabase CLI can help you identify unused indexes:

1
supabase inspect db index-stats

监控 #

🌐 Monitoring

检查被屏蔽的查询 #

🌐 Check for blocked queries

查询 pg_locks 和 pg_stat_activity 来查看当前活动的查询以及等待锁的查询。

🌐 Query pg_locks and pg_stat_activity to see currently active queries and queries waiting for locks.

Supabase CLI 提供了查看这些指标的命令：

🌐 The Supabase CLI provides commands to view these metrics:

1
supabase inspect db locks
2
supabase inspect db blocking

大规模删除后监控表膨胀 #

🌐 Monitor table bloat after large deletes

在删除大量行时，空间并不总是会被回收并可用。通常情况下，行会被标记为已删除，但空间不会立即释放。你可以监控表膨胀情况，看看空间是否正在被回收：

🌐 When deleting a large number of rows, the space is not always reclaimed and available for use. In normal cases, the rows are marked as deleted but the space is not immediately freed. You can monitor table bloat to see if the space is being reclaimed:

1
supabase inspect db bloat

回收磁盘空间 #

🌐 Reclaiming disk space

为了回收被删除行释放的磁盘空间，Postgres 的自动清理（autovacuum）会自动运行，将被删除的行标记为可重用，但它可能并不总能跟上大量删除的速度。

🌐 To reclaim the disk space freed by deleted rows, Postgres' autovacuum process runs automatically to mark deleted rows as reusable, but it may not always keep up with large deletes.

如果自动清理跟不上，你可以手动触发清理：

🌐 If autovacuum is not keeping up, you can trigger a manual vacuum:

1
vacuum (verbose) logs;

为了回收磁盘空间，而不仅仅是将元组标记为可重用，可以使用 VACUUM FULL。注意，这会重写整个表，并获得一个 ACCESS EXCLUSIVE 锁：

🌐 To reclaim disk space rather than only marking tuples as reusable, use VACUUM FULL. Note that this rewrites the entire table and takes an ACCESS EXCLUSIVE lock:

1
-- This locks the table for the duration — use during maintenance windows only
2
vacuum full logs;

在不加锁的情况下，回收磁盘空间最有效的方法是使用 pg_repack。

🌐 The most efficient way to reclaim disk space, without locks, is to use pg_repack.

🌐 Related links