On Wed, Apr 25, 2012 at 7:08 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>> Is it established practice in the Postgres world to separate indexes
>> from tables? I would assume that the reasoning of Richard Foote -
>> albeit for Oracle databases - is also true for Postgres:
>
> Yes, it's an established practice. I'd call it something just short of
> a best practice though, as it really depends on your situation.
What are the benefits?
> I'd
> take those articles with a grain of salt, as they are very
> Oracle-specific (e.g. we do not have fat indexes (yet!), nor segments).
True. As far as I understand disk layout segments in Oracle serve the
purpose to cluster data for a database object. With that feature
missing the situation would be worse in Postgres - unless you manually
do something similar by using tablespaces for that.
> I also find his examples a bit contrived, and the whole "multi-user"
> argument irrelevant for common cases.
Why is that?
> I lean towards using separate
> tablespaces in Postgres, as the performance outweighs the additional
> complexity.
What about his argument with regards to access patterns (i.e.
interleaving index and table access during an index scan)? Also,
Shaun's advice to have more spindles available sounds convincing to
me, too.
> It's down on the tuning list however: much more important
> is getting your kernel/volumes configured correctly, allocating
> shared_buffers sanely, separating pg_xlog, etc.
That does make a lot of sense. Separating pg_xlog would probably the
first thing I'd do especially since the IO pattern is so dramatically
different from tablespace IO access patterns.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/