PostgreSQL Tablespaces: Moving Tables and Indexes Across Storage Tiers

Most PostgreSQL deployments treat storage as a monolith — every table and index lives on the same disk volume, fighting for the same I/O bandwidth. This works fine until your database grows past a comfortable size and you start watching query latency climb because your hot indexes are competing with cold archive data for disk reads. PostgreSQL tablespaces solve this by letting you physically separate where data lives on disk, giving you fine-grained control over which storage tier each object uses. The result is a database that can be both fast for active workloads and cost-effective for historical data, without changing a single application query.

What are PostgreSQL Tablespaces?

A PostgreSQL tablespace is a named storage location that maps a logical identifier to a physical directory on the filesystem. When you create a table or index in a specific tablespace, PostgreSQL writes its data files into that directory rather than the default data directory. From the application's perspective nothing changes — queries work exactly the same way. From the OS and storage perspective, the data physically lives on a different device with its own I/O characteristics.

PostgreSQL ships with two built-in tablespaces that always exist:

• pg_default — the default tablespace for user objects when no tablespace is specified. Maps to $PGDATA/base.
• pg_global — used exclusively for shared system catalogs like pg_database, pg_authid, and pg_tablespace itself. You cannot create user objects here.

Under the hood, PostgreSQL implements tablespaces using symlinks. The $PGDATA/pg_tblspc/ directory contains symlinks named after each tablespace's OID, pointing to the actual filesystem directory you specified. This design means tablespace directories can be anywhere on the filesystem — different mount points, different physical devices, or even network-attached storage — as long as the PostgreSQL process can read and write to them.

The primary use cases for tablespaces are:

• Distributing I/O across multiple physical disks to reduce contention
• Placing frequently-accessed indexes on fast NVMe storage for low-latency reads
• Moving cold or archive data to inexpensive HDD or bulk storage to reduce cost
• Complying with data tiering policies that require different storage classes for different data ages

Creating and Managing Tablespaces

Step 1: Prepare the Filesystem Directories

Before creating a tablespace in PostgreSQL, you must create the target directory on the filesystem and ensure it is owned by the postgres OS user. PostgreSQL will refuse to create a tablespace in a directory it cannot write to, or in a directory that already contains data.

# Create mount point for fast NVMe storage
sudo mkdir -p /nvme/pg_tablespace
sudo chown postgres:postgres /nvme/pg_tablespace

# Create mount point for archive/cold storage
sudo mkdir -p /archive/pg_tablespace
sudo chown postgres:postgres /archive/pg_tablespace

In production, these directories will typically be on separately mounted volumes. Your NVMe drive might be mounted at /nvme and your archive HDD array at /archive. The PostgreSQL process only cares that the directory exists and is writable — the underlying storage type is invisible to it.

Step 2: Create Tablespaces in PostgreSQL

With the directories ready, create the tablespaces using CREATE TABLESPACE. This requires superuser privileges.

-- Create tablespaces
CREATE TABLESPACE fast_nvme LOCATION '/nvme/pg_tablespace';
CREATE TABLESPACE cold_archive LOCATION '/archive/pg_tablespace';

-- List all tablespaces with their locations and sizes
SELECT spcname, pg_tablespace_location(oid) AS location,
       pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;

Once created, the tablespace is available cluster-wide. Any database in the cluster can create objects in any tablespace, provided the database owner has the CREATE privilege on that tablespace.

Step 3: Create Objects in Specific Tablespaces

You can specify a tablespace at object creation time using the TABLESPACE clause. You can also set a session-level default to avoid repeating the clause on every statement.

-- Create table in specific tablespace
CREATE TABLE high_frequency_events (
  id BIGSERIAL PRIMARY KEY,
  user_id INT,
  event_type TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
) TABLESPACE fast_nvme;

-- Create index in specific tablespace
CREATE INDEX CONCURRENTLY idx_events_user_created
ON events(user_id, created_at DESC)
TABLESPACE fast_nvme;

-- Set default tablespace for a session
SET default_tablespace = 'fast_nvme';
CREATE TABLE new_table (id SERIAL, data TEXT);
-- This table goes to fast_nvme

Note that when you create a table with a PRIMARY KEY or UNIQUE constraint, PostgreSQL creates the backing index automatically. That index will inherit the table's tablespace unless you explicitly use a different one. Always verify where constraint indexes land using the monitoring queries shown later in this article.

Moving Objects Between Tablespaces

Moving Tables

To relocate an existing table to a different tablespace, use ALTER TABLE SET TABLESPACE. PostgreSQL will physically copy all the table's data files to the new location and update the catalog entries.

-- Move a single table (takes ACCESS EXCLUSIVE lock, rewrites file)
ALTER TABLE orders SET TABLESPACE fast_nvme;

-- Move all tables in a schema using a DO block
DO $$
DECLARE
  t TEXT;
BEGIN
  FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
    EXECUTE 'ALTER TABLE ' || quote_ident(t) || ' SET TABLESPACE fast_nvme';
  END LOOP;
END;
$$;

-- Move ALL tables at once from one tablespace to another (PostgreSQL 14+)
ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE fast_nvme;

Moving Indexes

Indexes can be moved independently of their parent tables. This is useful when you want to put indexes on faster storage than the underlying table data — a common pattern for read-heavy workloads where the bottleneck is index traversal rather than table scans.

-- Move a single index (requires rebuild)
ALTER INDEX idx_orders_customer SET TABLESPACE fast_nvme;

-- Move all indexes in a tablespace (PostgreSQL 14+)
ALTER INDEX ALL IN TABLESPACE pg_default SET TABLESPACE fast_nvme;

Changing the Database Default Tablespace

You can change the default tablespace for an entire database. This affects only newly created objects — it does not move existing tables or indexes.

-- Set the default tablespace for new objects in this database
ALTER DATABASE myapp SET TABLESPACE fast_nvme;
-- Note: this does NOT move existing objects, only sets default for new ones

To migrate all existing objects after changing the default, you still need to use ALTER TABLE ALL IN TABLESPACE or ALTER INDEX ALL IN TABLESPACE explicitly.

Tablespaces for Storage Tiering

Storage tiering is the practice of assigning data to different storage classes based on how frequently it is accessed. PostgreSQL tablespaces give you the mechanism to implement this at the object level, with no middleware or application changes required.

A typical three-tier strategy looks like this:

• NVMe SSD (fast_nvme): Active indexes, frequently queried tables, write-heavy tables receiving high INSERT/UPDATE rates
• SSD (pg_default): Standard OLTP tables, data from the current month or quarter
• HDD/Archive (cold_archive): Old partitions, audit logs, historical data that is rarely queried but must be retained

NVMe for Active Indexes and Hot Tables

Indexes benefit the most from fast storage because index scans are almost entirely random I/O. A B-tree traversal on a billion-row table may touch dozens of 8 KB pages scattered across the index file. On HDD, each random read costs 4–10 ms. On NVMe, it costs 50–100 microseconds. For a query that traverses three index levels, that difference compounds into real user-visible latency.

Place your most-hit indexes and the tables behind your busiest API endpoints on NVMe:

-- Create index in specific tablespace
CREATE INDEX CONCURRENTLY idx_events_user_created
ON events(user_id, created_at DESC)
TABLESPACE fast_nvme;

-- High-frequency table on NVMe
CREATE TABLE high_frequency_events (
  id BIGSERIAL PRIMARY KEY,
  user_id INT,
  event_type TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
) TABLESPACE fast_nvme;

HDD for Archives and Cold Partitions

Partitioned tables are the ideal companion to tablespace-based storage tiering. When you partition a large table by date, you can move older partitions to cheap storage while keeping recent partitions on fast storage — with no impact on query routing. PostgreSQL's partition pruning ensures that queries with date filters only scan the partitions that are relevant.

-- Archive old partitions to cold storage
-- Assuming monthly partitions
ALTER TABLE orders_2023_01 SET TABLESPACE cold_archive;
ALTER TABLE orders_2023_02 SET TABLESPACE cold_archive;

-- Recent partitions stay on fast storage
ALTER TABLE orders_2026_03 SET TABLESPACE fast_nvme;

This pattern is especially powerful for append-only datasets like event logs, audit trails, and IoT sensor data. You accumulate data fast into NVMe-backed partitions, then roll older partitions to cold storage on a schedule — a cron job or pg_partman background task handles the ALTER TABLE SET TABLESPACE calls automatically.

Tablespace Monitoring and Maintenance

Once you have multiple tablespaces in use, you need visibility into where your data lives and how much space each tablespace consumes. PostgreSQL exposes this through system catalog views and functions.

-- Tablespace size and location overview
SELECT
  spcname AS tablespace,
  pg_tablespace_location(oid) AS path,
  pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace
ORDER BY pg_tablespace_size(oid) DESC;

-- All objects and the tablespace they live in
SELECT t.spcname AS tablespace, c.relname AS object, c.relkind
FROM pg_class c
JOIN pg_tablespace t ON c.reltablespace = t.oid
ORDER BY t.spcname, c.relkind;

-- Find tables in the public schema that are still on pg_default (or unset)
SELECT tablename, tablespace
FROM pg_tables
WHERE schemaname = 'public'
  AND (tablespace IS NULL OR tablespace = 'pg_default')
ORDER BY tablename;

The third query is particularly useful after a migration — any table with a NULL tablespace is implicitly using pg_default. Run this periodically to catch newly created tables that missed the tablespace assignment.

For backup operations, PostgreSQL's pg_basebackup handles tablespaces automatically and supports remapping them to different paths in the backup destination:

# pg_basebackup includes all tablespaces automatically
pg_basebackup -h localhost -U replication_user -D /backup/base \
  --tablespace-map /nvme/pg_tablespace=/backup/nvme_ts \
  --tablespace-map /archive/pg_tablespace=/backup/archive_ts \
  -P -Xs -R

Key Takeaways

Working with JusDB on PostgreSQL Storage Strategy

Implementing tablespace-based storage tiering sounds straightforward when you read the documentation, but getting it right in production requires understanding your workload's I/O patterns, your partition lifecycle, and your hardware failure modes. A misconfigured tablespace that goes offline takes the entire PostgreSQL cluster down with it. A poorly planned migration that locks a critical table during business hours does the same thing.

At JusDB, we help engineering teams design and implement PostgreSQL storage architectures that match their actual workload — not a generic template. Whether you are starting from scratch with a new NVMe-backed cluster, migrating a decade of data across storage tiers, or trying to reduce your cloud storage bill by moving cold partitions to cheaper volumes, we have done it before at scale.

Our PostgreSQL services cover tablespace design and implementation, partition lifecycle management, storage tiering automation, and backup strategy validation. We also review your existing setup to identify tables and indexes that are on the wrong storage tier and costing you latency you should not be paying.

If you are running PostgreSQL in production and storage I/O is a concern, explore our PostgreSQL services or contact us to talk through your specific situation. Storage decisions made early in a database's life are much easier to get right than trying to retrofit them after your data has grown to terabytes.

Related reading:

• PostgreSQL Declarative Partitioning with pg_partman
• PostgreSQL Index Bloat and REINDEX Guide
• pgBackRest for PostgreSQL Backup and Recovery