PostgreSQL : Tablespaces

PostgreSQL stores all the data under its data directory(PGDATA), but that does not mean that data cannot be stored outside the data directory. Most of the database administrators make use of the default tablespace and overlook the powerful features of tablespaces. 

Meaning of tablespace in Postgres

A tablespace in PostgreSQL is simply a location on disk where the database can store data files. By default, PostgreSQL stores all data inside the PGDATA directory. However, in real-world scenarios, you might want to spread data across multiple storage devices or different drives or folders to better performance

• Performance – by distributing I/O load across different storage devices
• Scalability – by placing large tables or indexes on faster storage disks.
• Storage Management – by using cheaper storage for less critical data and high-performance SSDs for frequently accessed data.

Command to check tablespaces in PostgreSQL

\db or \db+

Creating a new tablespace (user-defined)

 

postgres=# CREATE TABLESPACE technodb LOCATION 'C:\Program Files\PostgreSQL\pg_technodb';

CREATE TABLESPACE

postgres=# \db+

                          List of tablespaces

  Name    |  Owner   |Location           | Access privileges | Options |  Size   | Description

------------+----------+----------------+-------------------+---------+---------+--

 pg_default | postgres |                 |                   |         | 29 MB   |

 pg_global  | postgres |                 |                   |         | 534 kB  |

 technodb   | postgres | C:\Program Files\PostgreSQL\pg_technodb |     | 0 bytes |

(3 rows)

 

postgres=# 

 

To create a tablespace, a physical directory should be present before creating the tablespace.

we can assign tablespaces to individual databases, tables, or indexes.