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.