Thread: Physical Database Configuration

Physical Database Configuration

From
Jonathan Bartlett
Date:
I know the current method for specifying alternate drives for PG tables is
by using symlinks.  I had some ideas for simple ways to do this in PG
code, but wanted to know if anyone was working on this right now.  I'd
hate to take the time to start messing with this if others were already on
it.

Jon


Re: Physical Database Configuration

From
"Shridhar Daithankar"
Date:
On 24 Jun 2003 at 14:48, Jonathan Bartlett wrote:

> I know the current method for specifying alternate drives for PG tables is
> by using symlinks.  I had some ideas for simple ways to do this in PG
> code, but wanted to know if anyone was working on this right now.  I'd
> hate to take the time to start messing with this if others were already on
> it.

Well, correct solution is to implement tablespaces on which objects like
databases, tables and indexes can be put.

There was a long discussion on this and there was a tablespaces patch. It was
agreed that tablespace as a set of directories would be a good point to start.

I have no idea what is the status of that effort right now. You can search the
archives or I hope this kicks a fresh discussion..:-)

Please correct me if I am wrong. I am quoting from off my head.. not a trusted
source..

Bye
 Shridhar

--
Harriet's Dining Observation:    In every restaurant, the hardness of the butter
pats    increases in direct proportion to the softness of the bread.


Re: Physical Database Configuration

From
nolan@celery.tssi.com
Date:
> Well, correct solution is to implement tablespaces on which objects like
> databases, tables and indexes can be put.

I've not looked at the SQL standard, but it seems to me like the order
should be:

Databases
   Tablespaces
      Schemas
         Objects (tables, indexes, functions, etc.)

And it really isn't hierarchical.  As I understand them (based on my
Oracle background), tablespaces, unlike schemas, do NOT create a layer
of data abstraction.   That is to say, while the same table name
can exist in multiple schemas, only one instance of a given table name
within a given schema can exist, regardless of what tablespace it is in.

That makes the tablespace a property of an object.

Whether or not two databases can share tablespaces isn't clear to me,
though as a DBA I can think of good reasons why they probably shouldn't
do so, I'm not sure if that is an absolute.

> I have no idea what is the status of that effort right now. You can search the
> archives or I hope this kicks a fresh discussion..:-)

I'm game, though I'm also not ready to lead such a project, probably not
even the discussion on it.
--
Mike Nolan


Re: Physical Database Configuration

From
Jonathan Bartlett
Date:
My solution did not involve tablespaces, but was more of a quick solution
to make it easier for admins to do _some_ sort of physical configuration.

The idea is that the developer could do something like

'create alternate location ALTERNATE_LOCATION_NAME for
DATABASE_OBJECT_NAME at "/PATH/TO/PHYSICAL/FILE";'

We would have a system table holding theses values. Then, all database
commands which create a file for an object, call open_object(oid,
object_name) or something to create the file object.  This will first look
in the new system table to see if there is a mapping for an object of this
name.  If so, it will create a symlink to "/PATH/TO/PHYSICAL/FILE" for the
oid before opening the file.

Anyway, if people are working on tablespaces, I'll defer to them.  This
small fix is something that I might actually have time to do, but
tablespaces definitely not.

Jon

On Wed, 25 Jun 2003 nolan@celery.tssi.com wrote:

> > Well, correct solution is to implement tablespaces on which objects like
> > databases, tables and indexes can be put.
>
> I've not looked at the SQL standard, but it seems to me like the order
> should be:
>
> Databases
>    Tablespaces
>       Schemas
>          Objects (tables, indexes, functions, etc.)
>
> And it really isn't hierarchical.  As I understand them (based on my
> Oracle background), tablespaces, unlike schemas, do NOT create a layer
> of data abstraction.   That is to say, while the same table name
> can exist in multiple schemas, only one instance of a given table name
> within a given schema can exist, regardless of what tablespace it is in.
>
> That makes the tablespace a property of an object.
>
> Whether or not two databases can share tablespaces isn't clear to me,
> though as a DBA I can think of good reasons why they probably shouldn't
> do so, I'm not sure if that is an absolute.
>
> > I have no idea what is the status of that effort right now. You can search the
> > archives or I hope this kicks a fresh discussion..:-)
>
> I'm game, though I'm also not ready to lead such a project, probably not
> even the discussion on it.
> --
> Mike Nolan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: Physical Database Configuration

From
Fernando Schapachnik
Date:
En un mensaje anterior, nolan@celery.tssi.com escribió:
> > Well, correct solution is to implement tablespaces on which objects like
> > databases, tables and indexes can be put.
>
> I've not looked at the SQL standard, but it seems to me like the order
> should be:
>
> Databases
>    Tablespaces
>       Schemas
>          Objects (tables, indexes, functions, etc.)
>

I'm not well versed in the SQL standard here, so maybe this is plain wrong, but
I think it would be nice to have some kind of separation between the logical
structure of the table (developer concern) and the physical disposition (DBA
concern), unlike what Oracle does (CREATE TABLE ... TABLESPACE ... OTHER
PHYSICAL PARAMETERS HERE).

Maybe a way is having storage classes:

CREATE TABLE ... STORAGE CLASS <name>.
STORAGE CLASS <name> TABLESPACE ...

Example of use:

Developer:

CREATE TABLE a (...) STORAGE CLASS big_tuples;
CREATE TABLE b (...) STORAGE CLASS heavy_use;

DBA:

STORAGE CLASS big_tuples TABLESPACE x;
STORAGE CLASS heavy_use TABLESPACE y;

Regards.

Fernando.

Re: Physical Database Configuration

From
Ron Johnson
Date:
On Wed, 2003-06-25 at 10:51, Jonathan Bartlett wrote:
> My solution did not involve tablespaces, but was more of a quick solution
> to make it easier for admins to do _some_ sort of physical configuration.
>
> The idea is that the developer could do something like
>
> 'create alternate location ALTERNATE_LOCATION_NAME for
> DATABASE_OBJECT_NAME at "/PATH/TO/PHYSICAL/FILE";'
>
> We would have a system table holding theses values. Then, all database
> commands which create a file for an object, call open_object(oid,
> object_name) or something to create the file object.  This will first look
> in the new system table to see if there is a mapping for an object of this
> name.  If so, it will create a symlink to "/PATH/TO/PHYSICAL/FILE" for the
> oid before opening the file.
>
> Anyway, if people are working on tablespaces, I'll defer to them.  This
> small fix is something that I might actually have time to do, but
> tablespaces definitely not.

IMHO, this is all that's *really* necessary.  That, and an ALTER
that (takes an exclusive lock on the relevant objects and) lets the
DBA move ALTERNATE_LOCATION_NAME from "/PATH/TO/PHYSICAL/FILE" to
"/NEW/PATH/TO/PHYSICAL/FILE".

On question, though.  I've noticed that once a file grows beyond
1GB, PostgreSQL creates a new file and starts appending to a new
file.  Presumably, that same mode of operation would still occur.

> On Wed, 25 Jun 2003 nolan@celery.tssi.com wrote:
>
> > > Well, correct solution is to implement tablespaces on which objects like
> > > databases, tables and indexes can be put.
> >
> > I've not looked at the SQL standard, but it seems to me like the order
> > should be:
> >
> > Databases
> >    Tablespaces
> >       Schemas
> >          Objects (tables, indexes, functions, etc.)
> >
> > And it really isn't hierarchical.  As I understand them (based on my
> > Oracle background), tablespaces, unlike schemas, do NOT create a layer
> > of data abstraction.   That is to say, while the same table name
> > can exist in multiple schemas, only one instance of a given table name
> > within a given schema can exist, regardless of what tablespace it is in.
> >
> > That makes the tablespace a property of an object.
> >
> > Whether or not two databases can share tablespaces isn't clear to me,
> > though as a DBA I can think of good reasons why they probably shouldn't
> > do so, I'm not sure if that is an absolute.
> >
> > > I have no idea what is the status of that effort right now. You can search the
> > > archives or I hope this kicks a fresh discussion..:-)
> >
> > I'm game, though I'm also not ready to lead such a project, probably not
> > even the discussion on it.
> > --
> > Mike Nolan

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------


Re: [HACKERS] Physical Database Configuration

From
Shridhar Daithankar
Date:
On Wednesday 25 June 2003 21:21, Jonathan Bartlett wrote:
> My solution did not involve tablespaces, but was more of a quick solution
> to make it easier for admins to do _some_ sort of physical configuration.
>
> The idea is that the developer could do something like
>
> 'create alternate location ALTERNATE_LOCATION_NAME for
> DATABASE_OBJECT_NAME at "/PATH/TO/PHYSICAL/FILE";'
>
> We would have a system table holding theses values. Then, all database
> commands which create a file for an object, call open_object(oid,
> object_name) or something to create the file object.  This will first look
> in the new system table to see if there is a mapping for an object of this
> name.  If so, it will create a symlink to "/PATH/TO/PHYSICAL/FILE" for the
> oid before opening the file.

What you are describing is a subset of tablespace functionality we are
discussing offline. There are few minor differences though.

1. There is no physical file but location would be a directory.
2. The location could also be assigned at the time of object creation.

Otherwise it is exactly what is under consideration..:-)

 Shridhar