On 10/1/07, S Sharma <data_arch@yahoo.com> wrote:
> Hi All,
>
> The default table space defined in db conf file is used for all database
> tables as well as indexes. So putting the indexes on another table space
> requires manually dropping and re-creating indexes.
> It would be nice to have a feature to define a default table space for
> indexes in db conf file and all indexed are created in that table space.
> This would allow creating a good database architecture to avoid disc
> contention easily.
>
> Thanks
> Data_arch
Although the most basic optimization suggested when using tablespaces
is always "Put indexes on one and data on another to avoid disk
contention", I doubt that the ideal optimization for many workloads,
which means sticking such a thing in a config file might not be such a
good idea. In other words, a DBA probably ought to think harder about
optimizing his/her use of tablespaces than just "I'll put indexes on
this one and data on another". See
http://www.depesz.com/index.php/2007/09/30/finding-optimum-tables-placement-in-2-tablespace-situation/
and http://people.planetpostgresql.org/xzilla/ for two recent blog
posts on the subject. But now I'll be quiet, because I have no
evidence to prove any of the above :)
- Josh