Re: DELETE vs TRUNCATE explanation

On Wed, Jul 11, 2012 at 7:05 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.
>

It strikes me as a contrived case rather than a use case. What sort of app
repeatedly fills and truncates a small table thousands of times ... other
than a test app to see whether you can do it or not?

The main point of truncate is to provide a more efficient mechanism to
delete all data from large tables. If your app developers don't know within
a couple orders of magnitude how much data your tables hold, and can't
figure out whether to use delete or truncate, I can't find much sympathy in
my heart.

Craig