Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm setting up a dedicated postgresql server on ubuntu, using 500GB RAID 1. I'm wondering if someone might share a simple, yet effective partitioning schema that I can use, since I'm pretty new to all this.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

You won't get much performance benefit from partitioning with a single big RAID (though you will avoid major headaches from / filling up).

Generally I would just use the OS defaults unless you're really comfortable sizing things yourself and just make sure you have a good sized chunk of space where you intend to create the PGDATA directory. The OS partitioning suggestions below are what I use on FreeBSD systems, it's probably OK for Ubuntu too.

 /                    1GB (or larger)
 [SWAP]               2GB or 2*RAM Size
 /var                 10GB
 /usr                 10GB
 /usr/local           15GB
 /tmp                 2GB
 /home                10GB ("Big enough for home directories")
 -------------------------------------------------------------
 (OS Total)           50 GB
 [PGDATA]             Postgres data directory ("The rest")

(Ubuntu may not make use of /usr/local, if not you can fold some of that into /usr)


Re: Postgres-specific partitioning, if you're doing this on a single RAID-1 just make [PGDATA] big enough to hold your DB and transaction logs, plus some growth room.

If this is a production box and you're expecting a lot of DB activity what you want to do is separate this out onto a bunch of RAID-1s to separate disk activity (super-high-volume you may even consider splitting it across controllers).

Oracle has lots of great whitepapers on this and I highly recommend stealing their designs: They aren't simple, but they are effective. The partitioning scheme I use (each on their own pair of disks) is:

[PGDATA]               5-10GB
[PGDATA]/pg_xlog       5-10GB (More if you expect lots of transaction logs)
[PGDATA]/base          Big enough for your database
[PGDATA]/[Tablespace]  Optional - Separate out "high-traffic" table spaces
[PGDATA]/xlog_archive  Big enough for archived transaction logs & dumps
share|improve this answer
add comment

I've a postgresql server with some interesting activity between 600-1000 transactions per second and my partitioning is something like this:

/      - 10GB
swap   - 15GB (I have 8GB RAM)
/var   - 20GB
/home  - 60GB (don't ask why, but I think 60gb is too much for home)
pgdata - 100 GB

According with your data, would be interesting if you dive into table partitioning, to avoid scanning a huge table in the case you need a small part of data. For example, I use partitioned tables to store logs so I have a master table called logs and monthly tables called YYYYMM_logs inherited from the master. Then I use a trigger function to put each log on the corresponding partitioned table.

share|improve this answer
add comment

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.