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