TOAST performance (was Re: [GENERAL] Delete Performance)

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:
> [ complains that deletes are slow in table containing toasted data ]

I did some experimentation here and found a rather surprising
dependency: the time to delete a bunch of data is pretty much
directly proportional to the disk space it occupies.  This says
that we're paying through the nose for having XLOG make copies
of about-to-be-modified pages.

I did:

create table foo (f1 text);
insert into foo values ('a short entry');
insert into foo select * from foo;
-- repeat enough times to build up 32K rows total
delete from foo;

The "delete" took about 2 seconds.  I then did it over with the
'value' being a 5K chunk of text, which according to octet_length
got compressed to 3900 bytes.  (This'd require two rows in the TOAST
table.)  This time the delete took 127 seconds.  I was expecting
about a 3X penalty since we needed to delete three rows not one,
but what I got was a 60X penalty.

Trying to understand this, I did some profiling and found that most
of the time was going into XLogInsert and XLOG I/O.  That's when I
remembered that the actual data volume involved is considerably
different in the two cases.  Allowing for tuple header overhead and
so forth, the small-data case involves about 1.8MB, the large-data
case about 131MB, or about 70 times as much data.

I believe this indicates that what's determining the runtime is the fact
that the XLOG code writes out an image of each page modified in the
transaction.  These page images will be the bulk of the XLOG traffic
for the TOAST table (since there are only four or so tuples on each
TOAST page, the actual XLOG delete records take little space by
comparison).

I've worried for some time that the decision to XLOG page images was
costing us a lot more performance than could be justified...

One trick we could perhaps pull is to postpone deletion of TOAST tuples
until VACUUM, so that the bulk of the work is done in a noncritical path
(from the point of view of the application anyway).  I'm not sure how
this interacts with the way that we re-use a TOAST entry when other
fields in the row are updated, however.  It might be too difficult for
VACUUM to tell when to delete a TOAST item.
        regards, tom lane