Does Timescale Cloud support having multiple databases? One cluster with multiple instances (one database per client for multi-tenancy)?
It does not, although you can use schemas for similar results. There are a number of operational considerations that led to this decision.
Each database installs extensions separately, so they can run different versions of TimescaleDB. All need to be updated independently. With multiple databases, automatically managing extensions and upgrades becomes much more difficult, and especially if you are working towards HA replication and/or multi-node.
Due to this versioning, backup management is also more complicated, as we need to match up extension versions (including TimescaleDB) if we need to restore from backups.
Provisioning multiple databases makes the UI presentation more confusing for users. We want to give you visibility into databases from the Timescale Cloud console, and eventually provide the ability to control the service via programmatic APIs (and even from services like Terraform).
However, users, roles, and settings/GUCs are sometimes at system level, sometimes at database level, and sometimes they “mask” lower levels when it comes to RBAC. That is, you can see something at the system level, then override it at database level. Often a user who’s not a Postgres expert doesn’t know if some configuration setting should be system or database level – frankly, the reasons are often very implementation-specific or historical and are not that obvious.
All of this significantly makes the system harder to understand or configure. We looked into various ways to design our Cloud UI around settings management, but were very unhappy with the solutions we came up with from a usability/understandability perspective.
This isn’t blaming Postgres! Configuring multiple databases makes total sense if designing for on-premise bare metal, where requesting and provisioning a new physical server from IT takes months. Given all the challenges above, it made less sense to us from a first principles perspective in the cloud era, when s new service is one click away.
So how do we support multiple clients as isolated services in Timescale Cloud without being able to provision multiple databases? We can’t just raise a cluster-per-client that would be prohibitive.
Yep, multi-tenancy is the common reason behind this question.
Typically, if you have an API in front through which all tenants requests go, then using a database schema for logical isolation works just fine. Your API can match tenant to schema and take care of the isolation.
If you are giving direct database access to your tenants, then I’m not sure that per tenant logical databases is the right solution anyway. While it gives security isolation, it doesn’t provide any resource isolation, so one tenant request could overwhelm the database. If you need resource isolation, we would then recommend running separate database services, right sized for each tenant.