You can store your PostgreSQL data on different disks to improve performance and manage storage.
Let’s see how it works with a concrete example. Imagine we have two disks: /mnt/fast_ssd and /mnt/large_hdd. We want to put our main application data on the fast SSD and our archive data on the slower, larger HDD.
First, we need to create the directories on our disks that PostgreSQL will use.
sudo mkdir -p /mnt/fast_ssd/pgdata/main
sudo mkdir -p /mnt/large_hdd/pgdata/archive
Next, we tell PostgreSQL about these directories by creating "tablespaces." A tablespace is essentially a pointer to a directory on the filesystem. We’ll create two: main_tablespace and archive_tablespace.
CREATE TABLESPACE main_tablespace LOCATION '/mnt/fast_ssd/pgdata/main';
CREATE TABLESPACE archive_tablespace LOCATION '/mnt/large_hdd/pgdata/archive';
Now, when you create a new database, you can specify which tablespace it should use. By default, it will use the pg_default tablespace, which is usually located in your main PostgreSQL data directory.
CREATE DATABASE my_app_db TABLESPACE main_tablespace;
CREATE DATABASE my_archive_db TABLESPACE archive_tablespace;
This is great for new databases, but what about existing ones? You can move existing tables and indexes to a different tablespace. Let’s say you have a large table in my_app_db that you want to move to the archive tablespace.
-- Connect to your database
\c my_app_db
-- Move the table
ALTER TABLE large_data_table SET TABLESPACE archive_tablespace;
-- You can also move indexes separately
ALTER INDEX large_data_table_pkey SET TABLESPACE archive_tablespace;
This command doesn’t actually copy the data. Instead, it updates PostgreSQL’s catalog to point to the new location, and then it performs an INSERT into the new tablespace location and DELETE from the old. This can be a lengthy operation for very large tables.
The core problem tablespaces solve is I/O contention and storage tiering. If your primary database is on a single disk, all reads and writes compete for that disk’s bandwidth. By splitting databases or specific tables/indexes onto different physical devices, you can direct I/O operations to separate controllers and spindles (or SSDs), effectively increasing your aggregate I/O capacity. This is especially useful for separating high-transaction "hot" data from large, infrequently accessed "cold" data.
The pg_default tablespace is created automatically when PostgreSQL initializes. Its location is defined by the data_directory setting in postgresql.conf. When you create a new database without specifying a TABLESPACE, it inherits the tablespace of the template database it’s based on (usually template1), which in turn defaults to pg_default.
When you run ALTER TABLE ... SET TABLESPACE ..., PostgreSQL creates new files for the table’s data and indexes in the target tablespace’s directory. It then initiates a transaction that reads all rows from the old table location and inserts them into the new one. Once the copy is complete, it swaps the new table with the old one and then drops the old table’s files. This requires enough free space in both the old and new tablespace locations to accommodate the data during the operation.
The most surprising thing about tablespaces is how they interact with VACUUM FULL. If you ALTER TABLE ... SET TABLESPACE ... and then perform a VACUUM FULL on that table, the table’s data will be rewritten again to the new tablespace location, but this time it will be the only copy. This means if you have a very large table and want to move it to a new tablespace and reclaim space, performing ALTER TABLE ... SET TABLESPACE ... followed by VACUUM FULL is a common, albeit resource-intensive, strategy. However, VACUUM FULL is an exclusive lock, so it’s usually better to use ALTER TABLE ... SET TABLESPACE ... followed by a regular VACUUM and then REINDEX if needed, or simply let the next VACUUM handle it.
The next thing you’ll likely want to explore is how to manage tablespaces when migrating your entire PostgreSQL instance or when dealing with recovery scenarios.