Re: Status of tablespaces - Mailing list pgsql-general

On Tue, 28 Jan 2003, Sean Chittenden wrote:

> 1) extents - appending/writing to already allocated, zero'ed out data
>    allowed for significantly faster inserts (don't know if this is
>    really the case or not, or if it's still valid, but that was the
>    Gospel for the time)

With postgres using the filesystem, it's probably quite the opposite. If
a file has had the space allocated already, when "appending" into this
allocated space, the OS has only once choice of block on the disk to
update, and you are reasonably likely to have to wait to read that block
before writing it. If the OS is allocating new blocks to the end of the
file, you will definitely not do a read of the block before writing it,
and the OS also has the opportunity to chose from any free blocks on the
disk, rather than the block that happened to be the most convenient free
block when the file was zeroed.

Possibly, if your writes fragmented across a lot of tables, and you do
the zeroing-out early enough, and you happen to do a lot of long linear
scans on the tables, you'd end up with better performance by creating
the files first. But I wouldn't anticipate that to be a commmon case.

Note, too, that one of the reasons pre-allocating extents in Oracle
gives such an advantage is because its block allocation system is not
so intelligent as modern (well, if you call Berkeley FFS "modern" :-))
filesystems.

> 2) extents in table spaces - were invaluable when creating cyclical
>    data tables.  For example: in a table space, allocate extents 8
>    extents (each extent big enough for the day's worth of data - an
>    extent being pre-allocated disk space), then with a stored
>    procedure/rule, each day had its data inserted into an extent.  On
>    the 8th day, drop the oldest extent, reformat the extent, then
>    re-add the extent to the table space: instant daily truncation
>    without having to do a delete on 200M rows.

Cool...but what does that do to the indexes and foreign key relationships?

> Hrm, now that I think about it, I suppose this could be done with
> tables instead of extents and a view on top of them...  ::ponders::

It can make a lot of queries work not so well, because the optimizer
won't do certain optimizations across a UNION SELECT that it will do
on a single table. But you could always just re-code your queries to
work directly against the appropriate tables. You'd probably want to
build some little query generator to do this sort of thing.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC