Re: [PERFORMANCE] expanding to SAN: which portion best to move

On Mon, May 16, 2011 at 4:19 AM, Robert Klemme
<shortcutter(at)googlemail(dot)com> wrote:
>> - If the planner chooses a Bitmap Index Scan, it effectively scans the
>> index to figure out which table blocks to read, and then reads those
>> table blocks in block number order, so that the I/O is sequential,
>> with skips.
>
> Are these two separate phases (i.e. first scan index completely, then
> access table)?

Yes.

> Separating index and tables might not be a totally good idea
> generally.  Richard Foote has an excellent article about Oracle but I
> assume at least a few things do apply to PostgreSQL as well - it's at
> least worth as something to check PostgreSQL's access patterns
> against:
>
> http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-some-thoughts-part-i-everything-in-its-right-place/
>
> I would probably rather try to separate data by the nature and
> frequency of accesses.  One reasonable separation would be to leave
> all frequently accessed tables *and* their indexes on local RAID and
> moving less frequently accessed data to the SAN.  This separation
> could be easily identified if you have separate tables for current and
> historic data.

Yeah, I think the idea of putting tables and indexes in separate
tablespaces is mostly to bring more I/O bandwidth to bear on the same
data. But there are other reasonable things you might do also - e.g.
put the indexes on an SSD, and the tables on a spinning disk, figuring
that the SSD is less reliable but you can always rebuild the index if
you need to...

Also, a lot of people have reported big speedups from putting pg_xlog
on a dedicated RAID 1 pair, or moving the PostgreSQL logs off the data
partition. So those sorts of divisions should be considered also.
Your idea of dividing things by access frequency is another good
thought.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company