One of the many reasons "the answer is Postgres" is due to its extensibility.
The ability to extend Postgres has given rise to an ecosystem of Postgres extensions that change the behavior of the database to support a wide range of interesting capabilities. At Crunchy Data we are big fans of PostGIS, the geospatial extender for Postgres.
Another extension we are asked about often is TimescaleDB.
TimescaleDB is an open-source extension designed to make SQL scalable for time-series data. Timescale, Inc., the company behind TimescaleDB, provides an Apache 2 edition of TimescaleDB that is packaged as a Postgres extension that provides automated partitioning across time and space.
We are often asked about the potential to deploy the Apache 2 edition of TimescaleDB as an extension within our Crunchy PostgreSQL for Kubernetes using PGO, the open source Postgres Operator. We announced that we added the Apache 2 edition of TimescaleDB to PGO 4.7, and we have brought TimescaleDB into PGO v5.
Let us look at how you can deploy the TimescaleDB extension as part of an HA Postgres cluster native to Kubernetes using the PGO Postgres Operator.
Deploying TimescaleDB on Kubernetes with PGO
For brevity, we will assume that you have already deployed PGO, the Postgres Operator from Crunchy Data in your Kubernetes environment. Read more on how you can quickly get started with PGO:
Since TimescaleDB is packaged in the PostgreSQL container, you can customize your Postgres configuration to get TimescaleDB up and running. We can do this before starting up the Postgres container by creating a manifest that looks similar to this:
apiVersion: postgres-operator.crunchydata.com/v1beta1 kind: PostgresCluster metadata: name: hippo spec: image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-13.5-0 postgresVersion: 13 instances: - name: instance1 dataVolumeClaimSpec: accessModes: - 'ReadWriteOnce' resources: requests: storage: 1Gi backups: pgbackrest: image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.36-0 repos: - name: repo1 volume: volumeClaimSpec: accessModes: - 'ReadWriteOnce' resources: requests: storage: 1Gi patroni: dynamicConfiguration: postgresql: parameters: shared_preload_libraries: timescaledb
The above loads the TimescaleDB shared library along with the other standard
shared libraries loaded with PGO. You can apply the manifest with
Once the cluster is initialized,
connect to the Postgres cluster
as a superuser.
You may want to create your own account that can be a superuser, or configure access to the
For the purposes of this exercise, we can connect to the database using
kubectl exec -it -n postgres-operator -c database \ $(kubectl get pods -n postgres-operator --selector='postgres-operator.crunchydata.com/cluster=hippo,postgres-operator.crunchydata.com/role=master' -o name) \ -- psql
First, confirm that the TimescaleDB library was loaded:
SHOW shared_preload_libraries ;
should yield something similar to:
shared_preload_libraries ----------------------------- pgaudit,pgaudit,timescaledb
This setup has the added benefit that the TimescaleDB library will be loaded on any replicas added to this Postgres cluster.
Now, add the TimescaleDB extension to the database:
CREATE EXTENSION timescaledb;
To test that TimescaleDB is successfully installed, go ahead and create a "hypertable" and start inserting data:
CREATE TABLE hippos ( observed_at timestamptz NOT NULL, total int NOT NULL ); SELECT create_hypertable('hippos', 'observed_at'); INSERT INTO hippos SELECT ts, (random() * 100)::int FROM generate_series(CURRENT_TIMESTAMP - '1 year'::interval, CURRENT_TIMESTAMP, '1 minute'::interval) ts;
This is just a small data set so you can observe that the TimescaleDB set up
works. Try using a smaller increment (e.g.
'5 seconds'::interval) to generate
a larger data set!
At Crunchy Data, we want to provide users with the benefits of trusted open source Postgres, including the powerful ecosystem of Postgres extensions. TimescaleDB provides interesting functionality for managing time series data in Postgres. By enabling the easy deployment of TimeScaleDB through PGO, you can easily deploy TimescaleDB to manage your time series data in Postgres natively in Kubernetes.
Jonathan S. Katz
November 23, 2021 •More by this author