I understand query tuning and table design play a large role in performance, but taking that factor away and focusing on just hardware, what is the best hardware to get for Pg to work at the highest level (meaning speed at returning results)?
Depends heavily on the particular application, but most performance problems were caused by I/O (some of them because of bad table or application design, some of them by slow drives).
How does pg utilize multiple processors? The more the better?
Linux version uses processes, so it's able to use multiple processors. (Not sure about Windows version, but I guess it uses threads.)
Are queries spread across multiple processors? Is Pg 64 bit? If so what processors are recommended?
Hard to tell, as for example I've seen several benchmarks about Xeons from Intel, half of them saying that's the right CPU for PostgreSQL, the other half saying there are better CPUs. But as I've said before - in most cases the performance problems are caused by slow drives - take your money and put them in more RAM / better drives (SCSI).
I read this : http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/node12.html POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs. However, if only a single database connection is active, it can only use one CPU. POSTGRESQL does not use multi- threading to allow a single process to use multiple CPUs. Its pretty old (2003) but is it still accurate? if this statement is accurate how would it affect connection pooling software like pg_pool?
Yes, it's quite accurate. But see this (plus the rest of the documents in the "Docs" section on that site)
http://www.powerpostgresql.com/PerfList
RAM? The more the merrier right? Understanding shmmax and the pg config file parameters for shared mem has to be adjusted to use it. Disks? standard Raid rules right? 1 for safety 5 for best mix of performance and safety?
Yes, the more RAM you can get, the better the performance (usually). The problem is you've forgotten to mention the size of the database and the usage patterns. If the whole database fits into the RAM, the performance can only increase in most cases.
In case of clients that mostly write data into the database, the amount of RAM won't help too much as the data need to be written to the disk anyway (unless you turn off 'fsync' which is a really stupid thing to do in case of important data).
RAID - a quite difficult question and I'm not quite a master in this field, so I'll just quote some simple truths from the article mentioned above:
1) more spindles == better So buy multiple small disks rather than one large one, and spread the reads / writes across all of them using RAID 0, tablespaces or partitioning. 2) separate the transaction log from the database It's mostly written, and it's the most valuable data you have. And in case you use PITR, this is the only thing that really needs to be backed up. 3) RAID 0+1/1+0 > RAID 5
Any preference of SCSI over SATA? What about using a High speed (fibre channel) mass storage device?
SCSI is definitely better than SATA - the SATA are consumer level generally - the main criteria in it's development is capacity, and it definitely can't compete with SCSI 10k drives when it comes to transfer rates, seek times, CPU utilization, etc. (and that's what really matters with databases). And you can even buy 15k SAS drives for reasonable amount of money today ...
Tomas ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
