Postgres Tablespaces: Moving Tables to Different Storage Without Downtime

Applies toPostgres 9.0+RiskACCESS EXCLUSIVE lock on ALTER TABLE SET TABLESPACEUse caseMoving hot tables to SSD, cold tables to spinning disk

Every Postgres database defaults to a single tablespace stored on whatever disk your data directory lives on. That's fine until it isn't — until your write-heavy tables are fighting your archival tables for the same IOPS budget, or until you add a fast NVMe drive and have no way to tell Postgres to use it for specific tables.

Tablespaces fix this. They're named locations on disk that you can route objects to explicitly. Not magic tiering, not automatic, not zero-effort — but a real knob for placing hot tables on fast storage and cold tables on cheap storage.

Creating a Tablespace

-- Directory must exist and be owned by the postgres OS user
CREATE TABLESPACE fast_ssd LOCATION '/mnt/nvme/pgdata';
CREATE TABLESPACE cold_hdd LOCATION '/mnt/spindle/pgdata';

That's the whole creation. Postgres handles the rest — subdirectories, permissions, catalog registration. You can verify with \db in psql or:

SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;

Moving a Table

-- This takes ACCESS EXCLUSIVE — no reads or writes during move
ALTER TABLE events SET TABLESPACE fast_ssd;

The bad news: ALTER TABLE SET TABLESPACE acquires an ACCESS EXCLUSIVE lock and physically rewrites the table to the new location. On a busy table, this blocks everything. On a 200GB table, this takes minutes. Plan accordingly — maintenance window, not a casual Tuesday.

Moving indexes requires separate commands:

ALTER INDEX events_pkey SET TABLESPACE fast_ssd;
ALTER INDEX events_created_idx SET TABLESPACE fast_ssd;

Or move everything at once with:

-- Move all objects in the database to a tablespace
ALTER DATABASE mydb SET TABLESPACE fast_ssd;
-- This is non-blocking but only affects future objects

Setting Defaults

default_tablespace controls where new tables and indexes land when no explicit tablespace is specified:

-- Session-level
SET default_tablespace = 'fast_ssd';

-- Or in postgresql.conf for the whole cluster
default_tablespace = 'fast_ssd'

temp_tablespaces controls where sort and hash operations spill to disk. If you have a separate fast disk, this is worth setting:

-- Can be a list for round-robin across multiple temp locations
SET temp_tablespaces = 'fast_ssd,fast_ssd2';

Sort spills and hash joins are write-then-read-once workloads. A fast temp tablespace makes a real difference when work_mem is exhausted on complex queries.

Monitoring Tablespace Usage

-- Size of each tablespace
SELECT spcname,
       pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM pg_tablespace
WHERE spcname NOT IN ('pg_global', 'pg_default')
ORDER BY pg_tablespace_size(spcname) DESC;

-- Which tables are on which tablespace
SELECT schemaname, tablename, tablespace
FROM pg_tables
WHERE tablespace IS NOT NULL
ORDER BY tablespace, tablename;

Tables without an explicit tablespace entry use default_tablespace (or pg_default if that's unset). The tablespace column in pg_tables will be null for those.

What Tablespaces Don't Give You

A few things that come up in conversations about tablespaces that they actually can't do:

No auto-tiering. Postgres doesn't move data automatically based on access patterns. You decide what goes where and move it manually. There's no "tier this table to slow storage after 30 days."

No per-index tablespace in partitioned tables before PG14. Before Postgres 14, you couldn't set different tablespaces for different indexes on a partitioned table. PG14+ propagates the tablespace setting to child index partitions.

No sub-table granularity. You can't put hot rows on fast storage and cold rows on slow storage within the same table. That granularity requires partitioning first, then tablespace assignment per partition.

No compression control. Tablespaces route objects to a filesystem location. They don't change compression behavior. If you want ZFS compression on cold data, configure that at the filesystem level, not the tablespace level.

The Practical Pattern

The case that actually pays off: you have a large table that's write-heavy during business hours and occasionally scanned for analytics. Move the table to your fast SSD tablespace. Create a separate partitioned table for archived data (say, anything older than 90 days), put that partition on spinning disk. Queries that filter by date will hit the partition pruning and never touch the slow disk during hot operations.

Tablespaces are a 15-minute setup that can change your IOPS profile permanently. The lock during the initial move is the real cost. Everything after that is essentially free.

Building something that needs Postgres storage tiering? builds.anethoth.com tracks what we're shipping at Anethoth — database work included.