out-of-line (TOAST) storage ineffective when loading from dump?

From: "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: out-of-line (TOAST) storage ineffective when loading from dump?
Date: 2008-02-19 07:17:18
Message-ID: 684362e10802182317v17b6513ah5cebc1adab09c43b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Afaics, TOAST was invented so that big attributes wouldn't be in the
way (of readahead, the buffer cache and so on) when working with the
other attributes. This is based on the assumption that the other
attributes are accessed more often than the whole contents of the big
attributes.

Now I wonder how disk blocks are used when loading a dump with big
text data so that out-of-line storage is used. If disk block usage was
following this pattern:

heap page
toast heap page 1
toast heap page ..
toast heap page n
heap page
toast heap page 1
toast heap page ..
toast heap page n
heap page
toast heap page 1
toast heap page ..
toast heap page n
heap page

If further the assumption is correct, that the granularity of the
lower level chaches is bigger than the PostgreSQL page size, then that
would mean that loading from a dump destroys the advantage of
out-of-line storage.

I haven't got any numbers to back this theory up.

What do you think?

Markus Bertheau

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2008-02-19 08:48:23 Re: Pains in upgrading to 8.3
Previous Message Reid Thompson 2008-02-19 00:09:27 Re: Auto incrementing primary keys