Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgres truncate is slow

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

like image 957
toanong Avatar asked Nov 12 '13 17:11

toanong


1 Answers

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

like image 125
Steven Shi Avatar answered Sep 16 '22 13:09

Steven Shi