Samuel Gendler <sgendler(at)ideasculptor(dot)com> wrote:
> queries are definitely taking longer than we'd like them to
> Database currently occupies 91GB on disk.
> I get no resistance when I suggest going to 64GB of RAM.
One thing that jumps out at me is that with a 91GB database, and no
pushback on buying 64GB of RAM, it may be possible to get enough RAM
to keep the *active portion* of the database entirely in RAM. (By
"active portion" I mean that part which is repeated accessed to run
these queries.) If you can do that, then your bottleneck is almost
certainly going to be CPU, so you want fast ones. I hear that the
newest Intel chips do really well on PostgreSQL benchmarks. You
want the highest speed cores you can get, with fast access to fast
RAM, even if it means fewer cores. (Someone please jump in with
details.)
Given your insert-only, partitioned data, and your heavy reporting,
I would definitely try to get to what I describe above; your disk
system might not be where you need to spend the money. Of course, I
would still get a good RAID controller with BBU cache; I just don't
think you need to worry a whole lot about boosting your spindle
count.
-Kevin