Query PostgreSQL Tablespace Info with pg_tablespace

calendar Jul 3, 2026 / · 8 min read · postgresql database administration sql queries tablespaces pg_tablespace storage catalogs postgres dba  ·
Share on: linkedin copy

Query PostgreSQL Tablespace Info with pg_tablespace

What tablespaces exist in this cluster, who owns each one, and where do their files actually live on disk? pg_tablespace answers all three: it holds one row per tablespace with the owner OID, access control list, storage options, and — via pg_tablespace_location() — the on-disk path, all readable from a single catalog query without parsing configuration files or tracing symlinks by hand.

Purpose and Overview

Tablespaces are PostgreSQL's mechanism for controlling where data files land on disk. Every cluster starts with two built-in spaces: pg_default, which holds user tables and indexes, and pg_global, which holds the cluster-wide system catalogs. Additional tablespaces appear when a DBA runs CREATE TABLESPACE pointing at a different mount point — typically a faster SSD volume for a hot OLTP schema, a slower spinning disk for archive data, or a dedicated volume to isolate write-amplification from MVCC-heavy tables.

The pg_tablespace catalog exposes the metadata behind each space: its name, the OID of the role that owns it, the on-disk location resolved from a symlink under $PGDATA/pg_tblspc/, access control entries, and any storage options that influence how the query planner scores I/O against objects stored there. The psql meta-command \db shows the name and location but not the full structure; the catalog query reaches the options array and the ACL, which are the two fields most relevant to planner tuning and permission auditing.

A current tablespace inventory is a prerequisite for several DBA tasks: pre-migration audits that confirm where each schema's data physically lives, capacity checks after a disk replacement, permission reviews when a new application team is granted access, and verifying that planner cost overrides still reflect the underlying hardware's actual characteristics.

Sample Code

 1SELECT
 2    t.spcname                                           AS tablespace_name,
 3    pg_catalog.pg_get_userbyid(t.spcowner)              AS owner,
 4    pg_catalog.pg_tablespace_location(t.oid)            AS location,
 5    array_to_string(t.spcacl, ', ')                     AS access_privileges,
 6    array_to_string(t.spcoptions, ', ')                 AS options
 7FROM
 8    pg_catalog.pg_tablespace t
 9ORDER BY
10    t.spcname;

Notes: Works on PostgreSQL 9.2 and later. pg_tablespace_location() returns an empty string for the two built-in tablespaces (pg_default and pg_global) because they live inside $PGDATA rather than at a user-defined path. The spcacl and spcoptions columns are NULL by default; array_to_string(..., ', ') converts them to readable text when present. Requires at minimum the pg_read_all_stats built-in role (PostgreSQL 10+) or superuser access on older releases for a complete view of all rows.

Code Breakdown

The query draws from a single catalog table and resolves two OID-valued columns into human-readable output.

Resolving the Owner Name

1pg_catalog.pg_get_userbyid(t.spcowner) AS owner

spcowner stores an OID pointing to a row in pg_roles. Calling pg_get_userbyid() converts it to the role name in one expression, avoiding an explicit join to pg_roles or pg_authid. If the owning role has since been dropped, the function returns unknown (OID=n) rather than failing the query.

Reading the Filesystem Path

1pg_catalog.pg_tablespace_location(t.oid) AS location

PostgreSQL stores the tablespace path as a symlink under $PGDATA/pg_tblspc/ rather than directly in the catalog. The pg_tablespace_location() function resolves that symlink and returns the absolute filesystem path. For the built-in tablespaces the return value is an empty string, because those spaces have no external symlink — they live directly inside the data directory.

Flattening the Array Columns

1array_to_string(t.spcacl, ', ')     AS access_privileges,
2array_to_string(t.spcoptions, ', ') AS options

Both spcacl and spcoptions are PostgreSQL array columns. spcacl uses the standard ACL string format — for example, appuser=C/postgres indicates the CREATE privilege was granted to appuser by postgres. spcoptions stores key=value pairs when per-tablespace storage parameters have been set. array_to_string() collapses either array to a comma-separated string, keeping the output readable in a terminal without PostgreSQL array notation.

Key Tablespace Catalog Fields

spcname

The name used in DDL statements such as CREATE TABLE ... TABLESPACE name and ALTER TABLE ... SET TABLESPACE name. The names pg_default and pg_global are reserved; all user-created tablespace names must not begin with pg_. Renaming a tablespace with ALTER TABLESPACE ... RENAME TO updates this column immediately in the catalog.

spcowner

The OID of the role that owns the tablespace. The owner can grant CREATE access to other roles with GRANT CREATE ON TABLESPACE. Only a superuser or the tablespace owner can drop it, and a tablespace must be completely empty before the drop succeeds. This column is the first place to check when troubleshooting permission denied for tablespace errors from an application role.

pg_tablespace_location()

Storing the path as a symlink under $PGDATA/pg_tblspc/ rather than as a catalog column allows the physical files to be moved and the symlink updated without touching the catalog. The function resolves that indirection. A non-empty path is where pg_basebackup and filesystem-level backup tools look when handling this tablespace's data files.

spcoptions

This array holds tablespace-level parameters set via ALTER TABLESPACE ... SET (parameter = value). The most operationally significant are seq_page_cost and random_page_cost, which override the server-wide settings for objects stored in that tablespace. Setting them lower on an SSD-backed space tells the planner to favor index scans where random I/O is cheap. When the underlying hardware changes, these overrides must be revisited; stale values silently push the planner toward suboptimal plans.

Practical Applications

Pre-Migration Inventory

Before migrating a cluster to new hardware or rearchitecting storage volumes, run the catalog query to produce a complete tablespace map. Compare the location column against the target mount points on the destination host. Any tablespace pointing at a path that will not exist after migration needs a plan — either an ALTER TABLE ... SET TABLESPACE campaign before the cutover, or a symlink recreated on the target after the data files are copied.

Auditing Planner Cost Overrides

When query plans shift unexpectedly after hardware changes, spcoptions is the place to look. A tablespace configured with random_page_cost=1.1 for NVMe will carry that setting even if the underlying volume is later replaced with a spinning disk, silently pushing the planner toward index scans on slow random I/O. Reading spcoptions surfaces these overrides before they produce a performance regression.

Permission Verification

Grant reviews for a new application team often include confirming which tablespaces they can create objects in. The spcacl column exposes the full ACL in the same format as \dp output, and comparing it against current role memberships confirms whether access was granted correctly or needs adjustment via GRANT or REVOKE ON TABLESPACE.

Confirming Emptiness Before DROP TABLESPACE

A tablespace can only be dropped when no objects reside in it. Before running DROP TABLESPACE, verify the target is empty by joining pg_tablespace to pg_class on reltablespace and to pg_database on dattablespace. Any rows returned indicate objects that must be relocated or dropped first.

Capacity Monitoring Across Volumes

On systems with multiple tablespaces spread across different volumes, pairing pg_tablespace_location() output with an OS-level disk usage check maps each tablespace to its volume's available free space. This is the prerequisite step before sizing a new CREATE TABLESPACE on a replacement mount point, or deciding whether a volume needs expansion before the next scheduled bulk load.

Version Compatibility

The pg_tablespace catalog has been present since tablespace support was introduced in PostgreSQL 8.0. The most significant structural change came in PostgreSQL 9.2: the spclocation column was removed and replaced with the pg_tablespace_location() function. Queries written against PostgreSQL 9.1 or earlier that read spclocation directly fail on 9.2 and later; the function call is the correct replacement and works across all currently supported releases.

The spcoptions column for per-tablespace storage parameters was added in PostgreSQL 8.1 and has been stable since. The spcacl column follows the standard PostgreSQL ACL array format, consistent across all versions.

From PostgreSQL 14 onward, the built-in role pg_read_all_stats grants read access to tablespace catalog rows without a full superuser grant, which is useful for read-only monitoring accounts. On PostgreSQL 10 through 13 the equivalent requires direct SELECT privilege on pg_catalog.pg_tablespace, which itself requires superuser to grant. The query works without modification across all currently supported releases through PostgreSQL 17.

Best Practices

  • Grant CREATE ON TABLESPACE explicitly — the owner and superusers can create objects by default; application roles need an explicit grant rather than a change of tablespace ownership.
  • Review spcoptions after hardware changes — random_page_cost and seq_page_cost overrides set for NVMe persist after a volume replacement; verify they still match the real hardware before re-enabling production traffic.
  • Use pg_tablespace_location() not spclocation — the column was removed in 9.2; the function call is correct for all currently supported PostgreSQL versions.
  • Confirm emptiness before DROP TABLESPACE — join pg_class and pg_database on the tablespace OID to find any remaining objects before attempting the drop.
  • Run as superuser or pg_read_all_stats member for a complete inventory — a non-privileged role may see only tablespaces it owns or has been explicitly granted access to, producing a misleadingly partial list.
  • Verify tablespace symlinks in restore validation — pg_basebackup follows pg_tblspc symlinks, but confirm the restore procedure also recreates them correctly on the target host before declaring the restore complete.

References

Posts in this series