From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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 10:05:48 -0400
Message-ID: <23853.1342015548@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
References: <4FFCCAC4.4030503@ringerc.id.au>
<CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
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
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: tgl@sss.pgh.pa.us, daniel@heroku.com, ringerc@ringerc.id.au
Subject: Re: DELETE vs TRUNCATE explanation
In-Reply-To: <23853.1342015548@sss.pgh.pa.us>
* 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