To: Greg Smith <greg@2ndquadrant.com>
cc: VJK <vjkmail@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences 
In-reply-to: <4B9E4762.7060703@2ndquadrant.com> 
References: <20100313211742.GC2178@comppasch2>
	<600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com>
	<600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com>
	<4B9E4762.7060703@2ndquadrant.com>
Comments: In-reply-to Greg Smith <greg@2ndquadrant.com>
	message dated "Mon, 15 Mar 2010 10:42:42 -0400"
Date: Mon, 15 Mar 2010 10:53:22 -0400
Message-ID: <17919.1268664802@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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