Database Partitioning

Database partitioning is the division of a large table into smaller, more manageable segments called partitions, each storing a subset of the data based on defined criteria. This approach provides significant performance advantages for inserts, queries, and deletions, and enables more efficient data management at scale.

In database tables, rows are partitioned according to the values of selected columns or transformations of the column values. For example, a partition based on the column month partitions the rows based on the month in a date value. Similarly, a partition based on bucket(X) partitions rows according to a hash of the column X — this tends to distribute rows evenly over the partitions.

Up to four columns can be specified as partition keys. Partitioning is defined at table creation. Once applied to a table, it cannot be removed or changed.

Queries benefit from partitioning in several ways:

Inserts benefit from partitioning by allowing data to be aggregated in memory per partition before being written in batches, reducing write amplification from background data reorganization. Deletes of entire partitions are significantly faster than row-level deletes.

Partitioning is supported for the following database clients:

The following features are compatible with partitioned tables:

Partitions are based on transformations of column values (including the identity transformation, which leaves the value unchanged).

The following transformations are supported:

Partitions are defined for tables when the tables are created.

For details on table configuration options, see Creating a Table using the VAST Web UI.

Use this syntax to add partitioning when creating a table using Trino:

CREATE TABLE table (
    event_id int,
    event_time timestamp(6),
    event_type varchar
)
WITH (
    partitioning = ARRAY['day(event_time)', 'event_type']
);

In this example, the day transformation of the column event_time and the event_type column are used to create partitions. This creates a partition for each day and type combination.

Use this syntax to add partitioning when creating a table using Spark or the VAST Query Engine:

CREATE TABLE table (
    event_id int,
    event_time timestamp(6),
    event_type string
)
PARTITIONED BY (day(event_time), event_type)

In this example, the day transformation of the column event_time and the event_type column are used to create partitions.

You can delete partitions from an existing database table. This operation removes all rows matching the targeted partition boundaries.

Caution

This action is non-ACIDic. Modifying partition subsets while concurrent write procedures are targeted at the parent table can result in an indeterminate data execution state.

For a table created with partitions in Trino, such as:

CREATE TABLE table(col1 INTEGER, col2 INTEGER) WITH (partitioning = ARRAY['col1', 'bucket(col2, 1024)'])

Use this syntax to delete the partitions:

DELETE FROM "table$partitions vast.allow_non_acid" WHERE col1 = 'value' and col2_bucket = 'transformed-value2'

where table is the table partitioned by columns col1 and col2 (which uses the bucket transform function).

Note

The parameter flag vast.allow_non_acid must be appended to the targeted execution syntax statement.

For a table created with partitions in Spark, such as:

CREATE TABLE table(c1 BOOLEAN, c2 INTEGER, c3 INTEGER) PARTITIONED BY (bucket(8, c2), c1)

Use this syntax to delete the partitions:

ALTER TABLE `table vast.allow_non_acid` DROP PARTITION (c2_bucket=4, c1=true)

where table is the table partitioned by columns c1 and c2 (which uses the bucket transform function).

Note

The parameter flag vast.allow_non_acid must be appended to the targeted execution syntax statement.

To add partitioning parameters to a table execution configuration statement, use the table create command inside the command line interface and specify the --partitions-config parameter.