Skip site navigation (1) Skip section navigation (2)

Re: Reduction in WAL for UPDATEs

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Kenneth Marshall" <ktm(at)rice(dot)edu>, <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: Reduction in WAL for UPDATEs
Date: 2007-03-28 15:44:28
Message-ID: 87tzw5l6eb.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> 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.

The reason I think this is idea is exciting is that later I would suggest
applying it to HOT updates. Having to keep a spare tuple's worth of space in
every page is pretty annoying. But if we could get by with the average
half-tuple dead space to do an update-- or even several updates--it would make
a huge difference.

> 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.

I agree that these values need a second look. I think a TOAST_TUPLE_THRESHOLD
well smaller than the current value would still easily pay its way. With a
little caution to avoid wasting too much effort on the last few bytes I
suspect even as low as 400-500 bytes is probably worthwhile.

Also, it may make sense to take into account what percentage of the overall
tuple a field is. It doesn't make much sense to start toasting fields in a
table that consists of fourty 40-byte varcahars for example. Whereas it
probably does make sense to toast a single 500-byte varchar in a table if the
rest of the table consists of just ten integers.

But considering how large the toast pointer itself is, how expensive it is to
fetch it, and that we need one for each attribute, it still won't be able to
usefully handle anything under 32 bytes or so. That's still a lot more than a
single byte indicating that the field is unchanged. It's not uncommon to have
tables that are wide because they have lots of small data in them.

In fact looking at this code now, is there a bug here? I don't see anything in
there stopping us from trying to toast varlenas that are smaller than a toast
pointer. Have I just missed something obvious?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-03-28 16:05:07
Subject: Re: Reduction in WAL for UPDATEs
Previous:From: August ZajoncDate: 2007-03-28 15:37:24
Subject: Re: Reduction in WAL for UPDATEs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group