Storage Location Patch Proposal for V7.3

"Jim Buttafuoco" <jim@buttafuoco.net> writes:

I propose to add a default data location, index and temporary locations
to the pg_shadow table to allow a DBA to specify locations for each
user when they create databases, tables and indexes or need temporary
disk storage (either for temporary tables or sort files).

Have you read any of the previous discussions about tablespaces?
This seems to be tablespaces with an off-the-cuff syntax. I'd
suggest taking a hard look at Oracle's tablespace facility and
seeing how closely we want to duplicate that.

PG_SHADOW add dat_location, idx_location, tmp_location (all default to
PG_DATA)

What does location have to do with users?

I propose to change the names of the on disk directories from 999999 to
99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from
PG_DATABASE).

No, that doesn't scale to arbitrary locations; furthermore it requires
an unseemly amount of knowledge in low-level file access code about
exactly what kind of object each table is. The symlinks should just
be named after the OIDs of the locations' rows in pg_location.

The direction I've been envisioning for this is that each table has
a logical identification <pg_database OID>, <pg_class OID> as well
as a physical identification <pg_location OID>, <relfilenode OID>.
The path to access the table can be constructed entirely from the
physical identification: $PGDATA/base/<pg_location OID>/<relfilenode OID>.

One problem to be addressed if multiple databases can share a single
physical location is how to prevent relfilenode collisions. Perhaps
we could avoid the issue by adding another layer of subdirectories:
$PGDATA/base/<pg_location OID>/<pg_database OID>/<relfilenode OID>.
That is, each database would have a subdirectory within each location
that it's ever used. (This would make DROP DATABASE a lot easier,
among other things.)

regards, tom lane

Tom,

Yes, locations = tablespaces (I really don't care if we call them
locaitons or tablespaces, I was just using LOCATIONS because that's what
we have now...) is there a SQL standard for this???.

As for locations and user, Under Oracle a user is assigned a default
tablespace and a temporary tablespace via the "CREATE USER" command.
Also "CREATE DATABASE" allows you to specify the SYSTEM tablespace where
all objects will go unless a storage clause is added duration object
creation. "CREATE TABLE" and "CREATE INDEX" both take a storage clause.

As for the actual data file location, I believe under each loc oid we
would have pg_port #/DB OID/pg_class OID might be the way to go.

The example below has 3 tablespaces/locations PGDATA/DB1/DB2
PG_LOCATIONS (or PG_TABLESPACES) would have the following rows
PGDATA | /usr/local/pgsql/data
DB1 | /db1
DB2 | /db2

/usr/local/pgsql/data/5432/1 <<template1
^----------- <<default location/tablespace
^--------- <<Default PG Port

/db1/data/5432
^-------------------------<< second location default PG PORT
/db1/data/5432/65894834/99999999
^------<< somedb/sometable
/db1/data/5432/65894834/88888888
^------<< somedb/someindex

/db2/data/5432
^-------------------------<< DB2

"Jim Buttafuoco" <jim@buttafuoco.net> writes:

I propose to add a default data location, index and temporary

locations

to the pg_shadow table to allow a DBA to specify locations for each
user when they create databases, tables and indexes or need

temporary

disk storage (either for temporary tables or sort files).

Have you read any of the previous discussions about tablespaces?
This seems to be tablespaces with an off-the-cuff syntax. I'd
suggest taking a hard look at Oracle's tablespace facility and
seeing how closely we want to duplicate that.

PG_SHADOW add dat_location, idx_location, tmp_location (all default

to

PG_DATA)

What does location have to do with users?

I propose to change the names of the on disk directories from 999999

to

99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from
PG_DATABASE).

No, that doesn't scale to arbitrary locations; furthermore it requires
an unseemly amount of knowledge in low-level file access code about
exactly what kind of object each table is. The symlinks should just
be named after the OIDs of the locations' rows in pg_location.

The direction I've been envisioning for this is that each table has
a logical identification <pg_database OID>, <pg_class OID> as well
as a physical identification <pg_location OID>, <relfilenode OID>.
The path to access the table can be constructed entirely from the
physical identification: $PGDATA/base/<pg_location OID>/<relfilenode

OID>.

"Jim Buttafuoco" <jim@buttafuoco.net> writes:

As for the actual data file location, I believe under each loc oid we
would have pg_port #/DB OID/pg_class OID might be the way to go.

Introducing pg_port into the paths would be a bad idea, since it
would prevent restarting a postmaster with a different port number.
I think if a DBA is running multiple postmasters, it's up to him
to avoid pointing more than one of them at the same "location"
directory. (Maybe we could enforce that with lock files? Not
sure it's worth the trouble though.)

regards, tom lane

Tom Lane wrote:

"Jim Buttafuoco" <jim@buttafuoco.net> writes:

I propose to add a default data location, index and temporary locations
to the pg_shadow table to allow a DBA to specify locations for each
user when they create databases, tables and indexes or need temporary
disk storage (either for temporary tables or sort files).

Have you read any of the previous discussions about tablespaces?
This seems to be tablespaces with an off-the-cuff syntax. I'd
suggest taking a hard look at Oracle's tablespace facility and
seeing how closely we want to duplicate that.

Sorry I missed the conversation about tablespaces. One of the reasons I think
Postgres is so usable is because it does not require the use of tablespace
files. If by tablespace, you mean to declare a directory on a device as a
tablespace, then cool. If you want to create tablespace "files" ala Oracle, you
are heading toward an administration nightmare. Don't get me wrong, the ability
to use a file as a tablespace would be kind of cool, i.e. you can probably use
raw devices, but please to not abandon the way postgres currently works.

On our Oracle server, we have run out of space on our tablespace files and not
known it was coming. I am the system architect, not the DBA, so I don't have
(nor want) direct control over the oracle database operation. Our newbe DBA did
not make the table correctly, so they did not grow. Alas he was laid off, thus
we were left trying to figure out what was happening.

Postgres is easier to configure and get right. IMHO that is one of its very
important strengths. It is almost trivial to get a working SQL system up and
running which performs well.

Tom,

I was just looking for a way to keep different postmasters out of each
others way. If you think the DBA can do it great or if the DBA changes
port have him/her fix the locations.

Jim

"Jim Buttafuoco" <jim@buttafuoco.net> writes:

As for the actual data file location, I believe under each loc oid

we

mlw <markw@mohawksoft.com> writes:

Tom Lane wrote:

This seems to be tablespaces with an off-the-cuff syntax. I'd
suggest taking a hard look at Oracle's tablespace facility and
seeing how closely we want to duplicate that.

Sorry I missed the conversation about tablespaces. One of the reasons I think
Postgres is so usable is because it does not require the use of tablespace
files. If by tablespace, you mean to declare a directory on a device as a
tablespace, then cool. If you want to create tablespace "files" ala Oracle, you
are heading toward an administration nightmare.

No, that's not one of the parts of Oracle's facility that I want to
duplicate.

I think our idea of a tablespace/location/whatchacallit should just be
a directory somewhere that table files can be created in. What seems
worthwhile to steal from Oracle is the syntax that assigns particular
tables to particular tablespaces. If we're compatible on syntax, that
should ease porting of existing applications --- and as far as I can see
at the moment, there's no reason *not* to be compatible at that level.
I don't want to borrow Oracle's ideas about space management semantics,
however.

regards, tom lane

Mark,

This is why I choose to use the term "LOCATION" instead of "TABLESPACE"
. A "LOCATION" is a directory just like Postgresql has today. All the
patch would add is the ability to put object under different "LOCATION"
for the same database.

Jim

Tom Lane wrote:

"Jim Buttafuoco" <jim@buttafuoco.net> writes:

I propose to add a default data location, index and temporary

locations

to the pg_shadow table to allow a DBA to specify locations for

each

user when they create databases, tables and indexes or need

temporary

disk storage (either for temporary tables or sort files).

Have you read any of the previous discussions about tablespaces?
This seems to be tablespaces with an off-the-cuff syntax. I'd
suggest taking a hard look at Oracle's tablespace facility and
seeing how closely we want to duplicate that.

Sorry I missed the conversation about tablespaces. One of the reasons

I think

Postgres is so usable is because it does not require the use of

tablespace

files. If by tablespace, you mean to declare a directory on a device

as a

tablespace, then cool. If you want to create tablespace "files" ala

Oracle, you

are heading toward an administration nightmare. Don't get me wrong,

the ability

to use a file as a tablespace would be kind of cool, i.e. you can

probably use

raw devices, but please to not abandon the way postgres currently

works.

On our Oracle server, we have run out of space on our tablespace files

and not

known it was coming. I am the system architect, not the DBA, so I

don't have

(nor want) direct control over the oracle database operation. Our

newbe DBA did

not make the table correctly, so they did not grow. Alas he was laid

off, thus

we were left trying to figure out what was happening.

Postgres is easier to configure and get right. IMHO that is one of its

very

important strengths. It is almost trivial to get a working SQL system

up and

Jim Buttafuoco wrote:

Mark,

This is why I choose to use the term "LOCATION" instead of "TABLESPACE"
. A "LOCATION" is a directory just like Postgresql has today. All the
patch would add is the ability to put object under different "LOCATION"
for the same database.

That is a very excellent point. While I am not in the circle that makes these
decisions, I hope your words are heard.

I understand the desire to stay with "standards" and it is impossible to deny
defacto standards, but I do understand that defacto standards have to be
challenged when they don't make sense. A prime example is PostgreSQL's
inner/outer join syntax. It is incompatible with Oracle, but compatible with
the documented SQL standard.

Since "tablespace" is not part of the SQL standard, maybe it makes sense to
define a more specific syntax. The term "location" makes sense, because it is
not a tablespace as Oracle defines it. There is a real danger is trying to
support a different interpretation of an existing "defacto" syntax, in that it
will behave differently than expected.

Since "tablespace" is not part of the SQL standard, maybe it makes

sense to

define a more specific syntax. The term "location" makes sense,

because it is

not a tablespace as Oracle defines it.

It *is* an "OS managed tablespace" in terms of IBM DB2.
Methinks the term "TABLESPACE" is perfect for PostgreSQL.
The fact whether it is a directory, a file or even a raw device
depends on how you create the tablespace.

The point is, that the syntax for "create table" and "create index"
can be compatible in this case, imho without confusing many.
Not the "create tablespace" syntax, but that is imho not an issue.

Andreas

I just wanted to affirm that Tom's description sounds like av very good
way to go.

You get the best of two worlds with the possibility to tune servers and
yet still
very easy to manage. i.e. If you don't need it, don't mess with it and
everything
will work just fine.
I don't either see any reason not to use the Oracle syntax since it is
so widely used
and it works very well for those of us that also work on Oracle (but in
postgresql
without the extent and storage clauses).

Regards
Stefan

Tom Lane wrote:

Stefan Rindeskar wrote:

I just wanted to affirm that Tom's description sounds like av very good
way to go.

You get the best of two worlds with the possibility to tune servers and
yet still
very easy to manage. i.e. If you don't need it, don't mess with it and
everything
will work just fine.
I don't either see any reason not to use the Oracle syntax since it is
so widely used
and it works very well for those of us that also work on Oracle (but in
postgresql
without the extent and storage clauses).

I absolutely agree with the concept of defining a location for data from within
the database. No argument.

The only two issues I can see are:

(1) Do not require the use of files as table spaces ala Oracle. That is an
admin nightmare. (Again, it would be cool, however, to be able to use table
space files so that PostgreSQL could have raw access as long as it is not a
requirement.) I don't think Tom is thinking about table space files, so I'm not
worried.

(2) I have a concern about expected behavior vs existing syntax. If PostgreSQL
uses "create tablespace" in such a way that an Oracle DBA will expect it to
work as Oracle does, it may cause a bit of confusion. We all know that
"confusion" between an open source solution and a "defacto" solution is used as
club.

I am at home now, Tomorrow I will get the exact Oracle syntax and post
example commands for CREATE TABLESPACE, CREATE DATABASE, CREATE TABLE
and CREATE INDEX, CREATE USER.

Jim

On Mon, Nov 05, 2001 at 12:26:17PM -0500, Jim Buttafuoco allegedly wrote:

The example below has 3 tablespaces/locations PGDATA/DB1/DB2
PG_LOCATIONS (or PG_TABLESPACES) would have the following rows
PGDATA | /usr/local/pgsql/data
DB1 | /db1
DB2 | /db2

<SNIP>

/db1/data/5432
^-------------------------<< second location default PG PORT
/db1/data/5432/65894834/99999999
^------<< somedb/sometable
/db1/data/5432/65894834/88888888
^------<< somedb/someindex

/db2/data/5432
^-------------------------<< DB2

Should data/ even be in there? /db2/5432 seems to be the correct value.
Either that or change the location to /db2/data. Implicitly creating an
extra directory isn't something I would like to happen, especially if it
doesn't happen for PGDATA itself.

My $.02,

Mathijs
--
And the beast shall be made legion. Its numbers shall be increased a
thousand thousand fold. The din of a million keyboards like unto a great
storm shall cover the earth, and the followers of Mammon shall tremble.

Jim, I see now that you submitted a new version. Folks, do we have a
direction for this patch. Discussion of the patch is at:

http://candle.pha.pa.us/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Jim Buttafuoco wrote:

Hi all,

The following is a description of a patch I am proposing for 7.3.
Please read and comment.

Thanks
Jim

This proposal covers the ability to allow a DBA (and general users) to
specify where a database and it's individual objects will reside. I
propose to add a default data location, index and temporary locations
to the pg_shadow table to allow a DBA to specify locations for each
user when they create databases, tables and indexes or need temporary
disk storage (either for temporary tables or sort files). The "CREATE
DATABASE" command will be changed to also take an INDEX location and
temporary location. All 3 locations will default to the values from
pg_shadow for the user that is creating the database. Both the "CREATE
TABLE" and "CREATE INDEX" commands will be changed to add "WITH
LOCATION" optional argument (location will default to values from
PG_DATABASE which were set by the "CREATE DATABASE" command).

The following system tables will be changed as follows
PG_SHADOW add dat_location, idx_location, tmp_location (all default to
PG_DATA)
PG_DATABASE add dat_location, idx_location, tmp_location (all default
to same from PG_SHADOW)
PG_CLASS add rellocation (default to dat_location for tables,
idx_location for indexes from PG_DATABASE)

Add a GLOBAL table pg_locations to track valid locations

Add the following commands to manage locations
CREATE LOCATION locname PATH 'file system directory';
DROP LOCATION locname; (this will have to look into each db to make
sure that any objects are not using it. Don't know how this will be
done yet!)

I propose to change the names of the on disk directories from 999999 to
99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from
PG_DATABASE). A SYMLINK from 99999_INDEX and 99999_TEMP will be made
back to 99999_DATA will be made so the WAL functions will continue to
work.

Again from my earlier attempt at this patch, I believe this capability
will not only improve performance (see my earlier emails. Where
depending on the type of disks the improvement was between 0% and 100%
performance gain running pg_bench) but also give DBA's the flexibility
to spread the data files over multiple disks without having to "hack"
the system using symbolic links.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jim, I see now that you submitted a new version. Folks, do we have a
direction for this patch.

I didn't like it at the time, and still don't. We are not that far away
from having proper tablespaces, and I think that kluges that provide
part of the functionality will just get in the way when it comes time
to do it right.

regards, tom lane

On Fri, 22 Feb 2002, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jim, I see now that you submitted a new version. Folks, do we have a
direction for this patch.

I didn't like it at the time, and still don't. We are not that far away
from having proper tablespaces, and I think that kluges that provide
part of the functionality will just get in the way when it comes time
to do it right.

What kind of time frame is "not that far away"? For v7.3?

If not, and someone can clarify what I'm understanding this patch will do,
its essentially going to setup a directory structure of:

data/base/<dboid>/<tbloid>.idx/indx

?

If we aren't going to have tablespaces for v7.3, there we are talking 6->8
months before we do, and the above sounds like a reasonable interim
solution for this ...

"Marc G. Fournier" <scrappy@hub.org> writes:

On Fri, 22 Feb 2002, Tom Lane wrote:

I didn't like it at the time, and still don't. We are not that far away
from having proper tablespaces, and I think that kluges that provide
part of the functionality will just get in the way when it comes time
to do it right.

What kind of time frame is "not that far away"? For v7.3?

My guess is that any of the inner circle of hackers could make this
happen with about a week's work. Whether someone will find time before
7.3 is unknown (particularly seeing that we haven't set a target date
for 7.3). Personally, schemas are a higher priority for me ...

regards, tom lane

All,

I still believe that postgresql needs this feature. I have many postgresql
systems that have over 500GB of data+indexes. Using symbolic links is a BIG
pain in the A??. Every time I run vacuum I have to go and fix the links
again. Also I have many disks that are running out of space. This patch
would allow me the ability to move my tables and indexes around. I
personally don't see the difference between my patch and what people are
calling "Tablespaces" . Oracle's definition is "A group of files that contain
database objects" , under my patch tablespaces and locations are the same
thing except postgresql uses file system directories to contain the group of
objects.

To recap my patch (location = tablespace here)

Allow the DBA to create locations with a CREATE LOCATION command or CREATE
TABLESPACE command if you like tablespace instead of LOCATION.

Then for DATABASES (and schemas when available) CREATE DATABASE WITH
DATA_LOCATION = XXX and INDEX_LOCATION = YYY where XXX and YYY are the
DEFAULT values for OBJECT creation if not LOCATION is given.

CREATE TABLE and CREATE INDEX will create tables and indexes in the defaults
from the CREATE DATABASE/SCHEMA commands above.

CREATE TABLE WITH LOCATION=AAA and CREATE INDEX WITH LOCATION BBB would create
the table/index with the alternate location (only if the location was created
with a CREATE LOCATION command)

The create table command would also have to be change to support primary key/
unique index syntax.

create table SAMPLE
(
c1 text primary key location CCC,
c2 text unique location DDD
);

I hope this explains my patch better. As I said before and I believe this
to be true, This patch will enable the DBA to place tables/indexes on any
disk either for performance and/or space reasons. Also I believe this is
another check off item for people looking at postgresql when comparing with
Oracle/Sybase/DB2 ...

Thanks for your time
Jim

I think Jim has some very good points here. What does his
implementation lack? Seems pretty valuable to me.

---------------------------------------------------------------------------

Jim Buttafuoco wrote:

All,

I still believe that postgresql needs this feature. I have many postgresql
systems that have over 500GB of data+indexes. Using symbolic links is a BIG
pain in the A??. Every time I run vacuum I have to go and fix the links
again. Also I have many disks that are running out of space. This patch
would allow me the ability to move my tables and indexes around. I
personally don't see the difference between my patch and what people are
calling "Tablespaces" . Oracle's definition is "A group of files that contain
database objects" , under my patch tablespaces and locations are the same
thing except postgresql uses file system directories to contain the group of
objects.

To recap my patch (location = tablespace here)

Allow the DBA to create locations with a CREATE LOCATION command or CREATE
TABLESPACE command if you like tablespace instead of LOCATION.

Then for DATABASES (and schemas when available) CREATE DATABASE WITH
DATA_LOCATION = XXX and INDEX_LOCATION = YYY where XXX and YYY are the
DEFAULT values for OBJECT creation if not LOCATION is given.

CREATE TABLE and CREATE INDEX will create tables and indexes in the defaults
from the CREATE DATABASE/SCHEMA commands above.

CREATE TABLE WITH LOCATION=AAA and CREATE INDEX WITH LOCATION BBB would create
the table/index with the alternate location (only if the location was created
with a CREATE LOCATION command)

The create table command would also have to be change to support primary key/
unique index syntax.

create table SAMPLE
(
c1 text primary key location CCC,
c2 text unique location DDD
);

I hope this explains my patch better. As I said before and I believe this
to be true, This patch will enable the DBA to place tables/indexes on any
disk either for performance and/or space reasons. Also I believe this is
another check off item for people looking at postgresql when comparing with
Oracle/Sybase/DB2 ...

Thanks for your time
Jim

Jim, I see now that you submitted a new version. Folks, do we have a
direction for this patch. Discussion of the patch is at:

http://candle.pha.pa.us/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Jim Buttafuoco wrote:

Hi all,

The following is a description of a patch I am proposing for 7.3.
Please read and comment.

Thanks
Jim

This proposal covers the ability to allow a DBA (and general users) to
specify where a database and it's individual objects will reside. I
propose to add a default data location, index and temporary locations
to the pg_shadow table to allow a DBA to specify locations for each
user when they create databases, tables and indexes or need temporary
disk storage (either for temporary tables or sort files). The "CREATE
DATABASE" command will be changed to also take an INDEX location and
temporary location. All 3 locations will default to the values from
pg_shadow for the user that is creating the database. Both the "CREATE
TABLE" and "CREATE INDEX" commands will be changed to add "WITH
LOCATION" optional argument (location will default to values from
PG_DATABASE which were set by the "CREATE DATABASE" command).

The following system tables will be changed as follows
PG_SHADOW add dat_location, idx_location, tmp_location (all default to
PG_DATA)
PG_DATABASE add dat_location, idx_location, tmp_location (all default
to same from PG_SHADOW)
PG_CLASS add rellocation (default to dat_location for tables,
idx_location for indexes from PG_DATABASE)

Add a GLOBAL table pg_locations to track valid locations

Add the following commands to manage locations
CREATE LOCATION locname PATH 'file system directory';
DROP LOCATION locname; (this will have to look into each db to make
sure that any objects are not using it. Don't know how this will be
done yet!)

I propose to change the names of the on disk directories from 999999 to
99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from
PG_DATABASE). A SYMLINK from 99999_INDEX and 99999_TEMP will be made
back to 99999_DATA will be made so the WAL functions will continue to
work.

Again from my earlier attempt at this patch, I believe this capability
will not only improve performance (see my earlier emails. Where
depending on the type of disks the improvement was between 0% and 100%
performance gain running pg_bench) but also give DBA's the flexibility
to spread the data files over multiple disks without having to "hack"
the system using symbolic links.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026