如何在 postgresql 中截断大小大于 5231MB 的大表

Question

已经尝试过删除和截断，但花了很长时间。还试图使用以下查询找出该表上是否存在任何锁定进程

SELECT bl.pid          AS blocked_pid,
         a.usename       AS blocked_user,
         kl.pid          AS blocking_pid,
         ka.usename      AS blocking_user,
         a.current_query AS blocked_statement
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
  WHERE NOT bl.GRANTED;

不返回任何行

Answer 1

TRUNCATE 应该很快，除非它无法在对象上获取 AccessExclusiveLock，在这种情况下，它可以无限期地等待。

上述应该显示阻塞会话的查询不会识别对象级锁，这是TRUNCATE获取的锁类型。

这里提到了它，我假设这个查询是从哪里来的：

https://wiki.postgresql.org/wiki/Lock_Monitoring

以下查询可能有助于查看哪些进程正在阻塞 SQL 语句（这些语句仅查找行级锁，而不是对象级锁） 锁）。

---

以下是PG 9.1问题的演示：

会议#1：

test=> create table footable(id int);
CREATE TABLE
test=> begin;
BEGIN
test=> insert into footable values(1);
INSERT 0 1
test=> 

（未提交）

会议 #2

test=> truncate table footable;

（被会话 #1 阻止）

会议 #3

test=> SELECT bl.pid          AS blocked_pid,
         a.usename       AS blocked_user,
         kl.pid          AS blocking_pid,
         ka.usename      AS blocking_user,
         a.current_query AS blocked_statement
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
  WHERE NOT bl.GRANTED;
 blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement 
-------------+--------------+--------------+---------------+-------------------
(0 rows)

根据此查询，没有会话被阻止，因此显然是错误的。

我建议您在此处尝试其他查询：https://wiki.postgresql.org/wiki/Find_Locks

在此示例中，生成以下输出：

-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 113270
relation           | 2660062
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 5/2548
pid                | 4419
mode               | AccessExclusiveLock
granted            | f
virtualtransaction | 4/2031
pid                | 31775
mode               | RowExclusiveLock
granted            | t

Answer 2

TRUNCATE是删除表的最快方法。从文档中：

TRUNCATE 可快速删除一组表中的所有行。它与每个表上的非限定 DELETE 具有相同的效果，但由于它实际上并不扫描表，因此速度更快。此外，它可以立即回收磁盘空间，而不需要后续的 VACUUM 操作。这在大型表上最有用。

您还可以DROP并重新创建表，该表将与 TRUNCATE 相同。

你的DELETE最终也会完成，你只需要给Postgres一些时间。

如果需要清除所有内容而不锁定表，可以批量删除：

DELETE FROM things LIMIT 1000;

把它放在某个地方，直到桌子空了，然后去吃点零食。