simple question - what is the best way to improve a database's performance using hardware only?
in this situation a few very very large tables are being queried very regularly by 1-4 processes at a time.
we are executing thousands of queries, many of which take +10s to return, and all of which return only a small quantity of data. this suggests to me that HDD seek time is the bottleneck.
as part of this process, we also need to create summary tables from our raw data tables. one of these queries may take hours to run.
please assume that all software/database optimisation has been done already.
Assume this because we have spent some time doing code/db optimisation and are ready to spend some of the budget on hardware. i understand that more software/db optimisation is possible but that is not the current focus.
we do not run out of ram at the moment, but could possibly allocate more to the DB.
the current platform is windows, this may change depending on the hardware solution.
the database is postres 8.4.