What is Postgresql Tablespace and How to Manage it

Databases and database objects are hosted in tablespaces.
Through tablespaces, we can place the objects on any disk or directories as we want.
Once the tablespace is created, we only need to mention the tablespace while creating
the objects.
While it is possible to keep a database and all its objects in one tablespace, it is also
possible to place certain objects in different tablespaces.

Tablespace Management

postgres@srv1:~$ sudo mkdir /erp
postgres@srv1:~$ sudo chown postgres:postgres /erp
postgres=# CREATE TABLESPACE erp_tbs LOCATION ‘/erp’ ;
postgres-# \db
Name | Owner | Location
------------+----------+----------
erp_tbs | postgres | /erp
pg_default | postgres |
pg_global | postgres |
postgres=# \! ls -l /erp
drwx------ 2 postgres postgres 6 Jul 2 14:43 PG_13_202007201
postgres-# \! ls -l /pg/data/13.6/pg_tblspc/
lrwxrwxrwx 1 postgres postgres 4 Jul 2 14:43 33016 -> /erp

The “/erp” folder is automatically linked to the “/pg/data/13.6/pg_
tblsc/33016” folder. “1” stands for the link and at the far left ‘rwx’ characters
denotes privileges.

Although tablespaces are located outside of the cluster directory, they are still part of
the cluster (addressed with symbolic links in the pg_tblspc directory). They cannot be
added to another database cluster.

If the tablespace’s data files are deleted or disk failure occurs, the database cluster
becomes inaccessible and cannot be started. You need to restore from backup.
The system catalog and objects are located in the tablespace associated with the
database. It is possible to create a different tablespace by specifying the tablespace
while creating the object.

For example, tables that uses lot of DML operations can be placed on disks like SSD,
etc. with high IOPS capacity.

To facilitate management and/or increase performance, you can create tablespaces on
different disks and spread the database and objects on these tablespaces.
Since the physical structure and the logical structure are separate from each other, it
provides ease of management.
The concept of tablespace is logical, while the data files on disk are the physical
equivalent of tablespaces.

# The tablespace can be deleted after deleting or moving all
objects/databases in it.
drop tablespace tablespace_ismi ;
# Renaming tablespace
ALTER TABLESPACE erp_tbs RENAME TO yeni_isim;
# Changing tablespace ownership
ALTER TABLESPACE erp_tbs OWNER TO yeni_sahip;

By default, there are two tablespaces pg_global and pg_default.
pg_global is for shared system catalog objects.
pg_default is the default tablespace of template0/1 databases and newly created
databases (unless a different tablespace is specified with the tablespace parameter).
While creating the tablespace, the user who will be the owner of the tablespace can be
specified with this parameter ‘owner’.

Temp Tablespace

postgres@srv1:~$ sudo mkdir /tmp_tbs
postgres@srv1:~$ sudo chown postgres:postgres /tmp_tbs
postgres@srv1:~$ psql
postgres=# alter system set temp_tablespaces='/tmp_tbs' ;
postgres=# CREATE temp TABLE tmp_ekip (id integer PRIMARY KEY ,
Ad varchar(40))
tablespace tmp_tbs1;
postgres=# INSERT INTO tmp_ekip VALUES(1, 'Ahmed Kul');
postgres=# INSERT INTO tmp_ekip VALUES(2, 'Ömer Muhacir');
postgres=# SELECT * FROM public.tmp_ekip order by 1 desc;

The directory where the files of temp objects (table, index) and large sort operations
will be executed is determined by the temp_tablespaces parameter.
Reporting etc., which uses a lot of sorting operations can be placed in the directory of
SSD/Flash/RamDisk etc. It will increase performance if located on fast disks.

postgres=# CREATE DATABASE erp_db WITH TABLESPACE = erp_tbs ;
CREATE DATABASE
postgres=# \c erp_db
postgres=# CREATE TABLE ekip (id integer PRIMARY KEY , Ad
varchar(40));
# Since tablespace is not specified, it is created in the erp_
tbs tablespace.
postgres=# INSERT INTO ekip VALUES (1, 'Ahmed Ensar');
postgres=# INSERT INTO ekip VALUES(2, 'Ömer Muhacir');
postgres=# CREATE INDEX emp_idx on ekip(Ad);
# Since tablespace is not specified, it is created in the erp_
tbs tablespace.
# Changing the default tablespace.
ALTER DATABASE erp_db SET default_tablespace=’yeni_tbs’;
# Or on a session basis
SET default_tablespace = ik_tbs;
# Moving all the objects in the database to another tablespace
should be done by considering interruptions.
ALTER DATABASE erp_db SET TABLESPACE yeni_tablespace;

Tablespace & Database : While creating the database, the catalog schema is recorded
to this tablespace by specifying the tablespace parameter. Likewise, if tablespace is not
specified while creating objects, they are created in the same tablespace.

Kategori seçin...