PostgreSQL Multi-Tenant Telemetry: Database per Tenant

Scope: one platform, many customers' buildings

This page is for teams running a multi-customer telemetry platform on PostgreSQL: a BMS analytics or energy-monitoring service, a CAFM or CMMS vendor that stores sensor readings for each client estate, an IoT platform hosting devices for many organisations. A tenant is a customer whose data must never be visible to another customer. The question is how to separate tenants inside PostgreSQL, and what each choice costs to operate.

The readings model is the one in how to store IoT sensor data; partitioning, retention and storage sizing work the same way under every model here and are not repeated. Hosting choices for PostgreSQL itself are covered in the cloud databases section.

Three models compared

Database per tenant

The PostgreSQL documentation recommends separate databases when a cluster holds unrelated projects or users that should be, for the most part, unaware of each other. That describes separate customers well. When a client connects, it names one database, and it is not possible to access more than one database per connection, so a query cannot accidentally join across tenants. Two caveats from the same page: by default all users can connect to all databases, so revoke CONNECT from PUBLIC; and the databases share one WAL, which constrains backup and recovery options.

-- tenant_template holds the current schema: point, reading (partitioned), roll-ups, functions
CREATE DATABASE tenant_harbour TEMPLATE tenant_template;
REVOKE CONNECT ON DATABASE tenant_harbour FROM PUBLIC;
CREATE ROLE harbour_app LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE tenant_harbour TO harbour_app;

The operational costs

• Connections. Each server connection belongs to one database, so an ingest service and a dashboard API that serve 200 tenants need connections into 200 databases. PostgreSQL's max_connections is typically 100 by default, and the server sizes shared memory and other resources from it, so raising it is not free. PgBouncer keeps a pool per user and database pair (pool_size is the maximum per pair), which bounds connections per tenant but still multiplies them by the number of tenants.
• Templates. CREATE DATABASE clones template1 by default, or a named template. A tenant template keeps new tenants consistent, but existing tenants still need migrations. Concurrency matters here: the documentation states that CREATE DATABASE fails if any other connection to the template exists when it starts, so provision tenants one at a time from a job that is the only client of the template (not run here).
• Migrations. A schema change becomes a loop over databases. Record the schema version in each database, make migrations re-runnable, and alert on tenants left behind.
• Backup and restore. pg_dump dumps a single database, which is exactly the per-tenant export or restore a customer contract may ask for. Physical backups and point-in-time recovery cover the whole cluster.
• Scaling out. Because tenants are already separate databases, moving a large tenant to its own cluster is a dump and restore (or logical replication) of one database, with no data to split.

Schema per tenant

Each tenant gets identical tables in its own schema inside one database. The PostgreSQL documentation is explicit that schemas, unlike databases, are not rigidly separated: a user can access objects in any schema of the connected database if privileges allow. Isolation therefore rests on privileges (one role per tenant, USAGE only on its own schema) and on every query resolving to the right schema.

CREATE SCHEMA tenant_harbour;
CREATE SCHEMA tenant_riverside;
CREATE TABLE tenant_harbour.reading   (point_id integer, ts timestamptz, value double precision,
                                       PRIMARY KEY (point_id, ts));
CREATE TABLE tenant_riverside.reading (point_id integer, ts timestamptz, value double precision,
                                       PRIMARY KEY (point_id, ts));
INSERT INTO tenant_harbour.reading VALUES (101, '2026-10-27 09:00+00', 13.2);

SET search_path TO tenant_riverside;
SELECT count(*) AS riverside_rows FROM reading;
--  riverside_rows
-- ----------------
--               0

SET search_path TO tenant_harbour;
SELECT count(*) AS harbour_rows FROM reading;
--  harbour_rows
-- --------------
--             1

Pitfalls

• Pooling. A session-level SET search_path is listed by PgBouncer as never compatible with transaction pooling: the next client on that server connection would inherit it. Schema-qualify names in the application, give each tenant its own role, or use session pooling; setting the path per transaction (SET LOCAL) carries the same verification caveat as the tenant setting described under row-level security below.
• search_path security. The documentation warns that adding a schema to search_path effectively trusts every user with CREATE on it. Do not grant tenant roles CREATE on shared schemas, and keep public locked down.
• Scale of the catalogue. Telemetry tables are usually partitioned by time, so tenants x tables x partitions grows quickly in one catalogue. Planning and maintenance work grows with it; keep partition counts per tenant modest.

Shared tables with tenant_id and row-level security

All tenants share one set of tables, every row carries tenant_id, and a row security policy limits each query to the current tenant. When RLS is enabled and no policy grants access, PostgreSQL applies default deny: no rows are visible. A policy's USING expression filters what can be read; its WITH CHECK expression rejects writes for another tenant with an error.

The application role must not be a superuser, must not have BYPASSRLS, and should not own the table (owners bypass RLS unless the table uses FORCE ROW LEVEL SECURITY). In the example below the tenant is passed as a custom setting scoped to the transaction with set_config(..., true). Everything below ran in a single PGlite session; behaviour under concurrent connections could not be checked.

Security warning: not recommended for production until verified with your actual PgBouncer transaction pooling. The example shows how the policy works; it does not show that tenant context held in session state is safe behind a transaction-pooling proxy, and that was not verified here. The reasons:

• Session state outlives the request. In transaction pooling, PgBouncer gives a server connection to a client only for one transaction, then to the next client. A custom setting is session state of that server connection. The PostgreSQL documentation says a committed SET persists until the end of the session unless overridden by another SET; PgBouncer lists SET/RESET as never compatible with transaction pooling.
• No reset between clients. PgBouncer's documentation states that in transaction pooling server_reset_query (default DISCARD ALL) is not used, because clients must not use session-based features. So nothing cleans a value that one client left behind. server_reset_query_always exists, but the documentation describes it as a workaround for broken setups.
• set_config(..., true) scope is narrow and easy to get wrong. With is_local true the value applies only during the current transaction. Outside an explicit BEGIN ... COMMIT it is gone by the next statement (silent zero rows, shown below). With is_local false, or a plain SET, it stays on the server connection.
• A missed SET either fails closed or leaks. If a request forgets to set its tenant and the connection carries no value, the policy below returns zero rows or raises an error (fail closed, shown below). If the connection still carries a value from an earlier client's session-level SET, the request silently runs as that earlier tenant (a cross-tenant leak, simulated below). A policy cannot tell a stale value from a fresh one, so a fail-closed policy does not protect against this case.

Before relying on this approach, verify with the PgBouncer version, pool mode and driver you will run: that every code path sets the tenant inside an explicit transaction, that nothing issues a session-level SET or set_config(..., false) for the tenant key, and what a request sees on a connection previously used by another tenant. Until then, prefer one of the safer alternatives below.

CREATE TABLE tenant (tenant_id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE reading (
    tenant_id integer NOT NULL REFERENCES tenant (tenant_id),
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision NOT NULL,
    PRIMARY KEY (tenant_id, point_id, ts)     -- tenant first: every query filters on it
);
INSERT INTO tenant VALUES (1, 'Harbour Tower'), (2, 'Riverside Campus');
INSERT INTO reading VALUES
    (1, 101, '2026-10-27 09:00+00', 13.2),
    (1, 101, '2026-10-27 09:01+00', 13.4),
    (2, 201, '2026-10-27 09:00+00', 21.0);

ALTER TABLE reading ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON reading
    USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer);

CREATE ROLE telemetry_app NOLOGIN;
GRANT SELECT, INSERT ON reading TO telemetry_app;

SET ROLE telemetry_app;

SELECT count(*) AS rows_without_tenant FROM reading;
--  rows_without_tenant
-- ---------------------
--                    0

BEGIN;
SELECT set_config('app.tenant_id', '1', true);
SELECT tenant_id, point_id, ts, value FROM reading ORDER BY point_id, ts;
--  tenant_id | point_id |           ts           | value
-- -----------+----------+------------------------+-------
--          1 |      101 | 2026-10-27 09:00:00+00 |  13.2
--          1 |      101 | 2026-10-27 09:01:00+00 |  13.4

INSERT INTO reading VALUES (2, 201, '2026-10-27 09:01+00', 21.1);
-- ERROR:  new row violates row-level security policy for table "reading"
-- SQLSTATE 42501
ROLLBACK;

BEGIN;
SELECT set_config('app.tenant_id', '2', true);
INSERT INTO reading VALUES (2, 201, '2026-10-27 09:01+00', 21.1);
SELECT tenant_id, point_id, ts, value FROM reading ORDER BY point_id, ts;
--  tenant_id | point_id |           ts           | value
-- -----------+----------+------------------------+-------
--          2 |      201 | 2026-10-27 09:00:00+00 |    21
--          2 |      201 | 2026-10-27 09:01:00+00 |  21.1
COMMIT;

The empty-string pitfall

Why NULLIF(..., '')? In the PGlite run (PostgreSQL 18.6), after a transaction that set app.tenant_id locally ended, the setting did not go back to missing: in the same session it read as an empty string. The PostgreSQL documentation we checked does not describe this case. It says current_setting(name, true) returns NULL when there is no such setting, and that unknown two-part names are kept as placeholders; the empty string is what we observed, so treat the NULLIF guard as a defensive measure and check it on your version. A policy that casts the value straight to integer then raises an error on the next query instead of returning no rows. The run showed both behaviours:

SELECT current_setting('app.tenant_id', true) = '' AS empty_string_after_commit;
--  empty_string_after_commit
-- ---------------------------
--  t

SELECT current_setting('app.tenant_id', true)::integer;
-- ERROR:  invalid input syntax for type integer: ""
-- SQLSTATE 22P02

SELECT count(*) AS rows_after_commit FROM reading;   -- the NULLIF policy fails closed
--  rows_after_commit
-- -------------------
--                  0

RESET ROLE;   -- back to the superuser that created the tables
SELECT count(*) AS rows_as_superuser FROM reading;   -- superusers bypass RLS
--  rows_as_superuser
-- -------------------
--                  4

SELECT current_setting('app.tenant_id', true) IS NULL AS missing_before_any_set;
--  missing_before_any_set
-- ------------------------
--  t

SELECT set_config('app.tenant_id', '1', true);      -- autocommit: its own transaction
SELECT count(*) AS rows_next_statement FROM reading;
--  rows_next_statement
-- ---------------------
--                    0

SELECT current_setting('app.tenant_id', true) = '' AS empty_after_autocommit;
--  empty_after_autocommit
-- ------------------------
--  t

Fail closed: a missing, empty or invalid tenant never shows all rows

Whatever carries the tenant, write the policy so that "no tenant" means "no rows", never "all rows". The policy above compares tenant_id with NULLIF(current_setting('app.tenant_id', true), '')::integer: a missing setting gives NULL, an empty one becomes NULL, and tenant_id = NULL is never true, so no row is visible; WITH CHECK turns the same NULL into an error, which the documentation states for false or null results. A value that is not an integer makes the cast fail, so the query errors instead of running. The run below, in a fresh session as telemetry_app with the same tables and policy, shows each case, then a policy written the wrong way round for contrast.

SELECT current_setting('app.tenant_id', true) IS NULL AS setting_missing;
--  setting_missing
-- -----------------
--  t

SELECT count(*) AS rows_setting_missing FROM reading;
--  rows_setting_missing
-- ----------------------
--                     0

INSERT INTO reading VALUES (1, 101, '2026-10-27 09:02+00', 13.5);
-- ERROR:  new row violates row-level security policy for table "reading"
-- SQLSTATE 42501

BEGIN;
SELECT set_config('app.tenant_id', '', true);
SELECT count(*) AS rows_setting_empty FROM reading;
--  rows_setting_empty
-- --------------------
--                   0
ROLLBACK;

BEGIN;
SELECT set_config('app.tenant_id', 'harbour', true);
SELECT count(*) AS rows_setting_invalid FROM reading;
-- ERROR:  invalid input syntax for type integer: "harbour"
-- SQLSTATE 22P02
ROLLBACK;

-- "no tenant set" is treated as "all tenants"
CREATE POLICY fail_open ON reading
    USING (current_setting('app.tenant_id', true) IS NULL
           OR tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer);

SET ROLE telemetry_app;
SELECT current_setting('app.tenant_id', true) IS NULL AS setting_missing;
--  setting_missing
-- -----------------
--  t

SELECT count(*) AS rows_visible FROM reading;   -- every tenant's rows
--  rows_visible
-- --------------
--             3

How a stale session value leaks to the next client (single-session simulation)

Failing closed covers a missing value, not a stale one. The run below simulates two clients that a transaction pool gave the same server connection, as successive statements in one PGlite session (no PgBouncer was run). Client A sets the tenant at session level by mistake. Client B forgets to set its tenant and sees tenant 1's rows. When B does set tenant 2 locally, the stale session value returns as soon as B commits, as the PostgreSQL SET documentation describes.

-- client A: session-level, not transaction-local (is_local = false)
SELECT set_config('app.tenant_id', '1', false);

-- client B, same server connection, forgets to set its tenant
SELECT count(*) AS rows_seen_by_next_client FROM reading;
--  rows_seen_by_next_client
-- --------------------------
--                         2      <- tenant 1's rows

-- client B sets its own tenant correctly, inside a transaction
BEGIN;
SELECT set_config('app.tenant_id', '2', true);
SELECT count(*) AS rows_tenant_2 FROM reading;
--  rows_tenant_2
-- ---------------
--              1
COMMIT;

SELECT current_setting('app.tenant_id', true) AS value_after_commit;
--  value_after_commit
-- --------------------
--  1                             <- the stale value is back

-- what PgBouncer's default reset query would do (it is not run in transaction pooling)
RESET ROLE;
DISCARD ALL;
SET ROLE telemetry_app;
SELECT current_setting('app.tenant_id', true) = '' AS empty_after_discard_all;
--  empty_after_discard_all
-- -------------------------
--  t

SELECT count(*) AS rows_after_discard_all FROM reading;
--  rows_after_discard_all
-- ------------------------
--                       0

Further RLS details that matter

• Indexes. Lead every primary key and index with tenant_id; the policy adds a tenant_id = ... condition to every query.
• Constraints leak existence. The documentation notes that unique, primary key and foreign key checks bypass row security, and warns of covert channels. With tenant_id inside every unique key, one tenant cannot detect another tenant's keys; a global unique column (a device serial number unique across all tenants) would let a tenant probe for values it cannot see.
• Keep policies simple. The documentation recommends policies that look only at the current row; sub-queries to other tables in a policy can create race conditions that leak information.
• Admin and ingest roles. A platform ingest service that writes for many tenants either connects as each tenant's role per batch (the role-per-tenant alternative below), sets the tenant per batch like any other client (subject to the warning above), or runs as a separate role with BYPASSRLS that is never exposed to customer-facing code.

Safer alternatives for tenant context behind PgBouncer

The alternatives below do not keep the tenant in session state that a pooler could hand to another client. Each is built only on documented behaviour; none was run with PgBouncer here, and each costs more connections or pools than one shared pool.

• One database role per tenant, RLS keyed on current_user. Each tenant's requests log in as that tenant's role, and the policy compares the row with current_user, as in the examples of the PostgreSQL row security documentation. The PgBouncer configuration documentation states that, unless a fixed user= is set for the database, PgBouncer logs into the destination database with the client user name, meaning that there will be one pool per user; default_pool_size is the maximum number of server connections per user/database pair. A server connection therefore only ever serves one tenant, so a forgotten SET cannot borrow another tenant's identity. Trade-offs: pools and server connections multiply with tenants (up to tenants x pool size, bounded by max_db_connections, max_user_connections and PostgreSQL's max_connections); one credential per tenant to issue, store and rotate (PgBouncer's auth_file or auth_query); the application needs a connection pool per tenant too. Do not switch tenants with SET ROLE on a pooled connection: current_user can be changed with SET ROLE, which is session state like any other SET.
• Database per tenant. The strongest boundary inside one cluster, described above; pools are per database and user, with the same connection arithmetic as one role per tenant, plus per-database migrations.
• Session pooling. PgBouncer's session mode keeps a server connection for the whole client connection and supports all PostgreSQL features, and its default server_reset_query, DISCARD ALL, runs when the client disconnects. Tenant settings then behave as in a direct connection, but connection reuse drops sharply, so you need roughly one server connection per concurrent client connection. A long-lived application connection that serves several tenants in turn still needs its own discipline (set per transaction, reset after).

Our editorial view: where tenants are few enough for the connection arithmetic to work, one role per tenant or one database per tenant is the safer default behind transaction pooling; keep the session-setting approach for direct connections or session pooling, or adopt it with transaction pooling only after you have verified it in your own environment.

-- same tenant and reading tables and rows as above, in a fresh session
ALTER TABLE reading ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_by_role ON reading
    USING      (tenant_id = substring(current_user FROM '^tenant_([0-9]+)$')::integer)
    WITH CHECK (tenant_id = substring(current_user FROM '^tenant_([0-9]+)$')::integer);

CREATE ROLE tenant_1 NOLOGIN;      -- LOGIN PASSWORD '...' in a real deployment
CREATE ROLE tenant_2 NOLOGIN;
CREATE ROLE reporting_app NOLOGIN; -- a role that is not a tenant
GRANT SELECT, INSERT ON reading TO tenant_1, tenant_2, reporting_app;

SET ROLE tenant_1;
SELECT current_user, count(*) AS rows_visible FROM reading GROUP BY current_user;
--  current_user | rows_visible
-- --------------+--------------
--  tenant_1     |            2

INSERT INTO reading VALUES (2, 201, '2026-10-27 09:01+00', 21.1);
-- ERROR:  new row violates row-level security policy for table "reading"
-- SQLSTATE 42501
RESET ROLE;

SET ROLE tenant_2;
SELECT tenant_id, point_id, ts, value FROM reading ORDER BY point_id, ts;
--  tenant_id | point_id |           ts           | value
-- -----------+----------+------------------------+-------
--          2 |      201 | 2026-10-27 09:00:00+00 |    21
RESET ROLE;

SET ROLE reporting_app;            -- fails closed: not a tenant role
SELECT count(*) AS rows_visible_non_tenant_role FROM reading;
--  rows_visible_non_tenant_role
-- ------------------------------
--                             0

INSERT INTO reading VALUES (1, 101, '2026-10-27 09:02+00', 13.5);
-- ERROR:  new row violates row-level security policy for table "reading"
-- SQLSTATE 42501

; pgbouncer.ini: no user= on the database entry, so each login role gets its own pool
[databases]
telemetry = host=db.internal port=5432 dbname=telemetry

[pgbouncer]
pool_mode = transaction
default_pool_size = 2        ; per user/database pair, i.e. per tenant (illustrative)
max_db_connections = 100     ; cap across all tenant pools (illustrative)
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

Notes on the role-per-tenant policy

The role name carries the tenant id (tenant_1, tenant_2), so the policy needs no lookup table; any role that does not match the pattern, including a misconfigured service account, sees no rows and cannot write. A mapping table from role to tenant also works, but the PostgreSQL documentation warns that policies with sub-queries on other tables can create race conditions, so keep such a table administrator-owned and rarely changed. The pool sizes in the configuration are illustrative assumptions: choose them from the number of tenants, each tenant's peak concurrency and the server's max_connections. The policy compares one column with a value computed from current_user; its effect on query plans was not examined here.

Connection pooling across the models

PgBouncer offers session, transaction and statement pooling. In transaction pooling a server connection is assigned to a client only for the duration of a transaction, which lets many clients share few connections but breaks session features: its compatibility table lists SET/RESET, LISTEN, session advisory locks and SQL PREPARE as never compatible. For multi-tenant telemetry this means:

• Shared tables + RLS with a session setting: not recommended for production behind transaction pooling until verified there (see the warning above). If you do verify and adopt it, set the tenant with set_config('app.tenant_id', ..., true) or SET LOCAL as the first statement inside an explicit transaction (BEGIN ... COMMIT), never at session level; in autocommit mode the value lasts only for that one statement, as the run above shows.
• Shared tables + RLS with one role per tenant: one pool per tenant role; nothing tenant-specific is held in session state.
• Schema per tenant: schema-qualified names, one role per tenant, or session pooling; a search_path set per transaction has the same verification caveat as a tenant setting.
• Database per tenant: pools are per database and user, so plan pool_size, max_db_connections and max_connections together, and let idle tenant pools close.

Decision guide, limitations and when not to use PostgreSQL multi-tenancy

The guide below is our judgement, not a rule from the PostgreSQL documentation; the tenant counts and sizes in it are illustrative. Weigh tenant count and size, isolation and contract requirements, migration and monitoring capacity, connection limits and how tenants are expected to grow.

• Database per tenant when tenants are relatively few (tens rather than thousands is an illustrative scale, limited in practice by connections, pools, migrations and monitoring), contracts require separable backups and deletion, tenants differ in retention or extensions, or some tenants will move to their own cluster later.
• Shared tables + RLS when tenants are many and small (one building each, a few hundred points), when you want one migration path, and when platform-wide analytics matter. Behind PgBouncer transaction pooling, take the tenant from the login role (one role per tenant) or use session pooling; the session-setting variant is not recommended for production there until you have verified it with your pooler. Partition by time as usual; partition or index by tenant only if large tenants dominate.
• Schema per tenant in our view rarely wins for telemetry: it carries the migration cost of separate databases and the weaker isolation of a shared database. It suits a moderate number of tenants that need per-tenant customisation of tables.
• Hybrid (one option, not a default): shared tables for the long tail, a dedicated database or cluster for large or sensitive tenants, with a routing table that maps tenant to connection string.

Limitations. None of these models isolates CPU, memory, I/O or WAL between tenants on one server. If a customer requires guaranteed performance or separate encryption keys and recovery points, give them a separate cluster or instance. And none of them replaces application-level checks: authenticate the tenant before setting it, and audit administrative access.

When not to do this in PostgreSQL at all. If per-tenant raw telemetry volumes call for a dedicated time-series engine, or tenants need data residency in different regions, the tenancy boundary moves to the deployment (one instance per region or per tenant). See the time-series database selection guide and the time-series overview. For running PostgreSQL locally to try these patterns, see PostgreSQL in Docker.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Managing Databases, Overview
• PostgreSQL 18 documentation: CREATE DATABASE
• PostgreSQL 18 documentation: Schemas
• PostgreSQL 18 documentation: Row Security Policies
• PostgreSQL 18 documentation: CREATE POLICY
• PostgreSQL 18 documentation: Connections and Authentication
• PostgreSQL 18 documentation: System Administration Functions
• PostgreSQL 18 documentation: pg_dump
• PgBouncer: Features
• PgBouncer: Configuration
• PostgreSQL 18 documentation: SET
• PostgreSQL 18 documentation: Customized Options
• PostgreSQL 18 documentation: System Information Functions (current_user)

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.