Good Partition for Postgresql Server

Question

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.

Answer 1

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