# How Buzz Implements Postgres Monthly Range Partitioning for High-Traffic Event Data

> Learn how Buzz implements Postgres monthly range partitioning with Rust validation for efficient, high-volume event log data management.

- Repository: [Block Open Source/buzz](https://github.com/block/buzz)
- Tags: deep-dive
- Published: 2026-08-29

---

**Buzz uses PostgreSQL's native range partitioning with automated monthly child table creation, implementing strict validation and allow-listing in Rust to safely manage high-volume event and delivery log data.**

The block/buzz repository handles high-traffic event data by employing a **Postgres monthly range partitioning** strategy that segments data across calendar-month boundaries. This architecture optimizes query performance and maintenance operations for the `events` and `delivery_log` tables while ensuring continuous write availability through automated partition provisioning.

## Schema Foundation: Range-Partitioned Parent Tables

The partitioning architecture begins with parent table definitions in [`schema/schema.sql`](https://github.com/block/buzz/blob/main/schema/schema.sql) that declare range boundaries on timestamp columns, establishing the structural foundation for monthly data segmentation.

### The events Table Structure

Located at lines 196-238 of [`schema/schema.sql`](https://github.com/block/buzz/blob/main/schema/schema.sql), the `events` table implements `PARTITION BY RANGE (created_at)` to distribute rows across discrete monthly partitions. The schema defines a catch-all partition for historical data alongside specific monthly child tables that cover individual calendar months, enabling efficient partition pruning for time-bounded queries.

### The delivery_log Table

Similarly, the `delivery_log` table (lines 26-44 of [`schema/schema.sql`](https://github.com/block/buzz/blob/main/schema/schema.sql)) declares `PARTITION BY RANGE (delivered_at)`, ensuring delivery tracking data follows the same monthly segmentation strategy as the events table. This consistency allows both high-volume tables to benefit from parallelized vacuuming, indexing, and data archival operations.

## Automated Partition Provisioning

Rather than relying on manual DDL execution, Buzz implements a Rust-based partition manager that programmatically creates future partitions before they are needed by the application.

### The Partition Manager Core

The core logic resides in [`crates/buzz-db/src/store/partition.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/store/partition.rs). The entry point function `ensure_future_partitions(pool, months_ahead)` calculates upcoming monthly date ranges and ensures child tables exist prior to data arrival. This asynchronous function computes start and end timestamps for each month in the look-ahead window and validates generated partition suffixes to prevent SQL injection.

### Startup Integration

The relay application invokes this manager during its initialization sequence. In [`crates/buzz-relay/src/main.rs`](https://github.com/block/buzz/blob/main/crates/buzz-relay/src/main.rs) (lines 211-214), the startup code executes:

```rust
db.ensure_future_partitions(3).await?;

```

This call guarantees that PostgreSQL always contains partitions provisioned for the current month plus two future months, preventing write failures when calendar boundaries approach.

## Partition Creation Workflow

When `ensure_future_partitions` executes, it orchestrates a rigorous validation and creation pipeline for each target month:

1. **Date Calculation**: Computes start (`YYYY-MM-01`) and end (`YYYY-MM-01` of the next month) timestamps for each upcoming month.
2. **Suffix Generation**: Creates partition suffixes in `YYYY_MM` format (e.g., `2026_03`).
3. **Existence Verification**: Queries the PostgreSQL catalog (`SELECT COUNT(*) ... WHERE c.relispartition = true`) to verify whether the expected partition already exists.
4. **Conditional Creation**: Executes `CREATE TABLE IF NOT EXISTS ... PARTITION OF ... FOR VALUES FROM (...) TO (...)` only when the partition is missing.

The underlying `ensure_partition` function (lines 84-160 in [`partition.rs`](https://github.com/block/buzz/blob/main/partition.rs)) handles the actual DDL execution, treating overlaps with existing catch-all partitions as successful completion states rather than errors.

## Safety Mechanisms and DDL Protection

The partition manager implements multiple defense layers to prevent SQL injection and unauthorized table modification during automated operations.

### Table Allow-Listing

A compile-time constant `PARTITIONED_TABLES` (line 13 of [`partition.rs`](https://github.com/block/buzz/blob/main/partition.rs)) explicitly restricts automated DDL operations to only the `events` and `delivery_log` tables. This allow-list ensures the manager cannot accidentally create partitions or execute DDL against unintended database relations, even if configuration errors occur.

### Input Validation

Before executing any dynamic SQL, the code validates all inputs through dedicated validation functions:

- **`validate_partition_suffix`**: Enforces that partition names contain only digits and underscores, rejecting any non-conforming characters that could indicate injection attempts.
- **`validate_date_str`**: Ensures all date strings strictly match the `YYYY-MM-DD` format before they are interpolated into DDL statements, preventing malformed date ranges from reaching the SQL layer.

These validations operate on the principle that all dynamic components of the `CREATE TABLE` statement must pass semantic validation before execution.

## Summary

- Buzz implements **Postgres monthly range partitioning** on the `events` and `delivery_log` tables using `PARTITION BY RANGE` declarations on timestamp columns in [`schema/schema.sql`](https://github.com/block/buzz/blob/main/schema/schema.sql).
- The `ensure_future_partitions` function in [`crates/buzz-db/src/store/partition.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/store/partition.rs) automates the creation of monthly child tables with a configurable look-ahead window, typically set to 3 months.
- **Allow-listing** via `PARTITIONED_TABLES` and strict **input validation** through `validate_partition_suffix` and `validate_date_str` protect against SQL injection and unauthorized DDL execution.
- Partitions are provisioned automatically at application startup via [`crates/buzz-relay/src/main.rs`](https://github.com/block/buzz/blob/main/crates/buzz-relay/src/main.rs) to ensure continuous write availability across calendar month boundaries.

## Frequently Asked Questions

### Which tables in Buzz use monthly range partitioning?

The `events` table (partitioned by `created_at`) and the `delivery_log` table (partitioned by `delivered_at`) both implement monthly range partitioning, as defined in [`schema/schema.sql`](https://github.com/block/buzz/blob/main/schema/schema.sql) lines 196-238 and 26-44 respectively.

### How does Buzz automatically create new monthly partitions?

The partition manager in [`crates/buzz-db/src/store/partition.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/store/partition.rs) provides the `ensure_future_partitions` asynchronous function, which calculates upcoming month ranges and issues safe `CREATE TABLE ... PARTITION OF` DDL statements. The relay service calls this function with a `months_ahead` parameter at startup to maintain a rolling window of future partitions.

### What safety measures prevent accidental table modification or SQL injection?

Buzz uses an explicit allow-list (`PARTITIONED_TABLES`) that restricts automation to only the `events` and `delivery_log` tables. Additionally, the `validate_partition_suffix` and `validate_date_str` functions sanitize all user-influenced inputs before they reach the database, ensuring partition names and date ranges conform to strict `YYYY_MM` and `YYYY-MM-DD` patterns respectively.

### Can the number of future partitions be configured at runtime?

Yes. The `ensure_future_partitions` function accepts a configurable `months_ahead` integer parameter. While the relay application typically passes `3` to maintain three months of provisioned partitions, operators can adjust this value based on data retention policies or anticipated traffic spikes without modifying the core partitioning logic.