Space Partitioning for Multi-Tenant IoT

Multi-tenant IoT platforms ingest high-velocity telemetry from thousands of devices that belong to distinct customers, sites, or business units, and every one of those tenants expects predictable query latency and hard data boundaries. A single time-only hypertable pushes all of that traffic into one hot chunk per interval, which serialises writes behind a shared lock, skews I/O onto one disk, and forces every tenant-scoped query to scan data it will immediately discard. Space partitioning solves a single focused problem: it fans each time interval out across a small, fixed set of hash-addressed chunks so that writes parallelise, tenant-scoped reads prune to a fraction of the data, and compression and retention operate on tenant-aligned units. This guide shows how to configure that second partitioning dimension correctly, wire it into the surrounding lifecycle, and validate that the planner is actually pruning.

This page sits under the Core Hypertable Architecture & Partitioning Strategy reference, which governs how chunks are created, compressed, and dropped. Space partitioning never runs alone — it composes with time-based chunk partitioning strategies to produce the deterministic two-dimensional layout that downstream aggregates and retention depend on.

The diagram captures the core mechanism the rest of this page builds on: the primary time dimension slices data into intervals, and the space dimension hashes each row’s tenant_id into one of N partitions within that interval. Total chunk count per interval equals the number of space partitions, so the sections below treat that count as the central tuning knob.

# Prerequisites

Space partitions are declared at hypertable creation and cannot be altered afterward without recreating the hypertable, so the schema and capacity decisions here are effectively permanent. Confirm each item before running any DDL:

• TimescaleDB 2.10+ installed and timescaledb present in shared_preload_libraries in postgresql.conf
• PostgreSQL 14+ (required for the planner improvements that make chunk exclusion reliable across two dimensions)
• A telemetry table with an explicit tenant identifier column (tenant_id UUID or site_id BIGINT) that appears in the overwhelming majority of read queries
• Any primary key or UNIQUE constraint includes both the time column and the space partitioning column — TimescaleDB rejects unique constraints that do not cover every partitioning key
• A forecast of tenant cardinality and per-tenant ingestion skew, so the partition count can be tied to parallel I/O capacity rather than tenant count
• The create_hypertable() and add_dimension() functions available (both ship with the core extension; no additional extension is required)

The connection user needs CREATE on the target schema and ownership of the table it converts into a hypertable. For automation, provision a dedicated role rather than reusing the ingestion role — the security boundaries and access control patterns cover least-privilege policy owners for this kind of DDL job.

# Step-by-Step Implementation

The five steps below map onto the diagram: create the base table, attach the time dimension (the vertical split), attach the space dimension (the horizontal fan-out), index for tenant-scoped access, then verify the layout materialises as intended.

# Step 1 — Model the tenant boundary in the schema

The tenant column must be NOT NULL and stable for the lifetime of a row. A UUID tenant key hashes evenly and avoids the hot-spotting that monotonic integer keys can produce when tenants are onboarded in bursts.

CREATE TABLE IF NOT EXISTS iot_telemetry (
    time        TIMESTAMPTZ NOT NULL,
    tenant_id   UUID        NOT NULL,
    device_id   TEXT        NOT NULL,
    metric_name TEXT        NOT NULL,
    value       DOUBLE PRECISION,
    tags        JSONB
);

# Step 2 — Create the hypertable with the time dimension

Convert the table to a hypertable, setting the primary time dimension and its interval. Align chunk_time_interval with your query and retention cadence first; the space dimension multiplies whatever chunk count that interval produces. For sizing the interval itself, see how to calculate optimal chunk_interval for IoT sensor data.

SELECT create_hypertable(
    'iot_telemetry',
    by_range('time', INTERVAL '1 day'),
    if_not_exists => TRUE
);

# Step 3 — Attach the space (hash) dimension

Add the second dimension with by_hash over tenant_id. The partition count is the load-bearing decision. Keep it small and fixed — commonly 4 to 16 — and tie it to the number of parallel I/O paths (disks, NVMe namespaces, or tablespaces) available for writes, not to how many tenants exist.

SELECT add_dimension(
    'iot_telemetry',
    by_hash('tenant_id', number_partitions => 8),
    if_not_exists => TRUE
);

A hash function maps every tenant_id to one of the 8 partitions. Thousands of tenants collapse into 8 buckets; adding tenants never adds partitions. Raising number_partitions as tenant count grows is the classic mistake — it multiplies chunks per interval, inflating catalog rows and autovacuum pressure without improving isolation.

$$\text{chunks per interval} = N_{\text{space}} \qquad \text{total chunks} \approx N_{\text{space}} \times \left\lceil \frac{T_{\text{retention}}}{\Delta t_{\text{chunk}}} \right\rceil$$

With number_partitions => 8, a 1-day interval, and a 30-day retention horizon, the hypertable holds roughly 8 × 30 = 240 active chunks — a comfortable catalog size. Doubling the partition count doubles that number for no isolation benefit.

# Step 4 — Index for tenant-scoped pruning

The planner prunes space partitions only when a query filters on the partitioning column. Lead composite indexes with tenant_id so per-tenant reads hit a single partition’s chunks, and reach for partial or BRIN structures on high-cardinality tags — the best practices for chunk indexing on high-cardinality tags go deeper on that trade-off.

CREATE INDEX IF NOT EXISTS idx_telemetry_tenant_time
    ON iot_telemetry (tenant_id, time DESC);

CREATE INDEX IF NOT EXISTS idx_telemetry_metric_partial
    ON iot_telemetry (metric_name, value)
    WHERE metric_name IN ('cpu_usage', 'memory_free', 'network_bytes');

# Step 5 — Verify the two-dimensional layout

Confirm both dimensions registered before ingesting at scale. timescaledb_information.dimensions reports one row per dimension per hypertable.

SELECT dimension_number, column_name, dimension_type, num_partitions
FROM   timescaledb_information.dimensions
WHERE  hypertable_name = 'iot_telemetry'
ORDER  BY dimension_number;

You expect two rows: a Time dimension with no fixed partition count, and a Space dimension reporting num_partitions = 8. The concrete create_hypertable / add_dimension recipe, including the idempotent DO block variant, is expanded in configuring space partitions for multi-tenant time-series.

# Configuration Parameters Reference

The default partitioning_func is a consistent hash; it distributes many tenants evenly but does nothing for skew caused by one tenant emitting orders of magnitude more rows than the rest. That case is a data-distribution problem, not a partition-count problem — isolate the whale tenant into its own hypertable or table if it threatens the shared partitions.

# Integration with Adjacent Features

Space partitioning changes how the surrounding lifecycle behaves, and getting the interactions right is what turns a partitioned table into a maintainable platform.

Continuous aggregates. Because a continuous aggregate refresh policy materialises on chunk boundaries, the refresh engine can process each space partition’s newly appended chunks independently, parallelising materialisation across tenants instead of serialising behind one hot chunk. Align the aggregate’s time_bucket width and the refresh window with your chunk_time_interval so no bucket straddles a boundary and forces redundant recomputation. The definition syntax and planner rewrites live in materialized view architecture & syntax, and window tuning in refresh policy design & scheduling.

CREATE MATERIALIZED VIEW IF NOT EXISTS iot_hourly_agg
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       tenant_id,
       device_id,
       metric_name,
       avg(value) AS avg_value,
       max(value) AS max_value,
       count(*)   AS record_count
FROM   iot_telemetry
GROUP  BY bucket, tenant_id, device_id, metric_name
WITH NO DATA;

SELECT add_continuous_aggregate_policy('iot_hourly_agg',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour',
    if_not_exists     => TRUE);

Compression. A space partition is a natural compression unit. Segmenting compressed chunks by tenant_id keeps each tenant’s rows contiguous in columnar storage, which improves both ratio and per-tenant scan speed — the trade-offs of choosing segmentby/orderby keys are covered in columnar compression models for high-frequency telemetry. Compress older chunks to columnar form before retention drops them to squeeze storage 70–90% while keeping historical tenant analytics queryable.

Retention. TimescaleDB drops whole chunks, and a chunk holds data for all tenants whose hash landed in that partition. A retention policy therefore evaluates the chunk’s time range, not any individual tenant’s oldest record, so shared chunks only drop once the entire interval ages past the threshold. Where tenants need different horizons, map business TTLs to per-hypertable drop_after intervals as described in TTL policy mapping and enforcement, and consider a separate longer retention on the aggregate so summaries outlive the raw rows.

SELECT add_retention_policy('iot_telemetry',
    drop_after    => INTERVAL '30 days',
    if_not_exists => TRUE);

# Performance Validation

Space partitioning is only worth its catalog cost if the planner actually prunes and if the chunks distribute evenly. Two checks confirm both.

Confirm chunk exclusion. Run a tenant-scoped query under EXPLAIN (ANALYZE, BUFFERS). Only chunks belonging to that tenant’s hash partition should appear as child nodes under the Append; excluded partitions are simply absent from the plan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT time, value
FROM   iot_telemetry
WHERE  tenant_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
  AND  time > now() - INTERVAL '3 days';

Inspect chunk distribution and job health. Check that chunks spread across partitions and that background jobs are succeeding.

-- Recent chunks and their time ranges across space partitions.
SELECT chunk_name, range_start, range_end, is_compressed
FROM   timescaledb_information.chunks
WHERE  hypertable_name = 'iot_telemetry'
ORDER  BY range_start DESC
LIMIT  20;

-- Refresh / compression / retention job outcomes and durations.
SELECT j.proc_name, s.last_run_status, s.last_successful_finish,
       s.total_failures, s.last_run_duration
FROM   timescaledb_information.job_stats s
JOIN   timescaledb_information.jobs j USING (job_id)
WHERE  j.hypertable_name = 'iot_telemetry'
ORDER  BY s.last_start DESC;

Rising total_failures or a last_run_status other than Success usually points at lock contention from a whale tenant’s ingestion surge overlapping a policy window; the troubleshooting notes below map the common failure states to fixes.

# Troubleshooting

ERROR: cannot create a unique index without the column "time" (used in partitioning) — a primary key or UNIQUE constraint omitted a partitioning column. Every unique constraint on a space-partitioned hypertable must include both the time column and tenant_id. Redefine the constraint as, for example, (tenant_id, device_id, time).

Queries scan every partition despite a tenant filter. The predicate is not sargable against the space dimension — often because tenant_id is wrapped in a function, cast, or supplied as a non-UUID literal. Pass the value as the exact column type and filter with plain equality so the planner can hash it to a single partition.

One partition is far larger than the others. A single high-volume tenant is hashing into it. Hashing distributes tenants, not row volume, so it cannot balance a whale. Move that tenant to a dedicated hypertable, or split its device stream, rather than raising number_partitions.

ERROR: cannot change configuration on already partitioned dimension — you tried to alter number_partitions after creation. Space dimensions are fixed at declaration; to change the count, create a new hypertable with the target partitioning and migrate with INSERT ... SELECT or a copy job during a maintenance window.

Catalog bloat and slow planning after months of ingestion. Total chunk count outgrew the catalog because the partition count was set too high for the tenant workload. Confirm with the total chunks formula above, then rebuild with a smaller number_partitions and, if needed, a coarser chunk_time_interval.

Frequently Asked Questions

# Related

• Configuring Space Partitions for Multi-Tenant Time-Series — the full idempotent create_hypertable / add_dimension recipe and planner validation.
• Best Practices for Chunk Indexing on High-Cardinality Tags — partial and BRIN indexing for device tags and dynamic metric names.
• Time-Based Chunk Partitioning Strategies — the time dimension that space partitioning multiplies against.
• Compression Models for High-Frequency Telemetry — segmenting columnar storage by tenant.
• Security Boundaries & Access Control — RLS and least-privilege policy owners for multi-tenant hypertables.

Cross-topic: Continuous Aggregate Creation & Refresh Management · Data Retention, Compression & Lifecycle Automation

← Back to Core Hypertable Architecture & Partitioning Strategy