Tablespaces and size of the data

Create a database:

α=> CREATE DATABASE data_physical;

CREATE DATABASE

α=> \c data_physical

You are now connected to database "data_physical" as user "student".

Create a tablespace:

postgres$ mkdir /var/lib/postgresql/ts_dir

α=> CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir';

CREATE TABLESPACE

Create a table:

α=> CREATE TABLE t(n integer) TABLESPACE ts;

CREATE TABLE

α=> INSERT INTO t SELECT 1 FROM generate_series(1,1000);

INSERT 0 1000

Here is the database size:

α=> SELECT pg_size_pretty(pg_database_size('data_physical')) AS db_size;

 db_size 
---------
 7426 kB
(1 row)

The table size:

α=> SELECT pg_size_pretty(pg_total_relation_size('t')) AS t_size;

 t_size 
--------
 56 kB
(1 row)

The tablespaces size:

α=> SELECT pg_size_pretty(pg_tablespace_size('pg_default')) AS pg_default_size,
pg_size_pretty(pg_tablespace_size('ts')) AS ts_size;

 pg_default_size | ts_size 
-----------------+---------
 79 MB           | 60 kB
(1 row)

Note that the tablespace size is slightly more the table size due to some internal files stored by PostgreSQL in the tablespace directory.

Move the table:

α=> ALTER TABLE t SET TABLESPACE pg_default;

ALTER TABLE

Now the tablespaces are:

α=> SELECT pg_size_pretty(pg_tablespace_size('pg_default')) AS pg_default_size,
pg_size_pretty(pg_tablespace_size('ts')) AS ts_size;

 pg_default_size |  ts_size   
-----------------+------------
 79 MB           | 4096 bytes
(1 row)

Drop the tablespace:

α=> DROP TABLESPACE ts;

DROP TABLESPACE