A tablespace is a named mapping from a logical label to a directory path on the operating system filesystem. It tells PostgreSQL where to physically store the files that back a database object — tables, indexes, sequences, or entire databases.

Unlike Oracle, where a tablespace is a container for datafiles which are themselves managed by the RDBMS, PostgreSQL tablespaces are essentially symbolic links to directories. The OS filesystem is the real container; PostgreSQL simply points at it.

The root of everything is $PGDATA — the data directory specified when initdb is run. Inside that directory the following structure exists:

Default Tablespace (pg_default) — base/ in Detail

The base/ directory is the physical backing of the pg_default tablespace. Every database gets its own numbered subdirectory named after its OID. Inside that directory live both system catalog files and all user-created tables and indexes that were not assigned to a custom tablespace.

Custom Tablespace Directory Structure

When you create a custom tablespace at /mnt/nvme/pg/, PostgreSQL creates a versioned subdirectory inside it, then a database-OID subdirectory inside that:

What Happens When a Table or Index Exceeds 1 GB?

PostgreSQL never makes a single OS file larger than 1 GB (= 8 192 bytes × 131 072 pages). When a relation file reaches that limit, a new segment file is automatically created with a numeric suffix appended to the relfilenode. All segments are the same relfilenode — only the suffix changes.

Every relation has an OID (Object Identifier) in the system catalog. The actual filename on disk is the relation's relfilenode — initially identical to its OID, but may diverge after TRUNCATE, CLUSTER, or VACUUM FULL (all of which rewrite the file).

Step-by-Step: CREATE TABLE → physical file

-- 1. Create a table in a specific tablespace
CREATE TABLE orders (
    id     BIGSERIAL PRIMARY KEY,
    amount NUMERIC(12,2),
    status TEXT
) TABLESPACE fast_ssd;

-- 2. Find its OID and relfilenode
SELECT oid, relname, relfilenode, reltablespace
FROM   pg_class
WHERE  relname = 'orders';

--  oid  | relname | relfilenode | reltablespace
-- ------+---------+-------------+---------------
-- 16400 | orders  |       16400 |         24601

-- 3. Compute the actual file path
SELECT pg_relation_filepath('orders');
-- pg_tblspc/24601/PG_17_202307071/16384/16400

PostgreSQL resolves the path as:

What Happens on TRUNCATE or VACUUM FULL?

-- Before TRUNCATE: relfilenode = 16400
TRUNCATE orders;

-- After TRUNCATE: relfilenode = 16450 (new file, old one deleted)
SELECT relfilenode FROM pg_class WHERE relname = 'orders';
-- 16450

The old file 16400 is unlinked from the OS. A brand-new file 16450 is created. The OID stays 16400 but the physical filename changes. This is why relfilenode and oid can diverge.

Each relation file is divided into fixed-size pages (default 8 KB, set at compile time). A page is the unit of I/O — the smallest amount PostgreSQL reads from or writes to disk.

Tuples grow upward from the bottom of the page; the ItemId array grows downward from just after the header. The free space in the middle is what INSERT consumes. The Free Space Map (FSM) fork tracks which pages have enough free space to avoid scanning the heap during inserts.

-- Inspect a page directly (requires pageinspect extension)
CREATE EXTENSION pageinspect;

SELECT lower, upper, special, pagesize
FROM   page_header(get_raw_page('orders', 0));

-- lower = end of ItemId array (bytes from start)
-- upper = start of tuple data (bytes from start)
-- free  = upper - lower

SELECT t_ctid, t_xmin, t_xmax, t_infomask, t_data
FROM   heap_page_items(get_raw_page('orders', 0))
LIMIT  5;

The catalog tables that track tablespace storage are:

-- All tablespaces and their OS paths
SELECT spcname, pg_tablespace_location(oid) AS location
FROM   pg_tablespace;

-- spcname    | location
-- -----------+---------------
-- pg_default |
-- pg_global  |
-- fast_ssd   | /mnt/nvme/pg/

-- All relations in a given tablespace
SELECT n.nspname, c.relname, c.relkind, c.relfilenode
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
JOIN   pg_tablespace t ON t.oid = c.reltablespace
WHERE  t.spcname = 'fast_ssd'
ORDER BY c.relname;

Every relation is stored across potentially multiple files due to two orthogonal splitting mechanisms: segments and forks.

Segments (Size-Based Splitting)

A single relation file grows until it hits the segment size limit (default 1 GB, defined at compile time as BLCKSZ × RELSEG_SIZE). At that point, PostgreSQL creates a new segment file:

This segmentation ensures compatibility with filesystems that have file size limits, and also makes backup tools simpler.

Forks (Logical Data Splitting)

Each relation actually has up to four forks — separate files for different types of storage data:

-- See all physical files for a relation
SELECT pg_relation_filepath('orders');             -- main
SELECT pg_relation_filepath('orders') || '_fsm';  -- fsm
SELECT pg_relation_filepath('orders') || '_vm';   -- vm

-- Or check from the shell
-- $ ls -lh $PGDATA/base/16384/16385*
-- -rw------- 1 postgres postgres  8.0M  orders main segment 0
-- -rw------- 1 postgres postgres  24K   orders FSM
-- -rw------- 1 postgres postgres   8K   orders VM

PostgreSQL ships with two built-in tablespaces that cannot be dropped:

-- These have no filesystem path (returned as empty string)
SELECT spcname, pg_tablespace_location(oid)
FROM   pg_tablespace
WHERE  spcname IN ('pg_default', 'pg_global');

-- Change the default tablespace for a DATABASE
ALTER DATABASE mydb SET TABLESPACE fast_ssd;

-- Change the default for new objects in the SESSION
SET default_tablespace = fast_ssd;

This is the conceptual crux that trips up DBAs moving between the two systems. They use the same word — "tablespace" — but mean fundamentally different things.

Create a Tablespace

-- Directory must exist, be owned by the OS postgres user, and be empty
-- $ mkdir -p /mnt/nvme/pg && chown postgres:postgres /mnt/nvme/pg

CREATE TABLESPACE fast_ssd
    OWNER postgres
    LOCATION '/mnt/nvme/pg';

Move Objects to a Different Tablespace

-- Move a single table (rewrites file; ACCESS EXCLUSIVE lock required)
ALTER TABLE orders SET TABLESPACE fast_ssd;

-- Move all tables in a database (run while connected to target db)
ALTER DATABASE mydb SET TABLESPACE fast_ssd;

-- Move all objects from one tablespace to another (superuser only)
ALTER TABLESPACE old_ssd RENAME TO archive_hdd;

Drop a Tablespace

-- Tablespace must be empty before dropping
DROP TABLESPACE old_ssd;

-- Check what's still in it
SELECT c.relname, n.nspname
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'old_ssd');

Tablespace-Level Storage Parameters

-- Set random_page_cost hint per tablespace (optimizer advice)
ALTER TABLESPACE fast_ssd
    SET (random_page_cost = 1.1);   -- NVMe: nearly sequential

ALTER TABLESPACE archive_hdd
    SET (random_page_cost = 4.0);   -- spinning disk default

-- seq_page_cost can also be set per tablespace (PG 16+)
ALTER TABLESPACE fast_ssd
    SET (seq_page_cost = 0.1);

Separate Indexes from Tables

Place indexes on an NVMe tablespace and tables on a slower drive to maximize index scan bandwidth independently of sequential heap scans:

CREATE TABLE events (...) TABLESPACE hdd_bulk;
CREATE INDEX events_ts_idx ON events (created_at) TABLESPACE nvme_fast;

Partition Tables Across Tablespaces

CREATE TABLE logs_2024 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    TABLESPACE nvme_hot;

CREATE TABLE logs_2022 PARTITION OF logs
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01')
    TABLESPACE hdd_archive;

TEMP Tablespace

-- Point sort/hash spill temp files to a fast local SSD
SET temp_tablespaces = 'local_nvme';

-- Or set cluster-wide in postgresql.conf
-- temp_tablespaces = 'local_nvme'

Monitoring Tablespace Size

-- Size of each tablespace
SELECT
    spcname,
    pg_size_pretty(pg_tablespace_size(oid)) AS size,
    pg_tablespace_location(oid)             AS location
FROM pg_tablespace
ORDER BY pg_tablespace_size(oid) DESC;

-- Top 10 largest relations in a tablespace
SELECT
    n.nspname || '.' || c.relname AS relation,
    c.relkind,
    pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM   pg_class c
JOIN   pg_namespace n   ON n.oid = c.relnamespace
JOIN   pg_tablespace t  ON t.oid = c.reltablespace
WHERE  t.spcname = 'fast_ssd'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT  10;