Use ALTER TABLE to Add Columns & Set TTL in Lindorm - Lindorm

Use ALTER TABLE to modify a table's schema — add or drop columns, update table attributes, or trigger a major compaction. This topic covers LindormTable and LindormTSDB.

Supported operations

Operation	Description
ADD COLUMN	Add one or more columns to a table
DROP COLUMN	Remove a column (LindormTable 2.6.6+)
SET table_options	Update table attributes such as TTL, compression, and hot/cold data separation (LindormTable only)
COMPACT	Trigger a major compaction to merge data files and reclaim disk space (LindormTable 2.3.4+)

Engine support

Operation	LindormTable	LindormTSDB
ADD COLUMN	Yes	Yes
DROP COLUMN	Yes (2.6.6+)	No
SET table_options	Yes	No
COMPACT	Yes (2.3.4+)	No

Syntax

alter_table_statement   ::= ALTER TABLE table_name alter_table_instruction

alter_table_instruction ::=
    add_column_list
  | DROP COLUMN [ IF EXISTS ] column_name
  | SET table_options
  | COMPACT (idcName)?

add_column_list ::= ADD [COLUMN] column_def ( ',' ADD [COLUMN] column_def )*
column_def      ::= column_name type [TAG]

Usage notes

ALTER TABLE does not lock the table. The table stays readable and writable throughout the Data Definition Language (DDL) operation. However, you may experience brief timeouts or jitter for the following reasons:

Shard restart during DDL: Each shard shuts down and restarts to reload metadata. The restart typically takes 10 ms to several hundred milliseconds. Requests that arrive while a shard is restarting may experience minor jitter. Schedule DDL operations during off-peak hours to minimize impact.
DDL waits for all shards: The statement returns only after all shards finish the change. On large tables, a short client timeout may cause a client-side timeout error — but the DDL continues running on the server until it completes.

ADD COLUMN

Both LindormTable and LindormTSDB support ADD COLUMN.

Constraints:

The new column cannot be a primary key column.
In LindormTSDB, only TAG and FIELD columns are supported. Timestamp columns cannot be added.

Examples

Add a column to the sensor table:

ALTER TABLE sensor ADD COLUMN n1 DOUBLE;

Add a single spatio-temporal column to the roads table:

ALTER TABLE roads ADD COLUMN g2 GEOMETRY(LINESTRING);

Add multiple spatio-temporal columns in one statement:

ALTER TABLE roads ADD COLUMN g2 GEOMETRY(LINESTRING), g3 GEOMETRY(POLYGON);

Verify the result:

DESCRIBE TABLE sensor;

DROP COLUMN

Requires LindormTable 2.6.6 or later.

Important

If your instance cannot be upgraded to LindormTable 2.6.6 or later in the console, contact Lindorm technical support (DingTalk ID: s0s3eg3).

DROP COLUMN is asynchronous. After the statement runs, the column is immediately invisible in the schema. However, the underlying data is not removed until the system completes a COMPACT operation. To speed up data cleanup, run FLUSH followed by COMPACT manually.

A column with the same name cannot be re-added until the data is fully cleared. For more information, see FAQ.

Example

Drop the n1 column from the sensor table:

ALTER TABLE sensor DROP COLUMN IF EXISTS n1;

Verify the result:

DESCRIBE TABLE sensor;

The column n1 no longer appears in the output. The underlying data is cleared only after compaction completes.

SET table_options

LindormTable only. Use SET to update table attributes at any time without downtime.

For the full list of table attributes, see Table attributes. To verify changes, run SHOW TABLE VARIABLES.

Important

When modifying MUTABILITY and CONSISTENCY:

Before creating an index, both attributes can be changed freely.
After creating an index:
MUTABILITY cannot be changed.
CONSISTENCY can only be changed from eventual to strong, not the other way around.

Enable dynamic columns

ALTER TABLE sensor SET DYNAMIC_COLUMNS = 'true';

For more information, see Dynamic columns.

Set table consistency

ALTER TABLE roads SET 'CONSISTENCY' = 'strong';

Set a data TTL

Set a TTL (in seconds) for the sensor table:

ALTER TABLE sensor SET TTL = '2592000';

Remove the TTL:

ALTER TABLE sensor SET TTL = '';

Verify the result: In the cluster management system, go to Overview, click the target database, and then click the target table. In the Current table details area, check the value of ttl. For more information about logging in, see Log on to the cluster management system.

Set a compression algorithm

Set the compression algorithm to ZSTD:

ALTER TABLE sensor SET COMPRESSION = 'ZSTD';

Disable compression:

ALTER TABLE sensor SET COMPRESSION = 'NONE';

Verify the result: In the cluster management system, go to Overview, click the target database, and then click the target table. In the Current table details area, click View table properties and check the COMPRESSION value.

Configure hot and cold data separation

For background, see Introduction to hot and cold data separation.

Separate data based on ingestion timestamp

Archive data to cold storage based on when it was written to the database:

-- Enable hot/cold separation for sensor based on ingestion timestamp; boundary = 1 day (86400 seconds)
ALTER TABLE sensor SET CHS = '86400', CHS_L2 = 'storagetype=COLD';

Verify the result:

SHOW TABLE VARIABLES FROM sensor LIKE 'CHS%';

For more information, see SHOW TABLE VARIABLES.

Separate data based on a custom time column

Important

This option has specific requirements. Review the usage notes before using it.

Archive rows to cold storage based on the business timestamp in a specified column:

-- Enable hot/cold separation for sensor using the time column; boundary = 1 day
ALTER TABLE sensor SET CHS = '86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN = 'COLUMN=time';

Verify the result:

SHOW TABLE VARIABLES FROM sensor LIKE 'CHS%';

Remove the hot/cold data boundary

ALTER TABLE sensor SET CHS = '', CHS_L2 = '';

Verify the result:

SHOW TABLE VARIABLES FROM sensor LIKE 'CHS%';

COMPACT

Requires LindormTable 2.3.4 or later. LindormTSDB does not support this operation.

A major compaction merges data files, clears deleted or expired data, and reclaims disk space — improving both read and write performance. The operation consumes system resources, so run it during off-peak hours when possible.

IDC targeting rules:

Scenario	Behavior
Single-zone instance	Runs on the single IDC; specifying `idcName` has no effect
Multi-zone instance, no `idcName`, version < 2.6.4.2	Runs on idc1 only
Multi-zone instance, no `idcName`, version >= 2.6.4.2	Runs on all IDCs
`idcName` specified	Runs on the specified IDC(s)

Examples

Run a major compaction on the sensor table (all IDCs on version 2.6.4.2+):

ALTER TABLE sensor COMPACT;

Run a major compaction on idc2 only:

ALTER TABLE sensor COMPACT 'idc2';

Run a major compaction on both idc1 and idc2:

ALTER TABLE sensor COMPACT 'idc1,idc2';

What's next

SHOW TABLE VARIABLES — verify table attribute changes
Table attributes — full list of attributes for SET
Dynamic columns — manage schemaless columns
SQL FAQ — common issues including DROP COLUMN re-add timing