Initially there are two tablespaces in the cluster. We can query them from the system catalog:
α=> SELECT spcname FROM pg_tablespace;
spcname ------------ pg_default pg_global (2 rows)
Obviously pg_tablespace is one of the cluster-level tables.
Similar information is shown by the psql command:
α=> \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
To create a new tablespace you need to create an empty directory first. The directory must be owned by the user which runs PostgreSQL server (usually postgres):
postgres$ mkdir /var/lib/postgresql/ts_dir
Now we can create a tablespace by specifying its directory:
α=> CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir';
CREATE TABLESPACE
α=> \db
List of tablespaces
Name | Owner | Location
------------+----------+----------------------------
pg_default | postgres |
pg_global | postgres |
ts | student | /var/lib/postgresql/ts_dir
(3 rows)
When creating a database, a default tablespace can be specified for it:
α=> CREATE DATABASE test TABLESPACE ts;
CREATE DATABASE
α=> \c test
You are now connected to database "test" as user "student".
This means that by default (when not explicitly specified otherwise) every object will be created in that tablespace.
α=> CREATE TABLE t(s text);
CREATE TABLE
α=> INSERT INTO t SELECT g.id::text FROM generate_series(1,100000) g(id);
INSERT 0 100000
α=> CREATE INDEX ON t(s);
CREATE INDEX
α=> VACUUM t;
VACUUM
We can get the location of an object's files this way:
α=> SELECT pg_relation_filepath('t');
pg_relation_filepath
---------------------------------------------
pg_tblspc/17568/PG_11_201809051/17569/17570
(1 row)
Let's look at the files:
postgres$ ls -l /var/lib/postgresql/11/alpha/pg_tblspc/17568/PG_11_201809051/17569/17570*
-rw------- 1 postgres postgres 3620864 фев 27 15:37 /var/lib/postgresql/11/alpha/pg_tblspc/17568/PG_11_201809051/17569/17570 -rw------- 1 postgres postgres 24576 фев 27 15:37 /var/lib/postgresql/11/alpha/pg_tblspc/17568/PG_11_201809051/17569/17570_fsm -rw------- 1 postgres postgres 8192 фев 27 15:37 /var/lib/postgresql/11/alpha/pg_tblspc/17568/PG_11_201809051/17569/17570_vm
We can see that the files belong to the different forks: main, vm, and fsm.
Objects can be moved between tablespaces, although it requires physical copying of the data to other files (unlike moving between schemas):
α=> ALTER TABLE t SET TABLESPACE pg_default;
ALTER TABLE
α=> SELECT pg_relation_filepath('t');
pg_relation_filepath ---------------------- base/17569/17577 (1 row)
We can get the size of a database and its objects by the means of several functions.
α=> SELECT pg_database_size('test');
pg_database_size
------------------
13461779
(1 row)
We can format the numbers in a human-readable form:
α=> SELECT pg_size_pretty(pg_database_size('test'));
pg_size_pretty ---------------- 13 MB (1 row)
Total size of a table, including its indexes:
α=> SELECT pg_size_pretty(pg_total_relation_size('t'));
pg_size_pretty ---------------- 5776 kB (1 row)
Table size without indexes...
α=> SELECT pg_size_pretty(pg_table_size('t'));
pg_size_pretty ---------------- 3576 kB (1 row)
...and indexes alone:
α=> SELECT pg_size_pretty(pg_indexes_size('t'));
pg_size_pretty ---------------- 2200 kB (1 row)
Also we can get separate forks sizes, for example:
α=> SELECT pg_size_pretty(pg_relation_size('t','vm'));
pg_size_pretty ---------------- 8192 bytes (1 row)
And one more function for a tablespace size:
α=> SELECT pg_size_pretty(pg_tablespace_size('ts'));
pg_size_pretty ---------------- 9582 kB (1 row)
Now let's drop the database.
α=> \c postgres
You are now connected to database "postgres" as user "student".
α=> DROP DATABASE test;
DROP DATABASE
We can drop the tablespace as well, as long there are no objects in it:
α=> DROP TABLESPACE ts;
DROP TABLESPACE
The end of demonstration.