What is a PostgreSQL tablespace, how is it laid out under pg_tblspc, and what do default_tablespace and temp_tablespaces control?
answer
- a named directory elsewhere
- symlink named by OID
- version subdirectory, then database OID
- permanent objects versus temp files
- not a separate backup unit
basics
~20 sA 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 sA **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 linesCREATE 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
Recall that a tablespace is a named directory, usually on another disk, where tables and indexes can be stored.
Explain the pg_tblspc symlink layout and the split between default_tablespace for permanent objects and temp_tablespaces for temporary objects and spill files.
Show the operational traps: standbys need the same mount path, a lost tablespace can stop the cluster, and backups must include every tablespace.
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