Can we have multiple tablespaces with in a database.
Hi Team,
Can we have multiple tablespaces with in a database in postgres?
Can we have a table on different tablespace same as Oracle?
Thanks,
On Fri, Feb 21, 2020 at 11:04 AM Daulat Ram <Daulat.Ram@exponential.com>
wrote:
Hi Team,
Can we have multiple tablespaces with in a database in postgres?
Yes.
Can we have a table on different tablespace same as Oracle?
Yes -- specify TABLESPACE option while creating that table.
Regards,
Amul
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.
David J.
On 2/20/20 11:46 PM, David G. Johnston wrote:
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
<mailto: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.
In some RDBMSs, you can partition tables across multiple tablespaces, but
they don't partition tables in anything close to the trigger-based method
that Postgres does (at least in 9.6).
--
Angular momentum makes the world go 'round.
Hi Amul ,
Please share the examples how we can create no. of tablespaces for a single database and how we can use them.
As I know we can create database on tablespace
1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs';
2. Create database test tablespace ‘conn_tbs';
Can we have multiple tablespaces with in a database in postgres?
Yes.
From: amul sul <sulamul@gmail.com>
Sent: Friday, February 21, 2020 11:16 AM
To: Daulat Ram <Daulat.Ram@exponential.com>
Cc: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Subject: Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:04 AM Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>> wrote:
Hi Team,
Can we have multiple tablespaces with in a database in postgres?
Yes.
Can we have a table on different tablespace same as Oracle?
Yes -- specify TABLESPACE option while creating that table.
Regards,
Amul
On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram <Daulat.Ram@exponential.com>
wrote:
Hi Amul ,
Please share the examples how we can create no. of tablespaces for a
single database and how we can use them.As I know we can create database on tablespace
1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION
'/mnt/pgdatatest/test/pgdata/conn_tbs';
2. Create database test tablespace ‘conn_tbs';Maybe I have misunderstood your question; there is no option to specify
more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.
If option is not specified then by default that object will be created
in conn_tbs.
Regards,
Amul
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.
Also , what are the differences between Oracle and Postgres Tablespacs?
Thanks,
From: amul sul <sulamul@gmail.com>
Sent: Friday, February 21, 2020 11:48 AM
To: Daulat Ram <Daulat.Ram@exponential.com>
Cc: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Subject: Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>> wrote:
Hi Amul ,
Please share the examples how we can create no. of tablespaces for a single database and how we can use them.
As I know we can create database on tablespace
1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs';
2. Create database test tablespace ‘conn_tbs';
Maybe I have misunderstood your question; there is no option to specify more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.
If option is not specified then by default that object will be created
in conn_tbs.
Regards,
Amul
On Fri, Feb 21, 2020 at 11:53 AM 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.Also , what are the differences between Oracle and Postgres Tablespacs?
To be honest I don't know anything about Oracle.
Regards,
Amul
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, although a 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), and you want to distribute database objects to take advantage of that (for example, put commonly-used large indexes on the SSDs).
PostgreSQL tablespaces do increase the administrative overhead of the database, and shouldn't be created unless there is a 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-2 tablespaces?
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, although a 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), and you want to distribute database objects to take advantage of that (for example, put commonly-used large indexes on the SSDs).
PostgreSQL tablespaces do increase the administrative overhead of the database, and shouldn't be created unless there is a 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-2 tablespaces?
From: amul sul <sulamul@gmail.com>
Sent: Friday, February 21, 2020 11:48 AM
To: Daulat Ram <Daulat.Ram@exponential.com>
Cc: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Subject: Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>> wrote:
Hi Amul ,
Please share the examples how we can create no. of tablespaces for a single database and how we can use them.
As I know we can create database on tablespace
1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs';
2. Create database test tablespace ‘conn_tbs';
Maybe I have misunderstood your question; there is no option to specify more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.
If option is not specified then by default that object will be created
in conn_tbs.
Regards,
Amul
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-2 tablespaces?
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/schema creation.
-----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-2 tablespaces?
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
On Fri, Feb 21, 2020 at 12:48 PM Daulat Ram <Daulat.Ram@exponential.com>
wrote:
What are the differences between Oracle and Postgres tablespace.
I hope this[1] wiki page will help you.
Can we assign tablespace during Postgres schema creation . As I know in
Oracle we assign the tablespace during user/schema creation.
AFAIK, there is no syntax to assign tablespace to a schema.
Regards,
Amul
1] https://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs
Hi,
You can create more than one tablespace and assign different objects on different tablespaces.
For example :
CREATE TABLESPACE test_data OWNER test LOCATION '/tmp/test_data';
CREATE TABLESPACE test_idx OWNER test LOCATION '/tmp/test_idx';
CREATE DATABASE test WITH TABLESPACE = test_data;
Then, for example, when create table and index, you can specify
CREATE TABLE test1
(
id int NOT NULL GENERATED ALWAYS AS IDENTITY,
comment text,
CONSTRAINT pk_test PRIMARY KEY
(
id
) USING INDEX TABLESPACE TEST_IDX
) TABLESPACE TEST_DATA;
CREATE INDEX sk_comment ON test1( comment ) TABLESPACE TEST_IDX;
Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96
[01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg]<http://www.aqsacom.com/>
From: Daulat Ram <Daulat.Ram@exponential.com>
Sent: Friday, February 21, 2020 7:23 AM
To: amul sul <sulamul@gmail.com>
Cc: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Subject: RE: Can we have multiple tablespaces with in a database.
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.
Also , what are the differences between Oracle and Postgres Tablespacs?
Thanks,
From: amul sul <sulamul@gmail.com<mailto:sulamul@gmail.com>>
Sent: Friday, February 21, 2020 11:48 AM
To: Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>>
Cc: pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>; pgsql-performance@lists.postgresql.org<mailto:pgsql-performance@lists.postgresql.org>
Subject: Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>> wrote:
Hi Amul ,
Please share the examples how we can create no. of tablespaces for a single database and how we can use them.
As I know we can create database on tablespace
1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs';
2. Create database test tablespace ‘conn_tbs';
Maybe I have misunderstood your question; there is no option to specify more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.
If option is not specified then by default that object will be created
in conn_tbs.
Regards,
Amul
Attachments:
image001.pngimage/png; name=image001.pngDownload
Thanks Patrick ,
From: Patrick FICHE <Patrick.Fiche@aqsacom.com>
Sent: Friday, February 21, 2020 9:54 PM
To: Daulat Ram <Daulat.Ram@exponential.com>; amul sul <sulamul@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: RE: Can we have multiple tablespaces with in a database.
Hi,
You can create more than one tablespace and assign different objects on different tablespaces.
For example :
CREATE TABLESPACE test_data OWNER test LOCATION '/tmp/test_data';
CREATE TABLESPACE test_idx OWNER test LOCATION '/tmp/test_idx';
CREATE DATABASE test WITH TABLESPACE = test_data;
Then, for example, when create table and index, you can specify
CREATE TABLE test1
(
id int NOT NULL GENERATED ALWAYS AS IDENTITY,
comment text,
CONSTRAINT pk_test PRIMARY KEY
(
id
) USING INDEX TABLESPACE TEST_IDX
) TABLESPACE TEST_DATA;
CREATE INDEX sk_comment ON test1( comment ) TABLESPACE TEST_IDX;
Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96
[01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg]<http://www.aqsacom.com/>
From: Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>>
Sent: Friday, February 21, 2020 7:23 AM
To: amul sul <sulamul@gmail.com<mailto:sulamul@gmail.com>>
Cc: pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>; pgsql-performance@lists.postgresql.org<mailto:pgsql-performance@lists.postgresql.org>
Subject: RE: Can we have multiple tablespaces with in a database.
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.
Also , what are the differences between Oracle and Postgres Tablespacs?
Thanks,
From: amul sul <sulamul@gmail.com<mailto:sulamul@gmail.com>>
Sent: Friday, February 21, 2020 11:48 AM
To: Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>>
Cc: pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>; pgsql-performance@lists.postgresql.org<mailto:pgsql-performance@lists.postgresql.org>
Subject: Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram <Daulat.Ram@exponential.com<mailto:Daulat.Ram@exponential.com>> wrote:
Hi Amul ,
Please share the examples how we can create no. of tablespaces for a single database and how we can use them.
As I know we can create database on tablespace
1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs';
2. Create database test tablespace ‘conn_tbs';
Maybe I have misunderstood your question; there is no option to specify more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.
If option is not specified then by default that object will be created
in conn_tbs.
Regards,
Amul