skip to content

What is a PostgreSQL tablespace, how is it laid out under pg_tblspc, and what do default_tablespace and temp_tablespaces control?

level: middleimportance: should knowfreq 38%

answer

  1. a named directory elsewhere
  2. symlink named by OID
  3. version subdirectory, then database OID
  4. permanent objects versus temp files
  5. not a separate backup unit

basics

~20 s

A PostgreSQL tablespace is a named directory, often on another disk, for storing tables and indexes; PGDATA/pg_tblspc holds a symlink to it per tablespace OID. default_tablespace places new tables and indexes; temp_tablespaces places temporary objects and spill files.

solid answer

~40 s

A **tablespace** lets you put relation files outside the data directory, for example cold archives on a cheaper disk. A superuser runs `CREATE TABLESPACE archive_hdd LOCATION '/mnt/archive_hdd/pgdata'` on an existing, empty directory owned by the PostgreSQL OS user, then grants CREATE on it. On disk, `PGDATA/pg_tblspc/<tablespace OID>` is a symlink to that location, which holds a version directory such as `PG_18_202506291` and inside it one directory per database OID. `default_tablespace` (default empty, meaning the database's default tablespace) supplies the tablespace for `CREATE TABLE` and `CREATE INDEX` without a `TABLESPACE` clause. `temp_tablespaces` (default empty) lists tablespaces for temporary tables and sort spill files, picking members at random. A tablespace is part of the cluster, never a separately restorable unit.

code

sql · 8 lines
sql
CREATE TABLESPACE archive_hdd LOCATION '/mnt/archive_hdd/pgdata';
GRANT CREATE ON TABLESPACE archive_hdd TO climate_admin;
ALTER TABLESPACE archive_hdd SET (random_page_cost = 6.0);

-- new climate tables land on the archive disk unless a TABLESPACE clause says otherwise
ALTER DATABASE climate_lab SET default_tablespace = 'archive_hdd';

SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;

go deeper

for a junior

Recall that a tablespace is a named directory, usually on another disk, where tables and indexes can be stored.

for a middle

Explain the pg_tblspc symlink layout and the split between default_tablespace for permanent objects and temp_tablespaces for temporary objects and spill files.

for a senior

Show the operational traps: standbys need the same mount path, a lost tablespace can stop the cluster, and backups must include every tablespace.

for a principal

Decide whether tiered storage belongs in tablespaces at all, or in partitioning plus archiving, given backup, replica and failure-domain costs.

## What a tablespace is A **tablespace** is a name for a directory where PostgreSQL may store the files of tables, indexes and whole databases. It controls **where on disk** data lives, plus a few planner cost settings for that storage; it is not a quota, not a security boundary, and not a backup unit. Two common reasons to create one: - **Capacity**: the volume holding `PGDATA` is full and cannot grow, so new or large objects go to another volume. - **Cost and speed**: hot indexes on fast storage, and cold, rarely read archive tables on a cheaper, slower disk. Tablespaces are **cluster-wide** objects: one tablespace can hold objects from any database, given CREATE privilege on it. ## Creating one 1. Create the directory with operating-system tools; `CREATE TABLESPACE` will not create it. It must be an absolute path, empty, and owned by the PostgreSQL OS user. 2. As a superuser, run `CREATE TABLESPACE archive_hdd LOCATION '/mnt/archive_hdd/pgdata';`. It cannot run inside a transaction block, and names starting with `pg_` are reserved. 3. Grant use to the roles that need it: `GRANT CREATE ON TABLESPACE archive_hdd TO climate_admin;`. 4. Place objects with a `TABLESPACE` clause on `CREATE TABLE`, `CREATE INDEX` or `CREATE DATABASE`, or move them later with `ALTER ... SET TABLESPACE`. On a streaming replica the tablespace path is passed across unchanged, so the same mount path must exist on every standby **before** the command runs on the primary. ## On disk: pg_tblspc and the layout below it | Tablespace | Where its files live | |---|---| | `pg_default` | `PGDATA/base/<database OID>/` | | `pg_global` (shared catalogs only) | `PGDATA/global/` | | A user tablespace | `PGDATA/pg_tblspc/<tablespace OID>` is a symlink to the location, which holds `PG_18_202506291/<database OID>/` | The version directory (`PG_` + major version + `_` + catalog version) lets successive PostgreSQL major versions use the same `LOCATION` without conflicts. `SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;` or psql's `\db` lists the locations. The manual warns against editing the `pg_tblspc` links by hand, and never while the server runs. The relation files inside a database directory are the storage leaf's subject. ## default_tablespace versus temp_tablespaces Both are ordinary user-settable parameters, so they can be set per session, per role, per database or in `postgresql.conf`. | | `default_tablespace` | `temp_tablespaces` | |---|---|---| | Applies to | permanent `CREATE TABLE` and `CREATE INDEX` without a `TABLESPACE` clause | temporary tables and their indexes, and temporary files such as sort and hash spills | | Value | one tablespace name | a list of names | | Default | empty string: the current database's default tablespace | empty string: the current database's default tablespace | | With several entries | not applicable | a random member each time; successive temporary objects in one transaction rotate through the list | Points that come up: - `default_tablespace` is not used for temporary tables or for `CREATE DATABASE`; a new database takes the template's tablespace unless `CREATE DATABASE ... TABLESPACE` says otherwise. - If `default_tablespace` names a tablespace that does not exist, PostgreSQL falls back to the database's default tablespace. - Temporary files are written to a `pgsql_tmp` directory: `PGDATA/base/pgsql_tmp` for `pg_default`, or a `pgsql_tmp` subdirectory inside the chosen tablespace. Pointing `temp_tablespaces` at a fast scratch volume keeps large sorts off the main disk. When a sort spills at all is decided by `work_mem`, the memory-tuning leaf's subject. - A database's own default tablespace (`CREATE DATABASE ... TABLESPACE`) also stores that database's system catalogs. ## Per-tablespace cost settings `ALTER TABLESPACE ... SET (...)` accepts exactly four options: `seq_page_cost`, `random_page_cost`, `effective_io_concurrency` and `maintenance_io_concurrency`. They override the server-wide parameters for relations in that tablespace, so the planner can cost a slow archive disk higher than an SSD. `pg_tablespace_size('archive_hdd')` reports how much the tablespace holds across all databases. ## What a tablespace is not Tablespace files depend on metadata in the main data directory. A tablespace therefore **cannot be detached and attached to another cluster** or backed up on its own, and losing one (disk failure, deletion, a RAM disk after reboot) can leave the whole cluster unreadable or unable to start. Treat every tablespace as part of the cluster's backup and durability plan, cold data included.

  • Can you drop a tablespace that still holds a table from another database?
    No. `DROP TABLESPACE` succeeds only when the tablespace is empty in every database of the cluster, and otherwise fails with `tablespace ... is not empty`. Because a tablespace is cluster-wide, you must find and move or drop the objects in each database first; `pg_tablespace_databases()` returns the OIDs of the databases that have objects there.
  • Why does the tablespace directory contain a folder like PG_18_202506291?
    It is a version-specific subdirectory: `PG_`, the major version and the catalog version number. It lets successive PostgreSQL major versions use the same `LOCATION` value without conflicts, because each version keeps its files under its own subdirectory.

  • CREATE TABLESPACE creates the directory if it is missing
  • A tablespace can be detached and attached to another cluster
  • Cold-data tablespaces can be left out of backups
  • default_tablespace also decides where sort spill files go
  • A tablespace limits how much disk a database may use