From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Daniel Farina <daniel(at)heroku(dot)com> |
Cc: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: DELETE vs TRUNCATE explanation |
Date: | 2012-07-11 14:05:48 |
Message-ID: | 23853.1342015548@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | ktm@rice.edu | 2012-07-11 14:19:54 | Re: DELETE vs TRUNCATE explanation |
Previous Message | Shaun Thomas | 2012-07-11 13:41:40 | Re: Synchronous Standalone Master Redoux |
From | Date | Subject | |
---|---|---|---|
Next Message | ktm@rice.edu | 2012-07-11 14:19:54 | Re: DELETE vs TRUNCATE explanation |
Previous Message | Ants Aasma | 2012-07-11 11:59:35 | Re: how could select id=xx so slow? |