> ## Documentation Index
> Fetch the complete documentation index at: https://docs.controlplane.com/llms.txt
> Use this file to discover all available pages before exploring further.

# TimescaleDB

> Deploy TimescaleDB — the PostgreSQL 18 time-series database — on Control Plane. Covers hypertables, compression, continuous aggregates, retention, PgBouncer pooling, and scheduled S3, GCS, or MinIO backups.

## Overview

TimescaleDB is a time-series database built as a PostgreSQL extension. This template deploys a single-instance PostgreSQL 18 server with the TimescaleDB Community extension preloaded and auto-created, giving you hypertables, columnar compression, continuous aggregates, and retention policies alongside regular relational tables — all through any PostgreSQL client or ORM. Optional PgBouncer connection pooling and scheduled backups to AWS S3, GCS, or a self-hosted MinIO instance are included.

<Info>
  TimescaleDB is licensed under the Timescale License (TSL). It is free to self-host, including all Community features (compression, continuous aggregates, retention); the license only forbids reselling TimescaleDB itself as a managed database service.
</Info>

<Note>
  TimescaleDB on Control Plane operates as a single-instance deployment, pinned to one replica. Do not scale up the replica count — PostgreSQL is a single-writer database, and additional replicas would run as isolated instances rather than a cluster.
</Note>

### What Gets Created

* **Stateful TimescaleDB Workload** — A single-replica PostgreSQL 18 + TimescaleDB 2.28.3 container on port `5432`. The extension is preloaded, created automatically in your database, and auto-tuned to the container's resources at first boot.
* **Volume Set** — Persistent storage for the database data directory, with optional autoscaling and 7-day snapshots.
* **Secret** — A dictionary secret storing the database username and password, injected into the container at startup.
* **Identity & Policy** — An identity bound to the workload with `reveal` access to the credentials secret, and scoped cloud storage access when backup is enabled.
* **PgBouncer Workload** *(optional)* — A PgBouncer connection pooler deployed as a separate workload in front of TimescaleDB.
* **Backup Cron Workload** *(optional)* — A scheduled `pg_dumpall` backup job that writes compressed SQL dumps to AWS S3, GCS, or MinIO.

<Note>
  This template does not create a GVC. You must deploy it into an existing GVC.
</Note>

## Installation

This template has no external prerequisites unless backup is enabled. To install, follow the instructions for your preferred method:

<CardGroup cols={2}>
  <Card title="UI" href="/template-catalog/install-manage/ui" icon="laptop">
    Browse, install, and manage templates visually
  </Card>

  <Card title="CLI" href="/template-catalog/install-manage/cli" icon="terminal">
    Manage templates from your terminal
  </Card>

  <Card title="Terraform" href="/template-catalog/install-manage/terraform" icon={<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 128 128"><g fill-rule="evenodd"><path d="M77.941 44.5v36.836L46.324 62.918V26.082zm0 0" fill="#5c4ee5"/><path d="M81.41 81.336l31.633-18.418V26.082L81.41 44.5zm0 0" fill="#4040b2"/><path d="M11.242 42.36L42.86 60.776V23.941L11.242 5.523zm0 0M77.941 85.375L46.324 66.957v36.82l31.617 18.418zm0 0" fill="#5c4ee5"/></g></svg>}>
    Declare templates in your Terraform configurations
  </Card>

  <Card
    title="Pulumi"
    href="/template-catalog/install-manage/pulumi"
    icon={<svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" id="Pulumi-Icon--Streamline-Svg-Logos" height="24" width="24">
    <desc>
        Pulumi Icon Streamline Icon: https://streamlinehq.com
    </desc>
    <path fill="#f26e7e" d="M4.683025 13.3318c0.869125 -0.5018 0.870575 -2.1264 0.003225 -3.62865s-2.27504 -2.313275 -3.1441725 -1.811475C0.672945 8.3935 0.6715 10.0181 1.53885 11.52035c0.86735 1.502275 2.27505 2.313275 3.144175 1.81145Zm0.0052 3.2167c0.86735 1.502275 0.865925 3.126875 -0.003225 3.628675 -0.86915 0.5018 -2.2768275 -0.309225 -3.144175 -1.81145 -0.8673525 -1.50225 -0.8659075 -3.126875 0.003225 -3.628675 0.8691325 -0.5018 2.276825 0.309225 3.144175 1.81145Zm5.922875 3.4243c0.86735 1.50225 0.8659 3.126775 -0.003225 3.62875 -0.869125 0.501775 -2.27685 -0.309325 -3.1442 -1.81155 -0.867325 -1.50225 -0.865875 -3.12685 0.00325 -3.628675 0.869125 -0.5018 2.276825 0.309225 3.144175 1.811475Zm-0.001925 -6.845275c0.86735 1.50225 0.8659 3.12685 -0.003225 3.628675 -0.869125 0.5018 -2.276825 -0.309225 -3.144175 -1.811475 -0.86735 -1.50225 -0.8659 -3.12685 0.003225 -3.62865 0.869125 -0.501825 2.276825 0.3092 3.144175 1.81145Z" stroke-width="0.25"></path>
    <path fill="#8a3391" d="M22.45775 11.524125c0.86725 -1.502225 0.865925 -3.12685 -0.003225 -3.62865 -0.869125 -0.501825 -2.276825 0.3092 -3.144175 1.811475 -0.86735 1.50225 -0.8659 3.126825 0.003225 3.62865 0.869125 0.501825 2.276825 -0.3092 3.144175 -1.811475Zm0.000175 3.2151c0.869075 0.5018 0.870625 2.1264 0.003225 3.62865 -0.86735 1.50225 -2.27505 2.313275 -3.144175 1.81145 -0.869125 -0.5018 -0.870575 -2.126425 -0.003225 -3.62865 0.86735 -1.50225 2.27505 -2.313275 3.144175 -1.81145ZM16.536225 18.157875c0.86915 0.501825 0.8706 2.126425 0.00325 3.628675 -0.86735 1.502125 -2.275075 2.313225 -3.1442 1.81145 -0.869125 -0.50175 -0.870575 -2.126425 -0.003225 -3.62865 0.867375 -1.502275 2.27505 -2.3133 3.144175 -1.811475Zm-0.003325 -6.843775c0.869125 0.5018 0.870575 2.126425 0.003225 3.628675s-2.27505 2.313275 -3.1442 1.811475c-0.869125 -0.501825 -0.870575 -2.126425 -0.003225 -3.628675 0.86735 -1.502275 2.27505 -2.313275 3.1442 -1.811475Z" stroke-width="0.25"></path>
    <path fill="#f7bf2a" d="M15.138225 2.06721c0 1.003615 -1.40625 1.817215 -3.14095 1.817215 -1.7347 0 -3.14095 -0.8136 -3.14095 -1.817215C8.856325 1.06359 10.262575 0.25 11.997275 0.25c1.7347 0 3.14095 0.81359 3.14095 1.81721ZM9.2166 5.482375c0 1.003625 -1.40625 1.8172 -3.14095 1.8172 -1.7347 0 -3.14095 -0.813575 -3.14095 -1.8172s1.40625 -1.817225 3.14095 -1.817225c1.7347 0 3.14095 0.8136 3.14095 1.817225Zm8.71005 1.8172c1.7347 0 3.14095 -0.813575 3.14095 -1.8172s-1.40625 -1.817225 -3.14095 -1.817225c-1.7347 0 -3.14095 0.8136 -3.14095 1.817225s1.40625 1.8172 3.14095 1.8172Zm-2.788425 1.605625c0 1.003625 -1.40625 1.8172 -3.14095 1.8172 -1.7347 0 -3.14095 -0.813575 -3.14095 -1.8172 0 -1.0036 1.40625 -1.8172 3.14095 -1.8172 1.7347 0 3.14095 0.8136 3.14095 1.8172Z" stroke-width="0.25"></path>
    </svg>}
  >
    Declare templates in your Pulumi programs
  </Card>
</CardGroup>

## Configuration

The default `values.yaml` for this template:

```yaml theme={null}
image: timescale/timescaledb:2.28.3-pg18 # PostgreSQL 18 + TimescaleDB Community edition

resources:
  minCpu: 200m
  minMemory: 512Mi
  maxCpu: 500m
  maxMemory: 1024Mi # timescaledb-tune sizes shared_buffers/workers from this at first boot

config:
  username: username
  password: password
  database: test # TimescaleDB extension is created automatically in this database

volumeset:
  capacity: 10 # initial capacity in GiB (minimum is 10)
  autoscaling:
    enabled: false # Set to true to enable autoscaling
    maxCapacity: 100 # Maximum capacity in GiB when autoscaling is enabled
    minFreePercentage: 10 # Minimum free percentage to trigger scaling when autoscaling is enabled
    scalingFactor: 1.2 # Scaling factor to determine how much to scale up when autoscaling is triggered

internalAccess: # Sets the internal firewall scope - if set to none, replicas will not be able to reach each other
  type: same-gvc # options: none, same-gvc, same-org, workload-list
  workloads:  # Note: can only be used if type is same-gvc or workload-list
    #- //gvc/GVC_NAME/workload/WORKLOAD_NAME

publicAccess:
  enabled: false # exposes 5432 via a TCP load balancer; connections are unencrypted — prefer internal access

pgbouncer:
  enabled: false
  image: edoburu/pgbouncer:v1.25.1-p0
  poolMode: transaction # options: session, transaction, statement
  defaultPoolSize: 25   # number of real Postgres connections PgBouncer maintains
  maxClientConn: 1000   # maximum number of client connections PgBouncer accepts
  replicas: 1

  resources:
    cpu: 200m
    memory: 128Mi

backup:
  enabled: false
  image: ghcr.io/controlplane-com/backup-images/postgres-backup:18.1.0 # PG18 client, matches server major
  schedule: "0 2 * * *"   # daily at 2am UTC

  resources:
    cpu: 100m
    memory: 128Mi

  provider: aws # Options: aws, gcp, or minio

  aws:
    bucket: my-backup-bucket
    region: us-east-1
    cloudAccountName: my-backup-cloudaccount
    policyName: my-backup-policy
    prefix: timescaledb/backups # folder name where your backups will be stored

  gcp:
    bucket: my-backup-bucket
    cloudAccountName: my-backup-cloudaccount
    prefix: timescaledb/backups # folder name where your backups will be stored

  minio: # Backup to a self-hosted MinIO workload (or any S3-compatible endpoint)
    endpoint: http://my-minio-workload:9000 # e.g. http://WORKLOAD_NAME:9000 for an internal MinIO template deployment
    bucket: my-backup-bucket
    accessKey: my-minio-username # matches the MinIO template's admin.username
    secretKey: my-minio-password # matches the MinIO template's admin.password
    prefix: timescaledb/backups # folder name where your backups will be stored
```

### Image and Resources

* `image` — The TimescaleDB image tag. Keep it in the default (Community) series; `-oss` tags remove compression, continuous aggregates, and retention.
* `resources.minCpu` / `resources.minMemory` — Minimum CPU and memory guaranteed to the workload.
* `resources.maxCpu` / `resources.maxMemory` — Maximum CPU and memory the workload can use. `timescaledb-tune` sizes `shared_buffers` and worker settings from `maxMemory` at first boot (for example, `shared_buffers` becomes \~25% of the memory limit).

<Note>
  Auto-tuning is captured only at first boot, when the data volume is empty. Raising `resources.maxMemory` on an existing deployment does not retune PostgreSQL — adjust settings manually with `ALTER SYSTEM`, or uninstall (which deletes the volume set) and reinstall.
</Note>

### Credentials

* `config.username` — Database username. **Change before deploying to production.**
* `config.password` — Database password. **Change before deploying to production.**
* `config.database` — Name of the database created on startup. The TimescaleDB extension is created automatically inside it.

<Note>
  These values are only applied on first startup when the data directory is empty. Updating them after the initial deployment has no effect on the running database. To change credentials or the database name on an existing instance, use PostgreSQL's native commands (e.g. `ALTER USER`, `ALTER DATABASE`).
</Note>

### Storage

* `volumeset.capacity` — Initial volume size in GiB (minimum 10).
* `volumeset.autoscaling.enabled` — Automatically expand the volume as it fills. When enabled:
  * `maxCapacity` — Maximum volume size in GiB.
  * `minFreePercentage` — Trigger a scale-up when free space drops below this percentage.
  * `scalingFactor` — Multiply the current capacity by this factor when scaling up.

### Internal Access

* `internalAccess.type` — Controls which workloads can connect to TimescaleDB on port `5432`:

| Type            | Description                                                     |
| --------------- | --------------------------------------------------------------- |
| `none`          | No internal access allowed                                      |
| `same-gvc`      | Allow access from all workloads in the same GVC                 |
| `same-org`      | Allow access from all workloads in the same organization        |
| `workload-list` | Allow access only from specific workloads listed in `workloads` |

* `internalAccess.workloads` — When `type` is `workload-list`, the list of workload links (e.g. `//gvc/GVC_NAME/workload/WORKLOAD_NAME`) allowed to connect.

### Public Access

* `publicAccess.enabled` — When `true`, exposes port `5432` through a TCP load balancer and assigns a public `*.cpln.app` canonical endpoint.

<Warning>
  Public access is unencrypted — the image ships no TLS certificates, so connections over the public endpoint are plaintext. Keep `publicAccess.enabled: false` and use internal access unless you accept plaintext connections.
</Warning>

### PgBouncer Connection Pooling

PgBouncer is an optional connection pooler that sits in front of TimescaleDB and multiplexes application connections into a smaller pool of real database connections. This reduces connection overhead and protects the database from exhaustion under high concurrency.

When enabled, PgBouncer is deployed as a separate workload and becomes the primary connection endpoint for your applications:

```text theme={null}
RELEASE_NAME-pgbouncer.GVC_NAME.cpln.local:5432
```

* `pgbouncer.enabled` — Enable or disable PgBouncer.
* `pgbouncer.poolMode` — Controls how connections are reused:

| Mode          | Description                                                                                                                                                                                                             |
| ------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `transaction` | Connection held only for the duration of a transaction, then returned to the pool. Best for most web and API workloads. Not compatible with session-level features (`SET` variables, temporary tables, advisory locks). |
| `session`     | Connection held for the entire client session. Compatible with all PostgreSQL features but provides less reuse.                                                                                                         |
| `statement`   | Connection returned after every statement. Transactions are not supported. Rarely used.                                                                                                                                 |

* `pgbouncer.defaultPoolSize` — Number of real database connections PgBouncer maintains per pool (default: `25`).
* `pgbouncer.maxClientConn` — Maximum number of client connections PgBouncer accepts (default: `1000`).
* `pgbouncer.replicas` — Number of PgBouncer instances. PgBouncer is stateless and can be scaled horizontally for high-throughput workloads.
* `pgbouncer.resources.cpu` / `pgbouncer.resources.memory` — Resources allocated to each PgBouncer replica.

<Note>
  PgBouncer shares the same credentials and identity as the TimescaleDB workload — no additional secrets or IAM configuration is required. The `userlist.txt` and `pgbouncer.ini` are generated automatically from your `config.username`, `config.password`, and `config.database` values at startup.
</Note>

### Backup

Backup is disabled by default. When enabled, a cron workload runs `pg_dumpall` on the configured schedule and uploads a compressed SQL dump to AWS S3, GCS, or a MinIO-compatible endpoint.

* `backup.enabled` — Enable scheduled backups.
* `backup.image` — The backup container image. Match its tag to the server major version — `18.1.0` for the default PostgreSQL 18 image, `17.1.0` for a PostgreSQL 17 image.
* `backup.schedule` — Cron expression for backup frequency (default: daily at 2am UTC).
* `backup.provider` — `aws`, `gcp`, or `minio`.
* `backup.resources.cpu` / `backup.resources.memory` — Resources for the backup cron container.

Complete the [backup prerequisites](#backup-prerequisites) for your provider before enabling backup.

## Connecting

| What                           | Value                                                                                                |
| ------------------------------ | ---------------------------------------------------------------------------------------------------- |
| Internal (same GVC)            | `RELEASE_NAME-timescaledb.GVC_NAME.cpln.local:5432`                                                  |
| Via PgBouncer *(when enabled)* | `RELEASE_NAME-pgbouncer.GVC_NAME.cpln.local:5432` — use this as your application endpoint            |
| Public *(when enabled)*        | The `status.canonicalEndpoint` of the `RELEASE_NAME-timescaledb` workload, port `5432` (unencrypted) |
| Credentials                    | `config.username` / `config.password`                                                                |

## Using TimescaleDB

Any PostgreSQL client or ORM works unchanged. Turn a regular table into a hypertable (automatically partitioned by time) and query it with time buckets:

```sql theme={null}
CREATE TABLE metrics (time timestamptz NOT NULL, device text, value double precision);
SELECT create_hypertable('metrics', by_range('time'));

INSERT INTO metrics VALUES (now(), 'sensor-1', 23.5);

SELECT time_bucket('1 hour', time) AS bucket, device, avg(value)
FROM metrics
GROUP BY bucket, device
ORDER BY bucket;
```

From here you can add columnar compression, continuous aggregates, and retention policies — all Community features included in the default image.

## Backup Prerequisites

### AWS S3

Before enabling backup with `provider: aws`, complete the following in your AWS account:

<Steps>
  <Step title="Create a bucket">
    Create an S3 bucket. Set `backup.aws.bucket` to its name and `backup.aws.region` to its region.
  </Step>

  <Step title="Set up a Cloud Account">
    If you do not have one, [create a Cloud Account](/guides/create-cloud-account) for your AWS account. Set `backup.aws.cloudAccountName` to its name.
  </Step>

  <Step title="Create an IAM policy">
    Create an IAM policy with the JSON below, replacing `YOUR_BUCKET_NAME`, then set `backup.aws.policyName` to the policy's name and `backup.aws.prefix` to the folder path for backups.
  </Step>
</Steps>

```json theme={null}
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:GetObjectVersion",
                "s3:PutObject",
                "s3:DeleteObject",
                "s3:DeleteObjectVersion",
                "s3:AbortMultipartUpload"
            ],
            "Resource": [
                "arn:aws:s3:::YOUR_BUCKET_NAME",
                "arn:aws:s3:::YOUR_BUCKET_NAME/*"
            ]
        }
    ]
}
```

### GCS

Before enabling backup with `provider: gcp`, complete the following in your GCP account:

<Steps>
  <Step title="Create a bucket">
    Create a GCS bucket. Set `backup.gcp.bucket` to its name.
  </Step>

  <Step title="Set up a Cloud Account">
    If you do not have one, [create a Cloud Account](/guides/create-cloud-account) for your GCP project. Set `backup.gcp.cloudAccountName` to its name — access is keyless, with no stored credentials.
  </Step>
</Steps>

<Warning>
  Grant the **Storage Admin** role (`roles/storage.objectAdmin` scoped to the bucket also works) to the GCP service account created for the Cloud Account. Set `backup.gcp.prefix` to the folder path for backups.
</Warning>

### MinIO

Before enabling backup with `provider: minio`, ensure your MinIO instance (or any S3-compatible endpoint) is accessible:

<Steps>
  <Step title="Create a bucket">
    Create a bucket on the server. Set `backup.minio.bucket` to its name.
  </Step>

  <Step title="Set the endpoint">
    Set `backup.minio.endpoint` to the S3 API address including the port. For the `minio` marketplace template deployed in the same GVC, use `http://WORKLOAD_NAME:9000`.
  </Step>

  <Step title="Set credentials">
    Set `backup.minio.accessKey` and `backup.minio.secretKey` to credentials with access to the bucket. For the MinIO template, these match the `admin.username` and `admin.password` values. Set `backup.minio.prefix` to the folder path for backups.
  </Step>
</Steps>

<Note>
  MinIO backup requires no Control Plane Cloud Account — credentials are passed directly to the backup job.
</Note>

## Restoring a Backup

Restoring a TimescaleDB dump is **not** the vanilla PostgreSQL procedure. The target server must run the **same TimescaleDB extension version** as the dump, and the replay must be wrapped in `timescaledb_pre_restore()` and `timescaledb_post_restore()`. Run the following from a client with access to the backup bucket and to a fresh database:

<Warning>
  Restore replays a full-cluster dump. Run it against a fresh instance, not a database that already holds data you want to keep.
</Warning>

**AWS S3:**

```sh theme={null}
export PGPASSWORD="PASSWORD"

psql -h RELEASE_NAME-timescaledb.GVC_NAME.cpln.local -U USERNAME -d DATABASE \
  -c "SELECT timescaledb_pre_restore();"

aws s3 cp "s3://BUCKET_NAME/PREFIX/BACKUP_FILE.sql.gz" - \
  | gunzip \
  | psql -h RELEASE_NAME-timescaledb.GVC_NAME.cpln.local -p 5432 -U USERNAME -d postgres

psql -h RELEASE_NAME-timescaledb.GVC_NAME.cpln.local -U USERNAME -d DATABASE \
  -c "SELECT timescaledb_post_restore();"

unset PGPASSWORD
```

For **GCS**, replace the download with `gsutil cp "gs://BUCKET_NAME/PREFIX/BACKUP_FILE.sql.gz" -`. For **MinIO**, add `--endpoint-url "http://MINIO_ENDPOINT:9000"` to the `aws s3 cp` command and export the MinIO access and secret keys as `AWS_ACCESS_KEY_ID` and `AWS_SECRET_ACCESS_KEY`.

## Important Notes

* **Change `config.password` before installing.** Credentials are written into the data directory at first boot; changing the value later does not change the database password.
* **Do not scale this workload.** It is single-writer PostgreSQL, pinned to one replica. Additional replicas would run as isolated instances, not a cluster.
* **Auto-tuning is captured at first boot only.** `timescaledb-tune` sizes memory settings from the container limit when the volume is empty; raising `resources.maxMemory` later does not retune.
* **Public access is unencrypted.** The image ships no TLS certificates — keep `publicAccess.enabled: false` unless you accept plaintext connections.
* **Keep the image tag in the default (Community) series.** `-oss` tags remove compression, continuous aggregates, and retention policies. If you pin a different `pgXX` server image, match `backup.image` to the same major (`17.1.0` for PostgreSQL 17).
* **Uninstall deletes the volume set.** A final snapshot is kept for 7 days; enable backups if the data matters long-term.

## External References

<CardGroup cols={2}>
  <Card title="TimescaleDB Documentation" icon="database" href="https://docs.timescale.com/">
    Official TimescaleDB documentation
  </Card>

  <Card title="Hypertables" icon="table" href="https://docs.timescale.com/use-timescale/latest/hypertables/">
    Hypertables, compression, continuous aggregates, and retention
  </Card>

  <Card title="PgBouncer Documentation" icon="database" href="https://www.pgbouncer.org/config.html">
    PgBouncer configuration reference
  </Card>

  <Card title="TimescaleDB Template" icon="github" href="https://github.com/controlplane-com/templates/tree/main/timescaledb">
    View the source files, default values, and chart definition
  </Card>
</CardGroup>