Thread: Feature Request - Defining default table space for Indexes in Conf file

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

---

Boardwalk for $500? In 2007? Ha!
Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/01/07 13:22, S Sharma 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.

ALTER INDEX foo SET TABLESPACE bar;

> This would allow creating a good database architecture to avoid disc
> contention easily.

How difficult is it to specify tablespace when creating an index?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHAnExS9HxQb37XmcRAiceAJ9vUNKVa8voo2gISHhzDgKY4OOkuQCgxuxG
jR6S8CY4INa+fKbOE00oqZk=
=3QvI
-----END PGP SIGNATURE-----

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

"Josh Tolley" <eggyknap@gmail.com> writes:
> On 10/1/07, S Sharma <data_arch@yahoo.com> wrote:
>> 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.

> 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".

Yeah, I think that argument is why we did not provide such a setup to
begin with...

            regards, tom lane