current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite
2

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?

share|improve this question
2  
Really, why don't just use RAID? –  leonbloy Jun 27 '12 at 17:20

2 Answers 2

up vote 7 down vote
  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
share|improve this answer
up vote 5 down vote

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.

share|improve this answer
    
Raid might be a good solution to the problem but it doesn't answer the question. Maybe he wants to have specific data on a specific disk, like put one heavily used table on fast disk and nothing else so the disk is only used by reads and writes to this table. –  Janning Oct 14 '13 at 20:49
1  
Sorry but you're wrong. The OP stated: 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. Note that the subjunctive clause in the parenthesis is taken as a kind of guess on how to do it. While it's quite possible to do this with tablespaces and have no redundancy, the better way for 99% of all users is to just put it all on a big RAID array, since then all the tables can benefit from the combined speed plus the redundancy. Nit picking doesn't really help. –  Scott Marlowe Dec 16 '13 at 4:25
    
Your are right, Scott. I voted for your answer. –  Janning Dec 16 '13 at 8:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.