Re: tablespace - datafile location

"Birchall, Austen" <austen.birchall@metoffice.gov.uk> wrote:

> Up to now I have been creating the sub-directories that (hold)
> the OS level files that are associated with individual
> tablespaces directly under the
>
> ..../psql/data directory i.e.
>
> db=# create tablespace test owner test_admin location
> '/var/lib/pgsql/data/test';
> Is this standard/best practise - I can see that the data
> directory could get 'cluttered' in a db that has many
> tablespaces.

I've never spent a lot of time browing the subdirectories.  Why
would "cluttered" matter?

> Is there a standard/recommended alternative for tablespace
> location for db which have many tablespaces?

It is not a good idea to put tablespaces under the $PGDATA
directory.  For one thing, it creates challenges for backup
software, which generally tries to copy everything under the
$PGDATA directory and then add everything from each non-default
tablespace.  Without a lot of care, you wind up copying everything
in every non-default tablespace twice.

Also, it doesn't really provide much benefit to use tablespaces
this way.  Unless the tablespace is on a separate filesystem, you
don't get any performance benefit, unless the type of filesystem
being used performs poorly with a large number of files in one
subdirectory.  If they are just being used for logical separation,
then separate schemas (namespaces) is a better way to do that.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company