Postgres distributing data across multiple disks

Question

I need your help to solve a problem as I am fairly new to Postgres and reading the manuals didn’t help.

We have a server with 4 internal hard disks. We have created a Postgres database with a few tables. We want to be able to spread our data across these 4 disks.

We want to specify somewhere (eg table space creation stage) that the data loaded into a set of tables should be distributed across the 4 disks.

Can you kindly give us the syntax or point us in the right direction?

Answer 1

1. Create four tablespaces each one on a different disk (see: http://www.postgresql.org/docs/current/static/sql-createtablespace.html)
2. Move the content of the tables to the tablespaces so that the data is distributed as you intend it to be using ALTER TABLE foobar SET tablespace = foospace, see here: http://www.postgresql.org/docs/current/static/sql-altertable.html
3. Move the content of the tables' indexes to the tablespaces using alter index idx_foo set tablespace = spacefoo, see here: http://www.postgresql.org/docs/current/static/sql-alterindex.html

Answer 2

The best way is to create a RAID-0 or RAID-10 array and let the OS handle this for you. RAID-0 has no redundancy so any single drive failure is catastrophic. RAID-10 allows for any one disk to fail and keep right on working with good performance. Avoid RAID-5 or RAID-6 due to very poor write performance.