Thread: One tablespace or several tablespaces

On Sat, Apr 2, 2016 at 1:42 PM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> I am developing an information system that will be used by several clientes,
> each client has its own database, and each database has exactly the same
> structure for each client. The only difference is the name of the database.
> I have been thinking about whether or not using one tablespace to create all
> of the databases or to use one tablespace for each database, but I have not
> found a good reason for using one method or the other.

Generally I have found it best to use a single tablespace except
when there is a clear reason to do otherwise.  Usually that reason,
if it exists, is to allow storing less frequently accessed data on
a slow, cheaper medium.

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

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: April-11-16 09:40
To: JORGE MALDONADO
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] One tablespace or several tablespaces

On Sat, Apr 2, 2016 at 1:42 PM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> I am developing an information system that will be used by several 
> clientes, each client has its own database, and each database has 
> exactly the same structure for each client. The only difference is the name of the database.
> I have been thinking about whether or not using one tablespace to 
> create all of the databases or to use one tablespace for each 
> database, but I have not found a good reason for using one method or the other.

Generally I have found it best to use a single tablespace except when there is a clear reason to do otherwise.  Usually
thatreason, if it exists, is to allow storing less frequently accessed data on a slow, cheaper medium.
 
--------
Another reason to use multiple tablespaces is if IO operations are slowing down significantly because the queries have
toaccess several large tables at the same time (i.e. read/write tens of GB by table). In this case, it might be a good
ideato distribute IO operations by spreading on several disks the tables that are often used together. 
 

Otherwise (smaller tables or expensive queries are only sporadic), keep everything together as Kevin suggested.

Daniel

Begin Daniel wrote:
> On Sat, Apr 2, 2016 at 1:42 PM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
>> I am developing an information system that will be used by several
>> clientes, each client has its own database, and each database has
>> exactly the same structure for each client. The only difference is the name of the database.
>> I have been thinking about whether or not using one tablespace to
>> create all of the databases or to use one tablespace for each
>> database, but I have not found a good reason for using one method or the other.
> 
> Generally I have found it best to use a single tablespace except when there is a clear reason to do
> otherwise.  Usually that reason, if it exists, is to allow storing less frequently accessed data on a
> slow, cheaper medium.
> --------
> Another reason to use multiple tablespaces is if IO operations are slowing down significantly because
> the queries have to access several large tables at the same time (i.e. read/write tens of GB by
> table). In this case, it might be a good idea to distribute IO operations by spreading on several
> disks the tables that are often used together.
> 
> Otherwise (smaller tables or expensive queries are only sporadic), keep everything together as Kevin
> suggested.

I have been told that the device I/O queue can become a bottleneck on
Linux if there is a lot of I/O going to a single device, and that it is
better to spread this across moltiple devices (via tablespaces or
striping).

I have not been able to personally observe this.

Yours,
Laurenz Albe