介绍
在处理非常大的 PostgreSQL 表时，比如说超过 15TB 的表，有时候日常的维护（如归档非常旧的数据）可能会变得非常具有挑战。尽管建立了很好的索引。最近，在尝试清理一个非常大的遗留表上的非常旧的数据时，就遇到了这个问题。
问题
最开始，使用了基于 ID 的标准分页方法。想象一下这样的查询：
DELETE FROM large_table
WHERE id IN (
SELECT id
FROM large_table
WHERE created_at < '2023-04-01'
ORDER BY id
LIMIT 10000
);虽然表面上看起来完全没问题，但是在执行时一直超时（由于 10 分钟的statement_timeout设置）。为什么？因为在这种情况下，数据库需要：
- 1. 扫描索引以查找旧的记录
- 2. 按 ID 对它们进行排序
- 3. 执行删除
- 4. 删除期间要维护好索引
虽然删除操作和索引维护都不是什么大问题；对于一个 15TB 的大型表，查找操作变得非常昂贵，尤其是在涉及索引时。
引入 CTID
PostgreSQL 为每行维护了一个叫做 CTID 的物理位置标识符。它是一个(page_number,row_number)元组，其中：
• page_number是表中的物理页（每个页通常为 8KB）
• row_number是该行在那个页面中的位置
下面就是您会看到它的一个样子：
SELECT ctid, id, created_at
FROM large_table
LIMIT 5;
-- Results might look like:
-- "(0,1)","1","2023-01-01" -- Page 0, row 1
-- "(0,2)","2","2023-01-01" -- Page 0, row 2
-- "(0,3)","3","2023-01-01" -- Page 0, row 3
-- "(1,1)","4","2023-01-01" -- Page 1, row 1
-- "(1,2)","5","2023-01-01" -- Page 1, row 2CTID 的有趣之处在于，它表示了物理存储的顺序。所以，我开始想知道，如果我们可以使用它，来按顺序地逐页处理表，那会怎样：
-- Process first 200,000 physical pages
WITH to_delete AS (
SELECT ctid
FROM large_table
WHERE ctid BETWEEN '(0,0)'::tid AND '(200000,0)'::tid
AND created_at < '2023-04-01'
FOR UPDATE SKIP LOCKED
)
DELETE FROM large_table
WHERE ctid IN (SELECT ctid FROM to_delete);请注意，当我们指定 CTID 范围时，如BETWEEN '(0,0)'::tid AND '(200000,0)'::tid，我们实际上是从这些页面内的所有位置查询所有行。每个页面通常都会包含多个行，这样就可以有效地按顺序读取表块。
那么有什么好处吗？这样查找起来非常快，不会像前面的查询那样，给系统带来很大的压力。这样清理任务就可以缓慢而轻松地持续执行清理了。
这并不是一个理想的解决方案（请参阅下面的权衡部分），但作为一个短期的缓解措施，这可以完成工作。
完整的解决方案
以下是我们构建的完整清理方案（基于 Ruby/Rails 的示例）。
请注意，我们使用了REPEATABLE READ隔离级别，来处理 CTID 的稳定性问题。CTID 在更新操作或VACUUM FULL操作期间可能会更改，因此我们需要一致的快照，以避免丢失行：
page_size = 200_000 # Number of pages to process at once
current_page = 0
cutoff_date = '2023-04-01'
deleted_count = 0
ApplicationRecord.transaction(isolation: :repeatable_read) do # Ensure consistent CTID snapshots
ApplicationRecord.with_statement_timeout(TIMEOUT) do
loop do
delete_sql = <<~SQL
WITH to_delete AS (
SELECT ctid
FROM large_table
WHERE ctid BETWEEN '(#{current_page * page_size},0)'::tid
AND '(#{(current_page + 1) * page_size},0)'::tid
AND created_at < '#{cutoff_date}'
FOR UPDATE OF large_table SKIP LOCKED
)
DELETE FROM large_table
WHERE ctid IN (SELECT ctid FROM to_delete)
RETURNING id;
SQL
result = ActiveRecord::Base.connection.exec_query(delete_sql)
deleted_count += result.rows.size
current_page += 1
# Check if there are any rows in next page range
check_sql = <<~SQL
SELECT EXISTS (
SELECT 1
FROM large_table
WHERE ctid BETWEEN '(#{current_page * page_size},0)'::tid
AND '(#{(current_page + 1) * page_size},0)'::tid
LIMIT 1
);
SQL
has_more_rows = ActiveRecord::Base.connection.exec_query(check_sql).rows[0][0]
break unless has_more_rows
end
end
end权衡
这个方法总体上比基于索引的删除要慢（如果有效的话）。我们正在对整个表进行一次顺序扫描，这意味着我们正在读取每一页，即使是那些没有旧记录的页面。这正是数据库的查询规划器和索引查找可以让我们避免的困难。
但是，这个方法可靠、相当快速并且不会给系统带来压力。我们现在不会因为删除量大而超时了，而是：
• 根据物理存储，以可预测的块数来处理表
• 避免了昂贵的索引操作
• 在数据库上保持了一致的负载
• 最终完成了清理
对于这种清理旧数据的特定场景，我会很乐意用速度来换取可预测性和可靠性。缓慢但成功的清理，比快速但总是失败的清理要好。最后，因为这是一项定期的工作，如果页面的分布没有完全更新，也是可以的。我们还可以选择，在执行任务之前先运行一次ANALYZE。
顺序扫描会怎么样？
您可能想知道，简单地强制 PostgreSQL 使用顺序扫描而不是索引扫描，是否能解决超时问题：
SET enable_indexscan = off;
SET enable_seqscan = on;
DELETE FROM large_table
WHERE created_at < '2023-04-01'
LIMIT 10000;遗憾的是并不能。数据库仍然必须按顺序扫描每一行（就像我们在程序中所做的那样，效率较低），并且根据行和行大小，该操作仍然会超时。
