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
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
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,
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
statements to implicitly create tables, indexes, or even entire databases in
specific tablespaces (of course, your user should have appropriate
privileges to make it possible).
Let’s create an example table in the already mentioned
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
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
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
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
directory in PostgreSQL Pods. To remove these mounts, you should edit
all Deployment objects for
pgReplica instances in your
Kubernetes cluster and remove the
VolumeMount entries related
to your tablespace.
You can see the list of Deployment objects with the kubectl
command. Running it for a default cluster named
cluster1 results in the
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
kubectl edit deploy <oblect_name> for
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.
For free technical help, visit the Percona Community Forum.
To report bugs or submit feature requests, open a JIRA ticket.
For paid support and managed or consulting services , contact Percona Sales.