postgres truncate is slow

Question

In postgres 9.2 (CentOS), TRUNCATE TABLE command occasionally took a really long time to run. One time, it took more than 1.5 hours to truncate a table with 100K records, even longer in other cases. This problem also happened when I used pgAdmin to truncate table. What is the possible cause? and how to improve the truncation performance?

There is 16GB of memory on the server and shared_buffers = 1536MB

That probably means the TRUNCATE process was waiting to obtain a lock; and some other process(es) took a long time to release their lock. But this question isn't really on topic here. — Jonathan Hall, Nov 12 '13 at 18:58

score 28 · Answer 1 · answered Jun 01 '19 at 23:08

Check if the truncate was blocked by any query

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));

If necessary terminate it SELECT pg_terminate_backend(PID);

score 20 · Answer 2 · edited Sep 25 '18 at 12:59

TRUNCATE has to flush shared_buffers for the table being truncated, and it has to unlink the old file, which can be slow on file systems with slow deletion like ext3.

1.5 hours is pretty extreme though, as we're usually talking seconds at most. It is highly likely that you have other sessions holding locks on the table that prevent the TRUNCATE from proceeding. See pg_catalog.pg_locks and pg_catalog.pg_stat_activity.

The PostgreSQL wiki article on lock monitoring should be useful.

See also: Postgresql Truncation speed

score 3 · Answer 3 · answered Mar 06 '18 at 11:12

3

Try to disconnect all other connections to the database. It took me forever to truncate 58000 records.

After I disconnected my postgres database from PyCharm DB Navigator, dev server etc took 118 msec total.

answered Mar 06 '18 at 11:12

jturi

1,615
15
11

that sounds like mixing correlation with causation; likely 1 or more connections had open transactions and/or were holding locks – Mitch Wheat Mar 04 '23 at 03:28

score 1 · Answer 4 · answered Oct 21 '20 at 17:02

This just happened to me and my team. We are using Postgres 12 and were doing some processing using Apache NiFi. It got stuck.

We did a:

systemctl status postgresql-12

Then I noticed a lot of "TRUNCATED Waiting" I proceeded to try and reload Postgres without success and then just killed each of the stuck processes.

It worked after that and we were able to restart the jobs and only took less than a seccond.

postgres truncate is slow

4 Answers4

Linked