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.
#
SyntaxThe WHERE
clause is not supported when attaching partitions.
#
DescriptionBefore executing ATTACH PARTITION
, the partition folders to be attached must
be made available to QuestDB using one of the following methods:
- Copying the partition folders manually
- Using a symbolic link
This section describes the details of each method.
#
Manual copyPartition 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 linksSymbolic links can be used to
attach partition folders that exist potentially in a different volume as cold
storage. The partitions attached in this way will be read-only. 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.
In Windows, symbolic links require admin privileges, and thus this method is not recommended.
note
SQL statements that hit partitions attached via symbolic links may have slower runtime if their volumes have a slower disk.
#
Properties using symbolic linksPartitions attached via the symbolic link approach are read-only for the following operations:
DETACH PARTITION
andDROP PARTITION
: Once the partition folders are unlinked, the symbolic links are removed, but the content remains. Detaching a partition that was attached via symbolic link does not create a copy<partition_name>.detached
.UPDATE
: Attempts to update the read-only partitions result in an error.INSERT
: Attemps to insert data into a read-only partition result in a critical-level log message being logged by the server, and the insertion is a no-op. If Prometheus monitoring is configured, an alert will be triggered.
For read-only partitions, the following operations are supported:
#
Example#
Manual copyAssuming the QuestDB data directory is /var/lib/questdb/db
, for a table x
with AWS S3 for cold storage:
Copy files from S3:
Execute the SQL
ALTER TABLE ATTACH PARTITION
command:After the SQL is executed, the partitions will be available to read.
#
Symbolic linkThe 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:
This SQL statement detaches partitions 2022, 2023, 2024, 2025, 2026, and 2027:
Assuming QuestDB's root directory to be /opt/homebrew/var/questdb/db
, the
content of the table folder is:
You can now move those <partition_name.detached>
folders to a different path,
potentially a different volume:
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
:
The content of the table folder should look like this now:
After the symbolic links have been created, the partitions can be attached with the following SQL statement:
The SQL reference to the partitions does not include the suffix .attachable
.
#
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.