postgres tablespace - ghdrako/doc_snipets GitHub Wiki

By default, PostgreSQL stores its data in a directory that is defined in the environmental variable called PGDATA. Tables and other objects are created within this directory in a separate file. You can create tablespaces outside of the default PGDATA data directory for larger systems, to isolate data from indexes, to create storage for application-related subject areas, to improve performance by creating tables on SSD devices, and to specify separate storage for partitions.

A tablespace is a directory that can be outside the PGDATA directory and can also belong to different storage. Tablespaces are mapped into the PGDATA directory by means of symbolic links stored in the pg_tblspc subdirectory. In this way, the PostgreSQL processes do not have to look outside PGDATA, but are still able to access “external” storage. A tablespace can be used to achieve different aims, such as enlarging the storage data or providing different stor- age performances for specific objects. For instance, you can create a tablespace on a slow disk to contain infrequently accessed objects and tables, keeping fast storage within another tablespace for frequently accessed objects.

Create tablespace

When you create tablespace(s), the database files are created outside the default data directory; however, there is a symbolic link from the data directory to the location of the tablespace data file location. During the backup, PostgreSQL backs up all the data from the tablespaces. There are two default tablespaces: the default for user data and the system catalog data. There is a symbolic link from the pg_tblspc to the filesystem.

Create a directory as shown:

$ sudo mkdir -p /newtablespace
$ sudo chown postgres:postgres /newtablespace

Now create a tablespace using the newly created directory by running the following command:

$ psql -c "CREATE TABLESPACE newtblspc LOCATION '/newtablespace'"

Check the pg_tblspc directory to see a symlink to the new tablespace:

$ ls -l $PGDATA/pg_tblspc
 total 0
 lrwxrwxrwx. 1 postgres postgres 14 Nov 3 00:24 24611 -> /newtablespace

List tablespaces

postgres=# \db
 List of tablespaces
 Name | Owner | Location
 ------------+----------+----------------
 newtblspc | postgres | /newtablespace
 pg_default | postgres |
 pg_global | postgres |

Create tables in specific tablespace

postgres=# create table employee (id int) TABLESPACE newtblspc;

If you create a table inside the new tablespace, here is how the relation path appears. In the following log, it shows how the table is pointing to the appropriate tablespace:

postgres=# select pg_relation_filepath('employee');
 pg_relation_filepath
 ---------------------------------------------
 pg_tblspc/24611/PG_13_201909212/14187/24612
 (1 row)

And now, if you describe the table, you should be able to see the tablespace in which the table got created:

postgres=# \d employee
 Table "public.employee"
 Column | Type | Collation | Nullable | Default
 --------+---------+-----------+----------+---------
 id | integer | | |
 Tablespace: "newtblspc"

Create tables in specific tablespace with the set Command

SET default_tablespace =data01;
DROP TABLE IF EXISTS log2023_m1;
CREATE TABLE log2023_m1 PARTITION OF log FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
SET default_tablespace =data02;
CREATE TABLE log2023_m2 PARTITION OF log FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

Move to tablespace

To move a table to another tablespace, the syntax looks like the following:

ALTER TABLE <schemaname.tablename> SET TABLESPACE <tablespace_name>;
ALTER TABLE percona.foo SET TABLESPACE newtblspc;

To move an index to a new tablespace, the syntax looks like the following:

ALTER TABLE <schemaname.indexname> SET TABLESPACE <tablespace_name>;
ALTER INDEX percona.foo_id_idx SET TABLESPACE newtblspc;

Find location of Tables in a Tablespace

SELECT pg_relation_filepath('log2023_m1');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16384/PG_14_202107181/13748/16399
(1 row)
postgres=# SELECT pg_relation_filepath('log');
 pg_relation_filepath
----------------------
postgres=# SELECT pg_relation_filepath('log_default');
 base/13748/1640