Re: [GENERAL] Physical Database Configuration

On Thursday 26 June 2003 20:22, Tom Lane wrote:
> nolan(at)celery(dot)tssi(dot)com writes:
> > I disagree. Just as you can have multiple schemas within one database
> > you can have multiple tablespaces within one database.
> >
> > And the tablespace is irrelevant as far as specifying an object is
> > concerned. A fully qualified object would be:
> > database.schema.object,
> > not tablespace.database.schema.object or
> > database.tablespace.schema.object.
>
> Right, the tablespace structure is really orthogonal to the
> database/schema structure.

Well, consider this. Keep in mind that all of them are directories..

Tablespace1
db1
schema1
table1
table2
db2
schema1
table1
table2

Tablespace2
db1
schema1
index1

Now, unlike Oracle, I don't remember anybody suggesting to get away with
$PGDATA entirely because we have tablespaces. $PGDATA is going to be
available and will continue to host at least one directory for each database.
So postgresql now where to find all tablespaces that hosts that particular
database related object.

Certainly does not sound impossible or illogical to me.

And I agree. The paradigm, 'A tablespace can host multiple database and a
database can span across multiple tablespaces' is absolutely arthgonal.
Convoluted is what I would term it. But hey, it's the most flexible
one!!..:-)

> Physically a tablespace is a directory with sub-directories for
> databases under it --- so $PGDATA/base plays the role of the default
> tablespace for a cluster. (The reason you need per-database
> sub-directories is mostly to support DROP DATABASE, which has to be
> able to nuke a database without knowing exactly what's in it.) But
> this structure doesn't have anything to do with the logical structure
> of the database cluster.

Well, with above proposal, drop database should be as simple. It's just that
it would be more than one `rm -rf`rather than just one.

HTH

Shridhar