On Fri, 2006-05-19 at 21:37, Kenji Morishige wrote: > Where can I find any documentation to partition the tablespace disk files onto > different physical arrays for improved performance? There have been quite a few posts to this list in the past about this, so searching it might be a good start. Firstly, you need to defined "improved performance". Are we talking transactional throughput (OLTP), or batch updates (ETL), or report generation (OLAP stuff)??? Or some other scenario. For write performance, the general rules are: You can only commit 1 transaction per rotation of a properly fsynced disc that holds the WAL file (i.e. the pg_xlog directory). So, putting that on it's own fast spinning disc is step one for improved performance. A battery backed cache unit (BBU) on a RAID controller is a must. RAID 1+0 is a good choice for your data partition. For many hardware RAID controllers with the above mentioned BBU moving the pg_xlog to another partition is no real help. Cheap RAID controllers are often worse than no RAID controller. If you can't afford a good RAID controller, you're probably better off with software RAID than using a cheapie. For READ performance: Often settings in postgresql.conf are far more important than the drive layout. Lots of RAM is a good thing. Assuming you've got lots of RAM, making shared_buffers anywhere from 10 to 25% of it is a pretty good size. work_mem usually works well at around 16 meg or so. drop random_page_cost to about 2 for most systems. Lastly, read this: http://www.varlena.com/GeneralBits/Tidbits/perf.html
