From: ktm@rice.edu <ktm@rice.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Farina <daniel@heroku.com>
Cc: Craig Ringer <ringerc@ringerc.id.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: DELETE vs TRUNCATE explanation
Date: Wed, 11 Jul 2012 09:19:54 -0500
Message-ID: <20120711141954.GJ15829@aart.rice.edu> (raw)
In-Reply-To: <23853.1342015548@sss.pgh.pa.us>
References: <4FFCCAC4.4030503@ringerc.id.au>
<CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
<23853.1342015548@sss.pgh.pa.us>
On Wed, Jul 11, 2012 at 10:05:48AM -0400, Tom Lane 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.
>
> regards, tom lane
>
+1 TRUNCATE needs to keep the same properties independent of the size
of the table. Smearing it into a DELETE would not be good at all. If
there are optimizations that can be done to keep its current behavior,
those might be possible, but the complexity may not be worthwhile for
a relative corner case.
Regards,
Ken
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ktm@rice.edu, tgl@sss.pgh.pa.us, daniel@heroku.com, ringerc@ringerc.id.au
Subject: Re: DELETE vs TRUNCATE explanation
In-Reply-To: <20120711141954.GJ15829@aart.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox