postgres truncate is slow

performance postgresql centos truncate
toanong picture toanong · Nov 12, 2013 · Viewed 13.4k times · Source

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

Answer

Craig Ringer picture Craig Ringer · Nov 13, 2013

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

Related questions

Best way to select random rows PostgreSQL

I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a very large table with 500 Million rows, I …

Read More
postgresql COUNT(DISTINCT ...) very slow

I have a very simple SQL query: SELECT COUNT(DISTINCT x) FROM table; My table has about 1.5 million rows. This query is running pretty slowly; it takes about 7.5s, compared to SELECT COUNT(x) FROM table; which takes about 435ms. …

Read More
Is it possible to get a history of queries made in postgres

Is it possible to get a history of queries made in postgres? and is it be possible to get the time it took for each query? I'm currently trying to identify slow queries in the application I'm working on. I'm …

Read More