PostgreSQL tablespace management: moving data without downtime

PostgreSQL tablespaces map relations to specific filesystem paths. Moving data between tablespaces is not an online operation in core PostgreSQL: ALTER TABLE SET TABLESPACE copies the relation’s data files while holding an ACCESS EXCLUSIVE lock for the entire duration. For a large relation, reads and writes block for minutes or hours. This guide covers three operational paths: the built-in offline move for small or maintenance-tolerant relations, offline symlink relocation for entire tablespace directories, and near-zero-downtime workarounds for heavy tables.

What tablespace moves enable

Operators use tablespaces to tier storage (NVMe for hot partitions, cold disk for archives), evacuate a full volume, or isolate high-churn relations onto dedicated spindles. Moving a relation changes its physical backing file under $PGDATA/pg_tblspc/<oid> without rewriting SQL references. Unlike VACUUM FULL or CLUSTER, a tablespace move does not reorder tuples; it only changes the filesystem location. The operation is bounded by raw copy speed plus WAL logging, not by table rewrite complexity. Plan for the lock window, destination disk space, and the extra WAL volume generated during the copy.

Prerequisites

• Superuser or ownership of the target relations.
• Destination free space greater than or equal to the relation size. If the destination filesystem also holds WAL or pg_repack work tables, add corresponding headroom.
• Schedule built-in ALTER TABLE moves during a maintenance window. SET TABLESPACE has no ONLINE or CONCURRENTLY modifier in any current PostgreSQL version.
• Symlink relocation requires stopping the entire cluster. Never update $PGDATA/pg_tblspc/<oid> while PostgreSQL is running.
• For large tables under continuous load, use pg_repack or a logical-replication cutover instead of the built-in command. pg_repack requires a primary key or unique NOT NULL index and roughly 2x the table size in free disk space.

Procedure

Move a single table or index (built-in, offline)

This is the simplest path and the only one built into core PostgreSQL. It is fully blocking.

1. Check the relation size.

   SELECT pg_size_pretty(pg_total_relation_size('schema.table'));
   

2. Move the table.

   -- WARNING: blocks reads and writes for the entire copy
   ALTER TABLE schema.table SET TABLESPACE new_tablespace;
   

   The command acquires an ACCESS EXCLUSIVE lock. All queries queue behind it until the copy finishes.

3. Move indexes separately. ALTER TABLE SET TABLESPACE moves the table and its TOAST data automatically, but it does not move indexes.

   ALTER INDEX schema.idx1 SET TABLESPACE new_tablespace;
   ALTER INDEX schema.idx2 SET TABLESPACE new_tablespace;
   

4. Verify the move.

   -- reltablespace = 0 means pg_default
   SELECT relname, reltablespace
   FROM pg_class
   WHERE relnamespace = 'schema'::regnamespace
     AND relname IN ('table', 'idx1', 'idx2');
   

Move all user tables in a tablespace at once

If you are evacuating an old tablespace and can tolerate downtime, use the bulk form. It operates within the current database only, excludes system catalogs, and moves only tables and materialized views. Indexes remain behind and must be relocated separately.

ALTER TABLE ALL IN TABLESPACE old_tablespace
SET TABLESPACE new_tablespace;

Append NOWAIT to fail immediately if any relation cannot acquire the lock:

ALTER TABLE ALL IN TABLESPACE old_tablespace
SET TABLESPACE new_tablespace NOWAIT;

Relocate a tablespace directory (cluster offline)

When the underlying mount point or directory must change (for example, storage migration or filesystem reorganization), relocate the backing directory and update the symlink. PostgreSQL backends cache open file descriptors and path metadata, so the cluster must be stopped before any filesystem change.

1. Identify the tablespace OID.

   SELECT oid, spcname FROM pg_tablespace WHERE spcname = 'mytablespace';
   

2. Stop PostgreSQL.

   pg_ctl stop -D $PGDATA -m fast
   

3. Move the physical directory. If the old and new paths are on the same mount, mv is atomic. If you are crossing filesystems, use cp -a or rsync, then remove the source only after confirming the cluster starts.

   cp -a /old/mount/tablespace_data /new/mount/tablespace_data
   

4. Update the symlink in pg_tblspc. The symlink name is the tablespace OID.

   # WARNING: verify the OID and path before running
   ln -snf /new/mount/tablespace_data "$PGDATA/pg_tblspc/<oid>"
   

5. Start PostgreSQL and confirm the new location.

   SELECT spcname, pg_tablespace_location(oid) AS location
   FROM pg_tablespace
   WHERE spcname = 'mytablespace';
   

6. After confirming the cluster is healthy and the tablespace is accessible, remove the old source directory if you used a copy-based method.

Near-zero-downtime alternatives for large tables

For large tables where an exclusive lock is unacceptable, the built-in command is not viable. The two common workarounds are:

• pg_repack. Rebuilds the table online with only a brief ACCESS EXCLUSIVE lock at the final swap. It requires a primary key or unique NOT NULL index and roughly 2x disk space. Native tablespace targeting in pg_repack depends on version-specific CLI options; consult the pg_repack documentation for your release before planning the move.
• Logical replication cutover. Create a publication on the source, a subscription on a target cluster where the table already lives in the desired tablespace, and switch application traffic once the subscriber catches up. This avoids locking the source table but requires managing schema, sequences, and slot cleanup.

Configure temporary tablespaces

temp_tablespaces is a comma-separated list of tablespace names PostgreSQL uses for temporary objects. If unset, temp objects land in pg_default. Setting it helps isolate sort and hash spill I/O from your main data directory.

SET temp_tablespaces = 'fast_ssd, fallback_disk';

PostgreSQL picks one entry at random for each temp object. List tablespaces in order of preference, but remember that random selection means load spreads rather than strictly tiers.

Verifying the move

After any tablespace change, confirm that the catalog and filesystem agree.

• Query pg_class.reltablespace to confirm the relation points to the new tablespace OID. A value of 0 means pg_default.
• For symlink relocations, pg_tablespace_location(oid) must return the new absolute path.
• Check that filesystem usage on the destination volume grew by the expected amount and that the source volume dropped after the old files were unlinked.
• If you moved indexes, verify each one individually. Missing an index move is the most common oversight.

Common pitfalls

• Indexes stay behind. ALTER TABLE SET TABLESPACE moves the table and its TOAST data, but indexes remain on the old tablespace unless moved separately. The bulk ALL IN TABLESPACE form behaves the same way for tables and materialized views.
• WAL amplification. The move generates WAL for every copied block. On replicas, this appears as replication lag proportional to the relation size. Do not disable archiving to speed up the move; that endangers point-in-time recovery and replicas.
• Mid-copy disk exhaustion. The old file is not removed until the new copy is complete. The destination volume must have room for the full relation. If the old and new tablespaces share a filesystem, that filesystem must hold both copies until the move commits.
• Symlink updates while running. Updating $PGDATA/pg_tblspc/<oid> while PostgreSQL is running risks data corruption and crash recovery failure because backends cache open file descriptors. Always stop the cluster first.
• ALL IN TABLESPACE skips system catalogs. It only moves user relations in the current database. You cannot use it to relocate global objects.

Signals to monitor during a move

How Netdata helps

• Chart PostgreSQL lock waits alongside disk I/O on the destination volume to confirm copy progress and estimate completion.
• Track replication lag to quantify WAL impact on streaming replicas.
• Alert on long-running ALTER TABLE queries that exceed the expected maintenance window.
• Alert on filesystem capacity for source and destination tablespace paths.
• Chart transaction rate drops during the lock window to communicate impact to stakeholders.

Related guides

• How PostgreSQL actually works in production: a mental model for operators
• PostgreSQL ALTER TABLE blocked: zero-downtime DDL patterns
• PostgreSQL autovacuum blocked by long-running transaction: detection and fix
• PostgreSQL autovacuum not running: detection, causes, and fixes
• PostgreSQL autovacuum tuning: per-table thresholds for high-churn workloads
• PostgreSQL blocking queries: finding the root blocker in a lock cascade
• PostgreSQL checkpoint storms: detection, causes, and tuning
• PostgreSQL: checkpoints are occurring too frequently – what to tune
• PostgreSQL connection exhaustion: detection, diagnosis, and prevention
• PostgreSQL connection refused: pg_hba, listen_addresses, and TCP diagnosis
• PostgreSQL: database is not accepting commands to avoid wraparound data loss
• PostgreSQL dead tuples piling up: why autovacuum can’t keep up