Обсуждение: schemas and tablespaces

Hello

I had an issue this day, a project's database
gone too large, and it was hard to track it down.

before my question, i will describe my setup, thus
it may be needed to understand why am i asking whatever
i will ask :)

So, we have a few databases, each database is related
to a developer group. Each group puts their projects
into separate schemas into their own database.
This approach performed very well, since all of
our websites can reuse the same permanent connection,
hance they are in the same database, just needs to
access a different schema.

The issue that happend today was, i've noticed that
one of our client's database had gone too big. It
was a bit hard for him to track down which project
had caused this issue.

For this i had the idea to put all projects into
separate tablespaces by their schemas at the next
upgrade. But as i check the current documentation
there were no such feature that all of a schema's
sub-elements can be put into a tablespace.
You can assign a tablespace to every table, but
it's not possible to define a default tablespace for
new tables according to which schema are they being
created in.

Also using this i could track each project's disk usage.

Currently, we are using 8.1.4

Are there any solutions for this out there? Or any
upcoming solutions in feature versions maybe.

Bye,

Gergely Czuczy
mailto: gergely.czuczy@harmless.hu

--
Weenies test. Geniuses solve problems that arise.

Gergely CZUCZY <phoemix@harmless.hu> writes:
> For this i had the idea to put all projects into
> separate tablespaces by their schemas at the next
> upgrade. But as i check the current documentation
> there were no such feature that all of a schema's
> sub-elements can be put into a tablespace.

Nope.  The original tablespace patch actually did have the notion
of a default tablespace associated with a schema, but we took it
out after a little experience with the patch.  See very long thread
http://archives.postgresql.org/pgsql-hackers/2004-10/msg00583.php

            regards, tom lane

On Fri, Nov 10, 2006 at 10:26:36AM -0500, Tom Lane wrote:
> Gergely CZUCZY <phoemix@harmless.hu> writes:
> > For this i had the idea to put all projects into
> > separate tablespaces by their schemas at the next
> > upgrade. But as i check the current documentation
> > there were no such feature that all of a schema's
> > sub-elements can be put into a tablespace.
>
> Nope.  The original tablespace patch actually did have the notion
> of a default tablespace associated with a schema, but we took it
> out after a little experience with the patch.  See very long thread
> http://archives.postgresql.org/pgsql-hackers/2004-10/msg00583.php
Are you planning to re-enable this functionality in the future?
It may worth reconsidering, an other need arised for it by my issue.

Bye,

Gergely Czuczy
mailto: gergely.czuczy@harmless.hu

--
Weenies test. Geniuses solve problems that arise.