Choosing Time-Series Partition Intervals

Scope: what the interval decides

This page is for engineers who have already decided to partition time-series data by time (the basics are in How to Store IoT Sensor Data) and now have to pick the width of each slice. Each engine names it differently: a partition range in PostgreSQL and MySQL, chunk_interval on a TimescaleDB hypertable, PARTITION BY HOUR | DAY | WEEK | MONTH | YEAR in QuestDB, the shard group duration of an InfluxDB bucket, and the PARTITION BY expression of a ClickHouse table.

The interval controls six things at once:

• Pruning granularity: a query for the last hour reads at least one whole partition's worth of index or data structures.
• Active index size: the indexes of the partition currently being written should stay in memory, or every insert turns into disk reads.
• Retention granularity: dropping data by age removes whole partitions.
• Late-data cost: in engines that rewrite or recompact a partition when old data arrives, a larger partition means more rewriting.
• Object count: more partitions mean more planning work, more metadata in memory, more files or more unmerged parts.
• Compression batch size: in columnar engines a very small partition gives the compressor less to work with.

Step 1: compute rows per partition

Take the campus from the storage guide, whose figures are illustrative assumptions: 5,000 BMS points, sampled every 60 seconds today and possibly every second after an upgrade, with 90 days of raw retention. The query below turns those assumptions into rows per partition and the number of partitions the retention window needs (a month is taken as 30 days).

SELECT r.rate, i.label AS interval,
       to_char(r.rows_per_day * i.days, 'FM999,999,999,999') AS rows_per_partition,
       ceil(90 / i.days)::int AS partitions_for_90_days
FROM (VALUES ('60 s', 5000 * 1440::bigint), ('1 s', 5000 * 86400::bigint)) AS r(rate, rows_per_day)
CROSS JOIN (VALUES ('1 hour', 1/24.0), ('1 day', 1.0), ('7 days', 7.0), ('1 month', 30.0)) AS i(label, days)
ORDER BY r.rate DESC, i.days;

 rate | interval | rows_per_partition | partitions_for_90_days
------+----------+--------------------+------------------------
 60 s | 1 hour   | 300,000            |                   2160
 60 s | 1 day    | 7,200,000          |                     90
 60 s | 7 days   | 50,400,000         |                     13
 60 s | 1 month  | 216,000,000        |                      3
 1 s  | 1 hour   | 18,000,000         |                   2160
 1 s  | 1 day    | 432,000,000        |                     90
 1 s  | 7 days   | 3,024,000,000      |                     13
 1 s  | 1 month  | 12,960,000,000     |                      3

Step 2: check against each engine's published guidance

Applied to the campus

This is our reading of QuestDB's published guidance for the illustrative campus, not a QuestDB recommendation for it. At 60 seconds, QuestDB's volume table maps 7.2 million rows a day (its 5 to 30 million band) to WEEK, and a week is 50.4 million rows, inside the 30 to 80 million target, so the two pieces of guidance agree. At 1 second they point different ways: 432 million rows a day falls in the volume table's 30 to 500 million band, which it maps to DAY, while the 30 to 80 million rows-per-partition target would point to HOUR (18 million rows, below the target) or to splitting points across tables. The same page says tables with few columns can go higher than the target, which on our reading leans towards DAY for a narrow readings table; it does not say how much higher, so the documentation leaves this case open and we do not resolve it here. Settle it with the factors QuestDB names: column count and row width, how much out-of-order data arrives (larger partitions mean more rewriting), and the time windows your queries read. The other engines use different yardsticks, so check each one rather than carrying one number across products.

Step 3: check the active index against memory

For PostgreSQL and TimescaleDB, TimescaleDB's guidance treats the index being written as a key constraint. TimescaleDB's documentation gives an example: on a 64 GB server with about 16 GB of shared_buffers, index growth of about 2 GB a day suits a 7-day interval and 10 GB a day suits 1 day. That 25% rule is TimescaleDB's guidance for hypertable chunks. The PostgreSQL documentation gives no memory rule for partitions; it says only that too few partitions may leave indexes too large. Below we apply the TimescaleDB rule to the newest partition of a plain PostgreSQL table by analogy, as a rough check rather than documented PostgreSQL guidance.

The table below estimates primary-key index growth for the campus. It assumes about 33 bytes of (point_id, ts) index per row, the 33.1 bytes the storage sizing article recorded on a 180,000-row sample in one PGlite run. That figure is an illustrative measurement on a small PGlite sample (PostgreSQL 18.6 compiled to 32-bit WebAssembly), not a production benchmark and not representative of your server, so every size in the table below is an illustrative estimate built on it, not a constant: an index filled by live inserts in time order, with point_id leading, may be larger than one measured after a single load (our inference about page splits, not measured here). Replace it with a measurement from your own ingest and add any other indexes.

Reading the estimate

Under these assumptions (and with the 33-byte input taken from the illustrative PGlite sample), at 60 seconds a weekly or monthly interval keeps the estimated active index well inside the memory of a modest server, so the choice is driven by retention and query windows. At 1 second, even a daily partition's estimated index (about 14 GB) would only just fit the 16 GB shared_buffers of TimescaleDB's example; options worth evaluating are an interval of a few hours, fewer indexes, more memory, or a columnar engine. Which fits depends on your measured index size per row, the memory actually available for caching, other indexes on the table and how far back late data is written. This is the point where the TimescaleDB vs PostgreSQL comparison becomes relevant.

Step 4: align with retention and query windows

• Retention: with 90-day retention and monthly partitions, the oldest partition can only go once all of it is older than 90 days, so up to about 120 days are kept. With daily partitions, at most one extra day is kept. If retention is contractual, make the interval a fraction of it; the PostgreSQL documentation recommends designing partitions so that data removed together sits in one partition.
• Dashboards: if most queries cover the last 24 hours, a daily interval means they touch one or two partitions. A monthly interval still prunes to one partition, but that partition and its indexes are 30 times larger.
• Reports: a monthly energy report over daily partitions touches 28 to 31 partitions, which is fine; the PostgreSQL warning is about plans that keep thousands of partitions after pruning, not tens.
• Late data: if gateways regularly backfill hours or days of readings, smaller partitions limit the rewrite work in QuestDB and the un-compaction work in InfluxDB. See late-arriving and out-of-order sensor events.

Step 5: run it, including a change of interval (PostgreSQL)

PostgreSQL range partitions do not need to be the same width, so an interval can change without moving old data: keep the existing monthly partitions and create daily ones from a chosen date. A small function, run daily by a scheduler, creates partitions ahead of the data and is safe to re-run:

CREATE TABLE reading (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision,
    PRIMARY KEY (point_id, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE reading_2026_09 PARTITION OF reading FOR VALUES FROM ('2026-09-01 00:00+00') TO ('2026-10-01 00:00+00');
CREATE TABLE reading_2026_10 PARTITION OF reading FOR VALUES FROM ('2026-10-01 00:00+00') TO ('2026-11-01 00:00+00');

CREATE FUNCTION create_daily_partitions(first_day date, n_days int) RETURNS int
LANGUAGE plpgsql AS $$
DECLARE d date; made int := 0;
BEGIN
    FOR d IN SELECT generate_series(first_day, first_day + n_days - 1, interval '1 day')::date LOOP
        IF to_regclass(format('reading_%s', to_char(d, 'YYYY_MM_DD'))) IS NULL THEN
            EXECUTE format('CREATE TABLE %I PARTITION OF reading FOR VALUES FROM (%L) TO (%L)',
                           'reading_' || to_char(d, 'YYYY_MM_DD'),
                           d::timestamp AT TIME ZONE 'UTC', (d + 1)::timestamp AT TIME ZONE 'UTC');
            made := made + 1;
        END IF;
    END LOOP;
    RETURN made;
END $$;

SELECT create_daily_partitions('2026-11-01', 14) AS created;           -- 14
SELECT create_daily_partitions('2026-11-01', 16) AS created_on_rerun;  -- 2

SELECT c.relname AS partition, pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'reading'::regclass
ORDER BY c.relname
LIMIT 4;

     partition      |                                  bounds
--------------------+--------------------------------------------------------------------------
 reading_2026_09    | FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00')
 reading_2026_10    | FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00')
 reading_2026_11_01 | FOR VALUES FROM ('2026-11-01 00:00:00+00') TO ('2026-11-02 00:00:00+00')
 reading_2026_11_02 | FOR VALUES FROM ('2026-11-02 00:00:00+00') TO ('2026-11-03 00:00:00+00')

Overlaps are rejected

Changing the interval in the other direction needs care: a new monthly partition cannot overlap the daily ones already created. From the same run:

CREATE TABLE reading_2026_11 PARTITION OF reading FOR VALUES FROM ('2026-11-01 00:00+00') TO ('2026-12-01 00:00+00');

ERROR:  partition "reading_2026_11" would overlap partition "reading_2026_11_01"
SQLSTATE 42P17

How many partitions a query touches

A three-day query on the daily partitions scans three partitions; a seven-day query on October's monthly partition scans one, larger, partition. Both plans are from the same run (the table is empty, so only the plan shape matters):

EXPLAIN (COSTS OFF)
SELECT avg(value) FROM reading
WHERE point_id = 7 AND ts >= '2026-11-03 00:00+00' AND ts < '2026-11-06 00:00+00';

-- Aggregate
--   ->  Append
--         ->  Index Scan using reading_2026_11_03_pkey on reading_2026_11_03 reading_1
--               Index Cond: ((point_id = 7) AND (ts >= '2026-11-03 00:00:00+00'::timestamp with time zone) AND (ts < '2026-11-06 00:00:00+00'::timestamp with time zone))
--         ->  Index Scan using reading_2026_11_04_pkey on reading_2026_11_04 reading_2
--               Index Cond: (...same...)
--         ->  Index Scan using reading_2026_11_05_pkey on reading_2026_11_05 reading_3
--               Index Cond: (...same...)

EXPLAIN (COSTS OFF)
SELECT avg(value) FROM reading
WHERE point_id = 7 AND ts >= '2026-10-20 00:00+00' AND ts < '2026-10-27 00:00+00';

-- Aggregate
--   ->  Index Scan using reading_2026_10_pkey on reading_2026_10 reading
--         Index Cond: ((point_id = 7) AND (ts >= '2026-10-20 00:00:00+00'::timestamp with time zone) AND (ts < '2026-10-27 00:00:00+00'::timestamp with time zone))

Monitor the headroom

A missing future partition makes inserts fail, as the storage guide shows, so it is worth monitoring. Alert when the newest upper bound is less than a few days ahead:

SELECT max(upper_bound)::text AS covered_until,
       (max(upper_bound) - timestamptz '2026-11-01 00:00+00')::text AS headroom_from_1_nov
FROM (
    SELECT (regexp_match(pg_get_expr(c.relpartbound, c.oid), 'TO \(''([^'']+)''\)'))[1]::timestamptz AS upper_bound
    FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
    WHERE i.inhparent = 'reading'::regclass
) b;

--      covered_until      | headroom_from_1_nov
-- ------------------------+---------------------
--  2026-11-17 00:00:00+00 | 16 days

Changing the interval in other engines

• TimescaleDB: set_chunk_time_interval() affects only chunks created afterwards. If an undersized interval has already produced thousands of small chunks, the documentation describes a table-swap migration: create a new hypertable with the right interval and backfill in batches.
• QuestDB: partitioning cannot be changed after the table is created; create a new table, copy the data and swap names. Separately, QuestDB may split a partition when out-of-order data arrives and squash the splits later.
• InfluxDB OSS v2: set a custom shard group duration with --shard-group-duration on influx bucket create or influx bucket update.
• ClickHouse: the partition key is part of the table definition; ClickHouse's guidance is to start without partitioning if unsure and add it when access patterns are known.

-- at creation
CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  device_id   INT,
  value       DOUBLE PRECISION
) WITH (
  timescaledb.hypertable,
  timescaledb.chunk_interval = '1 day'
);

-- later: applies to new chunks only
SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 day');

Cross-database note: MySQL range partitions

MySQL 8.0 partitions a TIMESTAMP column by range on UNIX_TIMESTAMP(ts) (the manual permits no other expression on a TIMESTAMP column for this), requires the partitioning column in every unique key (so the primary key is (point_id, ts) again), and allows at most 8,192 partitions per table. A common pattern keeps a MAXVALUE partition at the end and splits it with REORGANIZE PARTITION as each new day approaches. EXPLAIN shows pruning in its partitions column:

CREATE TABLE reading (
    point_id  INT NOT NULL,
    ts        TIMESTAMP NOT NULL,
    value     DOUBLE,
    PRIMARY KEY (point_id, ts)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(ts)) (
    PARTITION p2026_11_01 VALUES LESS THAN (UNIX_TIMESTAMP('2026-11-02 00:00:00')),
    PARTITION p2026_11_02 VALUES LESS THAN (UNIX_TIMESTAMP('2026-11-03 00:00:00')),
    PARTITION pmax        VALUES LESS THAN MAXVALUE
);

ALTER TABLE reading REORGANIZE PARTITION pmax INTO (
    PARTITION p2026_11_03 VALUES LESS THAN (UNIX_TIMESTAMP('2026-11-04 00:00:00')),
    PARTITION pmax        VALUES LESS THAN MAXVALUE
);

INSERT INTO reading VALUES (7, '2026-11-02 10:00:00', 21.5), (7, '2026-11-03 10:00:00', 21.7);

EXPLAIN SELECT avg(value) FROM reading
WHERE point_id = 7 AND ts >= '2026-11-03 00:00:00' AND ts < '2026-11-04 00:00:00';

| table   | partitions  | type  | key     | rows | Extra       |
| reading | p2026_11_03 | range | PRIMARY | 1    | Using where |

Time zone caution

The MySQL 8.0 manual states that UNIX_TIMESTAMP() interprets a date argument as a value in the session time zone and converts it to a Unix timestamp in UTC. A partition bound such as UNIX_TIMESTAMP('2026-11-02 00:00:00') is therefore evaluated in the time zone of the session that runs the DDL and stored as a fixed integer. The run below shows it: the same literal gives two different bounds in two session time zones, and a reading written as 03:00 from a session at UTC+05:00 (22:00 UTC on 1 November) lands in the partition for 1 November. Create partitions from a session set to UTC (or whatever zone your boundaries are meant in), and remember that clients in other time zones see the same boundaries shifted.

SET time_zone = '+00:00';
SELECT UNIX_TIMESTAMP('2026-11-02 00:00:00') AS bound_utc_session;      -- 1793577600
SET time_zone = '+05:00';
SELECT UNIX_TIMESTAMP('2026-11-02 00:00:00') AS bound_plus5_session;    -- 1793559600

-- A fresh copy of the CREATE TABLE above (before the REORGANIZE), run from a session at '+00:00'
SELECT PARTITION_NAME, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'reading'
ORDER BY PARTITION_ORDINAL_POSITION;
-- p2026_11_01 | 1793577600
-- p2026_11_02 | 1793664000
-- pmax        | MAXVALUE

-- From a session at '+05:00'
SET time_zone = '+05:00';
INSERT INTO reading VALUES (7, '2026-11-02 03:00:00', 21.5);
EXPLAIN SELECT * FROM reading WHERE ts = '2026-11-02 03:00:00';
-- partitions: p2026_11_01

Illustrative starting points for building and IoT data

The table below is illustrative: the workloads are assumptions for the example and the intervals are starting points to test, not recommendations from any vendor. Choose your own from the factors on this page: measured rows per day and index bytes per row, the memory available for the indexes being written, the retention period and how precisely it must be enforced, the time windows your queries read, how much late data arrives and how far back, and the engine's own published guidance.

Trade-offs, limitations and when not to partition

• Do not partition small tables. The PostgreSQL documentation's rule of thumb is that partitioning pays off when the table would exceed the server's physical memory; ClickHouse describes partitioning as primarily a data management feature and suggests starting without it if unsure.
• Do not choose hourly partitions for convenience. 90 days of hourly partitions is 2,160 objects for a table whose queries mostly read days.
• Do not trust a single published number. Vendor targets are written for their storage engines; rows of different widths, extra indexes and query mixes move the answer. The PostgreSQL documentation recommends simulating the intended workload.
• Operations: automate partition creation, alert on headroom, and give the job that creates or drops partitions its own role. Partition DDL takes locks on the parent table; the PostgreSQL documentation notes that ATTACH PARTITION and DETACH PARTITION CONCURRENTLY take weaker locks than CREATE TABLE ... PARTITION OF.

For the query side of the same decision see Time-Series Database Performance; for dropping old partitions on a schedule see retention and downsampling.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Table Partitioning
• Tiger Data documentation: Size hypertable chunks
• QuestDB documentation: Time partitions
• InfluxDB OSS v2 documentation: InfluxDB shards and shard groups
• ClickHouse documentation: Choosing a partitioning key
• MySQL 8.0 Reference Manual: Restrictions and Limitations on Partitioning
• MySQL 8.0 Reference Manual: RANGE Partitioning
• MySQL 8.0 Reference Manual: Management of RANGE and LIST Partitions
• MySQL 8.0 Reference Manual: Date and Time Functions (UNIX_TIMESTAMP)
• MySQL 8.0 Reference Manual: Partitioning Limitations Relating to Functions

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.