Tablespace Administration in Postgres – Q&A
1. What is tablespace in Postgres?
A. It specifies in which location contents of the tables should be stored.
- We can’t use a database without tablespace
- Default tablespace is pg_default
- Users while creating objects can specify on which tablespace objects should be stored.
- Contents will be stored in form of files.
2. What are the types of tablespaces in Postgres?
A. There are 2 types of tablespace
- Default
- Non-default
By default, we have 2 types of tablespaces, pg_default, and pg_global.
|
1 2 3 4 5 6 |
postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions ------------------------------------------------------------------------- 1663 | pg_default | 10 | 1664 | pg_global | 10 | (2 rows) |
3. What is the default tablespace for all the databases?
A. pg_default is the default tablespace for all the databases.
Which is used for user-defined objects
The default path of pg_default tablespace is mentioned below
|
1 2 3 4 |
-bash-4.1$ cd $PGDATA -bash-4.1$ cd base/ -bash-4.1$ pwd /var/lib/pgsq1/12/data/base |
4. Why non-default tablespaces are created?
A. Non-default tablespaces are created in order to store the contents in other locations. Apart from pgdata.
Pgdata is the default location.
- In order to have a bigger storage area
- To have better performance
- Can make default tablespace for the database.
5. What is the default location where the database files will be stored?
A. The database files will be stored in the default location mentioned below
-
- Location : – $pgdata/base/oid of the database.
- Under base location, oid numbers are the databases oid number
- This is similar to dbid in oracle
|
1 2 3 4 5 6 7 8 9 10 |
-bash-4.1$ cd $PGDATA -bash-4.1$ cd base/ -bash-4.1$ pwd /var/lib/pgsq1/12/data/base -bash-4.1$ ls -1rt total 20 drwx . 2 postgres postgres 4096 Feb 6 00:31 1 drwx . 2 postgres postgres 4096 Feb 6 00:31 13835 drwx . 2 postgres postgres 12288 Feb 6 07:17 13836 -bash-4.1$ _ |
6. How many default tablespace can the database have?
A. At any given point in time, the database can have only one default tablespace.
7. What is pg_global tablespace?
A. pg_global is used for storing internal objects i.e catalog objects
- And the location will be the same as pg_default tablespace location i.e $pgdata location.
- Location of pgdata :- basepath/version/data/base/oid
- Used for dictionary objects.
|
1 2 3 4 |
-bash-4.1$ cd $PGDATA -bash-4.1$ cd base/ -bash-4.1$ pwd /var/lib/pgsq1/12/data/base |
8. When a client creates a table inside the database on which tablespace the table resides by default?
A. It will reside in the pg_default tablespace location of that database.
9. Can we make non-default tablespace into the default table for the database?
A. Yes it’s possible. While creating a database or altering a database.
Below is the output
-
- Below are the steps while creating the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
postgres=# create tablespace newtbs location '/var/lib/pgsgl/12/newtbs_files'; CREATE TABLESPACE postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions -------------------------------------------------------------------------------- 1663 | pg_default | 10 | 1664 | pg_global | 10 | 16384 | newtbs | 10 | (3 rows) postgres=# create database ourdb tablespace newtbs; CREATE DATABASE postgres=# select oid,datname,dattablespace from pg_database order by oid; oid | datname | dattablespace -------------------------------------------------------------------------------- 1 | template | 1663 13835 | template0 | 1663 13836 | postgres | 1663 16385 | ourdb | 16384 (4 rows) |
-
-
- Altering the database.
-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
postgres=# select oid,datname,dattablespace from pg_database order by oid; oid | datname | dattablespace ------------------------------------------------------------------------------------- 1 | templatel | 1663 13835 | template0 | 1663 13836 | postgres | 1663 16385 | ourdb | 16384 16387 | newdbs | 1663 (5 rows) postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions ------------------------------------------------------------------------------------- 1663 | pg_default | 10 | 1664 | pg_global | 10 | 16384 | newtbs | 10 | (3 rows) postgres=# alter database newdbs set tablespace newtbs; ALTER DATABASE postgres=# postgres=# select oid,datname,dattablespace from pg_database order by oid; oid | datname | dattablespace -------------------------------------------------------------------------------------- 1 | templatel | 1663 13835 | template0 | 1663 13836 | postgres | 1663 16385 | ourdb | 16384 16387 | newdbs | 16384 (5 rows) |
10. What is oid of the pg_default and pg_global?
A. The oid of pg_default and pg_global tablespaces are mentioned below.
|
1 2 3 4 5 6 |
postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions ------------------------------------------------------------------------- 1663 | pg_default | 10 | 1664 | pg_global | 10 | (2 rows) |
11. Can we see the location of default tablespaces?
A. No, we can’t see the location of default tablespaces
2 Ways to check the default tablespace details
Note: spcowner value – 10 is the Postgres owner value
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
postgres=# \db List of tablespaces Name | Owner | Location --------------------------------------------------------------------------------------- pg_default | postgres | pg_global | postgres | (2 rows) postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions --------------------------------------------------------------------------------------- 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | (2 rows) |
12. Command to create non-default tablespace?
A. Below is the output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
postgres=# create tablespace newtbs location '/var/lib/pgsq1/12/newtbs files'; CREATE TABLESPACE postgres# postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions --------------------------------------------------------------------------------- 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 16384 | newtbs | 10 | | (3 rows) postgres=# \db List of tablespaces Name | Owner | Location --------------------------------------------------------------------------------- newtbs | postgres | /var/lib/pgsql/12/newtbs_files pg_default | postgres | pg_global | postgres | (3 rows) -bash-4.1$ pwd /var/lib/pgsq1/12 -bash-4.1$ cd newtbs_files/ -bash-4.1$ ls PG_12_201909212 -bash-4.1$ pwd /var/lib/pgsq1/12/newtbs_files -bash-4.1$ cd PG_12_201909212/ -bash-4.1$ pwd /var/lib/pgsq1/12/newtbs_files/PG_12_201909212 |
Note: The location is showing as empty in the default tablespace
- And the location which is visible is considered as non-default tablespace
- A subdirectory is created with the name pg.
13. Command to check the pgdata location from the database level?
A. Below is the output
|
1 2 3 4 5 |
postgres=# show data_directory; data directory ------------------------------------------------------------------------------------- /var/lib/pgsq1/12/data (1 row) |
14. Can one database can use multiple tablespaces?
A. One database can use multiple tablespaces, but the default tablespace is one.
Below is the output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
postgres=# create database newdbdbs postgres-# ; CREATE DATABASE newdbdbs=# select oid,datname,dattablespace from pg_database order by oid; oid | datname | dattablespace --------------------------------------------------- 1 | templatel | 1663 13835 | template0 | 1663 13836 | postgres | 1663 16385 | ourdb | 16384 16387 | newdbs | 16384 16388 | newdbdbs | 1663 (6 rows) |
Using pg_default tablespace. But objects can be stored in different tablespaces.
|
1 2 3 4 5 6 7 8 9 10 |
postgres=# \c newdbdbs You are now connected to database "newdbdbs" as user "postgres". newdbdbs=# create table sampletab (no int) tablespace newtbs; CREATE TABLE newdbdbs=# \dt List of relations Schema | Name | Type | Owner ----------------------------------------------------------------------------------------- public | sampletab | table | postgres (1 row) |
Newtbs is the nondefault tablespace, where sampletab table contents are stored.
15. Command to check the tablespace size or the amount of space used?
A. Below is the command
|
1 2 3 4 5 6 7 8 9 10 11 |
postgres=# select pg_size_pretty(pg_tablespace_size('pg_default')); pg_size_pretty --------------------------------------------------------------------- 31 MB (1 row) postgres=# select pg_size_pretty(pg_tablespace_size('newtbs')); pg_size_pretty --------------------------------------------------------------------- 15 MB (1 row) |
16. How to check if the temporary tablespace is there for a particular session? And how to assign it if not exists?
A. Below are the steps
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
postgres=# create tablespace temptbsl location '/var/lib/pgsql/12/temptbsl_files'; CREATE TABLESPACE postgres=# postgres=# create tablespace temptbs2 location '/var/lib/pgsql/12/temptbs2_files'; CREATE TABLESPACE postgres=# postgres=# \db List of tablespaces Name | Owner | Location ------------------------------------------------------------------ newtbs | postgres | /var/lib/pgsql/12/newtbs_files pg_default | postgres | pg_global | postgres | temptbsl | postgres | /var/lib/pgsql/12/temptbsl_files temptbs2 | postgres | /var/lib/pgsql/12/temptbs2_files (5 rows) |
Below is the command to check if temp tablespace is assigned for a particular session or not
|
1 2 3 4 5 |
postgres=# show temp_tablespaces; temp_tablespaces ------------------------------------------------------------------------------- (1 row) |
Below is the command to assign to a particular session.
|
1 2 3 4 5 6 7 |
postgres=# set temp_tablespaces='temptbs1','temptbs2'; SET postgres=# show temp_tablespaces; temp_tablespaces -------------------------------------------------------------------------------- temptbsl, temptbs2 (1 row) |
If no temporary tablespace is created, then it will go to pg_default.
Below is the example,
We have created a table in temp tablespace
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres=# \q -bash-4.1$ pwd /var/lib/pgsq1/12 -bash-4.1$ tree temp* temptbsl_files |___PG 12 201909212 |__13836 temptbs2_files |___ PG 12 201909212 postgres=# create temp table tabl (no int) tablespace temptbsl; CREATE TABLE |
Once logged in
Contents are removed.
|
1 2 3 4 |
newdbdbs=# show temp_tablespaces; temp_tablespaces --------------------------------------------------------------------------------------- (1 row) |
(3 votes, average: 4.33 out of 5)
Loading...
