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

2008/2/19, Richard Huxton <dev(at)archonet(dot)com>:
> Markus Bertheau wrote:
> > 2008/2/19, Richard Huxton <dev(at)archonet(dot)com>:
> > I'm loading a table with some short attributes and a large toastable attribute.
> > That means that for every main table heap page several toast table heap pages
> > are written. This happens through the buffer cache and the background writer,
> > so maybe the pages aren't written in the order in which they were created in
> > the buffer cache, but if they are, they end up on disk (assuming that the file
> > system is not fragmented) roughly like that:
> >
> > main table heap page 1
> > toast table heap page 1
> > toast table heap page .
> > toast table heap page n
> > main table heap page 2
> > toast table heap page n+1
> > toast table heap page .
> > toast table heap page 2n
>
> Well, that's assuming:
> 1. You're not re-using space from previously deleted/updated rows.
> 2. You've not got a RAID array striping writes over multiple disks
> 3. The underlying filesystem + buffering isn't doing anything too clever.

> I think disk blocks on your more common file-systems are 4KB by default
> (ext2/3 and ntfs for example). I'm not aware of any default disk-block
> sizes more than the 8KB page-size of PG. Of course, the OS may read
> ahead if it sees you scanning, but it will do that on a file basis.

Ok, turns out that I was largely unaware of how smart file systems are
nowadays. The whole story looks like a good example of how PostgreSQL relies on
the file system and its caches for performant operation.

Thanks

Markus Bertheau