Re: Managing Space in PostgreSQL - DBA Perspective

On Mon, 16 Sep 2002, Orr, Steve wrote:

> I'm evaluating PostgreSQL as an alternative to Oracle and the more I look at
> PostgreSQL the more I like it!!!
>
> From a DBA perspective, how easy is space management? For instance... In
> PostgreSQL I can't correlate the data files to specific tablespaces by a
> naming convention. How easy is it to move things around without taking the
> database down?

It's not.

Thinking about it, it must be possible to use LOCK TABLE to then move the data
files and create the symlinks. This assumes that the backend isn't going to
hold an open file descriptor to the original file of course. Also one would
also have to address what happens when a data file split happens at 1GB. Even
then depending on what you think constitutes 'taking the database down' you
could have problems with normal usage for a short while.


> How easy is it to segregate I/O contending database objects
> like tables and indexes? Log files? Etc?

It's easy. Only it is a manual operation with the database shutdown. It
requires identifying the objects by oid and moving the same named files to the
intended location and setting symlinks in the data directories pointing to the
new pathnames.

> How easy is it to preallocate
> space?

As easy as partitioning etc. your disks and doing the file move and symlinking
business above.

> Is it possible to take a subset of tables/indexes offline and keep
> the database up?

You could revoke permissions on the tables from the users trying to use
them.

> Are there any other PostgreSQL space management challenges
> or gotchas that I have yet to discover?

Probably :)

One example is the MVCC nature which gives rise to dead tuples in the tables
when there are deletions or updates. This second one is the probably the most
likely to catch someone out. An update amounts to a delete and insert. VACUUM
needs to be run in order to reclaim dead space and then there is the potential
gotcha of the free space map (FSM) settings.


> I'm in the process of trying to answer many of these questions myself but
> I'm under a time crunch so I was wondering if anyone on this list could have
> compassion on this newbie and help me out. :-)
>
>
> Many thanks in advance,
> Steve Orr
>

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants