Thread: Physical Database Configuration
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
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.
> 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
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 >
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.
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) | +-----------------------------------------------------------
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