Table partitioning

Table partitioning is the technique of dividing table data into distinct chunks known as partitions. Once a table is partitioned, the partitions can be hidden, retrieved, or removed.

Partitioned tables provide more efficient scanning, inserting, and removal of data. When the database engine only needs to scan a single partition — or a small number of partitions instead of the whole table — this can significantly increase the overall performance of the scan.

There are two types of table partitioning: manual and expression-based.

Manual partitioning

Manual partitioning is achieved by specifying the partition name where the data should be inserted:

Example 1. Working with data in a table having two explicitly defined partitions

Insert data into two partitions: "foo" and "bar"

CREATE TABLE tbl();
INSERT INTO tbl(event) VALUES ('a') WITH (partition = 'foo');
INSERT INTO tbl(event) VALUES ('b') WITH (partition = 'bar');
INSERT INTO tbl(event) VALUES ('c') WITH (partition = 'foo');

If a partition name isn’t specified, the record is assigned to the default partition.

Insert data into the default partition

INSERT INTO tbl(event) VALUES ('x');

Retrieve data from all partitions

SELECT event FROM tbl;

Result when all partitions are attached

{"event":"a"}
{"event":"b"}
{"event":"c"}
{"event":"x"}

With DETACH PARTITION partition_name, a partition’s data becomes inaccessible, hidden from all queries.

Detach the "bar" partition and retrieve data from all partitions

ALTER TABLE tbl DETACH PARTITION "bar";
SELECT event FROM tbl;

Result when "bar" is detached

{"event":"a"}
{"event":"c"}
{"event":"x"}

ATTACH PARTITION restores accessibility to data contained in a previously detached partition.

Attach the "bar" partition, detach the "foo" partition, and retrieve data from all partitions

ALTER TABLE tbl ATTACH PARTITION "bar";
ALTER TABLE tbl DETACH PARTITION "foo";
SELECT event FROM tbl;

Result when "foo" is detached and "bar" is attached

{"event":"b"}
{"event":"x"}

By detaching all named partitions, only the data in the default partition remains accessible.

Detach both named partitions and retrieve data from the default partition

ALTER TABLE tbl DETACH PARTITION "foo";
ALTER TABLE tbl DETACH PARTITION "bar";
SELECT event FROM tbl;

Result when "foo" and "bar" are detached

{"event":"x"}

Expression-based partitioning

Expression-based partitioning is a convenient way to create partitions dynamically based on SQL expressions. At the time of data insertion, the partition name is automatically determined based on the PARTITION BY expression part of the CREATE TABLE command:

One common practice that is especially useful is creating partitions based on a specific period of time, for example, by days, weeks, months, quarters, etc. However, partition names can be defined and selected based on any user-defined expression using any data type or combination of fields.

Example 2. Creating a table with dynamic partitioning by date

In this example, the DATE function is used to extract the date portion of the EventTime field that contains DATETIME values.

The expression DATE(EventTime) is supplied as the argument to PARTITION BY when the table is created. This means that each new record inserted will have its date portion of the EventTime field automatically determine which partition it will be assigned to.

Create a table with date-based partitioning and insert the data

CREATE TABLE tbl () PARTITION BY DATE(EventTime);
INSERT INTO tbl(event, EventTime) VALUES('a', '2022-08-04 17:14:04'); (1)
INSERT INTO tbl(event, EventTime) VALUES('b', '2022-08-04 17:14:10'); (2)
INSERT INTO tbl(event, EventTime) VALUES('c', '2022-08-05 17:14:04'); (3)

1	Dynamically assigned to partition `2022-08-04`
2	Dynamically assigned to partition `2022-08-04`
3	Dynamically assigned to partition `2022-08-05`

Query for all data from all attached partitions

SELECT event, EventTime FROM tbl;

Result when all partitions are attached

{"event":"a","eventtime":"2022-08-04 17:14:04"}
{"event":"b","eventtime":"2022-08-04 17:14:10"}
{"event":"c","eventtime":"2022-08-05 17:14:04"}

Detach partition 2022-08-04 and retrieve all accessible data

ALTER TABLE tbl DETACH PARTITION "2022-08-04";
SELECT event, EventTime FROM tbl;

Result when partition 2022-08-04 is detached

{"event":"c","eventtime":"2022-08-05 17:14:04"}

Attach partition 2022-08-04, detach partition 2022-08-05, and retrieve all accessible data

ALTER TABLE tbl ATTACH PARTITION "2022-08-04";
ALTER TABLE tbl DETACH PARTITION "2022-08-05";
SELECT event, EventTime FROM tbl;

Result when partition 2022-08-05 is detached and partition 2022-08-04 is attached

{"event":"a","eventtime":"2022-08-04 17:14:04"}
{"event":"b","eventtime":"2022-08-04 17:14:10"}