Tablespaces in PostgreSQL helps to define or link the directory on the physical disk where the files of the objects(tables) are stored.
In Oracle, tablespaces are not links to the directory, tablespaces linked to physical files, and are allocated to a schema in a database so that all the tables created in that schema are stored in the allocated tablespace or we can mention a tablespace name while creating a table. The tablespace in PostgreSQL is assigned to a database or can be to a table and also one tablespace can be allocated to one or more databases.

Simple practical to show how the tablespace is created and how it is assigned to a database, their physical structures etc..

postgres=# create tablespace development location ‘/Library/PostgreSQL/data’;

CREATE TABLESPACE

postgres=# \db

                List of tablespaces

    Name     |  Owner   |         Location         

————-+———-+————————–

 development | postgres | /mnt/PostgreSQL/data

 pg_default  | postgres | 

 pg_global   | postgres | 

(3 rows)

When ever a new tablespace is created a link is also created in the following path

Datadirectory/pg_tblspc linking to actual location /mnt/PostgreSQL/data

cd /Library/PostgreSQL/12/data/pg_tblspc/

postgres$ ls -ltr

total 0

lrwx——  1 postgres  daemon  24 Nov 15 12:46 16582 -> /mnt/PostgreSQL/data

postgres=# create database DEVLDB tablespace development;

CREATE DATABASE

postgres=# \l+

                                                                List of databases

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   |   Size   | Tablespace  |                Description                 

———–+———-+———-+———+——-+———————–+———-+————-+——————————————–

  devldb    | postgres | UTF8     | C       | C     |                       | 8025 kB  | development | 

 postgres  | postgres | UTF8     | C       | C     |                       | 8033 kB  | pg_default  | default administrative connection database

 template0 | postgres | UTF8     | C       | C     | =c/postgres          +| 7881 kB  | pg_default  | unmodifiable empty database

           |          |          |         |       | postgres=CTc/postgres |          |             | 

 template1 | postgres | UTF8     | C       | C     | =c/postgres          +| 8025 kB  | pg_default  | default template for new databases

           |          |          |         |       | postgres=CTc/postgres |          |             | 

(6 rows)

postgres=# \c devldb

You are now connected to database “devldb” as user “postgres”.

devldb=# create table t1(name char(30));

CREATE TABLE

devldb=# select table_catalog,table_schema,table_name from information_schema.tables where table_name=’t1′;

 table_catalog | table_schema | table_name 

—————+————–+————

 devldb        | public       | t1

(1 row)

So the above table is created in devldb database , and the file related to the table should be stored in /Library/PostgreSQL/data .

We can check the path of the physical file of a table using below function

devldb=# SELECT pg_relation_filepath(‘t1’);

            pg_relation_filepath             

———————————————

 pg_tblspc/16582/PG_12_201909212/16583/16584

postgres$ cd /mnt/PostgreSQL/data/PG_12_201909212/16583

16583 postgres$ ls -l 16584

-rw——-  1 postgres  daemon  0 Nov 15 12:49 16584