A Tablespace is a PostgreSQL feature that is used to store data on a volume that is different from the primary data directory. While most workloads do not require them, tablespaces can be particularly helpful for larger data sets or utilizing particular hardware to optimize performance on a particular PostgreSQL object (a table, index, etc.). Some examples of use cases for tablespaces include:
- Partitioning larger data sets across different volumes
- Putting data onto archival systems
- Utilizing hardware (or a storage class) for a particular database
- Storing sensitive data on a volume that supports transparent data-encryption (TDE)
In order to use PostgreSQL tablespaces properly in a highly-available, distributed system, there are several considerations that need to be accounted for to ensure proper operations:
- Each tablespace must have its own volume; this means that every tablespace for every replica in a system must have its own volume.
- The filesystem map must be consistent across the cluster
- The backup & disaster recovery management system must be able to safely backup and restore data to tablespaces
Additionally, a tablespace is a critical piece of a PostgreSQL instance: if PostgreSQL expects a tablespace to exist and it is unavailable, this could trigger a downtime scenario.
While there are certain challenges with creating a PostgreSQL cluster with high-availability along with tablespaces in a Kubernetes-based environment, the PostgreSQL Operator adds many conveniences to make it easier to use tablespaces in applications.
How Tablespaces Work in the PostgreSQL Operator
As stated above, it is important to ensure that every tablespace created has its own volume (i.e. its own persistent volume claim). This is especially true for any replicas in a cluster: you don’t want multiple PostgreSQL instances writing to the same volume, as this is a recipe for disaster!
One of the keys to working with tablespaces in a high-availability cluster is to
ensure the filesystem that the tablespaces map to is consistent. Specifically,
it is imperative to have the
LOCATION parameter that is used by PostgreSQL to
indicate where a tablespace resides to match in each instance in a cluster.
The PostgreSQL Operator achieves this by mounting all of its tablespaces to a
/tablespaces in the container. While each tablespace will
exist in a unique PVC across all PostgreSQL instances in a cluster, each
instance’s tablespaces will mount in a predictable way in
The PostgreSQL Operator takes this one step further and abstracts this away from you. When your PostgreSQL cluster initialized, the tablespace definition is automatically created in PostgreSQL; you can start using it immediately! An example of this is demonstrated in the next section.
The PostgreSQL Operator ensures the availability of the tablespaces across the different lifecycle events that occur on a PostgreSQL cluster, including:
- High-Availability: Data in the tablespaces is replicated across the cluster, and is available after a downtime event
- Disaster Recovery: Tablespaces are backed up and are properly restored during a recovery
- Clone: Tablespaces are created in any cloned cluster
- Deprovisioining: Tablespaces are deleted when a PostgreSQL instance or cluster is deleted
Adding Tablespaces to a New Cluster
Tablespaces can be used in a cluster with the
pgo create cluster
command. The command follows this general format:
pgo create cluster hacluster \ --tablespace=name=tablespace1:storageconfig=storageconfigname \ --tablespace=name=tablespace2:storageconfig=storageconfigname
For example, to create tablespaces name
PVCs that use the
nfsstorage storage type, you would execute the following
pgo create cluster hacluster \ --tablespace=name=faststorage1:storageconfig=nfsstorage \ --tablespace=name=faststorage2:storageconfig=nfsstorage
Once the cluster is initialized, you can immediately interface with the
tablespaces! For example, if you wanted to create a table called
faststorage1 tablespace, you could execute the following SQL:
CREATE TABLE sensor_data ( sensor_id int, sensor_value numeric, created_at timestamptz DEFAULT CURRENT_TIMESTAMP ) TABLESPACE faststorage1;
Adding Tablespaces to Existing Clusters
You can also add a tablespace to an existing PostgreSQL cluster with the
pgo update cluster command.
Adding a tablespace to a cluster uses a similar syntax to creating a cluster
with tablespaces, for example:
pgo update cluster hacluster \ --tablespace=name=tablespace3:storageconfig=storageconfigname
NOTE: This operation can cause downtime. In order to add a tablespace to a PostgreSQL cluster, persistent volume claims (PVCs) need to be created and mounted to each PostgreSQL instance in the cluster. The act of mounting a new PVC to a Kubernetes Deployment causes the Pods in the deployment to restart.
When the operation completes, the tablespace will be set up and accessible to use within the PostgreSQL cluster.
For more information on how tablespaces work in PostgreSQL please refer to the PostgreSQL manual.