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