Thread: Can we have multiple tablespaces with in a database.

Please pick a single list to post to.  Performance seems like the unnecessary one here.

On Thu, Feb 20, 2020 at 10:34 PM Daulat Ram <Daulat.Ram@exponential.com> wrote:

Can we have multiple tablespaces with in a database in postgres?

I fell as if I'm missing something in your question given the presence of the "CREATE TABLESPACE" SQL command and the related non-command documentation covered here:

https://www.postgresql.org/docs/12/manage-ag-tablespaces.html  

 

Can we have a table on different tablespace same as Oracle?

There is no provision to assign two tablespaces to a single physical table.  To the benefit of those who don't use the other product you may wish to say exactly what you want to do instead of comparing it to something that many people likely have never used.

> On Feb 20, 2020, at 22:23, Daulat Ram <Daulat.Ram@exponential.com> wrote:
>
> That will be great if you  share any doc where it’s mentioned that we can’t use multiple tablespace for a single
database.I have to assist my Dev team regarding tablespaces. 

A single PostgreSQL database can have any number of tablespaces.  Each table has to be in one specific tablespace,
althougha table can be in one tablespace and its indexes in a different one. 

If a PostgreSQL table is partitioned, each partition can be in a different tablespace.

Oracle "style" tends to involve a lot of tablespaces in a database; this is much less commonly done in PostgreSQL.  In
general,you only need to create tablespace in a small number of circumstances: 

(a) You need more space than the current database volume allows, and moving the database to a larger volume is
inconvenient;
(b) You have multiple volumes with significantly different access characteristics (like an HDD array and some SSDs),
andyou want to distribute database objects to take advantage of that (for example, put commonly-used large indexes on
theSSDs). 

PostgreSQL tablespaces do increase the administrative overhead of the database, and shouldn't be created unless there
isa compelling need for them./ 

--
-- Christophe Pettus
   xof@thebuild.com

You mean we can have only single default tablespace for a database but the database objects can be created on
different-2tablespaces?
 

Can you please share the Doc URL for your suggestions given in trail mail.

Please correct me.

-----Original Message-----
From: Christophe Pettus <xof@thebuild.com> 
Sent: Friday, February 21, 2020 11:57 AM
To: Daulat Ram <Daulat.Ram@exponential.com>
Cc: amul sul <sulamul@gmail.com>; pgsql-general@lists.postgresql.org
Subject: Re: Can we have multiple tablespaces with in a database.



> On Feb 20, 2020, at 22:23, Daulat Ram <Daulat.Ram@exponential.com> wrote:
> 
> That will be great if you  share any doc where it’s mentioned that we can’t use multiple tablespace for a single
database.I have to assist my Dev team regarding tablespaces.
 

A single PostgreSQL database can have any number of tablespaces.  Each table has to be in one specific tablespace,
althougha table can be in one tablespace and its indexes in a different one.
 

If a PostgreSQL table is partitioned, each partition can be in a different tablespace.

Oracle "style" tends to involve a lot of tablespaces in a database; this is much less commonly done in PostgreSQL.  In
general,you only need to create tablespace in a small number of circumstances:
 

(a) You need more space than the current database volume allows, and moving the database to a larger volume is
inconvenient;
(b) You have multiple volumes with significantly different access characteristics (like an HDD array and some SSDs),
andyou want to distribute database objects to take advantage of that (for example, put commonly-used large indexes on
theSSDs).
 

PostgreSQL tablespaces do increase the administrative overhead of the database, and shouldn't be created unless there
isa compelling need for them./
 

--
-- Christophe Pettus
   xof@thebuild.com

> On Feb 20, 2020, at 22:34, Daulat Ram <Daulat.Ram@exponential.com> wrote:
>
> You mean we can have only single default tablespace for a database but the database objects can be created on
different-2tablespaces? 

Yes.

> Can you please share the Doc URL for your suggestions given in trail mail.

https://www.postgresql.org/docs/current/manage-ag-tablespaces.html

--
-- Christophe Pettus
   xof@thebuild.com

What are the differences between Oracle and Postgres tablespace.

Can we assign tablespace during Postgres schema creation . As I know in Oracle we assign the tablespace during
user/schemacreation.  

-----Original Message-----
From: Christophe Pettus <xof@thebuild.com>
Sent: Friday, February 21, 2020 12:07 PM
To: Daulat Ram <Daulat.Ram@exponential.com>
Cc: amul sul <sulamul@gmail.com>; pgsql-general@lists.postgresql.org
Subject: Re: Can we have multiple tablespaces with in a database.



> On Feb 20, 2020, at 22:34, Daulat Ram <Daulat.Ram@exponential.com> wrote:
>
> You mean we can have only single default tablespace for a database but the database objects can be created on
different-2tablespaces? 

Yes.

> Can you please share the Doc URL for your suggestions given in trail mail.

https://www.postgresql.org/docs/current/manage-ag-tablespaces.html

--
-- Christophe Pettus
   xof@thebuild.com