Re: Reduction in WAL for UPDATEs

On Wed, 2007-03-28 at 11:17 -0400, Tom Lane wrote:
> > This one is similar, if you keep a bunch of static data attached to
> > some small dynamic data your WAL and table bloats.
>
> Actually, PG does extremely well on that in the situation where the
> static data is *really* wide, ie, wide enough to be toasted out-of-line.
> Simon's proposal will only help for an intermediate range of situations
> where the row is wide but not very wide.

Trouble is, thats lots of commonly updated tables. Thin tables generate
only small amounts of WAL, while very wide tables are optimised for
UPDATE already.

But right now most relational tables that represent Major Entities, i.e.
objects in the real world, have row lengths in the range 100-2000 bytes.

Page hit counters, Customer Accounts, Financials-to-date, Event
bookings, Seats sold.

> It strikes me that a more useful solution might come from the recent
> discussions about offering more user control of per-column toasting
> decisions. Or maybe we just need to revisit the default toast
> thresholds --- AFAIR there has never been any significant study of
> the particular values that Jan picked originally.

That's effectively definable vertical partitioning. The user has to know
about this and do something about it themselves. By default we compress
before we move out, so you'd need to know that also.

I've investigated that route briefly but there does seem to be a
surprising overhead in splitting off small pieces of data. Currently
TOAST seems to be optimised for when we have more than one chunk of
data.

I agree there's something worth looking at there, but even so I don't
see a direct correspondence between seldom updated (=> WAL reduction is
beneficial) and seldom used (=> TOAST is beneficial) columns.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com