Tablespaces in PostgreSQL: What Matters and What Doesn’t

You Probably Don’t Need Your Tablespaces

Of everything an Oracle DBA carries into PostgreSQL, tablespaces cause the most unnecessary work — because the instinct is to recreate the Oracle layout, and that instinct is wrong. In Oracle a tablespace is fundamental: every segment lives in one, with quotas, storage parameters and per-user defaults. In PostgreSQL a tablespace is almost nothing — a named symlink to a directory on another filesystem — and entirely optional.

What a PostgreSQL tablespace actually is

By default every object lives in the data directory (the pg_defaulttablespace) and you never think about it. A tablespace is just a way to say “put these files on that disk”:

-- create one only when you have a real physical-placement reason
CREATE TABLESPACE fast LOCATION '/mnt/nvme/pg';

-- then steer specific objects to it
CREATE INDEX ix_hot ON orders (created_at) TABLESPACE fast;
ALTER TABLE big_cold_table SET TABLESPACE archive;

No quotas. No segment management. No AUTOEXTEND. No requirement that a user have a default tablespace. It is purely a filesystem placement hint.

The one real reason to create one

Physical storage tiering. Put your hottest indexes on NVMe, or move a large, rarely-touched archive table onto cheaper disk. That’s it. If every filesystem on the box is the same, you almost certainly want zero custom tablespaces and should collapse a dozen Oracle tablespaces into the default.

What to know before you create one

• Cluster-wide, not per-database. A tablespace belongs to the whole instance and can hold objects from any database.
• They complicate backup & replication. Base backups follow the symlinks, and a physical standby must be able to honour the same paths. Every custom tablespace is one more thing to keep in sync.
• temp_tablespaces is the useful knob most people miss — point large sorts and hashes at a fast or separate disk without touching your tables.
• WAL is separate. The write-ahead log isn’t in a tablespace; relocate it with a symlink on pg_wal if you need it on its own spindle.