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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
