In PostgreSQL, you must move a 400 GB cold archive table to a cheaper disk with ALTER TABLE ... SET TABLESPACE; what does it lock, what does it move, and how do you do it safely?
answer
- a full copy, not a rename
- the strongest table lock
- indexes stay behind
- partitioned parents move nothing
- lock queue and WAL volume
basics
~20 sPostgreSQL's ALTER TABLE ... SET TABLESPACE copies the table and its TOAST data under ACCESS EXCLUSIVE, blocking even reads until commit; indexes stay put. Move partitions one at a time with lock_timeout, move indexes separately, or use pg_repack --tablespace online.
solid answer
~50 s`ALTER TABLE ... SET TABLESPACE` is a **physical copy**: PostgreSQL writes every block of the table, and of its TOAST table and TOAST index, into new files in the target tablespace, and the old files are discarded after commit. It holds an **`ACCESS EXCLUSIVE`** lock throughout, so for a 400 GB table even `SELECT` waits for the whole copy, and with `wal_level` at `replica` or `logical` the copy is also written to WAL. The table's **indexes are not moved**: move each with `ALTER INDEX ... SET TABLESPACE` (also `ACCESS EXCLUSIVE`) or rebuild it with `REINDEX (TABLESPACE ...) ... CONCURRENTLY`. On a **partitioned** table the command moves nothing and only sets the default for future partitions. Do it safely in small pieces, one partition at a time, with `lock_timeout` set so a queued lock cannot stall traffic, or move it online with `pg_repack --tablespace`.
code
sql · 10 lines-- move one yearly partition of the climate archive to the cheap disk
SET lock_timeout = '5s';
ALTER TABLE climate_archive.readings_2019 SET TABLESPACE archive_hdd;
-- its indexes stayed behind: rebuild them in the new tablespace
REINDEX (TABLESPACE archive_hdd) INDEX CONCURRENTLY climate_archive.readings_2019_pkey;
REINDEX (TABLESPACE archive_hdd) INDEX CONCURRENTLY climate_archive.readings_2019_station_idx;
-- confirm where the files now live
SELECT pg_relation_filepath('climate_archive.readings_2019');go deeper
Remember that moving a table to another tablespace copies its data and blocks the table while it runs.
Explain the ACCESS EXCLUSIVE lock, that TOAST moves with the table while indexes do not, and that a partitioned parent moves nothing.
Plan the move: per-partition steps, lock_timeout against the lock queue, WAL and standby impact, index rebuilds, and pg_repack when downtime is not acceptable.
Argue for a design where data is born in the right tablespace through time partitioning, so large online relocations are rare events rather than routine work.
## What the command really does Moving a table between tablespaces is not a rename. Tablespaces are directories, possibly on different file systems, so `ALTER TABLE climate_archive.readings_2019 SET TABLESPACE archive_hdd` creates new files in the target tablespace and **copies every block** of the table into them. At commit the table points at the new files and the old ones are scheduled for removal. Three consequences: - **Duration** is proportional to the table's size, so a 400 GB table may take a long time on a slow destination disk. - **Disk space**: until commit, the data exists twice, once on each volume. - **WAL**: when `wal_level` is `replica` (the default) or `logical`, the copied pages are written to the WAL, so expect a burst of WAL roughly the size of the table, growth in the WAL archive and lag on standbys. Only `wal_level = minimal`, or an unlogged table, skips it. ## The lock and its blast radius `SET TABLESPACE` takes an **`ACCESS EXCLUSIVE`** lock on the table, the strongest table lock in PostgreSQL. It conflicts with every other lock mode, including the `ACCESS SHARE` lock a plain `SELECT` takes, so **readers and writers both wait** for the entire copy. The danger starts before the copy begins. If a long-running query holds `ACCESS SHARE`, the `ALTER TABLE` waits in the lock queue, and every new query on the table queues behind the waiting `ACCESS EXCLUSIVE` request. A cold table nobody reads may survive this; a table still referenced by a nightly report will not. Setting `lock_timeout` before the command makes it give up instead of stalling everyone. Lock levels per DDL command and `lock_timeout` are the schema-changes leaf's subject. ## What moves and what does not | Object | Moved by `ALTER TABLE ... SET TABLESPACE`? | |---|---| | The table's main data files | Yes | | Its TOAST table and the TOAST table's index | Yes | | The table's own indexes (primary key, secondary indexes) | **No**: move each with `ALTER INDEX ... SET TABLESPACE` | | Partitions of a partitioned table | **No**: nothing is moved; only partitions created later default to the new tablespace | The bulk forms `ALTER TABLE ALL IN TABLESPACE fast_ssd SET TABLESPACE archive_hdd` and `ALTER INDEX ALL IN TABLESPACE ...` move every table or index of the **current database** in one tablespace, optionally filtered with `OWNED BY`. They lock all the objects first and then move them one by one, so the locks are held for the whole run. With `NOWAIT` the command fails if it cannot take every lock immediately; it does not skip the busy ones. System catalogs are not moved by these forms. ## Doing it safely 1. **Make the unit small.** If the archive is partitioned by year, move one partition at a time; each move is shorter and locks only that partition. Then run `ALTER TABLE ... SET TABLESPACE` on the partitioned parent, which moves nothing but makes partitions created later land in the archive tablespace. 2. **Check the destination and the WAL path.** Free space on the target volume, and room in the WAL archive and on standbys for the extra WAL. 3. **Prepare every standby.** The target tablespace's mount path must exist on all of them. 4. **Set `lock_timeout`** (for example `5s`) and retry in a quiet window if it times out. 5. **Move the indexes** afterwards, or rebuild them in place with `REINDEX (TABLESPACE archive_hdd) INDEX CONCURRENTLY ...`, which blocks writes far less than `ALTER INDEX ... SET TABLESPACE`. 6. **Verify** with `pg_relation_filepath()` or the `reltablespace` column of `pg_class`, where 0 means the database's default tablespace. ## Online alternatives For a table that must stay readable and writable, `pg_repack --tablespace archive_hdd --moveidx` rebuilds the table and its indexes in the target tablespace while traffic continues, holding ACCESS EXCLUSIVE only briefly at the start and the end. It is the online version of `SET TABLESPACE`; how pg_repack works belongs to the bloat leaf. For tables that grow by time, the better long-term design is to partition by period and create old partitions directly in the archive tablespace, so nothing large ever has to move. Whichever route you take, the archive tablespace then becomes part of the backup set and of every standby's storage, so check both before calling the move done.
- Why does ALTER TABLE ALL IN TABLESPACE ... NOWAIT sometimes fail at once on a busy database?The ALL IN TABLESPACE form locks every table it will move before moving any, and NOWAIT makes it fail if any one of those `ACCESS EXCLUSIVE` locks is not available immediately. A single long transaction touching one of the tables is enough. Moving tables individually, or filtering with `OWNED BY`, shrinks the set of locks needed at once.
- How is ALTER DATABASE ... SET TABLESPACE different from moving tables one by one?It changes the database's default tablespace and moves every table and index stored in the old default tablespace, system catalogs included. No one may be connected to the database, the database must have nothing in the new tablespace yet, and it cannot run in a transaction block. Objects already placed in other tablespaces are not touched.
- SET TABLESPACE takes a light lock, so reads continue during the copy
- Moving a table to another tablespace also moves its indexes
- SET TABLESPACE on a partitioned parent moves every partition
- NOWAIT skips tables it cannot lock and moves the rest
- A tablespace move writes no WAL because it only relocates files