Table Partitioning

Relevant source files

The following files were used as context for generating this wiki page:

• src/table/create.rs
• src/backend/table_builder.rs
• src/backend/postgres/table.rs
• src/backend/mysql/table.rs
• tests/postgres/table.rs
• tests/mysql/table.rs
• changelog/1.0.0-rc.34.md

Table Partitioning in SeaQuery provides a programmatic interface for defining and managing partitioned tables across different database backends, primarily PostgreSQL and MySQL. This feature allows developers to split large tables into smaller, more manageable pieces (partitions) based on specific criteria like ranges, lists, or hashes.

The system is integrated into the TableCreateStatement workflow, enabling the definition of a master table's partitioning strategy or the creation of specific child partitions. SeaQuery abstracts the syntax differences between backends while maintaining support for engine-specific features such as PostgreSQL's PARTITION OF or MySQL's PARTITION BY KEY.

Sources: src/table/create.rs:114-121, changelog/1.0.0-rc.34.md:58-75

Architecture and Components

The partitioning logic is built into the core schema builder module. It uses several enums and structs to represent the Abstract Syntax Tree (AST) of a partition clause.

Core Data Structures

Structure	Description
PartitionBy	Defines the strategy for splitting data (Range, List, Hash, or Key).
PartitionValues	Defines the specific values or bounds for a partition (In, FromTo, LessThan, or With).
PartitionDefinition	Represents a named partition and its associated values, used primarily in MySQL.
TableCreateStatement	The primary statement builder containing fields for partitioning logic.

Sources: src/table/create.rs:136-155, src/table/create.rs:114-129

Component Relationships

The following diagram illustrates how partitioning components are integrated into the TableCreateStatement.

The relationship between the statement and its partitioning components.
Sources: src/table/create.rs:114-155

Partitioning Strategies

SeaQuery supports several strategies for defining how data is distributed.

Range Partitioning

Data is assigned to partitions based on a range of values in the partition key columns.

• Postgres Syntax: PARTITION BY RANGE (col)
• Method: .partition_by_range([Column])

Sources: src/table/create.rs:271-282, src/backend/postgres/table.rs:175-179

List Partitioning

Data is assigned to partitions based on whether the key values appear in a discrete list of values.

• Postgres Syntax: PARTITION BY LIST (col)
• Method: .partition_by_list([Column])

Sources: src/table/create.rs:297-308, src/backend/postgres/table.rs:180-184

Hash Partitioning

Data is assigned by calculating a hash of the partition key and performing a modulus operation.

• Postgres Syntax: PARTITION BY HASH (col)
• Method: .partition_by_hash([Column])

Sources: src/table/create.rs:323-334, src/backend/postgres/table.rs:185-189

Key Partitioning (MySQL Only)

Similar to hash partitioning, but utilizes the database's internal hashing function.

• MySQL Syntax: PARTITION BY KEY (col)
• Method: .partition_by_key([Column])

Sources: src/table/create.rs:349-360, src/backend/mysql/table.rs:163-166

Backend Implementation and SQL Generation

The TableBuilder trait defines how these AST components are translated into SQL. Each backend implements specific logic to handle its supported partitioning syntax.

SQL translation flow for different database backends.
Sources: src/backend/table_builder.rs:8-90, src/backend/postgres/table.rs:174-213, src/backend/mysql/table.rs:149-216

PostgreSQL Partitioning

PostgreSQL uses a declarative partitioning model where a child table is explicitly declared as a "partition of" a master table.

Feature	SQL Syntax Generated	Method
Child Table	CREATE TABLE ... PARTITION OF "master"	.partition_of(Master::Table)
Value Ranges	FOR VALUES FROM (x) TO (y)	.values_from_to([x], [y])
In List	FOR VALUES IN (1, 2, 3)	.values_in([1, 2, 3])
Hash Modulus	FOR VALUES WITH (MODULUS m, REMAINDER r)	.values_with(m, r)

Sources: src/backend/postgres/table.rs:197-213, src/table/create.rs:381-470

MySQL Partitioning

MySQL typically defines partitions within the master table's creation statement using partition definitions.

Feature	SQL Syntax Generated	Method
Range Bounds	VALUES LESS THAN (x)	.values_less_than([x])
In List	VALUES IN (1, 2, 3)	.add_partition(name, Some(PartitionValues::In(...)))
Naming	PARTITION p0 ...	.add_partition(Alias::new("p0"), ...)

Sources: src/backend/mysql/table.rs:194-216, src/table/create.rs:488-517

Examples

PostgreSQL Range Partition

Creating a master table and a child partition in PostgreSQL.

// Master Table
Table::create()
    .table(Glyph::Table)
    .col(ColumnDef::new(Glyph::Id).integer().not_null())
    .partition_by_range([Glyph::Id])
    .to_string(PostgresQueryBuilder);

// Child Partition
Table::create()
    .table(Alias::new("glyph_1"))
    .partition_of(Glyph::Table)
    .values_from_to([1], [10])
    .to_string(PostgresQueryBuilder);

Sources: tests/postgres/table.rs:608-629

MySQL List Partition

Defining partitions directly on the table in MySQL.

Table::create()
    .table(Glyph::Table)
    .col(ColumnDef::new(Glyph::Id).integer().not_null())
    .partition_by_list([Glyph::Id])
    .add_partition(
        Alias::new("p0"),
        Some(PartitionValues::In(vec![1.into(), 2.into()]))
    )
    .to_string(MysqlQueryBuilder);

Sources: tests/mysql/table.rs:555-568

Summary

Table partitioning in SeaQuery offers a robust API for managing database-scale data distribution. By utilizing the PartitionBy and PartitionValues structures, the library ensures that complex SQL commands like PARTITION BY and FOR VALUES are generated correctly for PostgreSQL and MySQL, shielding the user from backend-specific syntax quirks while providing deep access to advanced partitioning features.

Sources: changelog/1.0.0-rc.34.md:58-75, src/table/create.rs:114-121