From: | "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com> |
---|---|
To: | "Richard Huxton" <dev(at)archonet(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: out-of-line (TOAST) storage ineffective when loading from dump? |
Date: | 2008-02-19 16:45:28 |
Message-ID: | 684362e10802190845l76ac4d3cs4f23874aa7f3d5fe@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | George Weaver | 2008-02-19 17:05:26 | Alter Domain Type |
Previous Message | Tom Lane | 2008-02-19 16:07:07 | Re: Analogue to SQL Server UniqueIdentifier? |