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.