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:
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 INTO tbl(event) VALUES ('x');
SELECT event FROM tbl;
{"event":"a"}
{"event":"b"}
{"event":"c"}
{"event":"x"}
With DETACH PARTITION
partition_name, a partition’s data becomes inaccessible, hidden from all queries.
ALTER TABLE tbl DETACH PARTITION "bar";
SELECT event FROM tbl;
{"event":"a"}
{"event":"c"}
{"event":"x"}
ATTACH PARTITION
restores accessibility to data contained in a previously detached partition.
ALTER TABLE tbl ATTACH PARTITION "bar";
ALTER TABLE tbl DETACH PARTITION "foo";
SELECT event FROM tbl;
{"event":"b"}
{"event":"x"}
By detaching all named partitions, only the data in the default partition remains accessible.
ALTER TABLE tbl DETACH PARTITION "foo";
ALTER TABLE tbl DETACH PARTITION "bar";
SELECT event FROM tbl;
{"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.
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 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 |
SELECT event, EventTime FROM tbl;
{"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"}
ALTER TABLE tbl DETACH PARTITION "2022-08-04";
SELECT event, EventTime FROM tbl;
{"event":"c","eventtime":"2022-08-05 17:14:04"}
ALTER TABLE tbl ATTACH PARTITION "2022-08-04";
ALTER TABLE tbl DETACH PARTITION "2022-08-05";
SELECT event, EventTime FROM tbl;
{"event":"a","eventtime":"2022-08-04 17:14:04"}
{"event":"b","eventtime":"2022-08-04 17:14:10"}