Tablespaces and Storage Placement
Index I/O and table I/O are competing on the same disk, saturating the queue. Moving indexes to a second volume immediately separates the two workloads.
Every table and index PostgreSQL creates lives in some tablespace — by default, the cluster's single pg_default tablespace, wherever PGDATA itself lives on disk. A tablespace is just a named pointer to a directory PostgreSQL has permission to write to; creating one, and then placing specific tables or indexes in it, is how a busy table's heavy write traffic can be physically separated from its own indexes' equally heavy write traffic, or how cold, rarely-touched data can be moved onto cheaper, slower storage without touching the schema, the queries, or the application at all.\n\nThis lab builds two real tablespaces on two separate directories, places a table and its index directly into one, moves an existing table into the other, and confirms a specific, easy-to-miss fact directly: tablespaces belong to the whole PostgreSQL cluster, not to any one database — a tablespace created while connected to one database is immediately usable from every other database in the same cluster.
Two Real Tablespaces
CREATE TABLESPACE fast_ssd LOCATION '/tmp/fast';
CREATE TABLESPACE archive_hdd LOCATION '/tmp/slow';
Each points at a real, existing, PostgreSQL-writable directory — no special filesystem required, just a location the server process can read and write.
Placing New Objects Directly
CREATE TABLE sql_products_ts (id int PRIMARY KEY, name text) TABLESPACE fast_ssd;
CREATE INDEX idx_products_ts_name ON sql_products_ts(name) TABLESPACE fast_ssd;
A table and one of its indexes, placed directly in fast_ssd at creation time — no separate move step needed when the placement is known upfront.
Moving an Existing Table
CREATE TABLE sql_event_archive (id int PRIMARY KEY, data text); -- created in the default tablespace
ALTER TABLE sql_event_archive SET TABLESPACE archive_hdd;
SELECT tablename, tablespace FROM pg_tables WHERE tablename IN ('sql_products_ts','sql_event_archive');
tablename | tablespace
-------------------+-------------
sql_products_ts | fast_ssd
sql_event_archive | archive_hdd
Both placements confirmed directly from pg_tables, not assumed from the commands having run without error.
Cluster-Global, Confirmed Directly
-- fast_ssd was created while connected to beer_db. Switch to a completely different database:
\c postgres
SELECT spcname FROM pg_tablespace WHERE spcname = 'fast_ssd'; -- still visible
CREATE TABLE other_db_table (id int) TABLESPACE fast_ssd; -- works immediately
A tablespace created in one database is immediately visible and usable from every other database in the same cluster — it is not scoped to the database it happened to be created from.
A Real Symlink on Disk
$ ls -la $PGDATA/pg_tblspc/
lrwxrwxrwx 1 postgres postgres 9 19011 -> /tmp/fast
Every non-default tablespace shows up in PGDATA's pg_tblspc directory as a real symbolic link, named by the tablespace's OID, pointing at the actual location — the mechanism made visible directly, not just described.
Tablespace
A named pointer to a directory on disk where PostgreSQL stores the actual files for tables, indexes, or an entire database. Every cluster starts with pg_default (where user objects live unless told otherwise) and pg_global (shared catalog data); additional tablespaces are created explicitly with CREATE TABLESPACE, each represented in PGDATA's pg_tblspc directory as a symbolic link to the real location.
Cluster-global object
An object that belongs to the whole PostgreSQL server instance (the cluster) rather than to any single database within it — roles and tablespaces are the two most common examples. A tablespace created while connected to one database is immediately visible and usable from every other database on the same server, with no per-database setup required.
💽 Create Two Real Tablespaces
Create two tablespaces pointing at two separate directories, representing a fast and an archive volume.
as-postgres mkdir -p /tmp/fast /tmp/slowpsql -U postgres -d beer_db -c "CREATE TABLESPACE fast_ssd LOCATION '/tmp/fast';" -c "CREATE TABLESPACE archive_hdd LOCATION '/tmp/slow';"student@lab:~$ su root -c 'mkdir -p /tmp/fast /tmp/slow && chown postgres:postgres /tmp/fast /tmp/slow' student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLESPACE fast_ssd LOCATION '/tmp/fast';" -c "CREATE TABLESPACE archive_hdd LOCATION '/tmp/slow';" SET CREATE TABLESPACE CREATE TABLESPACE
📥 Place a New Table and Index Directly
Create a table and one of its indexes directly in fast_ssd at creation time.
psql -U postgres -d beer_db -c "CREATE TABLE sql_products_ts (id int PRIMARY KEY, name text) TABLESPACE fast_ssd;" -c "CREATE INDEX idx_products_ts_name ON sql_products_ts(name) TABLESPACE fast_ssd;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_products_ts (id int PRIMARY KEY, name text) TABLESPACE fast_ssd;" -c "CREATE INDEX idx_products_ts_name ON sql_products_ts(name) TABLESPACE fast_ssd;" SET CREATE TABLE CREATE INDEX
📤 Move an Existing Table and Verify Both Placements
Create a table in the default tablespace, move it to archive_hdd, then confirm both tables' real placement directly from pg_tables.
psql -U postgres -d beer_db -c "CREATE TABLE sql_event_archive (id int PRIMARY KEY, data text);" -c "ALTER TABLE sql_event_archive SET TABLESPACE archive_hdd;"psql -U postgres -d beer_db -c "SELECT tablename, tablespace FROM pg_tables WHERE tablename IN ('sql_products_ts','sql_event_archive');"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_event_archive (id int PRIMARY KEY, data text);" -c "ALTER TABLE sql_event_archive SET TABLESPACE archive_hdd;" SET CREATE TABLE ALTER TABLE student@lab:~$ psql -U postgres -d beer_db -c "SELECT tablename, tablespace FROM pg_tables WHERE tablename IN ('sql_products_ts','sql_event_archive');" SET tablename | tablespace -------------------+------------- sql_products_ts | fast_ssd sql_event_archive | archive_hdd (2 rows)
🌐 Confirm Tablespaces Are Cluster-Global, Not Database-Specific
Switch to a completely different database and confirm fast_ssd is immediately visible and usable there too.
psql -U postgres -d postgres -c "SELECT spcname FROM pg_tablespace WHERE spcname = 'fast_ssd';"psql -U postgres -d postgres -c "CREATE TABLE other_db_table (id int) TABLESPACE fast_ssd;"student@lab:~$ psql -U postgres -d postgres -c "SELECT spcname FROM pg_tablespace WHERE spcname = 'fast_ssd';" SET spcname ---------- fast_ssd (1 row) student@lab:~$ psql -U postgres -d postgres -c "CREATE TABLE other_db_table (id int) TABLESPACE fast_ssd;" SET CREATE TABLE
🔗 Find the Real Symlink Backing a Tablespace
Check PGDATA's pg_tblspc directory directly to see the real symbolic link a tablespace creates on disk.
as-postgres sh -c 'ls -la /var/lib/postgresql/18/data/pg_tblspc/'student@lab:~$ as-postgres sh -c 'ls -la /var/lib/postgresql/18/data/pg_tblspc/' total 8 drwx------ 2 postgres postgres 4096 Jun 26 16:02 . drwx------ 20 postgres postgres 4096 Jun 26 16:02 .. lrwxrwxrwx 1 postgres postgres 9 Jun 26 16:02 19011 -> /tmp/fast lrwxrwxrwx 1 postgres postgres 9 Jun 26 16:02 19012 -> /tmp/slow
Lab 3.4.5 complete — Block 3.4 complete. Tablespaces and storage placement, proven directly:\n\n\n Two real tablespaces built : ✅ separate directories, real\n Table + index placed directly : ✅ fast_ssd, confirmed\n Existing table moved : ✅ archive_hdd, confirmed via pg_tables\n Cluster-global nature proven : ✅ used from a different database\n Real symlink found on disk : ✅ pg_tblspc/19011 -> /tmp/fast\n
Enable JavaScript to run the live terminal and track your progress.