Time partitioning

Deep dive

How it works

In: a row (ts, ...), a query [lo, hi), a retention cutoff · Out: the partition the row belongs to, the rows in range, the dropped partitions · State: a sorted map start -> partition plus a default partition · Cost: route and pruning O(log P) per lookup; retention O(dropped partitions)

1. Boundaries. Partition k covers [start_k, start_k + width), half-open, aligned to a fixed zone (UTC), so every timestamp has exactly one partition.
2. Pre-create. A scheduler creates the next few partitions ahead of the clock; pg_partman defaults to premake 4 and a worker run every 3,600 s.
3. Route. Compute floor(ts / width), find the partition, append. No partition exists: error (PostgreSQL), or the default partition (pg_partman creates one for every set).
4. Prune. For [lo, hi) read partitions with start_lo <= start < hi, then filter rows inside.
5. Retention. Drop every partition that ends at or before the cutoff. PostgreSQL: DROP TABLE or DETACH PARTITION, and the docs say both avoid the VACUUM cost of a bulk DELETE.
6. Late rows. An old timestamp goes to its old partition while that exists; afterwards to the default partition or an error. Alert on the default.

Code

Daily partitions in a BTreeMap: precreate, route (late row to its old day, unknown day to the default), prune, retention drop. Asserts compare the query with a full-scan filter and the table after retention with a row-by-row delete. Compiled and run, std only.

use std::collections::BTreeMap;
use std::time::Instant;

const DAY: i64 = 86_400; // UTC day; the boundary time zone is part of the schema
type Row = (i64, f64); // (unix seconds, value)

#[derive(Default)]
struct Scan { parts: usize, rows: usize } // work counters

#[derive(Default)]
struct Table {
    parts: BTreeMap<i64, Vec<Row>>, // partition start -> rows of [start, start + DAY)
    default: Vec<Row>,              // rows that fit no existing partition
}

fn start_of(ts: i64) -> i64 { ts.div_euclid(DAY) * DAY } // floor, also for ts < 0

impl Table {
    /// Create empty partitions for the days from `now` to `now + days`.
    fn precreate(&mut self, now: i64, days: i64) {
        for d in 0..=days { self.parts.entry(start_of(now) + d * DAY).or_default(); }
    }
    /// Route by timestamp. A late row goes to its old partition if that still exists.
    fn insert(&mut self, ts: i64, v: f64) {
        match self.parts.get_mut(&start_of(ts)) {
            Some(p) => p.push((ts, v)),
            None => self.default.push((ts, v)),
        }
    }
    /// Rows with lo <= ts < hi: only partitions whose range overlaps are read.
    fn query(&self, lo: i64, hi: i64) -> (Vec<Row>, Scan) {
        let (mut out, mut sc) = (vec![], Scan::default());
        for (_, rows) in self.parts.range(start_of(lo)..hi) {
            sc.parts += 1;
            sc.rows += rows.len();
            out.extend(rows.iter().copied().filter(|r| lo <= r.0 && r.0 < hi));
        }
        sc.rows += self.default.len(); // the default partition can never be pruned
        out.extend(self.default.iter().copied().filter(|r| lo <= r.0 && r.0 < hi));
        (out, sc)
    }
    /// Retention: forget every partition that ends at or before `cutoff` (whole-partition drop).
    fn drop_before(&mut self, cutoff: i64) -> usize {
        let keep = self.parts.split_off(&(cutoff - DAY + 1)); // keys >= cutoff-DAY+1 survive
        let dropped = self.parts.len();
        self.parts = keep;
        dropped
    }
    fn rows(&self) -> usize { self.parts.values().map(Vec::len).sum::<usize>() + self.default.len() }
}

fn sorted(mut v: Vec<Row>) -> Vec<Row> { v.sort_by(|a, b| a.partial_cmp(b).unwrap()); v }

fn main() {
    let (t0, days, per_day) = (1_700_006_400i64, 90i64, 20_000i64); // t0 = 00:00 UTC
    let mut t = Table::default();
    t.precreate(t0, days);
    let mut all = vec![];
    for i in 0..days * per_day { // one row every 4.32 s, in time order
        let ts = t0 + i * DAY / per_day;
        t.insert(ts, (i % 97) as f64);
        all.push((ts, (i % 97) as f64));
    }
    t.insert(t0 + 5 * DAY + 60, 1.5); // late row: lands in day 5, not in the newest partition
    all.push((t0 + 5 * DAY + 60, 1.5));
    t.insert(t0 + 400 * DAY, 2.5); // far future, no partition yet: default partition
    all.push((t0 + 400 * DAY, 2.5));
    assert_eq!(t.parts[&(t0 + 5 * DAY)].len(), per_day as usize + 1);
    assert_eq!(t.default.len(), 1);

    let (lo, hi) = (t0 + 40 * DAY + 3600, t0 + 42 * DAY); // 23 h of day 40 + all of day 41 -> 2 partitions
    let (got, sc) = t.query(lo, hi);
    let full: Vec<Row> = all.iter().copied().filter(|r| lo <= r.0 && r.0 < hi).collect();
    assert_eq!(sorted(got), sorted(full)); // same answer as a full scan
    assert_eq!(sc.parts, 2); // hi = start of day 42 is exclusive
    println!("query: {} of {} partitions, {} of {} rows read", sc.parts, t.parts.len(), sc.rows, t.rows());

    let before = t.rows();
    let clock = Instant::now();
    let dropped = t.drop_before(t0 + 60 * DAY); // keep the newest 30 days
    let drop_us = clock.elapsed().as_micros();
    let clock = Instant::now();
    all.retain(|r| r.0 >= t0 + 60 * DAY); // row-by-row delete for comparison
    let del_us = clock.elapsed().as_micros();
    assert_eq!(dropped, 60);
    assert_eq!(t.rows(), all.len()); // identical content afterwards
    println!("retention: {dropped} partitions dropped ({} of {before} rows left): {drop_us} us vs row-by-row {del_us} us over {before} rows", t.rows());
    t.insert(t0 + 5 * DAY, 9.0); // late row for a dropped day: default, not a revived partition
    assert_eq!(t.default.len(), 2);
}

Output: query: 2 of 91 partitions, 40001 of 1800002 rows read; retention: 60 partitions dropped (600001 of 1800002 rows left). The 40,001 includes the one default-partition row, which every query must scan.

Complexity and constants

• Route: one division and one map lookup. Prune: O(log P) to the first overlap, then one step per partition.
• A query of w days reads about w + 1 daily partitions, whatever the table size.
• Metadata grows with P: PostgreSQL loads each touched partition's metadata into every session (docs); TimescaleDB flags over 1,000 chunks in one hypertable as a planning and memory warning sign.
• Against BigQuery's 10,000-partition limit, daily partitions last about 27 years and hourly about 14 months (arithmetic).

Efficiency

• Cost: retention in the model: 19-51 µs to drop 60 partitions of 20,000 rows, 0.9-1.5 ms to filter all 1.8 M rows (3 runs, measured on this Mac (Apple M4 Pro), RAM only). On disk a drop is a file or catalog delete; a bulk DELETE leaves dead rows for VACUUM (PostgreSQL docs). Bound: planner and metadata for P, I/O for the overlap.
• Levers:
  • Coarser interval — fewer partitions, less planning (BigQuery docs: month instead of day near the limit) — queries span many intervals anyway.
  • Clustering inside a partition — BigQuery docs recommend it when partitions are too coarse or the limit bites — filters on a second column.
  • DETACH PARTITION ... CONCURRENTLY — SHARE UPDATE EXCLUSIVE on the parent instead of ACCESS EXCLUSIVE (PostgreSQL docs) — retention on a busy table.
  • Compress or move cold partitions — TimescaleDB columnstore up to 98 % compression (docs); ClickHouse moves partitions to cheaper storage (docs) — old data still read.
• Naive → standard → state of the art:
  • One table, DELETE WHERE ts < cutoff · O(rows) plus vacuum
  • Time-range partitions, scheduled create and drop · O(dropped partitions)
  • Hidden transforms like day(ts) plus lifecycle policies (Iceberg, ILM) · no partition column to maintain, tiering and deletion automatic

Implementation notes

• Fix the boundary zone in the schema: BigQuery boundaries are UTC, Iceberg's day counts from 1970-01-01.
• Monitor the default partition (pg_partman: check_default()).
• PostgreSQL: give a new partition a CHECK constraint that excludes it from the default, or the attach scans the default under ACCESS EXCLUSIVE (docs).
• Detach first, back up or roll up, then drop. pg_partman detaches by default and never drops the last child.

Pitfalls

• Late data. Rows older than the cutoff arrive after their partition is gone: the code fills the default partition; PostgreSQL without one fails the insert.
• Time zone. A local day spans two UTC partitions, so a local-day query reads both. Iceberg's docs warn that a wrong source column or time zone in Hive-style partition values gives silently wrong results.
• No time predicate. Every partition is read; ClickHouse docs note that cross-partition queries can be slower than on an unpartitioned table.
• Hot newest partition. All writes hit it; add a second key (hash, device) if one node cannot take it (range partitioning).
• Too many partitions. BigQuery rejects jobs touching over 4,000 partitions; ClickHouse merges parts only within a partition, so high cardinality ends in "too many parts" errors.
• Row movement. Updating the partition key moves the row in PostgreSQL.
• Unique keys. PostgreSQL and TimescaleDB require unique indexes to contain all partition columns; uniqueness is enforced per partition.
• Edge values. BigQuery puts NULL and timestamps before 1960-01-01 or after 2159-12-31 into special partitions.

Variants and alternatives

In production

PostgreSQL with pg_partman; TimescaleDB hypertables (7-day chunks by default); BigQuery partition expiration; ClickHouse PARTITION BY toYYYYMM(date); Iceberg day(event_time); Elasticsearch ILM rollover and delete phases.

Implementations

Sources

• PostgreSQL, Table Partitioning. docs · CREATE TABLE. docs
• Google Cloud, Introduction to partitioned tables. docs · BigQuery quotas and limits. docs
• Tiger Data, Size hypertable chunks. docs · Unique indexes. docs · Drop chunks. docs
• ClickHouse, Custom partitioning key. docs · Choosing a partitioning key. docs
• Apache Iceberg, Partitioning. docs · Table spec. spec
• Elastic, Index lifecycle management. docs · Rollover. docs
• pg_partman documentation. docs