Tablespace
A location outside of the main data directory for storing data
A tablespace is a location outside of the main data directory for storing data. This enables the distribution of data over multiple file systems, for example for storing more frequently accessed data on faster storage, or splitting table data and indexes between different disks.
Tablespaces were introduced in PostgreSQL 8.0.
psql commands
\dblists basic information about tablespaces available to a cluster\db+displays additional information such as tablespace size
System catalogue
Tablespace information is stored in the system catalogue table pg_tablespace.
Configuration
Following configuration parameters affect tablespace usage:
default_tablespace: default tablespace in which to create (non-temporary) objects (tables and indexes) when not overridden by theCREATEcommand or the database's default tablespace settingtemp_tablespaces: list of tablespaces in which to create temporary objects
Tablespace functions
Following functions provide tablespace-related information:
pg_tablespace_databases(tablespace_oid): returns a list of OIDs of databases which have objects stored in the tablespacepg_tablespace_location(tablespace_oid): returns the on-disk path of the tablespace directory (PostgreSQL 9.2 and later; for non-default tablespaces only)pg_tablespace_size(tablespace_oid or tablespace_name): returns size of the specified tablespace (PostgreSQL 8.1 and later)has_tablespace_privilege(tablespace, privilege): does current user have privileges for tablespacehas_tablespace_privilege(user, tablespace, privilege): does user have privileges for tablespace
Change history
Work-in-progress
- PostgreSQL 15
allow_in_place_tablespacesdevelopment GUC added (commit 7170f215)
- PostgreSQL 14
- support for reindexing tablespaces added (commit c5b28604)
- PostgreSQL 8.0
- (commit 2467394e)
References
- PostgreSQL documentation: Tablespaces
- PostgreSQL documentation: CREATE TABLESPACE
- PostgreSQL documentation: ALTER TABLESPACE
- PostgreSQL documentation: DROP TABLESPACE
- PostgreSQL documentation: default_tablespace
- PostgreSQL documentation: temp_tablespaces
Useful links
- When to use tablespaces in PostgreSQL - March 2020 blog article by CyberTec