postgres truncate is slow

Question

在 CentOS 上使用的 postgres 9.2 版本中，TRUNCATE TABLE 命令有时需要很长时间才能执行。有一次，它花费了超过1.5小时来清空一个有10万条记录的表，在其他情况下需要更长时间。当我使用 pgAdmin 清空表时，也会出现这个问题。可能的原因是什么？如何提高截断性能？服务器上有16GB内存，shared_buffers = 1536MB。

- toanong

---

这可能意味着 TRUNCATE 进程正在等待获取锁，而一些其他进程花费了很长时间才释放它们的锁。但这个问题在这里并不是真正的话题。 - Jonathan Hall

Answer 1

检查是否有任何查询阻止了truncate操作

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

如果必要，终止它：SELECT pg_terminate_backend(PID);

- Steven Shi

---

为了终止阻塞查询以使截断操作完成，您应该使用blocking_id而不是pid。 - undefined

Answer 2

TRUNCATE命令必须清空shared_buffers中与被截断表相关的数据，还需要删除旧文件，而如果使用像ext3这样删除缓慢的文件系统会导致操作很慢。

然而1.5小时太长了，通常只需要几秒钟。极有可能有其他会话锁定了该表，阻止TRUNCATE继续执行。可以查看pg_catalog.pg_locks和pg_catalog.pg_stat_activity。

PostgreSQL锁监控页面可能会有所帮助。

另请参见：Postgresql Truncation speed

- Craig Ringer

Answer 3

尝试断开与数据库的所有其他连接。我花了很长时间来清空58000条记录。

在我断开PyCharm DB Navigator、dev server等与我的postgres数据库的连接之后，总共只花费了118毫秒。

- jturi

---

这听起来像是混淆了相关性和因果关系；很可能有一个或多个连接有未完成的事务和/或持有锁。 - Mitch Wheat

在Azure中对psql数据库进行了大约1小时的调查，使用现有的查询和Azure指标，但没有找到任何问题。在断开DBeavor连接后，对一个大约5G的表进行截断操作的时间从无法完成变为2秒。 - undefined

Answer 4

这件事情刚刚发生在我和我的团队身上。我们正在使用Postgres 12，使用Apache NiFi进行一些处理时卡住了。

我们执行了：

systemctl status postgresql-12

然后我注意到很多 "TRUNCATED Waiting"，尝试重新加载Postgres但没有成功，最后只能杀死卡住的进程。

这之后就可以正常工作了，我们能够重新启动任务，只需要不到一秒钟的时间。

- Will AE