{"id":1533,"date":"2023-11-27T12:20:51","date_gmt":"2023-11-27T09:20:51","guid":{"rendered":"http:\/\/pgdataeraold.local\/?p=1533"},"modified":"2023-11-27T12:20:51","modified_gmt":"2023-11-27T09:20:51","slug":"what-is-postgresql-tablespace-and-how-to-manage-it","status":"publish","type":"post","link":"https:\/\/pgdataera.com\/en\/what-is-postgresql-tablespace-and-how-to-manage-it\/","title":{"rendered":"What is Postgresql Tablespace and How to Manage it"},"content":{"rendered":"
\n
\"\"<\/figure><\/div>\n\n\n
\n\n\n\n

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.<\/p>\n\n\n\n

Tablespace Management<\/h2>\n\n\n\n
postgres@srv1:~$ sudo mkdir \/erp\npostgres@srv1:~$ sudo chown postgres:postgres \/erp\npostgres=# CREATE TABLESPACE erp_tbs LOCATION \u2018\/erp\u2019 ;\npostgres-# \\db\nName | Owner | Location\n------------+----------+----------\nerp_tbs | postgres | \/erp\npg_default | postgres |\npg_global | postgres |\npostgres=# \\! ls -l \/erp\ndrwx------ 2 postgres postgres 6 Jul 2 14:43 PG_13_202007201\npostgres-# \\! ls -l \/pg\/data\/13.6\/pg_tblspc\/\nlrwxrwxrwx 1 postgres postgres 4 Jul 2 14:43 33016 -> \/erp<\/pre>\n\n\n\n
\n\n\n\n
The \u201c\/erp\u201d folder is automatically linked to the \u201c\/pg\/data\/13.6\/pg_
tblsc\/33016\u201d folder. \u201c1\u201d stands for the link and at the far left \u2018rwx\u2019 characters
denotes privileges.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
If the tablespace\u2019s 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.<\/p>\n\n\n\n
For example, tables that uses lot of DML operations can be placed on disks like SSD,
etc. with high IOPS capacity.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
# The tablespace can be deleted after deleting or moving all\nobjects\/databases in it.\ndrop tablespace tablespace_ismi ;\n# Renaming tablespace\nALTER TABLESPACE erp_tbs RENAME TO yeni_isim;\n# Changing tablespace ownership\nALTER TABLESPACE erp_tbs OWNER TO yeni_sahip;<\/pre>\n\n\n\n
\n\n\n\n
By default, there are two tablespaces pg_global<\/code> and pg_default<\/code>.
pg_global<\/code> is for shared system catalog objects.
pg_default <\/code>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 \u2018owner\u2019.<\/p>\n\n\n\n
Temp Tablespace<\/h2>\n\n\n\n
postgres@srv1:~$ sudo mkdir \/tmp_tbs\npostgres@srv1:~$ sudo chown postgres:postgres \/tmp_tbs\npostgres@srv1:~$ psql\npostgres=# alter system set temp_tablespaces='\/tmp_tbs' ;\npostgres=# CREATE temp TABLE tmp_ekip (id integer PRIMARY KEY ,\nAd varchar(40))\ntablespace tmp_tbs1;\npostgres=# INSERT INTO tmp_ekip VALUES(1, 'Ahmed Kul');\npostgres=# INSERT INTO tmp_ekip VALUES(2, '\u00d6mer Muhacir');\npostgres=# SELECT * FROM public.tmp_ekip order by 1 desc;<\/pre>\n\n\n\n
\n\n\n\n
The directory where the files of temp objects (table, index) and large sort operations
will be executed is determined by the temp_tablespaces parameter<\/code>.
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.<\/p>\n\n\n\n
postgres=# CREATE DATABASE erp_db WITH TABLESPACE = erp_tbs ;\nCREATE DATABASE\npostgres=# \\c erp_db\npostgres=# CREATE TABLE ekip (id integer PRIMARY KEY , Ad\nvarchar(40));\n# Since tablespace is not specified, it is created in the erp_\ntbs tablespace.\npostgres=# INSERT INTO ekip VALUES (1, 'Ahmed Ensar');\npostgres=# INSERT INTO ekip VALUES(2, '\u00d6mer Muhacir');\npostgres=# CREATE INDEX emp_idx on ekip(Ad);\n# Since tablespace is not specified, it is created in the erp_\ntbs tablespace.\n# Changing the default tablespace.\nALTER DATABASE erp_db SET default_tablespace=\u2019yeni_tbs\u2019;\n# Or on a session basis\nSET default_tablespace = ik_tbs;\n# Moving all the objects in the database to another tablespace\nshould be done by considering interruptions.\nALTER DATABASE erp_db SET TABLESPACE yeni_tablespace;<\/pre>\n\n\n\n
Tablespace & Database :<\/mark> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 creatingthe objects.While it is possible to keep a database and all its objects in one tablespace, it is alsopossible to place …  What is Postgresql Tablespace and How to Manage it<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[300],"tags":[136,137,139],"class_list":["post-1533","post","type-post","status-publish","format-standard","hentry","category-tablespace-wal","tag-pg_tblspc-en","tag-tablespace-en","tag-temp-tablespace-en"],"_links":{"self":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/comments?post=1533"}],"version-history":[{"count":0,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1533\/revisions"}],"wp:attachment":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/media?parent=1533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/categories?post=1533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/tags?post=1533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}