How to debug differences in postgresql TRUNCATE times?

Question

postgres 8.3 / Ubuntu Karmic / 32-bit (in virtualbox):

  duration: 76.534 ms  statement: truncate audit.users cascade
  duration: 0.952 ms  statement: delete from audit.users

postgres 8.4 / Ubuntu lucid / 64-bit (native, on the machine hosting the karmic virtualbox):

  duration: 1469.271 ms  statement: truncate audit.users cascade
  duration: 0.988 ms  statement: delete from audit.users

So the DELETE statements are pretty much equivalent, but TRUNCATE takes 20x longer on one platform than the other. EXPLAIN doesn't seem to work on TRUNCATE. How do I find out what's taking so long?

Edited to add:

The above samples were taken when there was another idle connection open to the database, but no open transactions or other activity. I use TRUNCATE in the tearDown method of some automated tests, which is where I noticed the speed difference between platforms.

Answer 1

TRUNCATE needs a lock, setting this lock, might take some time because of other transactions. Try pg_locks to see what's going on.

Answer 2

truncate needs to lock the whole table. If there are any transactions running, it needs to wait for them to finish. Another side effect which is not evidenced here is that its a single bottleneck preventing any new transactions which need this table.

When it's a live system with dozens or hundreds of transactions using this table, this bottleneck itself may be an important issue. delete only locks single rows, so it's faster in many concurrent/live environments.

I'm not sure what you need this for, but you may want to build new "version" of data for this table to a temporary table, then (to keep lock/update time as short as possible) push it to the live table with delete + insert as select:

begin;

create temp table my_data on commit drop as
---... lengthy calculation here;

delete from data;

insert into data select * from my_data;

commit;

Answer 3

The way TRUNCATE works in PostgreSQL, it's very sensitive to how fast your filesystem can delete blocks, as well as whether it correctly honors the fsync system call when you write to flush the write cache out. My guess is that you have different filesystem setups on the two systems. For example, if the Lucid install is using ext4 and the Karmic one ext3, this is unsurprising behavior. Newer kernels will correctly turn fsync calls into disk cache flushing via write barriers; older ones let the drives lie to them about things being written. This is a good thing in terms of keeping the database writes safe during a crash, but performance drops a lot when the kernel does the right thing from a reliability perspective.