Using PostgreSQL tablespaces with Percona Operator for PostgreSQL¶

Tablespaces allow DBAs to store a database on multiple file systems within the same server and to control where (on which file systems) specific parts of the database are stored. You can think about it as if you were giving names to your disk mounts and then using those names as additional parameters when creating database objects.

PostgreSQL supports this feature, allowing you to store data outside of the primary data directory, and Percona Operator for PostgreSQL is a good option to bring this to your Kubernetes environment when needed.

Possible use cases¶

The most obvious use case for tablespaces is performance optimization. You place appropriate parts of the database on fast but expensive storage and engage slower but cheaper storage for lesser-used database objects. The classic example would be using an SSD for heavily-used indexes and using a large slow HDD for archive data.

Of course, the Operator already provides you with traditional Kubernetes approaches to achieve this on a per-Pod basis (Tolerations, etc.). But if you would like to go deeper and make such differentiation at the level of your database objects (tables and indexes), tablespaces are exactly what you would need for that.

Another well-known use case for tablespaces is quickly adding a new partition to the database cluster when you run out of space on the initially used one and cannot extend it (which may look less typical for cloud storage). Finally, you may need tablespaces when migrating your existing architecture to the cloud.

Each tablespace created by Percona Operator for PostgreSQL corresponds to a separate Persistent Volume, mounted in a container to the /tablespaces directory.

Creating a new tablespace¶

Providing a new tablespace for your database in Kubernetes involves two parts:

Configure the new tablespace storage with the Operator,
Create database objects in this tablespace with PostgreSQL.

The first part is done in the traditional way of Percona Operators, by modifying Custom Resource via the deploy/cr.yaml configuration file. It has a special spec.tablespaceStorages section with subsections names equal to PostgreSQL tablespace names.

The example already present in deploy/cr.yaml shows how to create tablespace storage named lake 1Gb in size with dynamic provisioning (you can see official Kubernetes documentation on Persistent Volumes for details):

spec:
...
  tablespaceStorages:
    lake:
      volumeSpec:
        size: 1G
        accessmode: ReadWriteOnce
        storagetype: dynamic
        storageclass: ""
        matchLabels: ""

After you apply this by running the kubectl apply -f deploy/cr.yaml command, the new lake tablespace will appear within your database. Please take into account that if you add your new tablespace to the already existing PostgreSQL cluster, it may take time for the Operator to create Persistent Volume Claims and get Persistent Volumes actually mounted.

Now you can append TABLESPACE <tablespace_name> to your CREATE SQL statements to implicitly create tables, indexes, or even entire databases in specific tablespaces (of course, your user should have appropriate CREATE privileges to make it possible).

Let’s create an example table in the already mentioned lake tablespace:

CREATE TABLE products (
    product_sku character(10),
    quantity int,
    manufactured_date timestamptz)
TABLESPACE lake;

It is also possible to set a default tablespace with the SET default_tablespace = <tablespace_name>; statement. It will affect all further CREATE TABLE and CREATE INDEX commands without an explicit tablespace specifier, until you unset it with an empty string.

As you can see, Percona Operator for PostgreSQL simplifies tablespace creation by carrying on all necessary modifications with Persistent Volumes and Pods. The same would not be true for the deletion of an already existing tablespace, which is not automated, neither by the Operator nor by PostgreSQL.

Deleting an existing tablespace¶

Deleting an existing tablespace from your database in Kubernetes also involves two parts:

Delete related database objects and tablespace with PostgreSQL,
Delete tablespace storage in Kubernetes.

To make tablespace deletion with PostgreSQL possible, you should make this tablespace empty (it is impossible to drop a tablespace until all objects in all databases using this tablespace have been removed). Tablespaces are listed in the pg_tablespace table, and you can use it to find out which objects are stored in a specific tablespace. The example command for the lake tablespace will look as follows:

SELECT relname FROM pg_class WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='lake');

When your tablespace is empty, you can log in to the PostgreSQL Primary instance as a superuser, and then execute the DROP TABLESPACE <tablespace_name>; command.

Now, when the PostgreSQL part is finished, you can remove the tablespace entry from the tablespaceStorages section (don’t forget to run the kubectl apply -f deploy/cr.yaml command to apply changes).

However, Persistent Volumes will still be mounted to the /tablespaces directory in PostgreSQL Pods. To remove these mounts, you should edit all Deployment objects for pgPrimary and pgReplica instances in your Kubernetes cluster and remove the Volume and VolumeMount entries related to your tablespace.

You can see the list of Deployment objects with the kubectl get deploy command. Running it for a default cluster named cluster1 results in the following output:

NAME                            READY   UP-TO-DATE   AVAILABLE   AGE
cluster1                        1/1     1            1           156m
cluster1-backrest-shared-repo   1/1     1            1           156m
cluster1-pgbouncer              3/3     3            3           154m
cluster1-repl1                  1/1     1            1           154m
cluster1-repl2                  1/1     1            1           154m
postgres-operator               1/1     1            1           157m

Now run kubectl edit deploy <oblect_name> for cluster1, cluster1-repl1, and cluster1-repl2 objects consequently. Each command will open a text editor, where you should remove the appropriate lines, which in case of the lake tablespace will look as follows:

...
spec:
    ...
    containers:
      - name: database
        ...
        volumeMounts:
          - name: tablespace-lake
            mountPath: /tablespaces/lake
    volumes:
      ...
      - name: tablespace-lake
        persistentVolumeClaim:
          claimName: cluster1-tablespace-lake
      ...

Finishing the edit causes Pods to be recreated without tablespace mounts.

Last update: 2024-05-02