Physical Database Configuration

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 have started working on tablespaces (to the extent that I am capable!),
based not on the rejected patch, but on Jim's eventual syntax proposal that
was never developed.

eg.

CREATE LOCATION blah AS '/exports/indexes'

CREATE DATABASE db WITH LOCATION loc;

CREAT TABLE foo (a PRIMARY KEY LOCATION blah) LOCATION blah;

..etc...

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.

If anyone wants to help me (as I've not had time to code on it for a while
due to phpPgAdmin), then they can email
me!

I'm working from a top-down perspective - eg. adding new catalog and grammar
and support functions before mucking about with low level storage...

Chris

On 25 Jun 2003 at 14:55, Christopher Kings-Lynne wrote:

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

I have started working on tablespaces (to the extent that I am capable!),
based not on the rejected patch, but on Jim's eventual syntax proposal that
was never developed.

Hmm... Remember feature freeze is 1st of July. So unless you send out a
minimally working patch before that, it won't be considered for 7.4.

CREATE LOCATION blah AS '/exports/indexes'

CREATE DATABASE db WITH LOCATION loc;

CREAT TABLE foo (a PRIMARY KEY LOCATION blah) LOCATION blah;

..etc...

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.

If anyone wants to help me (as I've not had time to code on it for a while
due to phpPgAdmin), then they can email
me!

I'm working from a top-down perspective - eg. adding new catalog and grammar
and support functions before mucking about with low level storage...

I would love to hack this one. Especially putting WAL in a location that is
configurable, I mean PG knowing where to find it's WAL.

If you complete the syntactic part, I guess a very very rough patch should be
possible before feature freeze but that is way tooooooo optimistic.

Besides I don't know what core thinks about introducing such a feature right
now. I think it would be a tough sell at this point of time..

Bye
Shridhar

--
Virtue is a relative term. -- Spock, "Friday's Child", stardate 3499.1

I have started working on tablespaces (to the extent that I am

capable!),

based not on the rejected patch, but on Jim's eventual syntax proposal

that

was never developed.

Hmm... Remember feature freeze is 1st of July. So unless you send out a
minimally working patch before that, it won't be considered for 7.4.

I have no intention of having it ready anywhere near 7.4 :)

I'm working from a top-down perspective - eg. adding new catalog and

grammar

and support functions before mucking about with low level storage...

I would love to hack this one. Especially putting WAL in a location that

is

configurable, I mean PG knowing where to find it's WAL.

This patch won't affect WAL location - that's a separate issue.

If you complete the syntactic part, I guess a very very rough patch should

be

possible before feature freeze but that is way tooooooo optimistic.

Not going to happen :) I haven't done very much on it yet.

Chris

On 25 Jun 2003 at 12:30, Shridhar Daithankar wrote:

On 25 Jun 2003 at 14:55, Christopher Kings-Lynne wrote:

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

I have started working on tablespaces (to the extent that I am capable!),
based not on the rejected patch, but on Jim's eventual syntax proposal that
was never developed.

For reference,

http://archives.postgresql.org/pgsql-hackers/2002-09/msg01780.php

Bye
Shridhar

--
Rules for Academic Deans: (1) HIDE!!!! (2) If they find you, LIE!!!! --
Father Damian C. Fandal

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

DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle
"extent" madness.

andrew

AgentM wrote:

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

I have started working on tablespaces (to the extent that I am capable!),
based not on the rejected patch, but on Jim's eventual syntax proposal that
was never developed.

Has anyone looked at the syntaxes used by other databases to control
tablespaces (Oracle, DB2, etc)? I have no strong desire to slavishly
follow Oracle, but it would be a shame to miss out on any good ideas.

regards, tom lane

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:

On Wednesday 25 June 2003 20:49, 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.)

That should be

Tablespaces
databases
schemas
objects

with each of them implemented as a directory and data files under it. If we
could get a quota check propogated in both direction, that would be pretty
good, may be a warning when things start getting close to limit.

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.

Well, if same table name exists in two different databases under same
tablespace, what's the problem?

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.

Well, I would say they should be allowed to.

Shridhar

On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:

Has anyone looked at the syntaxes used by other databases to control
tablespaces (Oracle, DB2, etc)? I have no strong desire to
slavishly follow Oracle, but it would be a shame to miss out on any
good ideas.

DB2:

CREATE TABLESPACE spacename ...

ALTER TABLESPACE spacename ...

RENAME TABLESPACE spacename TO newspacename

CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN spacename]

"INDEX IN" and "LONG IN" refer to the tablespace used to store the
indices and the LOB values for that table, respectively.

The create syntax revolves around nodegroups and such which are DB2
concepts i don't fully grok (i'm a programmer, not a DBA).

But, yeah, those are really the only entrypoints. You can't create an
index in a specific tablespace -- it will go wherever the table is set
to put indices.

I like the syntax ("IN spacename"), though. It's simple and
straightforward.

-johnnnnnnnnnn

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 Wednesday, June 25, 2003, at 12:10 PM, johnnnnnn wrote:

On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:

Has anyone looked at the syntaxes used by other databases to control
tablespaces (Oracle, DB2, etc)? I have no strong desire to
slavishly follow Oracle, but it would be a shame to miss out on any
good ideas.

DB2:
CREATE TABLESPACE spacename ...
ALTER TABLESPACE spacename ...
RENAME TABLESPACE spacename TO newspacename
CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN
spacename]
"INDEX IN" and "LONG IN" refer to the tablespace used to store the
indices and the LOB values for that table, respectively.
The create syntax revolves around nodegroups and such which are DB2
concepts i don't fully grok (i'm a programmer, not a DBA).
But, yeah, those are really the only entrypoints. You can't create an
index in a specific tablespace -- it will go wherever the table is set
to put indices.
I like the syntax ("IN spacename"), though. It's simple and
straightforward.

Oracle 8 examples:

CREATE TABLE name(dud INTEGER) storage 8M next 4M pctincrease 0
minextents 1 maxextents 200 tablespace TSNAME;

where storage, next, pctincrease, minextents, and maxentents are table
space usage granularity requests.

CREATE TABLESPACE TSNAME datafile '/path/file.dbf' size 100M,
'/another/file.dbf' size 50M default storage (initial 1M next 1M
pctincrease 0 maxentents 249);

where each comma-delimited item is an "extent"- simply put, a block
which Oracle is allowed to use for storage.

ALTER TABLESPACE TEMP ...;

allows for arbitrary placement of temporary table storage (higher-speed
area?)

ALTER TABLESPACE TSNAME default storage (...);

changes settings for tablespace.

ALTER TABLESPACE TSNAME coalesce;

more extent "optimization" granularlity.

CREATE ROLLBACK SEGMENT R1 tablespace TSNAME2 storage (...);

which allocates space for a rollback area.

ALTER ROLLBACK SEGMENT R1 offline/online;

allows for cleanup of rollback segment's area.

CREATE TABLE name(dud INTEGER PRIMARY KEY USING INDEX ....);

allows for pointing an index to a tablespace.

CREATE INDEX ind ON table(col) global/local partition by range(col)
(partition PART1 values less than (11) tablespace TS1,
partition PART2 values less than (21) tablespace TS2,
....
partition PART3 values less than (MAXVALUE) tablespace TS3);

allows for a partioned index across tablespaces, but whose grammar
setup could use some work.

ALTER TABLE table MODIFY PARTITION part1storage (...) logging/nologging
MOVE PARTITION
ADD PARTITION part1 values less than (...)
DROP PARTITION
TRUNCATE PARTITION
SPLIT PARTITION ... INTO ...
EXCHANGE PARTITION

a nasty alter table command related to partitions (a tablespace can
have multiple partitions).

I post this just so there a flavor of how many "optimization" options
are available in Oracle 8. Personally, I would prefer not to have so
many options but this listing should help folks so they don't paint
themselves into a corner while coding on the tablespaces.

All examples courtesy of "Oracle 8: Advanced Tuning and
Administration", Aronoff, Eyal, et al. ASIN: 0078822416 (c) 1998.
(perhaps a little outdated)

<><><><><><><><><

AgentM
agentm@cmu.edu

On Wed, 25 Jun 2003, Tom Lane wrote:

Has anyone looked at the syntaxes used by other databases to control
tablespaces (Oracle, DB2, etc)? I have no strong desire to slavishly
follow Oracle, but it would be a shame to miss out on any good ideas.

Informix is pretty bad.
First, you use an external app to create the tablespace (known as a
dbspace to informix). Lets call the new one 'newspace'. (the syntax is
onspaces -c -d newspace -p /path/to/space -s size_in_kb -o
offset_in_file I'll cry if we have something liek that in pg)

then to 'use' the space:

create table|index ... in newspace

There's a bizzare syntax for copying a table from one space to another,
but it is mostly useless since it runs in a transaction and if you have a
big table.. well you get the idea.

Where it gets more interesting is table fragments. Informix is able to
fragment a table based on a few different criteria. Each fragment goes in
a separate dbspace and the idea is the planner is smart enough to realize
that it can parellelize seq scans and various other IO operations... but
given the nature of postgres I don't think we could build something like
that...

(for the record, the fragment types are round robin and expression. You
can fragment based on a limited-edition where clause.. )

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

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 Wed, Jun 25, 2003 at 10:30:31AM -0500, Andrew Dunstan wrote:

DB2 looks good. I have horrid, horrid memories of wrestling with the
Oracle "extent" madness.

I do think that it's worth providing additional access points to
tablespaces, though. That is, it would make sense to me to allow
"CREATE INDEX indexname IN spacename", instead of attaching an
indexspace to a table.

This is especially true with postgresql, since i've seen more than one
proposal for multi-table indices. If we're spacing indices based on
the table, it's unclear where a given multi-table index should go.

It would also allow for other flexibilities, like putting join indices
(on foreign keys) in one tablespace, with indices for aggregation or
sorting in another tablespace.

So, my vote, as a non-code-contributing member, would be for a
DB2-style syntax, without the "INDEX IN" and "LONG IN" extensions, but
with the ability to put indices explicitly into a tablespace.

-johnnnnnn

DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle
"extent" madness.

I think Oracle's extents came from their fixed size data file legacy, in 9i
the extent limits appear to be completely overridable and sometimes even
ignored, such as the next extent size. I agree that the 128 extent limit
was a pain, and the default for each new extent to be larger than the
previous one created many problems.

Oracle also took physical abstraction one level beyond 'tablespaces'.
I think if each tablespace pointed to a specific directory, that'd be
sufficient for me. And since I envision the tablespace as an attribute
of the table that should take care of the 1GB file rollover issue, as
the rollover would occur in the same directory as the first file.

Without having delved into the code yet, setting up entries for user
default tablespaces and system information is probably at least as much
work as getting a tablespace to point to a specific directory for the
purposes of opening or creating files for an object.

My personal preference would be to have four tablespaces predefined as part
of a new database, though initially they could all point to the same place:

SYSTEM
USER
TEMP
INDEXES

What about the concepts of a 'read-only' tablespace, or taking tablespaces
offline?
--
Mike Nolan

johnnnnnn wrote:

On Wed, Jun 25, 2003 at 10:30:31AM -0500, Andrew Dunstan wrote:

DB2 looks good. I have horrid, horrid memories of wrestling with the
Oracle "extent" madness.

I do think that it's worth providing additional access points to
tablespaces, though. That is, it would make sense to me to allow
"CREATE INDEX indexname IN spacename", instead of attaching an
indexspace to a table.

This is especially true with postgresql, since i've seen more than one
proposal for multi-table indices. If we're spacing indices based on
the table, it's unclear where a given multi-table index should go.

It would also allow for other flexibilities, like putting join indices
(on foreign keys) in one tablespace, with indices for aggregation or
sorting in another tablespace.

I wonder why an index spanning multiple tables should be stored in a
different location than the tables itself. If we're talking about
derived tables, all data/index must be available at the same time to be
meaningful, so why not restrict them to the same tablespace? This sounds
like more flexibility than really useful to me.

The philosophy of pgsql is to let the os and the io system distribute
the load over disks and other resources, not to do it in the backend.
That's why we need much less organizational effort than other systems
that try to implement everything themselves, on raw devices etc.

Regards,
Andreas

That should be

Tablespaces
databases
schemas
objects

with each of them implemented as a directory and data files under it. If we
could get a quota check propogated in both direction, that would be pretty
good, may be a warning when things start getting close to limit.

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.
--
Mike Nolan