Skip to content
Tutorials Center
My learning

Topics

Lesson 79 of 90 ยท Security and Administration

Tablespaces and Physical Storage

See how PostgreSQL stores tables on disk: the data directory, 8 kB pages, relation files, CREATE TABLESPACE, moving tables and measuring size with pg_relation_size.

Everything you create in PostgreSQL, tables, indexes, sequences, ends up as files. Most of the time you never need to look at them, but sooner or later you will meet a question that is really about files: why is this table 40 GB when it holds 10 GB of data? Where does the disk space go during a rebuild? Can I put the big, rarely used tables on cheaper disks?

This lesson opens the box. You will learn how the data directory is organized, how one table becomes one or more files made of 8 kB pages, how to measure sizes with SQL, and how tablespaces let you place objects on other disks. The server-side commands (CREATE TABLESPACE, pg_relation_size) need a real PostgreSQL instance, so those blocks are read-only. A live SQLite editor shows the page-level behavior (growth, free pages and VACUUM) that carries over to PostgreSQL.

From table to file

Data directory One folder holds the whole cluster.
Relation file Each table or index is a file.
Page Files are cut into 8 kB blocks.
Tablespace A folder on another disk.
1 / 2

The data directory

A PostgreSQL cluster (see Clusters, Databases and Schemas) lives in one directory, usually called PGDATA. You can ask the server where it is:

SQL
-- PostgreSQL (read-only)
SHOW data_directory;

Inside, the important pieces are:

Path Contents
base/ One subdirectory per database, named by the database OID. This is where ordinary tables and indexes live.
global/ Cluster-wide tables, such as the list of databases and roles.
pg_wal/ The write-ahead log (see Transactions and ACID and Backup and Recovery).
pg_tblspc/ Symbolic links to the tablespaces you created.
pg_xact/, pg_multixact/ Records of which transactions committed.
postgresql.conf, pg_hba.conf Configuration and client authentication rules (sometimes stored elsewhere).
PG_VERSION, postmaster.pid The major version and the running server's process id.

Never edit or delete files in PGDATA by hand. PostgreSQL tracks them in the catalogs, and a missing or truncated file corrupts the database. File-level copies are only safe through the backup tools described in Backup and Recovery.

From a table to files

Each table and index is stored as its own file. The file name is a number called the relfilenode, and the file sits in the directory of its database. You can look up the exact path:

SQL
-- PostgreSQL (read-only)
CREATE TABLE note (id bigint, body text);
SELECT pg_relation_filepath('note');
-- base/16384/16421       (database OID / relfilenode, the numbers on your system differ)

A few rules explain most of what you see on disk:

  • Pages. A file is a sequence of 8 kB blocks called pages (the compile-time default). A page holds a header, an array of item pointers and the row versions (tuples). A row cannot span pages, so a row larger than about 2 kB is compressed or moved out of line (see TOAST below).
  • Segments. Files are limited to 1 GB. A larger table becomes 16421, 16421.1, 16421.2 and so on.
  • Forks. Besides the main file, a relation can have helper files: _fsm (the free-space map, which pages have room), _vm (the visibility map, which pages contain only visible rows) and _init (for unlogged tables, see Temporary and Unlogged Tables).
  • TOAST. Large values (long text, jsonb, bytea) are compressed and stored out of line in a companion table, so the main table stays compact. This is why a table with big text columns can be small while its TOAST table is large.
  • The relfilenode can change. Commands such as VACUUM FULL, CLUSTER, TRUNCATE and some ALTER TABLE forms rewrite a table into a new file with a new relfilenode. The OID stays the same.

Because rows are never updated in place (see MVCC Explained), files hold dead row versions until vacuum clears them. Freed space inside a file is reused by new rows but is normally not returned to the operating system. Only a rewrite (VACUUM FULL) or trailing empty pages truncated by regular vacuum shrinks the file.

Measuring size

PostgreSQL offers a family of size functions. They return bytes, so wrap them in pg_size_pretty:

SQL
-- PostgreSQL (read-only)
SELECT pg_size_pretty(pg_relation_size('note'))        AS main_fork_only,
       pg_size_pretty(pg_table_size('note'))           AS table_with_toast_and_maps,
       pg_size_pretty(pg_indexes_size('note'))         AS all_indexes,
       pg_size_pretty(pg_total_relation_size('note'))  AS everything;

-- The ten biggest tables in the current database
SELECT c.oid::regclass AS name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;

-- Whole databases
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

The difference between the functions matters:

  • pg_relation_size counts only the main fork of one relation (a table or an index).
  • pg_table_size adds the TOAST table and the free-space and visibility maps.
  • pg_indexes_size adds up the table's indexes.
  • pg_total_relation_size is table plus indexes, which is what the table really costs you.

Estimating how many pages a table uses is a good way to spot bloat. If pg_relation_size is many times larger than rows times average row width, dead space is the likely reason.

What pages teach you: a live demo

SQLite stores a database in 4 kB pages (its default), and the questions are the same: how many pages does the data use, what happens to the pages when rows are deleted, and what does it take to give the space back? Run the example. It fills a table, measures it, deletes half of the rows and measures again, then rewrites the file with VACUUM, which does for SQLite what VACUUM FULL does for PostgreSQL.

SQL Pages, free space and rewriting

Press Run (or Ctrl + Enter) to see the result here.

After the delete the file is the same size, because the freed space is scattered in small holes across most pages, and almost no page is entirely empty. Only the rewrite packs the remaining rows into roughly half the pages. PostgreSQL behaves the same way: regular VACUUM makes holes reusable, VACUUM FULL (which takes an ACCESS EXCLUSIVE lock while it copies the table) actually shrinks the file.

Tablespaces

A tablespace is a named location in the file system where PostgreSQL may store relation files. Without any setup, everything goes to two built-in tablespaces: pg_default (the base/ directory) and pg_global (for cluster-wide catalogs). You create more to spread data across disks, for example:

  • put hot tables and indexes on fast NVMe storage and archives on cheaper disks,
  • continue when the main volume is full by adding a new one,
  • keep a heavy index on its own device so its I/O does not compete with the table.
SQL
-- PostgreSQL (read-only). The directory must already exist, be empty and be owned
-- by the operating-system user that runs PostgreSQL. Only a superuser can run this.
CREATE TABLESPACE fastdisk LOCATION '/mnt/nvme/pgdata';

-- Place new objects there
CREATE TABLE orders (id bigint PRIMARY KEY, placed_at timestamptz)
  TABLESPACE fastdisk;

CREATE INDEX orders_placed_idx ON orders (placed_at)
  TABLESPACE fastdisk;

-- Allow a role to create objects in it
GRANT CREATE ON TABLESPACE fastdisk TO app_owner;

What actually happens: PostgreSQL creates a symbolic link in pg_tblspc/ whose name is the tablespace's OID and which points to your directory, then stores the files under a version-specific subdirectory with one folder per database. Tablespaces are cluster-wide: one tablespace can hold objects from several databases.

Choosing and moving

You can set defaults instead of naming the tablespace each time:

SQL
-- PostgreSQL (read-only)
ALTER DATABASE shop SET default_tablespace = 'fastdisk';   -- for new objects
SET temp_tablespaces = 'scratch';                          -- for sort spill files and temp tables

-- Move an existing table or index (rewrites the file)
ALTER TABLE orders SET TABLESPACE slowdisk;
ALTER INDEX orders_placed_idx SET TABLESPACE fastdisk;

-- Move everything of an owner in one tablespace to another
ALTER TABLE ALL IN TABLESPACE fastdisk OWNED BY app_owner SET TABLESPACE slowdisk;

Moving an object copies its files to the new location and holds an ACCESS EXCLUSIVE lock on the table while it does so, which blocks reads and writes. For a large table this means downtime, so plan it for a quiet window. A tablespace can only be dropped when it is empty: DROP TABLESPACE fastdisk;.

You can also tell the planner about different hardware per tablespace:

SQL
-- PostgreSQL (read-only)
ALTER TABLESPACE fastdisk SET (random_page_cost = 1.1, effective_io_concurrency = 200);

This feeds the cost model explained in Statistics and the Planner, so index scans on fast storage look cheaper to the planner.

Costs and warnings

Tablespaces are less popular than they used to be. Keep these points in mind:

  • Backups and replicas must know the paths. A physical standby needs the same directories to exist, or you must remap them (pg_basebackup --tablespace-mapping). A logical dump with pg_dump records tablespace names, and restoring on a server that lacks them fails unless you use --no-tablespaces.
  • No quota. PostgreSQL does not limit the size of a tablespace. A filled disk stops the database, and a full tablespace disk can break only the objects stored there.
  • More moving parts. Today many deployments use one big volume with striping (RAID, LVM, ZFS or a cloud disk) and skip tablespaces. Managed cloud services often do not allow CREATE TABLESPACE at all.
  • Partitioning is a better tool for hot and cold data. You can place each partition in a different tablespace (see Declarative Partitioning) and detach or drop old partitions instead of copying data.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table is 3.5 GB. How many files does PostgreSQL use for its main fork?

    Relation files are split into segments of at most 1 GB (names like 16421, 16421.1, 16421.2 and 16421.3), so 3.5 GB needs four segments.
  2. 2. Which function reports the size of a table together with all of its indexes?

    pg_relation_size is only the main fork, pg_table_size adds TOAST and the maps, and pg_total_relation_size adds the indexes too.
  3. 3. After you delete most rows of a large table and run a regular VACUUM, why is the file usually not smaller?

    Regular vacuum marks space reusable inside the file. Only a rewrite such as VACUUM FULL, or empty pages at the end of the file, gives space back to the file system.
  4. 4. What is true of ALTER TABLE ... SET TABLESPACE?

    Moving a table physically copies its files to the new location, blocking reads and writes during the copy.
  5. 5. Which is a good reason to use a tablespace?

    Tablespaces control where files live. They do not add speed by themselves, do not replace backups and PostgreSQL has no quota on them.

Summary

  • A cluster lives in PGDATA. Ordinary tables and indexes are files in base/<database oid>/, named by their relfilenode.
  • Files are made of 8 kB pages, split into 1 GB segments, with extra forks for free space and visibility, and TOAST for large values.
  • pg_relation_size, pg_table_size, pg_indexes_size and pg_total_relation_size measure what a table really occupies.
  • Regular vacuum makes space reusable; only a rewrite like VACUUM FULL shrinks files.
  • CREATE TABLESPACE places objects in another directory, and moving objects locks and copies them. Prefer partitioning or striped volumes unless you need distinct disks.

Related reading: how objects that skip durability work in Temporary and Unlogged Tables, and Monitoring and Catalogs for the system views.