Re: DELETE vs TRUNCATE explanation

On Wed, Jul 11, 2012 at 10:05:48AM -0400, Tom Lane wrote:
> Daniel Farina <daniel(at)heroku(dot)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
>

+1 TRUNCATE needs to keep the same properties independent of the size
of the table. Smearing it into a DELETE would not be good at all. If
there are optimizations that can be done to keep its current behavior,
those might be possible, but the complexity may not be worthwhile for
a relative corner case.

Regards,
Ken