PostgreSQL Tablespace managment

 

Tablesapce Basics


What is a tablespace?

It defines where the contents of the tables should be saved.

Rules:

  • We can't use a database unless we have tablespace.
  • pg_default is the default tablespace for all the databases.
  • We can define the non-default tablespace while creating a database or can alter the existing database/s by assigning a non-default tablespace.
  • Users can define on which tablespace the objects should be stored when creating them.

What are the different types of tablespaces present in the Postgres database?

There are two different types of tablespace:

Default: by default, two tablespaces will be created at the server level.

pg_default and pg_global

Non-default: User created manually. 

postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16449 | test_tbs   |       10 |        |
(3 rows)

postgres=# \db
                  List of tablespaces
    Name    |  Owner   |            Location
------------+----------+--------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 test_tbs   | postgres | /data/pgsql/databases/test_tbs
(3 rows)

What is the default location of default and non-default tablespaces?

Default tablespace location:  /var/lib/pgsql/15/data/base


[postgres@postgres ~]$ cd $PG_DATA
[postgres@postgres data]$ cd base/
[postgres@postgres base]$ pwd
/var/lib/pgsql/15/data/base


Non-default tablespace location: /var/lib/pgsql/15/data/pg_tblspc

[postgres@postgres ~]$ cd $PG_DATA
[postgres@postgres data]$ cd pg_tblspc/
[postgres@postgres pg_tblspc]$ pwd
/var/lib/pgsql/15/data/pg_tblspc
[postgres@postgres pg_tblspc]$ ls -rlt
total 0
lrwxrwxrwx. 1 postgres postgres 27 Jul 27 14:01 16445 -> /data/pgsql/databases/test_tbs


What is the use of pg_global default tablespace?

pg_default tablespace stores internal objects information like catalog objects.
the location of pg_default tablespace will be same as pg_default tablespace.
 

Tablespace management 

Create non-default tablespace:

postgres=# create tablespace prod_tbs location '/data/pgsql/databases/prod';
CREATE TABLESPACE
postgres=# \db
                  List of tablespaces
    Name    |  Owner   |            Location
------------+----------+--------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 prod_tbs   | postgres | /data/pgsql/databases/prod
 test_tbs   | postgres | /data/pgsql/databases/test_tbs
(4 rows)

postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16448 | prod_tbs   |       10 |        |
 16449 | test_tbs   |       10 |        |
(4 rows)


Drop non-default tablespace:

postgres=# drop tablespace dw;
DROP TABLESPACE

Note: make sure to move or remove all the objects which is part of dropping tablespace.


Creating a database with default and non-default tablespace:

postgres=# create database test_default;   --- no tablespace defined while creating the database
CREATE DATABASE
postgres=# create database test_non_default tablespace test_tbs;   --- defining non-defult tablespace while database creation
CREATE DATABASE
        
postgres=# \l+ test_default
                                                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges |  Size   | Tablespace | Description
--------------+----------+----------+-------------+-------------+------------+-----------------+-------------------+---------+------------+-------------
 test_default | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                   | 7537 kB | pg_default |
(1 row)

postgres=# \l+ test_non_default
                                                                     List of databases
       Name       |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges |  Size   | Tablespace | Description
------------------+----------+----------+-------------+-------------+------------+-----------------+-------------------+---------+------------+-------------
 test_non_default | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                   | 7537 kB | test_tbs   |
(1 row)


How to assign the non-default or different tablespce to existing database?

postgres=# alter database test2 set tablespace test_tbs;
ALTER DATABASE


Before:

postgres=# \l+ test2
                                                                List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges |  Size   | Tablespace | Description
-------+----------+----------+-------------+-------------+------------+-----------------+-------------------+---------+------------+-------------
 test2 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                   | 7537 kB | pg_default |
(1 row)

After:

postgres=# alter database test2 set tablespace test_tbs;
ALTER DATABASE
postgres=# \l+ test2
                                                                List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges |  Size   | Tablespace | Description
-------+----------+----------+-------------+-------------+------------+-----------------+-------------------+---------+------------+-------------
 test2 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                   | 7537 kB | test_tbs   |
(1 row)


How to see DATA directory location at database level?

postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/15/data
(1 row)


How to see tableapce utilization/size?

postgres=# select pg_size_pretty(pg_tablespace_size('prod_tbs'));
 pg_size_pretty
----------------
 50 MB
(1 row)


How and when to create a temporary tablespace in postgres database?

postgres=# create tablespace temp_tbs location '/data/pgsql/databases/tmp_tbs';
CREATE TABLESPACE
postgres=# show temp_tablespaces;
 temp_tablespaces
------------------

(1 row)

postgres=# set temp_tablespaces='temp_tbs';
SET

postgres=# show temp_tablespaces;
 temp_tablespaces
------------------
 temp_tbs
(1 row)

Note for temporary tablespace: 

We only utilize temporary tablespaces when we create temporary objects in the database. The reason for this is that if we do not designate a temporary tablespace for temp objects, they will be created in the default tablespace and may cause IO challenges with other objects IO, therefore, it is suggested/recommended to have a temporary tablespace assigned for temp tables and also, if the temp tablespace grows due to excessive activity it may cause space concerns.

Comments

Post a Comment

Popular posts from this blog

Tuples in Python

 Tuples Basically Tuples in Python are very similar to Lists, but, Tuples are immutable which means that they can not be modified/changed. We generally use Tuples to make sure the values are not changed like, dates, Year, and days of the week or dates on a Calendar.  Creating a Tuple: >>> my_t1=(1,2,3) >>> my_t2=('one','two','three') >>> my_t1 (1, 2, 3) >>> my_t2 ('one', 'two', 'three') -- check the length of tupple: >>> len(my_t1) 3 >>> len(my_t2) 3 -- Tuples can have mixed objects as like Lists. >>> my_t3=(1,'two',3,'four') >>> my_t3 (1, 'two', 3, 'four') -- indexing with tuples  >>> my_t3=(1,'two',3,'four') >>> my_t3[2] 3 Immutable Tuples We can not modify the value. Here is the example: >>> my_t3=(1,'two',3,'four') >>> my_t3[2]=4 Traceback (most recent call last):  ...
Read more

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

Boolean in Python

What is a Boolean? Ture or False (Make sure to use initial letter as a capital) For Example, >>> True True >>> true Traceback (most recent call last):   File "<stdin>", line 1, in <module> NameError: name 'true' is not defined Examples: >>> x= True >>> type(x) <class 'bool'> >>> 9 > 8 True >>> 8> 9 False  How to define/assign a variable without value? >>> x Traceback (most recent call last):   File "<stdin>", line 1, in <module> NameError: name 'x' is not defined Here is the way, you could define the variable without defining/assigning the value >>> x=None >>> x
Read more