tablespace managed by system vs managed by database

From: "Randall Parker" <randall(at)nls(dot)net>
To: "PostgreSQL-Dev" <pgsql-hackers(at)postgresql(dot)org>
Subject: tablespace managed by system vs managed by database
Date: 2000-06-21 22:36:36
Message-ID: 22341420623989@mail.nls.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I see the continuing discussion here about how to specify and manage tablespaces. I'd like to point out how DB2 does it
since their approach may be worthy of consideration. I've posted some examples and some comments before the
examples.

Note several things about the examples below:

1) A tablespace can be managed by either the database or by the operating system. DB2's terminology is DMS vs SMS
table spaces.

2) You can specify a FILE or a DEVICE or a directory (absence of a FILE or DEVICE keyword means a directory is being
specified) as the place to use as a tablespace.
I assume that the DEVICE keyword is for specifying raw devices so that the OS file system is bypassed entirely. They
don't support DEVICE on all operating systems for which they support DB2 btw.
The second example below is creating a tablespace in 3 directories on 3 drives using syntax that looks like NT or OS/2
syntax for the paths.

3) They allow absolute or relative paths. If relative then its relative to some main database directory for that particular
database.

4) The 10000 and 50000 numbers refer to a number of 4K pages.

5) The EXTENTSIZE is the number of pages to write to a particular directory or file or device before switching to the next
dir, file or device.
They speak of directories, files and devices used in this way as containers.

6) The ON NODE syntax is used in what sounds like clustered configurationsDMS. They refer to its use on MPP servers.

7) DB2 has a good separation of tablespaces and tables.
CREATE TABLE mytable IN mydatatablespace INDEX IN myindextablespace LONG IN myblobtablespace
allows one to pt the table in one table space, the indexes for that table in another tablespace and the LONG VARCHAR,
LOB and other blobish data in yet another tablespace.

CREATE TABLESPACE PAYROLL
MANAGED BY DATABASE
USING (DEVICE'/dev/rhdisk6' 10000,
DEVICE '/dev/rhdisk7' 10000,
DEVICE '/dev/rhdisk8' 10000)
OVERHEAD 24.1
TRANSFERRATE 0.9

CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
EXTENTSIZE 64
PREFETCHSIZE 32

CREATE TEMPORARY TABLESPACE TEMPSPACE2
MANAGED BY DATABASE
USING (FILE '/tmp/tempspace2.f1' 50000,
FILE '/tmp/tempspace2.f2' 50000)
EXTENTSIZE 256

CREATE TABLESPACE PLANS
MANAGED BY DATABASE
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn1hd01' 40000) ON NODE 1
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn3hd03' 40000) ON NODE 3
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn5hd05' 40000) ON NODE 5

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2000-06-21 22:49:38 Makefile.global is kind of a pain
Previous Message Tom Lane 2000-06-21 22:07:11 Re: Changes to functions and triggers