PostgreSQL 8.4 - Tablespace Optimization
- by FloE
I'm currently running a PostgreSQL Database with about 1.5 billion rows / 500 GB of data (including indices). There are several schemata: on for the (read only, irregular changes / updates) 'core-model' and one for every user (about 20 persons). The users can access the core and store data in their own schema, so everything is located in one database.
The server runs with CentOS and PostgreSQL 8.4 and is used for scientific studies, exploration etc and is running quite well.
These days an upgrade of the DB storage hard disks arrive - all with the same performance as the old ones.
I'm looking for the best way to distribute the data on these disks.
It would be possible to separate frequently used objects (the core-data) from the user schemata, but I'm not sure if this is really worth the effort.
It seems to be a much better idea to move the WAL files (pg_xlog directory) to its own partition.
http://www.postgresql.org/docs/8.4/static/wal-internals.html
What are your opinions?
Are there any tablespace- or partitioning-related performance documentations / benchmarks?
