From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Farina <daniel@heroku.com>
cc: Craig Ringer <ringerc@ringerc.id.au>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: DELETE vs TRUNCATE explanation
In-reply-to: 
 <CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
References: <4FFCCAC4.4030503@ringerc.id.au>
	<CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
Comments: In-reply-to Daniel Farina <daniel@heroku.com>
	message dated "Tue, 10 Jul 2012 22:22:27 -0700"
Date: Wed, 11 Jul 2012 10:05:48 -0400
Message-ID: <23853.1342015548@sss.pgh.pa.us>

Daniel Farina <daniel@heroku.com> writes:
> TRUNCATE should simply be very nearly the fastest way to remove data
> from a table while retaining its type information, and if that means
> doing DELETE without triggers when the table is small, then it should.
>  The only person who could thwart me is someone who badly wants their
> 128K table to be exactly 8 or 0K, which seems unlikely given the 5MB
> of catalog anyway.

> Does that sound reasonable?  As in, would anyone object if TRUNCATE
> learned this behavior?

Yes, I will push back on that.

(1) We don't need the extra complexity.

(2) I don't believe that you know where the performance crossover point
would be (according to what metric, anyway?).

(3) The performance of the truncation itself should not be viewed in
isolation; subsequent behavior also needs to be considered.  An example
of possible degradation is that index bloat would no longer be
guaranteed to be cleaned up over a series of repeated truncations.
(You might argue that if the table is small then the indexes couldn't
be very bloated, but I don't think that holds up over a long series.)

IOW, I think it's fine as-is.  I'd certainly wish to see many more
than one complainant before we expend effort in this area.

			regards, tom lane