Re: Table spaces again [was Re: Threaded Sorting]

On 7 Oct 2002 at 15:52, Hans-Jürgen Schönig wrote:

> >Can anybody please tell me in detail.(Not just a pointing towards TODO items)
> >1) What a table space supposed to offer?
> They allow you to define a maximum amount of storage for a certain set
> of data.

Use quota

> They help you to define the location of data.

Mount/symlink whereever you want assuming database supports one directory per
object metaphor. This is finer control than tablespaces as tablespaces often
hosts many objects from possibly many databases. Once a tablespace is created,
it's difficult to keep track of what all goes on a table space. You look at
directory structure and you get a clear picture..

> They help you to define how much data can be used by which ressource.

Which resource? I am confused. Disk space is only resource we are talking
about.

> >2) What a directory structure does not offer that table space does?
> You need to the command line in order to manage quotas - you might not
> want that.

Mount a directory on a partition. If the data exceeds on that partition, there
would be disk error. Like tablespace getting overflown. I have seen both the
scenarios in action..

> Quotas are handled differently on ever platform (if available).

Yeah. But that's sysadmins responsibility not DBA's.

> With tablespaces you can assign 30mb to use a, 120mb to user b etc. ...
> Table spaces are a nice abstraction layer to the file system.

Hmm.. And how does that fit in database metaphor? What practical use is that? I
can't imagine as I am a developer and not a DBA.

> how would you handle table spaces? just propose it to the hackers' list ...
> we should definitely discuss that ...
> a bad implementation of table spaces would be painful ...

I suggest a directory per object structure. A database gets it's own directory,
an index gets it's own dir. etc.

In addition to that, postgresql should offer capability to suspend a
database/table so that they can be moved without restarting database daemon.

This is to acknowledge the fact that database storage is relocatable. In
current implementation, database does not offer any assistance in relocating
the database structure on disk..

Besides postgresql runs a cluster of databases as opposed to single database
run by likes of oracle. So relocating a single database should not affect
others.

Additionally postgresql should handle out of disk space errors gracefully
noting that out of space for an object may not mean out of space for all
objects. Obviously tablespaces can do better here.

Let's say for each object that gets storage, e.g. database, table, index and
transaction log, we maintain a flag in metadata to indicate whether it's
relocated or not. t can offer a command to set this flag. Typically the command
sequence would look like this

sql> take <object> offline;
---
relocate/symlink/remount the appropriate dir.
--
sql> take <object> online mark relocated;

Postgresql should continue working if a relocated object experiences disk space
full.

Of course, if postgresql could accept arguments at object creation time for
alternate directories to symlink to, that would be better than sliced bread.

I believe giving each database it's own transaction log would be a great
advantage of this scheme.

These are some thoughts how it would work. I believe this course of action
would make the transition easier, offer a much better granular control over
object allocation on disk and ultimately would prove useful to users.

I might have lost couple of points as I took long time composing it. But having
all the possible objections dealt with should be the starting point.

Thanks once again..

Bye
Shridhar

--
Cheit's Lament: If you help a friend in need, he is sure to remember you-- the
next time he's in need.