Re: Fusion-io ioDrive

Well, what does a revolution like this require of Postgres?   That is the question.

I have looked at the I/O drive, and it could increase our DB throughput significantly over a RAID array.

Ideally, I would put a few key tables and the WAL, etc.  I'd also want all the sort or hash overflow from work_mem to go to this device.  Some of our tables / indexes are heavily written to for short periods of time then more infrequently later -- these are partitioned by date.  I would put the fresh ones on such a device then move them to the hard drives later.

Ideally, we would then need a few changes in Postgres to take full advantage of this:

#1  Per-Tablespace optimizer tuning parameters.  Arguably, this is already needed.  The tablespaces on such a solid state device would have random and sequential access at equal (low) cost.   Any one-size-fits-all set of optimizer variables is bound to cause performance issues when two tablespaces have dramatically different performance profiles.
#2  Optimally, work_mem could be shrunk, and the optimizer would have to not preferentially sort - group_aggregate whenever it suspected that work_mem was too large for a hash_agg.  A disk based hash_agg will pretty much win every time with such a device over a sort (in memory or not) once the number of rows to aggregate goes above a moderate threshold of a couple hundred thousand or so.
In fact, I have several examples with 8.3.3 and a standard RAID array where a hash_agg that spilled to disk (poor or -- purposely distorted statistics cause this) was a lot faster than the sort that the optimizer wants to do instead.   Whatever mechanism is calculating the cost of doing sorts or hashes on disk will need to be tunable per tablespace.

I suppose both of the above may be one task -- I don't know enough about the Postgres internals.

#3  Being able to move tables / indexes from one tablespace to another as efficiently as possible.

There are probably other enhancements that will help such a setup.  These were the first that came to mind.