Re: tablespaces and DB administration

On May 26, 2004, at 7:14 PM, pgsql(at)mohawksoft(dot)com wrote:

>> pgsql(at)mohawksoft(dot)com writes:
>>> First, we keep the standard PostgreSQL directory the way it has
>>> always
>>> been with template0, template1, pg_xlog, pg_clog, etc. in the same
>>> place.
>>> We can refer to this as the "system" directory. This makes sense
>>> because
>>> all the system level stuff is there. User databases should be
>>> discouraged
>>> from the system, and users should be encouraged to create and use
>>> separate
>>> tablespaces for their databases.
>>
>> Why?
>>
>> This seems to me to be pushing complexity onto users whether they
>> want/need it or not. I think that only a relatively small number of
>> installations will have any use for tablespaces, and we should not
>> try to coerce average users into worrying about them.
>
> I forgot to specify that tablepaces should be on separate volumes.
> (sorry)
> If all they have is one volume, no worries, but instructing the use of
> alternate volumes for system and data will improve performance by
> separating WAL and data operations.
>
> Tablespaces are a familiar construct to experienced DBAs who may not be
> familiar with PostgreSQL. PostgreSQL being similar to other databases
> will
> have it better "make sense" to new users.
>
> Users are primarily, if not stupid, ignorant. They will read the
> absolute
> minimum needed to achieve a goal and little else. I say this with the
> utmost respect, because I and probably everyone else on this group is
> guilty of the same thing. So, the "preferred" installation procedure,
> i.e.
> the one with the easy to follow directions, should showcase features
> the
> user should know, and leave the user in a good place. IMHO, the user's
> database on one volume and pg_xlog on another is a better starting
> place.

Yes, that is generally the case (prefer pg_xlog on separate spindle),
but no
need to *forcibly* overcomplicate things if the box has only one
spindle,
or if they have only one single RAID'd partition configured. We should
continue to err on the side of keeping the path to a functional system
nice and simple, yet still offering superb functionality. Oracle gets
this
wrong. pg_autovacuum is another good step in this direction.

----
James Robinson
Socialserve.com