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

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 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, 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) 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. 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 (lines 211-214), the startup code executes:

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) 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) 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.
• The ensure_future_partitions function in 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 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 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}