On Mon, May 16, 2011 at 10:19 AM, Robert Klemme
<shortcutter@googlemail.com> wrote:
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.
Well, after reading your article i have been reading some materail about it on the internet, stating that separating indexes from data for performance benefits is a myth.
I found your comment "So then a single query will only ever access one of both at a time." very smart (no sarcasm there).
I also found a thread on AskTom that said mainly "the goal is to achieve even io." (that makes absolute sense)
In my situation, where i need extra space on a SAN, it seems logical to separate the tables from the indexes, to achieve just that: roughly even IO.. (put tables on san, leave indexes on raid10 cluster)
Or am i being silly?
Cheers,
WBL
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw