Re: DELETE vs TRUNCATE explanation

On 07/11/2012 01:22 PM, Daniel Farina wrote:
> On Tue, Jul 10, 2012 at 5:37 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> Hi
>>
>> After seeing a few discussions here and on Stack Overflow I've put together
>> a quick explanation of why "DELETE FROM table;" may be faster than "TRUNCATE
>> table" for people doing unit testing on lots of tiny tables, people who're
>> doing this so often they care how long it takes.
>>
>> I'd love it if a few folks who know the guts were to take a look and verify
>> its correctness:
> I haven't said this before, but think it every time someone asks me
> about this, so I'll say it now:
>
> This is a papercut that should be solved with improved mechanics.
> 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?
Yep, I'd object. It's more complicated and less predictable. Also, as I
strongly and repeatedly highlighted in my post, DELETE FROM table; does
a different job to TRUNCATE. You'd at minimum need the effect of DELETE
followed by a VACUUM on the table and its indexes to be acceptable and
avoid the risk of rapid table + index bloat - and that'd be lots slower
than a TRUNCATE. You could be clever and lock the table then DELETE and
set xmax at the same time I guess, but I suspect that'd be a bit of work
and still wouldn't take care of the indexes.

It's also too complicated, not least because AFAIK util commands and
CRUD commands go through very different paths in PostgreSQL.

I guess you could propose and post a prototype patch for a new command
that tried to empty the table via whatever method it thought would be
fastest. Such a new command wouldn't be bound by the accepted and
expected rules followed by TRUNCATE so it could vary its behaviour based
on the table, doing a real truncate on big tables and a
delete-then-vaccum on small tables. I suspect you'd land up writing the
fairly complicated code for the potentially multi-table
delete-and-vaccum yourself.

Honestly, though, it might be much better to start with "how can
TRUNCATE of empty or near-empty tables be made faster?" and start
examining where the time goes.

--
Craig Ringer