Multiple disks

[hal]

What is the best/simplest way to split:
a database
multiple databases
a table
multiple tables
across more than one disk drive?

I know that this has come up before but I can't find
any info. A pointer to a HOWTO or other info would
be wonderful.

hal


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Post by hal
...
across more than one disk drive?

LVM or RAID solutions should do the job.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[scott.marlowe]

Post by hal
a database
multiple databases
a table
multiple tables
across more than one disk drive?
I know that this has come up before but I can't find
any info. A pointer to a HOWTO or other info would
be wonderful.

The current king of putting postgresql on multiple platters and getting
best overall performance is a battery backed caching raid controller run
one or more many disk RAID-5 arrays. The more disks, the better up to a
dozen or two.

The red headed step child is the alternate location stuff, discussed in
the docs here:
http://www.postgresql.org/docs/7.4/static/manage-ag-alternate-locs.html
which will allow you to put individual databases in different locations.
It's a hackish kludge that should be replaced by the upcoming tablespaces
patches. I'm not sure if those will be done by 7.5 release or not.

The next way is to individually link thinks like indexes onto other
volumes. The procedure is basically, create the index, figure out which
file in $PGDATA/base/oidofyourdbhere is the index, shut down postgresql,
copy to file elsewhere, softlink it, restart apache. This setup will not
survive reindexing or dropping / recreating the index.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Post by scott.marlowe
The next way is to individually link thinks like indexes onto other
volumes. The procedure is basically, create the index, figure out which
file in $PGDATA/base/oidofyourdbhere is the index, shut down postgresql,
copy to file elsewhere, softlink it, restart apache. This setup will not
survive reindexing or dropping / recreating the index.

It should also be noted that this hack doesn't cope well with tables or
indexes exceeding 1Gb. You'd have to separately symlink each segment,
and deal with creating new links every time the table/index grows past
another 1Gb boundary.

Tablespaces will be a lot nicer when we have them. For now, a RAID
array is definitely the best bet.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Cris Carampa]

Post by scott.marlowe
The current king of putting postgresql on multiple platters and getting
best overall performance is a battery backed caching raid controller run
one or more many disk RAID-5 arrays.

I thought the best choice were RAID-10 (1+0) (when available). There
isn't a write penalty with RAID-5?

Thank you. Kind regards,

[scott.marlowe]

Post by Cris Carampa

Post by scott.marlowe
The current king of putting postgresql on multiple platters and getting
best overall performance is a battery backed caching raid controller run
one or more many disk RAID-5 arrays.

I thought the best choice were RAID-10 (1+0) (when available). There
isn't a write penalty with RAID-5?

there is for software RAID-5 or for non-battery backed cached RAID-5, but
for a battery backed controller, the penalty is basically gotten rid of by
being hidden in a lazy write. Plus the controller can "gang" writes to
individual drives to achieve a very good throughput.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)