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.


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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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