April 18, 2024
Summary: in this tutorial, you will learn when tablespaces are useful in PostgreSQL.
Introduction
Tablespaces are designed to allow Postgres clusters to be spread across multiple storage devices. Create tablespace creates a symbolic link in the pg_tblspc directory in the cluster’s data directory pointing to the newly-created tablespace directory.
Using tablespaces makes database administration more complicated, because the data directory no longer contains all the data.
In the vast majority of cases, you shouldn’t create extra tablespaces in PostgreSQL. In particular, it never makes sense to create a tablespace on the same file system as the data directory or on the same file system as another tablespace.
So what are the benefits of tablespaces that justify the administrative complexity?
High throughput IOPS
If you have your data spread over several file systems on different devices, you can distribute the I/O load. However, this can be done just as well or better by striping on a lower level. If you use striping on the operating system level, you will get a better spread than you will by carefully placing tables and indexes.
If you want to access some indexes faster, you can move indexes or tables onto another filesystem that is faster/has more provisioned IOPS to speed up important queries. Be sure to adjust the tablespace options to tell the query planner how fast the new tablespace is!
Database growth
If for whatever reasons you can’t grow the filesystem that your database lives in, you can create new tablespaces in other mounted filesystems, and move existing objects and/or use the new tablespace as future default for objects.
If you run out of space on a disk, a tablespace will give you the option to add more storage. However, today you typically use a volume manager of some description to do this on the operating system level.
Storage quota
If you want to put a size limit on a database or table, you can put it in a tablespace on a file system with limited size.
If you encounter an error message like following, it means that your database is running out of disk space.
ERROR: could not extend file "base/16384/61892": No space left on device
HINT: Check free disk space
Saving capital cost
If you have both fast, expensive and slow, cheap storage, you can put the objects that need good performance in a separate tablespace on fast storage. You can also put some historical data that rarely accessed in a tablespace on slow storage.
In that case, you should adjust the seq_page_cost, random_page_cost and effective_io_concurrency options on the tablespace to tell the optimizer about the performance characteristics.
Utilizing file system’s features
Have a table with very compressible data? Try moving it to a tablespace on a ZFS filesystem that has compression enabled. ZFS filesystem also supports simplified volume management with dynamic resizing and RAID features.
Temporary files
Temporary tables and indexes are created by PostgreSQL either when explicitly asked to (“CREATE TEMP TABLE..”) or when it needs to hold large datasets temporarily for completing a query.
It is possible to tell PostgreSQL to place such objects in a separate tablespace. For example, if too many temporary tables are created in normal course, it might be possible to speed up your queries by placing such objects in a tablespace with faster hardware, faster/unjournaled/uncompressed filesystems, or even in-memory filesystems.
If you want temporary files (both for temporary tables and for query processing) to be created somewhere else than in the default tablespace, you can set the temp_tablespaces parameter to a different tablespace.
Summary
If you are running in a virtualized environment with virtualized storage, all these points are moot, with the exception as storage quota. Since almost everybody uses virtualization these days, tablespaces are becoming an increasingly irrelevant PostgreSQL feature.
Tablespaces are rarely relevant in PostgreSQL. Resist the temptation to create tablespaces and leave all data in the default tablespace.