Re: Need suggestions

In response to Jack W <dbdevelop2000(at)gmail(dot)com>:

> I will create several databases on PostGreSQL. All the databases have the
> same structure: same number of table/index.
> I have two choices:
> 1. For each database, I create a new tablespace and create a new database in
> the tablespace.
> 2. I only create one tablespace. Create all the databases on the same
> tablespace.
>
> What is the advantage and disadvantage of the two choices? For the first
> choice, different database locates in different physical directory on the
> hard drive. For the second choice, all the database locate in the same
> physical directory.
>
> Another possibility is to create a new "database cluster directory" for each
> database. Then each database is managed by different database server
> instance using different connection.
>
> Which way is better? Thanks a lot.

Depends on what you're trying to accomplish, which you don't state.

The typical reason for tablespaces would be to store different parts of
your database cluster on different physical storage, thus a hard drive
being saturated with writes doesn't slow down other tables that are on
a completely different hard drive. I can't think of many other reasons
to use tablespaces.

The typical reason for running multiple instances is that the global
settings must change, i.e. the listening port or listening address
must be different, or the roles and server-wide config settings must
be different.

Without knowing what problem you're trying to solve, I can't recommend
one or the other, but hopefully the previous paragraphs will help.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/