Re: slow query - will CLUSTER help?

> What are your thoughts on the right way to use SSDs in a RAID to
> enhance postgres I/O performance? In an earlier reply, you
> indicated one of a "RAID1+0 consisting of several spindles,
> NVRAM-based solution (SSD or PCIe card), or a SAN"

Well, it's a tiered approach. If you can identify your tables with the most critical OLTP needs, you can create a separate tablespace specifically for SSD storage to give them the performance they need. After that, you might consider partitioning even those tables, as older data won't be accessed as often, so won't need those kind of IOPS long-term. Older partitions could be slated toward the RAID.

Regarding what kind of SSD, just make sure the drives themselves are capacitor-backed. Current SSDs have only a few microseconds of write delay, but it's enough of a race condition to lead to corruption in power outages without some assurance in-transit data is committed.

If you have the money, stuff like FusionIO PCIe cards are extremely fast, on the order of 10x faster than a standard SSD. I'd personally reserve these for performance-critical things like online trading platforms, since they're so costly.

Then of course, SANs can mix the world of RAID and SSD, because they often have internal mechanisms to deliver requested IOPS by spreading storage allocations along installed components necessary to match them. This is probably the most expensive overall approach, but many larger companies either already have SANs, or will eventually need one anyway.

That's just a bird's eye view of everything. There's obviously more involved. :)

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email