From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Smith <greg@2ndquadrant.com>
Cc: VJK <vjkmail@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
Date: Mon, 15 Mar 2010 10:53:22 -0400
Message-ID: <17919.1268664802@sss.pgh.pa.us> (raw)
In-Reply-To: <4B9E4762.7060703@2ndquadrant.com>
References: <20100313211742.GC2178@comppasch2>
<600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com>
<600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com>
<4B9E4762.7060703@2ndquadrant.com>
Greg Smith <greg@2ndquadrant.com> writes:
> VJK wrote:
>> Since Pg does not use the concept of rollback segments, it is unclear
>> why deletion produces so much disk IO (4GB).
> With PostgreSQL's write-ahead log, MVCC and related commit log, and
> transactional DDL features, there's actually even more overhead that can
> be involved than a simple rollback segment design when you delete things:
For an example like this one, you have to keep in mind that the
toast-table rows for the large bytea value have to be marked deleted,
too. Also, since I/O happens in units of pages, the I/O volume to
delete a tuple is just as much as the I/O to create it. (The WAL
entry for deletion might be smaller, but that's all.) So it is entirely
unsurprising that "DELETE FROM foo" is about as expensive as filling the
table initially.
If deleting a whole table is significant for you performance-wise,
you might look into using TRUNCATE instead.
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, greg@2ndquadrant.com, vjkmail@gmail.com
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
In-Reply-To: <17919.1268664802@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