Postgresql - tablespace interaction - notes

In postgresql, the tablespaces are actually a cluster level entity, they arent part of a specific database or schema.

Hence same postgresql tablespace can be used by schema1 from pgdb1 database and schema2 from pgdb2 database

By default postgresql has 2 tablespaces...

1) pg_global which is used by the pg_catalog schema of postgresql

2) pg_default is the default tablespace for all other objects, this is the default tablespace as you see below for template0 and template 1, so eventually all the new databases created from them too.

postgres=# \l+
                                                                                   List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   |  Siz
e   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------+-----
----+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                       | 7597
 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +| 7441
 kB | pg_default | unmodifiable empty database
           |          |          |             |             |            |                 | postgres=CTc/postgres |
    |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +| 7669
 kB | pg_default | default template for new databases
           |          |          |             |             |            |                 | postgres=CTc/postgres |
    |            |
(3 rows)

3) As per dbi services, unlike oracle, postgresql doesnt give much value to tablespace. Except in only one condition which is the temporary tablespace for temp data. If we use the permenant table for temp table, the space @ the file system is wasted. So we better create a seperate tablespace for it.

4) Temporary tablespace in postgresql @ os will just be a directory, this directory will host files per temp table and temp log operation like huge sorting. Once the temp table is dropped (or session closed) or operation for temp log is done, the file will be cleaned and directory will stay empty.

5) To drop a tablespace, one has to empty the tablespace

6) Each table will be created as a file inside the directory (OS) vs tablespace (postgresql)

Excercise:
Create a tablespace:

Attempt 1) Create tablespace without mentioning the disk path

a) How the default tablespace directory looks now:

-bash-4.2$ pwd
/pgDATA/data
-bash-4.2$ ls -altr pg_tblspc
total 4
drwx------.  2 postgres postgres    6 Oct 16 23:07 .
drwx------. 20 postgres postgres 4096 Oct 22 19:02 ..
-bash-4.2$

b) When examining the pg_tablespace, \db+ for the pg_global and pg_default tablespace...

postgres=# \db+
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 22 MB  | <<< no filepath
 pg_global  | postgres |          |                   |         | 531 kB | <<< no filepath
(2 rows)
postgres=# select * from pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)
postgres=# \d pg_tablespace
            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null |
 spcname    | name      |           | not null |
 spcowner   | oid       |           | not null |
 spcacl     | aclitem[] |           |          |
 spcoptions | text[]    | C         |          |
Indexes:
    "pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

c) Try create the tablespace without location keyword...

create tablespace pgtst; >>> failed due to syntax issue.

Output:

postgres=# create tablespace pgtst;
ERROR:  syntax error at or near ";"
LINE 1: create tablespace pgtst;
                               
d) Try to rerun the query this time with proper location (but is the same location as $PGDATA/data)
So we fix the query and retry:

postgres=# create tablespace pgtst location '/pgDATA/data/pg_tblspc';
WARNING:  tablespace location should not be inside the data directory <<< did you notice this warning (https://postgrespro.com/list/thread-id/1861145)
CREATE TABLESPACE
postgres=#

A new directory is created...

-bash-4.2$ ls -l
total 0
lrwxrwxrwx. 1 postgres postgres 22 Oct 22 22:11 16387 -> /pgDATA/data/pg_tblspc
drwx------. 2 postgres postgres  6 Oct 22 22:11 PG_15_202209061
-bash-4.2$ ls -altr PG_15_202209061
total 0
drwx------. 2 postgres postgres  6 Oct 22 22:11 .
drwx------. 3 postgres postgres 42 Oct 22 22:11 ..
-bash-4.2$ pwd
/pgDATA/data/pg_tblspc
-bash-4.2$

I you had text or other large object type data, where a tuple cant fit in single page. A TOAST would have been created.

e) From the blog https://postgrespro.com/list/thread-id/1861145, we learnt it is better we dont put our custom tablespace under $PGDATA (double size counting, pg_upgrade or cluster deletion incorrectly purging files etc...).So we will drop the tablespace now, after examining it is free.

f) Before we drop the tablespace, we have to check pg_class (which stores all the objects which has data stored in tuple form) across the databases in the cluster.

select
c.relname
,t.spcname
from
pg_class c
join pg_tablespace t on c.reltablespace=t.oid
where
t.spcname = 'pgtst';

select
c.relname
,t.spcname
from
pg_class c
join pg_tablespace t on c.reltablespace=t.oid
where
t.spcname = 'PGTST';

In my case, I had only 1 db which is postgres. In that there were no objects (with tuples) found.

postgres=# select
postgres-# c.relname
postgres-# ,t.spcname
postgres-# from
postgres-# pg_class c
postgres-# join pg_tablespace t on c.reltablespace=t.oid
postgres-# where
postgres-# t.spcname = 'pgtst';
 relname | spcname
---------+---------
(0 rows)

postgres=#
postgres=# select
postgres-# c.relname
postgres-# ,t.spcname
postgres-# from
postgres-# pg_class c
postgres-# join pg_tablespace t on c.reltablespace=t.oid
postgres-# where
postgres-# t.spcname = 'PGTST';
 relname | spcname
---------+---------
(0 rows)
postgres=#
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)
postgres=#

g) drop the tablespace:

drop tablespace pgtst;
postgres=# drop tablespace pgtst;
DROP TABLESPACE
postgres=#

-bash-4.2$ pwd
/pgDATA/data/pg_tblspc
-bash-4.2$ ls -altr
total 4
drwx------. 20 postgres postgres 4096 Oct 22 19:02 ..
drwx------.  2 postgres postgres    6 Oct 22 22:45 .
-bash-4.2$

So the tablespace is dropped as well.

Thanks