ALTER TABLE ATTACH PARTITION

Restores one or more partitions to the table where they have been detached from by using the SQL ALTER TABLE DETACH PARTITION statement.

This feature is part of the manual S3/cold storage solution, allowing restoring data manually.

Syntax#

Flow chart showing the syntax of ALTER TABLE with ATTACH PARTITION keyword

Description#

Before executing ATTACH PARTITION, the partition folders to be attached must be made available to QuestDB using one of the following methods:

This section describes the details of each method.

Manual copy#

Partition folders can be manually moved from where they are stored into the table folder in db. To make the partitions available for the attach operation, the files need to be renamed <partition_name>.attachable.

For example, in a table partitioned by year, given a partition folder named 2020.detached, rename it as 2020.attachable, and move it to the table folder.

Symbolic links#

Symbolic links can be used to attach partition folders that exist potentially in a different volume as cold storage. To make detached partition folders in cold storage available for attaching, for each partition folder, create a symbolic link with the name format<partition_name>.attachable from the table's folder, and set the target path to the detached partition folder.

info

  • SQL statements that hit partitions attached via symbolic links may have slower runtimes if their volumes have a slower disk.

  • In Windows, symbolic links require admin privileges, and thus this method is not recommended.

Properties using symbolic links#

Partitions attached via the symbolic link approach are considered read-only by these operations:

  • DROP/DETACH PARTITION: Once the partition folders are unlinked, the symbolic links are removed, but not the content remains. Detaching a partition that was attached via symbolic link does not create a copy <partition_name>.detached; in this case, the DETACH PARTITION operation is equal to DROP PARTITION.

Partitions attached via symbolic links are read-write for these operations:

  • INSERT, UPDATE, ADD/DROP INDEX, ADD/DROP/RENAME COLUMN: Data in the partition folders are modified in place as a result of the operation.

Example#

Manual copy#

Assuming the QuestDB data directory is /var/lib/questdb/db, for a table x with AWS S3 for cold storage:

  1. Copy files from S3:

    cd /var/lib/questdb/db/x
    # Table x is the original table where the partition were detached from.
    mkdir 2019-02-01.attachable && aws s3 cp s3://questdb-internal/blobs/20190201.tar.gz - | tar xvfz - -C 2019-02-01.attachable --strip-components 1
    mkdir 2019-02-02.attachable && aws s3 cp s3://questdb-internal/blobs/20190202.tar.gz - | tar xvfz - -C 2019-02-01.attachable --strip-components 1

  2. Execute the SQL ALTER TABLE ATTACH PARTITION command:

    ALTER TABLE x ATTACH PARTITION LIST '2019-02-01', '2019-02-02';

  3. After the SQL is executed, the partitions will be available to read.

Symbolic link#

The following example creates a table tab with some data, detaches all but the last partition, and demonstrates how to attach the partitions using symbolic links.

These SQL statements create table tab partitioned by year, and insert seven rows that result in a total of seven partitions:

CREATE TABLE tab (name STRING, age INT, dob TIMESTAMP) TIMESTAMP(dob) PARTITION BY YEAR;
INSERT INTO tab VALUES('B', 1, '2022-11-08T12:00:00.000000Z');
INSERT INTO tab VALUES('C', 2, '2023-11-08T12:00:00.000000Z');
INSERT INTO tab VALUES('D', 3, '2024-11-08T12:00:00.000000Z');
INSERT INTO tab VALUES('E', 4, '2025-11-08T12:00:00.000000Z');
INSERT INTO tab VALUES('F', 5, '2026-11-08T12:00:00.000000Z');
INSERT INTO tab VALUES('A', 0, '2027-11-08T12:00:00.000000Z');
INSERT INTO tab VALUES('0', 0, '2028-11-08T12:00:00.000000Z');

This SQL statement detaches partitions 2022, 2023, 2024, 2025, 2026, and 2027:

ALTER TABLE tab DETACH PARTITION WHERE dob < '2028';

Assuming QuestDB's root directory to be /opt/homebrew/var/questdb/db, the content of the table folder is:

2022.detached
2023.detached
2024.detached
2025.detached
2026.detached
2027.detached
2028.5
_cv
_meta
_todo_
_txn
_txn_scoreboard
seq

You can now move those <partition_name.detached> folders to a different path, potentially a different volume:

mv /opt/homebrew/var/questdb/db/tab/*.detached /cold_storage/tab

When you want to attach these partitions back, create a symlink for every partition to be attached from the table folder /opt/homebrew/var/questdb/db/tab:

ln -s /cold_storage/tab/2022.detached 2022.attachable
ln -s /cold_storage/tab/2023.detached 2023.attachable
ln -s /cold_storage/tab/2024.detached 2024.attachable
ln -s /cold_storage/tab/2025.detached 2025.attachable
ln -s /cold_storage/tab/2026.detached 2026.attachable
ln -s /cold_storage/tab/2027.detached 2027.attachable

The content of the table folder should look like this now:

2022.attachable -> /cold_storage/tab/2022.detached
2023.attachable -> /cold_storage/tab/2023.detached
2024.attachable -> /cold_storage/tab/2024.detached
2025.attachable -> /cold_storage/tab/2025.detached
2026.attachable -> /cold_storage/tab/2026.detached
2027.attachable -> /cold_storage/tab/2027.detached
2028.5
_cv
_meta
_todo_
_txn
_txn_scoreboard
seq

After the symbolic links have been created, the partitions can be attached with the following SQL statement:

ALTER TABLE tab ATTACH PARTITION LIST '2022', '2023', '2024', '2025', '2026', '2027';
info
  • The SQL reference to the partitions does not include the suffix .attachable.
  • The WHERE clause is not supported when attaching partitions.
  • The latest partition cannot be detached. However, it can be irreversibly deleted using DROP TABLE.

Limitation#

  • S3/Cold storage interaction is manual. Partitions can only be attached to the same table they were detached from. The table name must be the same. Moving partitions between tables or database instances is not supported.
  • The operation will fail if a partition already exists. We are working on functionality to allow merging data in the same partition for attaching.
Edit this page