Greg Smith <greg@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
