Re: TRUNCATE TABLE

Tom Lane wrote:
> Michael Stone <mstone+postgres@mathom.us> writes:
>> xfs' slowness is proportional to the *number* rather than the *size* of
>> the files. In postgres you'll tend to have fewer, larger, files than you
>> would in (e.g.) a source code repository, so it is generally more
>> important to have a filesystem that deletes large files quickly than a
>> filesystem that deletes lots of files quickly.
>
> The weird thing is that the files in question were hardly "large".
> IIRC his test case used a single int4 column, so the rows were probably
> 36 bytes apiece allowing for all overhead.  So the test cases with about
> 5K rows were less than 200K in the file, and the ones with 200K rows
> were still only a few megabytes.

Right.

> I tried the test on my Linux machine (which I couldn't do when I
> responded earlier because it was tied up with another test), and
> saw truncate times of a few milliseconds for both table sizes.
> This is ext3 on Fedora 6.
>
> So I'm still of the opinion that there's something broken about
> Adriaan's infrastructure, but maybe we have to look to an even
> lower level than the filesystem.  Perhaps he should try getting
> some bonnie++ benchmark numbers to see if his disk is behaving
> properly.

Well, I can hardly believe that something is broken with the infrastructure, because I have seen
the same behaviour on other hardware  (or it must be that I am using the standard postgresql.conf).

I started another test. I copied an existing database (not very large, 35 tables, typically a few
hundred up to a few thousand records) with CREATE DATABASE testdb TEMPLATE mydb and started to
remove random tables from testdb with DROP TABLE and TRUNCATE TABLE. I did this with the query tool
of pgAdmin III, to exclude any doubts about my own software (that uses pqlib). The hardware is an
Intel dual-core 17-inch MacBook Pro running Mac OS X 10.4.

I can not make any sense of the results. Truncating or dropping a table typically takes 1-2 ms or
30-70 ms or 200-500 ms. I have seen that truncating the *same* table with the *same* data takes 1
ms in one test and takes 532 ms in another one. The database has no foreign keys.

Based on these results, I still believe there is a problem in Postgres.

Regards,

Adriaan van Os