In this lesson, you will learn how to strategically map database objects to different physical storage hardware to optimize performance and cost.
Moving Beyond the Default
By default, PostgreSQL stores all your table data in a single location defined during the cluster's initialization. However, not all data is created equal. Some tables are "hot"—accessed constantly and requiring lightning-fast NVMe storage—while others are "cold," such as historical logs that are rarely queried and can reside on cheaper, slower HDDs.
Tablespaces are the mechanism that allows you to decouple your logical database structure from the physical file system. By defining a tablespace, you instruct PostgreSQL to store specific data files in a directory of your choosing rather than the default cluster directory.
Implementing Storage Tiers
To deploy tablespaces, you first ensure the operating system has the desired storage mounted. Once the directory exists, you create the tablespace within PostgreSQL:
-- Create a tablespace pointing to a high-speed SSD mount
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd_data/pg_tables';
-- Create a tablespace for long-term, low-priority storage
CREATE TABLESPACE slow_hdd LOCATION '/mnt/hdd_archive/pg_tables';
Once defined, you can assign tables or indexes to these tiers. If you have a massive table, you might place it in the fast_ssd tablespace to ensure low latency during scans, while keeping infrequently used audit logs in slow_hdd.
-- Move a table to the high-performance tier
ALTER TABLE user_activity SET TABLESPACE fast_ssd;
-- Create an index specifically on the slower storage
CREATE INDEX idx_logs_date ON audit_logs(created_at) TABLESPACE slow_hdd;
Why This Matters
By managing tablespaces, you align your database’s physical architecture with your infrastructure budget. You don’t need to pay for top-tier flash storage for every byte of data you own. Instead, you isolate high-throughput tables to prevent them from contending for I/O resources with background archival tasks. This segregation keeps your most critical queries running smoothly while managing the total cost of ownership for your storage stack.
Key Takeaway: Tablespaces allow you to physically isolate database objects onto different storage media, enabling performance tuning and cost optimization through tiered storage strategies.