ALTER TABLE SQUASH PARTITIONS

Merges partition parts back into the physical partition.

This SQL keyword is designed to use for downgrading QuestDB to a version earlier than 7.2, when partition split is introduced. Squashing partition parts makes the database compatible with earlier QuestDB versions.

Syntax​

Flow chart showing the syntax of the ALTER TABLE keyword

Flow chart showing the syntax of ALTER TABLE with SQUASH PARTITIONS keyword

Examples​

The SQL keyword SHOW PARTITIONS can be used to display partition split details.

For example, Let's consider the following table x containing split partitions:

SHOW PARTITIONS FROM x;
indexpartitionBynameminTimestampmaxTimestampnumRowsdiskSizediskSizeHumanreadOnlyactiveattacheddetachedattachable
0DAY2023-02-042023-02-04T00:00:00.000000Z2023-02-04T23:59:59.940000Z14400007128113668.0 MiBFALSEFALSETRUEFALSEFALSE
1DAY2023-02-052023-02-05T00:00:00.000000Z2023-02-05T20:59:59.880000Z12599996538854462.4 MiBFALSEFALSETRUEFALSEFALSE
2DAY2023-02-05T205959-8800012023-02-05T20:59:59.940000Z2023-02-05T21:59:59.940000Z600028388608080.0 MiBFALSETRUETRUEFALSEFALSE

The table is partition by day and there are two partitions for 2023-02-05 as a result of partition split.

To merge the two partitions:

ALTER TABLE x SQUASH PARTITIONS;

SHOW PARTITIONS FROM x;
indexpartitionBynameminTimestampmaxTimestampnumRowsdiskSizediskSizeHumanreadOnlyactiveattacheddetachedattachable
0DAY2023-02-042023-02-04T00:00:00.000000Z2023-02-04T23:59:59.940000Z14400007128113668.0 MiBFALSEFALSETRUEFALSEFALSE
1DAY2023-02-052023-02-05T00:00:00.000000Z2023-02-05T21:59:59.940000Z13200016538854462.4 MiBFALSETRUETRUEFALSEFALSE