Re: Need a hardware optimization pointer

Daniel Fitzgerald wrote:

> Hi all, I need some pointers. I want to optimize my pgsql databases
> hardware-wise. I can do this in Sybase, Oracle, SQL Server, etc. by
> putting let's say, indexes on one device, the trasaction logs on
> another, data on another drive, etc. Can I do this in Postgresql ? I
> have only used the simple
>
> CREATE DATABASE newdatabase ( didn't find an alternate syntax ) so far.

Well, first of all there are no tablespaces as of now in postgresql. You can use
alternate data locations as described in
http://www.postgresql.org/docs/7.3/static/sql-createdatabase.html

However this will take a database with all it's object to a different area.

Otherwise you could take a look inside pg_data directory and symlink required
objects to another place. Of course, do not do this while the database is running.

Just one thing to remember,do not symlink files. Symlink directories. If
postgresql ever happen to recreate the file such as drop/create table, the new
file will end up in oriinal location.

> Is it possible to do a specific component optimization or do I just
> create a stripe set across all drives and hope for the best ? Anyone
> have experience in this ? Any comments appreciated - thanks in advance.

Most important optimization you can do is to symlink WAL logs which reside in
$PGDATA/pg_xlogs to a different drive for high update/write environment.

Other than that relying on RAID is your best bet. You need to weigh
admnistrative overheads of symlinks over the benefits and decide.

BTW, tablespaces are in works and expected in 7.5. So next release should be bit
better from this respect.

HTH

Shridhar