PostgreSQL DBA Part 9: Tablespaces and Where Data Is Stored

This is Part 9 of the PostgreSQL DBA series. In Part 8 we covered databases, schemas and search_path, which are the logical containers for your tables. This part answers the physical question: on which disk do those tables actually live? In PostgreSQL the answer is a tablespace, a name that points to a directory on the server. Every table and index belongs to exactly one tablespace, and you can choose or change it with SQL.

By the end of this part you will be able to list the tablespaces of a cluster, create a new one on another disk, put tables, indexes, whole databases and temporary files in it, move existing objects between tablespaces, check how much space each one uses, and remove a tablespace safely. Every command is for Ubuntu with the PostgreSQL 18 cluster 18 main from Part 2.

What you will learn

• What a tablespace is, and the two built-in ones: pg_default and pg_global
• How to prepare a directory and run CREATE TABLESPACE, and what PostgreSQL creates on disk
• How to place tables, indexes and databases in a tablespace, and how default_tablespace works
• How to move existing tables and indexes with ALTER TABLE ... SET TABLESPACE, and what that locks
• How to send temporary files to their own disk with temp_tablespaces
• How to measure, tune and drop a tablespace without surprises

Prerequisites

• Parts 1 to 8 of this series, especially Part 3 (the data directory), Part 5 (postgresql.conf and reload) and Part 7 (roles and GRANT)
• One Ubuntu server with PostgreSQL 18 from the PGDG repository and the cluster 18 main running (pg_lsclusters shows it online)
• A Linux user with sudo, so you can run sudo -u postgres psql and create directories
• Optional: a second disk mounted on the server. We use the directories /mnt/fast_ssd and /mnt/slow_hdd; if you have only one disk, plain directories work the same way for practice

How it works

Think of the cluster as a library. Schemas are the sections (history, science), but the books still have to sit on a physical shelf somewhere. A tablespace is a shelf: a named place on the file system where PostgreSQL keeps the files of tables and indexes. Schemas and tablespaces are independent, so tables from one schema can sit on different shelves.

Every new cluster has two tablespaces, created by initdb:

When you create your own tablespace, you give PostgreSQL an empty directory, for example on a second disk. PostgreSQL records the tablespace in the pg_tablespace catalog and adds a symbolic link (a shortcut file) inside PGDATA/pg_tblspc. The link is named after the tablespace's OID (object identifier, the internal number PostgreSQL gives every object) and points to your directory.

server db1, cluster "18 main"
|
|-- /var/lib/postgresql/18/main            (PGDATA, system disk)
|     |-- base/           = pg_default    -> catalogs + tables of most databases
|     |-- global/         = pg_global     -> roles, list of databases
|     `-- pg_tblspc/
|           |-- 16395  -> /mnt/fast_ssd/pg18   (tablespace fast_ssd)
|           `-- 16397  -> /mnt/slow_hdd/pg18   (tablespace archive_hdd)
|
|-- /mnt/fast_ssd/pg18/PG_18_202506291/<database OID>/<file>   hot tables, indexes
`-- /mnt/slow_hdd/pg18/PG_18_202506291/<database OID>/<file>   old, rarely read data

Inside your directory PostgreSQL creates a subdirectory whose name depends on the server version: PG_18_ followed by a catalog version number. This lets a future major version use the same location without clashing with the old files, which matters during upgrades. Below that is one folder per database that has objects in the tablespace, named after the database OID.

Why would a DBA use tablespaces? The documentation gives two main reasons. First, when the disk holding PGDATA is full and cannot be extended, you can put new or large tables on another disk. Second, you can match data to storage: busy indexes on fast SSDs, old archive tables on cheaper, slower disks. A third common use is keeping large temporary sort files away from the main data disk.

Tablespaces also add work: every backup, standby server and restore must know about the extra directories. Many production servers use none at all and simply put PGDATA on one large, fast volume. Use tablespaces when you have a real storage reason, not by habit.

Looking at the tablespaces you already have

Step 1: List the tablespaces of the cluster

On db1, as the postgres user. Open psql and use the \db+ meta-command (from Part 4), then query the catalog directly.

sudo -u postgres psql

\db+
SELECT oid, spcname, spcowner::regrole, pg_tablespace_location(oid)
  FROM pg_tablespace;

                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 22 MB  |
 pg_global  | postgres |          |                   |         | 565 kB |
(2 rows)

  oid  |  spcname   | spcowner | pg_tablespace_location
-------+------------+----------+------------------------
  1663 | pg_default | postgres |
  1664 | pg_global  | postgres |

A fresh cluster has only the two built-in tablespaces. Their location is empty because they are not reached through pg_tblspc; they are the base and global folders inside PGDATA. pg_tablespace_location(oid) returns the file system path of a tablespace, and spcowner::regrole turns the owner's OID into a role name. Your sizes will differ.

Step 2: Create a sample database and see where a table's file lives

On db1, as the postgres user, in psql. We reuse the app_owner role idea from Parts 7 and 8 and create a small shopdb database with 50,000 orders. pg_relation_filepath() shows the path of a table's main data file, relative to PGDATA.

CREATE ROLE app_owner LOGIN PASSWORD 'Secret#2026';
CREATE DATABASE shopdb OWNER app_owner;
\c shopdb
SET ROLE app_owner;

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id int NOT NULL,
    order_date  date NOT NULL,
    amount      numeric(10,2) NOT NULL
);

INSERT INTO orders (customer_id, order_date, amount)
SELECT (g % 500) + 1,
       date '2026-01-01' + (g % 300),
       round((g % 997) * 1.37, 2)
  FROM generate_series(1, 50000) g;

SELECT oid, datname FROM pg_database WHERE datname = 'shopdb';
SELECT pg_relation_filepath('orders');

  oid  | datname
-------+---------
 16385 | shopdb
(1 row)

 pg_relation_filepath
----------------------
 base/16385/16387
(1 row)

The table lives in base/16385/16387: the pg_default tablespace (base), the folder of database OID 16385, and file number 16387. Your numbers will be different. Remember this path; in Step 8 we move the table and watch it change.

Creating a tablespace

Step 3: Prepare the directory on the new disk

On db1, as your sudo user (not postgres). PostgreSQL will not create the directory for you. The documentation says the directory must exist, should be empty, must be owned by the PostgreSQL system user, and must be given as an absolute path. Use a subdirectory of the mount point rather than the mount point itself, so the ownership change does not touch the mount point.

sudo mkdir -p /mnt/fast_ssd/pg18
sudo chown postgres:postgres /mnt/fast_ssd/pg18
sudo chmod 700 /mnt/fast_ssd/pg18
ls -ld /mnt/fast_ssd/pg18

drwx------ 2 postgres postgres 4096 Oct 30 12:47 /mnt/fast_ssd/pg18

The directory is owned by postgres and closed to every other user. That is all the preparation needed.

Step 4: Create the tablespace

On db1, as the postgres user, in psql. Only a superuser can create a tablespace, but it can give ownership to another role with OWNER. CREATE TABLESPACE cannot run inside a transaction block (BEGIN ... COMMIT).

CREATE TABLESPACE fast_ssd
    OWNER postgres
    LOCATION '/mnt/fast_ssd/pg18';
\db

CREATE TABLESPACE
            List of tablespaces
    Name    |  Owner   |      Location
------------+----------+--------------------
 fast_ssd   | postgres | /mnt/fast_ssd/pg18
 pg_default | postgres |
 pg_global  | postgres |
(3 rows)

Verify it worked by looking at both ends of the link, as your sudo user:

sudo ls -l /var/lib/postgresql/18/main/pg_tblspc/
sudo ls -la /mnt/fast_ssd/pg18/

total 0
lrwxrwxrwx 1 postgres postgres 18 Oct 30 12:47 16395 -> /mnt/fast_ssd/pg18
total 12
drwx------ 3 postgres postgres 4096 Oct 30 12:47 .
drwxr-xr-x 3 root     root     4096 Oct 30 12:47 ..
drwx------ 2 postgres postgres 4096 Oct 30 12:47 PG_18_202506291

The link 16395 is the OID of the new tablespace, and PostgreSQL has created the version folder PG_18_202506291 in your directory. Nothing else is there yet, because no table uses the tablespace.

Undo: DROP TABLESPACE fast_ssd; removes an empty tablespace (Step 12 covers dropping one that has objects in it).

Step 5: Allow an application role to use the tablespace

On db1, as the postgres user, in psql, connected to shopdb. Ordinary roles need the CREATE privilege on a tablespace before they can put objects in it. Without it, this is what app_owner gets:

\c shopdb
SET ROLE app_owner;
CREATE TABLE payments (payment_id bigint PRIMARY KEY) TABLESPACE fast_ssd;

ERROR:  permission denied for tablespace fast_ssd

Switch back to postgres and grant the privilege:

RESET ROLE;
GRANT CREATE ON TABLESPACE fast_ssd TO app_owner;
\db+ fast_ssd

                                        List of tablespaces
   Name   |  Owner   |      Location      |  Access privileges   | Options |  Size   | Description
----------+----------+--------------------+----------------------+---------+---------+-------------
 fast_ssd | postgres | /mnt/fast_ssd/pg18 | postgres=C/postgres +|         | 0 bytes |
          |          |                    | app_owner=C/postgres |         |         |
(1 row)

The C in app_owner=C/postgres means CREATE, granted by postgres (the access privilege codes were explained in Part 7). Undo: REVOKE CREATE ON TABLESPACE fast_ssd FROM app_owner;.

Placing tables and indexes in a tablespace

Step 6: Create a table and its indexes in the new tablespace

On db1, in psql connected to shopdb, as app_owner (SET ROLE app_owner;). The TABLESPACE clause puts the table in fast_ssd. An index is a separate object with its own tablespace: the index behind a primary key goes to the table's database default unless you add USING INDEX TABLESPACE.

SET ROLE app_owner;

CREATE TABLE payments (
    payment_id  bigint PRIMARY KEY USING INDEX TABLESPACE fast_ssd,
    order_id    bigint NOT NULL,
    paid_at     timestamptz NOT NULL
) TABLESPACE fast_ssd;

INSERT INTO payments
SELECT g, g, timestamptz '2026-01-01' + g * interval '1 minute'
  FROM generate_series(1, 20000) g;

CREATE INDEX payments_order_id_idx ON payments (order_id)
    TABLESPACE fast_ssd;

Verify it worked with the tablespace column of pg_tables and pg_indexes, and the file paths:

SELECT tablename, tablespace FROM pg_tables
 WHERE schemaname = 'public' ORDER BY tablename;

SELECT indexname, tablespace FROM pg_indexes
 WHERE schemaname = 'public' ORDER BY indexname;

SELECT pg_relation_filepath('payments') AS table_file,
       pg_relation_filepath('payments_pkey') AS pkey_file;

 tablename | tablespace
-----------+------------
 orders    |
 payments  | fast_ssd
(2 rows)

       indexname       | tablespace
-----------------------+------------
 orders_pkey           |
 payments_order_id_idx | fast_ssd
 payments_pkey         | fast_ssd
(3 rows)

                 table_file                  |                  pkey_file
---------------------------------------------+---------------------------------------------
 pg_tblspc/16395/PG_18_202506291/16385/16398 | pg_tblspc/16395/PG_18_202506291/16385/16401
(1 row)

An empty tablespace column means the object is in the database's default tablespace (here pg_default). The paths now go through pg_tblspc/16395, the link to /mnt/fast_ssd/pg18, then the version folder and database OID 16385. On disk, sudo ls -l /mnt/fast_ssd/pg18/PG_18_202506291/16385/ shows the files 16398, 16398_fsm, 16401 and 16403.

Step 7: Choose a tablespace automatically with default_tablespace

Typing TABLESPACE on every command is easy to forget. The default_tablespace setting supplies it for CREATE TABLE and CREATE INDEX commands that do not name one. Its default is an empty string, which means "use the current database's default tablespace". It is not used for temporary tables (Step 10) or for CREATE DATABASE.

For one session, on db1 in psql connected to shopdb as app_owner:

SHOW default_tablespace;
SET default_tablespace = fast_ssd;
CREATE TABLE refunds (refund_id bigint PRIMARY KEY, amount numeric(10,2));

SELECT c.relname, t.spcname
  FROM pg_class c
  LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
 WHERE c.relname LIKE 'refunds%' ORDER BY 1;
RESET default_tablespace;

 default_tablespace
--------------------

(1 row)

SET
CREATE TABLE
   relname    | spcname
--------------+----------
 refunds      | fast_ssd
 refunds_pkey | fast_ssd
(2 rows)

Both the table and its primary key index went to fast_ssd without a TABLESPACE clause. pg_class.reltablespace stores the tablespace OID of every table and index (0 means the database default).

Permanently for one role in one database, as postgres. This uses ALTER ROLE ... IN DATABASE ... SET from Part 8; it applies at the next login, not to SET ROLE:

ALTER ROLE app_owner IN DATABASE shopdb SET default_tablespace = 'fast_ssd';

psql -h localhost -U app_owner -d shopdb -c "SHOW default_tablespace;"

 default_tablespace
--------------------
 fast_ssd
(1 row)

Undo: ALTER ROLE app_owner IN DATABASE shopdb RESET default_tablespace; (the next login shows an empty value again), and DROP TABLE refunds; to remove the test table. You can also set default_tablespace in postgresql.conf, but a per-role or per-database setting is easier to reason about.

Moving existing objects

Step 8: Move a table and its index to another tablespace

On db1, in psql connected to shopdb as postgres. Moving a table does not move its indexes; move each index with ALTER INDEX.

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

ALTER TABLE orders SET TABLESPACE fast_ssd;

SELECT pg_relation_filepath('orders') AS table_file,
       pg_relation_filepath('orders_pkey') AS index_file;

ALTER INDEX orders_pkey SET TABLESPACE fast_ssd;

 table_size
------------
 2552 kB
(1 row)

ALTER TABLE
                 table_file                  |    index_file
---------------------------------------------+------------------
 pg_tblspc/16395/PG_18_202506291/16385/16409 | base/16385/16390
(1 row)

ALTER INDEX

Compare with Step 2: the table moved from base/16385/16387 to a new file 16409 in fast_ssd. PostgreSQL wrote a fresh copy and removed the old file. The index was still in base until the ALTER INDEX.

See the lock for yourself. Run the move inside a transaction, look at pg_locks (covered in depth in Part 29), then roll back:

BEGIN;
ALTER TABLE payments SET TABLESPACE pg_default;
SELECT relation::regclass, mode, granted
  FROM pg_locks
 WHERE pid = pg_backend_pid() AND relation = 'payments'::regclass;
ROLLBACK;

 relation |        mode         | granted
----------+---------------------+---------
 payments | AccessExclusiveLock | t
(1 row)

Verify what is now in the tablespace, with sizes:

SELECT c.relname, c.relkind, t.spcname,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
  FROM pg_class c
  JOIN pg_tablespace t ON t.oid = c.reltablespace
 WHERE t.spcname = 'fast_ssd'
 ORDER BY c.relname;

        relname        | relkind | spcname  |  size
-----------------------+---------+----------+---------
 orders                | r       | fast_ssd | 2552 kB
 orders_pkey           | i       | fast_ssd | 1112 kB
 payments              | r       | fast_ssd | 1024 kB
 payments_order_id_idx | i       | fast_ssd | 456 kB
 payments_pkey         | i       | fast_ssd | 456 kB
(5 rows)

In relkind, r is a table and i an index. This query only sees the current database, because pg_class is a per-database catalog. Undo: ALTER TABLE orders SET TABLESPACE pg_default; and ALTER INDEX orders_pkey SET TABLESPACE pg_default;.

Step 9: Give a whole database its own default tablespace

A database's default tablespace holds its system catalogs and is where its tables, indexes and temporary files go when nothing else is specified. On db1, as your sudo user, prepare a second directory for slower archive storage:

sudo mkdir -p /mnt/slow_hdd/pg18
sudo chown postgres:postgres /mnt/slow_hdd/pg18
sudo chmod 700 /mnt/slow_hdd/pg18

As the postgres user, in psql, create the tablespace and a database that uses it:

CREATE TABLESPACE archive_hdd LOCATION '/mnt/slow_hdd/pg18';
CREATE DATABASE reportdb OWNER app_owner TABLESPACE archive_hdd;

SELECT d.datname, t.spcname AS default_tablespace
  FROM pg_database d
  JOIN pg_tablespace t ON t.oid = d.dattablespace
 WHERE d.datname IN ('postgres', 'shopdb', 'reportdb')
 ORDER BY 1;

CREATE TABLESPACE
CREATE DATABASE
 datname  | default_tablespace
----------+--------------------
 postgres | pg_default
 reportdb | archive_hdd
 shopdb   | pg_default
(3 rows)

Every object created in reportdb without a TABLESPACE clause now lands on the slow disk, including its catalogs. Without the TABLESPACE clause, a new database uses the same tablespace as its template (normally pg_default); default_tablespace is not used here.

To move an existing database, use ALTER DATABASE ... SET TABLESPACE. It physically moves every table and index in the old default tablespace. The rules from the documentation: only the database owner or a superuser can do it, you need CREATE on the new tablespace, nobody may be connected to the database, it cannot run in a transaction block, and the database must have no objects in the new tablespace yet.

ALTER DATABASE reportdb SET TABLESPACE pg_default;

ALTER DATABASE

Verify by repeating the pg_database query above: reportdb now shows pg_default. If a session is connected to reportdb, the command waits a few seconds and then fails with ERROR: database "reportdb" is being accessed by other users. Find and end those sessions first (Part 10 covers pg_stat_activity and terminating sessions).

Temporary files on their own disk

Step 10: Point temp_tablespaces at a separate tablespace

When a sort or hash does not fit in work_mem, PostgreSQL writes temporary files. Temporary tables and their indexes are also files. The temp_tablespaces setting says where all of these go. Its default is an empty string, meaning the current database's default tablespace. You can list several tablespaces; PostgreSQL then picks one at random for each temporary object, which spreads the load.

On db1, as your sudo user, create the directory; then as postgres in psql, create the tablespace:

sudo mkdir -p /mnt/fast_ssd/pgtemp
sudo chown postgres:postgres /mnt/fast_ssd/pgtemp
sudo chmod 700 /mnt/fast_ssd/pgtemp
sudo -u postgres psql -c "CREATE TABLESPACE temp_ssd LOCATION '/mnt/fast_ssd/pgtemp';"

Edit /etc/postgresql/18/main/postgresql.conf (with sudo nano or your editor). The line is in the client connection defaults section:

# before
#temp_tablespaces = ''			# a list of tablespace names, '' uses
                                        # only default tablespace
# after
temp_tablespaces = 'temp_ssd'		# a list of tablespace names, '' uses
                                        # only default tablespace

This setting only needs a reload, not a restart. As postgres in psql:

SELECT pg_reload_conf();
SELECT name, setting, context, source
  FROM pg_settings WHERE name = 'temp_tablespaces';

 pg_reload_conf
----------------
 t
(1 row)

       name       | setting  | context |       source
------------------+----------+---------+--------------------
 temp_tablespaces | temp_ssd | user    | configuration file
(1 row)

The context value user means any user can also change it for their own session, and source confirms the value came from postgresql.conf. Note one safety rule from the documentation: a value set in postgresql.conf silently skips tablespaces that do not exist or that the user has no CREATE privilege on, so ordinary users can use temp_ssd without an explicit GRANT.

Verify it worked in two ways, connected to shopdb as postgres: a temporary table's file path, and a sort that spills to disk, with log_temp_files = 0 so every temporary file is logged.

CREATE TEMP TABLE cart_tmp (id int);
SELECT pg_relation_filepath('cart_tmp');

SET work_mem = '64kB';
SET log_temp_files = 0;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders ORDER BY amount DESC;

              pg_relation_filepath
------------------------------------------------
 pg_tblspc/16426/PG_18_202506291/16385/t3_16429
(1 row)

                      QUERY PLAN
------------------------------------------------------
 Sort (actual rows=50000 loops=1)
   Sort Key: amount DESC
   Sort Method: external merge  Disk: 1656kB
   ->  Seq Scan on orders (actual rows=50000 loops=1)
(4 rows)

sudo tail -3 /var/log/postgresql/postgresql-18-main.log

2026-10-30 12:53:16.459 UTC [1735] postgres@shopdb LOG:  temporary file: path "pg_tblspc/16426/PG_18_202506291/pgsql_tmp/pgsql_tmp1735.0", size 1695744
2026-10-30 12:53:16.459 UTC [1735] postgres@shopdb STATEMENT:  EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
	SELECT * FROM orders ORDER BY amount DESC;

The temporary table file (the t3_ prefix marks a temporary relation) and the 1.6 MB sort file (Sort Method: external merge) were both written under tablespace 16426, temp_ssd, in its pgsql_tmp folder. BUFFERS OFF is there only to keep the output short, because EXPLAIN ANALYZE shows buffer counts by default since PostgreSQL 18. Undo: comment the line out again in postgresql.conf and reload.

Monitoring, tuning and removing tablespaces

Step 11: Check sizes and set per-tablespace cost options

On db1, as postgres in psql. pg_tablespace_size() returns the space used by a whole tablespace across all databases. Ordinary users need CREATE on the tablespace or membership in pg_read_all_stats to call it.

SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
  FROM pg_tablespace ORDER BY spcname;

   spcname   |    size
-------------+------------
 archive_hdd | 8192 bytes
 fast_ssd    | 5668 kB
 pg_default  | 36 MB
 pg_global   | 605 kB
 temp_ssd    | 8192 bytes
(5 rows)

Watch these numbers next to df -h /mnt/fast_ssd in your monitoring: a full tablespace disk stops writes to every table on it, even if PGDATA has plenty of space.

A tablespace can also tell the query planner that its disk is faster or slower than the rest. Four options can be set per tablespace: seq_page_cost, random_page_cost, effective_io_concurrency and maintenance_io_concurrency. They override the server-wide settings of the same name for objects in that tablespace. The server defaults are seq_page_cost = 1 and random_page_cost = 4, a ratio that assumes spinning disks.

ALTER TABLESPACE fast_ssd SET (random_page_cost = 1.1);
SELECT spcname, spcoptions FROM pg_tablespace WHERE spcname = 'fast_ssd';

ALTER TABLESPACE
 spcname  |       spcoptions
----------+------------------------
 fast_ssd | {random_page_cost=1.1}
(1 row)

The value 1.1 is a common starting point for SSDs, not a documented rule; Part 28 covers planner cost tuning. Undo: ALTER TABLESPACE fast_ssd RESET (random_page_cost);. ALTER TABLESPACE can also rename a tablespace or change its owner, but it cannot change its location.

Step 12: Empty and drop a tablespace

On db1, as postgres in psql. A tablespace must be empty of objects from all databases before it can be dropped:

DROP TABLESPACE fast_ssd;

ERROR:  tablespace "fast_ssd" is not empty

First find which databases still have objects in it, with pg_tablespace_databases():

SELECT d.datname
  FROM pg_tablespace t
 CROSS JOIN LATERAL pg_tablespace_databases(t.oid) AS db(oid)
  JOIN pg_database d ON d.oid = db.oid
 WHERE t.spcname = 'fast_ssd';

 datname
---------
 shopdb
(1 row)

Connect to each database listed and move everything out. The ALL IN TABLESPACE form locks all matching tables first, then moves them one by one. Add OWNED BY role to move only one role's objects, or NOWAIT to fail at once instead of waiting for locks. Indexes need their own command:

\c shopdb
ALTER TABLE ALL IN TABLESPACE fast_ssd SET TABLESPACE pg_default;
ALTER INDEX ALL IN TABLESPACE fast_ssd SET TABLESPACE pg_default;

SELECT c.relname, c.relkind
  FROM pg_class c
  JOIN pg_tablespace t ON t.oid = c.reltablespace
 WHERE t.spcname = 'fast_ssd';

\c postgres
DROP TABLESPACE fast_ssd;

ALTER TABLE
ALTER INDEX
 relname | relkind
---------+---------
(0 rows)

DROP TABLESPACE

Verify: \db no longer lists fast_ssd, and sudo ls -la /mnt/fast_ssd/pg18/ shows an empty directory, because PostgreSQL removed its PG_18_... folder. The empty directory itself is left for you to remove with sudo rmdir /mnt/fast_ssd/pg18 if you no longer need it. If a session still lists the tablespace in temp_tablespaces, the drop can fail because of its temporary files; reset that setting first.

Common mistakes and how to fix them

ERROR: directory "/mnt/fast_ssd/pg18" does not exist

CREATE TABLESPACE never creates the directory. Create it as in Step 3, and check for typos and for a disk that is not mounted.

ERROR: could not set permissions on directory "/mnt/fast_ssd/pg18": Operation not permitted

The directory exists but is owned by root, so the postgres OS user cannot change it. Fix: sudo chown postgres:postgres /mnt/fast_ssd/pg18 and sudo chmod 700 /mnt/fast_ssd/pg18, then run the command again.

ERROR: tablespace location must be an absolute path

You wrote a relative path such as 'mnt/fast_ssd/pg18'. Start the path with /.

WARNING: tablespace location should not be inside the data directory

You pointed the tablespace at a folder inside /var/lib/postgresql/18/main. The command succeeds, but it gives no storage benefit and confuses backup tools. Drop it and use a directory outside PGDATA, ideally on another disk.

ERROR: permission denied for tablespace fast_ssd

The role has no CREATE privilege on the tablespace, either because you named it in a TABLESPACE clause or because default_tablespace points to it. Fix: GRANT CREATE ON TABLESPACE fast_ssd TO app_owner; or reset default_tablespace.

ERROR: some relations of database "reportdb" are already in tablespace "archive_hdd"

You ran ALTER DATABASE reportdb SET TABLESPACE archive_hdd while some tables of that database already live there. PostgreSQL adds the hint You must move them back to the database's default tablespace before using this command. Move those tables to the current default with ALTER TABLE ... SET TABLESPACE, then repeat.

ERROR: CREATE TABLESPACE cannot run inside a transaction block

The same applies to DROP TABLESPACE and ALTER DATABASE ... SET TABLESPACE. Run them on their own, outside BEGIN, and check that your migration tool does not wrap every script in a transaction.

A standby or restored server will not start because a tablespace path is missing

Physical copies of a cluster (pg_basebackup, standbys, file-level restores) need the tablespace directories too. Create the same paths on the other server, or map them to new ones with pg_basebackup's --tablespace-mapping option. Parts 18 and 33 show this in practice.

Practice exercise

1. Create a tablespace idx_ts in /mnt/fast_ssd/idx. 2. Move only the two indexes of the payments table into it, leaving the table where it is. 3. Prove it with pg_indexes and pg_tables.

Quick reference

# List tablespaces with locations and sizes (psql)
sudo -u postgres psql -c '\db+'

# Find the file of a table, relative to PGDATA
sudo -u postgres psql -d shopdb -c "SELECT pg_relation_filepath('orders');"

# Prepare a directory for a tablespace (as a sudo user)
sudo mkdir -p /mnt/fast_ssd/pg18
sudo chown postgres:postgres /mnt/fast_ssd/pg18
sudo chmod 700 /mnt/fast_ssd/pg18

# Create the tablespace and let a role use it
sudo -u postgres psql -c "CREATE TABLESPACE fast_ssd LOCATION '/mnt/fast_ssd/pg18';"
sudo -u postgres psql -c "GRANT CREATE ON TABLESPACE fast_ssd TO app_owner;"

# See the symbolic link PostgreSQL created
sudo ls -l /var/lib/postgresql/18/main/pg_tblspc/

# Create objects in it (SQL):
#   CREATE TABLE t (...) TABLESPACE fast_ssd;
#   CREATE INDEX i ON t (c) TABLESPACE fast_ssd;
#   ALTER ROLE app_owner IN DATABASE shopdb SET default_tablespace = 'fast_ssd';

# Move a table and its index (ACCESS EXCLUSIVE lock while copying)
sudo -u postgres psql -d shopdb -c "ALTER TABLE orders SET TABLESPACE fast_ssd;"
sudo -u postgres psql -d shopdb -c "ALTER INDEX orders_pkey SET TABLESPACE fast_ssd;"

# Create a database in a tablespace, or move one (no connections allowed)
sudo -u postgres psql -c "CREATE DATABASE reportdb OWNER app_owner TABLESPACE archive_hdd;"
sudo -u postgres psql -c "ALTER DATABASE reportdb SET TABLESPACE pg_default;"

# Temporary files: set temp_tablespaces = 'temp_ssd' in
# /etc/postgresql/18/main/postgresql.conf, then reload
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Size of each tablespace
sudo -u postgres psql -c "SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) FROM pg_tablespace;"

# Planner hint for a fast disk
sudo -u postgres psql -c "ALTER TABLESPACE fast_ssd SET (random_page_cost = 1.1);"

# Empty and drop a tablespace (run the ALTERs in every database that uses it)
sudo -u postgres psql -d shopdb -c "ALTER TABLE ALL IN TABLESPACE fast_ssd SET TABLESPACE pg_default;"
sudo -u postgres psql -d shopdb -c "ALTER INDEX ALL IN TABLESPACE fast_ssd SET TABLESPACE pg_default;"
sudo -u postgres psql -c "DROP TABLESPACE fast_ssd;"

Summary and what's next

• A tablespace is a named directory where PostgreSQL stores table and index files; pg_default (PGDATA/base) and pg_global (PGDATA/global) exist from the start
• Prepare an empty directory owned by postgres with mode 700, outside PGDATA, on permanent storage
• CREATE TABLESPACE needs a superuser and runs outside a transaction; other roles need GRANT CREATE ON TABLESPACE
• Use the TABLESPACE clause, USING INDEX TABLESPACE, default_tablespace or a database's own default to place objects
• ALTER TABLE / ALTER INDEX ... SET TABLESPACE copy the files under an ACCESS EXCLUSIVE lock; indexes move separately
• temp_tablespaces sends temp tables and sort files to their own disk and only needs a reload
• Check pg_tablespace_size() and disk space; empty a tablespace in every database before DROP TABLESPACE, and never rm its files

In Part 10 we look at connections and sessions: max_connections, pg_stat_activity and how to terminate sessions safely.