Updating table schema

Updating table schema in RT mode

ALTER TABLE table ADD COLUMN column_name [{INTEGER|INT|BIGINT|FLOAT|BOOL|MULTI|MULTI64|JSON [secondary_index='1']|STRING|TEXT [INDEXED [ATTRIBUTE]]|TIMESTAMP|FLOAT_VECTOR [KNN options]}] [engine='columnar']
ALTER TABLE table DROP COLUMN column_name
ALTER TABLE table MODIFY COLUMN column_name bigint

This feature only supports adding one field at a time for RT tables or the expansion of an int column to bigint. The supported data types are:

  • int - integer attribute
  • timestamp - timestamp attribute
  • bigint - big integer attribute
  • float - float attribute
  • bool - boolean attribute
  • multi - multi-valued integer attribute
  • multi64 - multi-valued bigint attribute
  • json - json attribute; use secondary_index='1' to create a secondary index on the JSON
  • string / text attribute / string attribute - string attribute
  • text / text indexed stored / string indexed stored - full-text indexed field with original value stored in docstore
  • text indexed / string indexed - full-text indexed field, indexed only (the original value is not stored in docstore)
  • text indexed attribute / string indexed attribute - full text indexed field + string attribute (not storing the original value in docstore)
  • text stored / string stored - the value will be only stored in docstore, not full-text indexed, not a string attribute
  • float_vector - vector attribute. You can use the same KNN and auto-embedding options as in CREATE TABLE
  • adding engine='columnar' to any attribute (except for json) will make it stored in the columnar storage

Important notes:

  • ❗It's recommended to backup table files before ALTERing it to avoid data corruption in case of a sudden power interruption or other similar issues.
  • Querying a table is impossible while a column is being added.
  • Newly created scalar attributes are set to 0.
  • Newly added float_vector columns without MODEL_NAME are initialized with zero vectors.
  • If you add a float_vector column with MODEL_NAME and FROM, existing rows are embedded automatically during ALTER TABLE ... ADD COLUMN.
  • When MODEL_NAME is specified, FROM is required. Use FROM='' to embed from all text fields and string attributes.
  • ALTER will not work for distributed tables and tables without any attributes.
  • You can't delete the id column.
  • When dropping a field which is both a full-text field and a string attribute the first ALTER DROP drops the attribute, the second one drops the full-text field.
  • Adding/dropping full-text field is only supported in the RT mode.
‹›
  • Example
Example
📋
mysql> desc rt;
+------------+-----------+
| Field      | Type      |
+------------+-----------+
| id         | bigint    |
| text       | field     |
| group_id   | uint      |
| date_added | timestamp |
+------------+-----------+
mysql> alter table rt add column test integer;
mysql> desc rt;
+------------+-----------+
| Field      | Type      |
+------------+-----------+
| id         | bigint    |
| text       | field     |
| group_id   | uint      |
| date_added | timestamp |
| test       | uint      |
+------------+-----------+
mysql> alter table rt drop column group_id;
mysql> desc rt;
+------------+-----------+
| Field      | Type      |
+------------+-----------+
| id         | bigint    |
| text       | field     |
| date_added | timestamp |
| test       | uint      |
+------------+-----------+
mysql> alter table rt add column title text indexed;
mysql> desc rt;
+------------+-----------+------------+
| Field      | Type      | Properties |
+------------+-----------+------------+
| id         | bigint    |            |
| text       | text      | indexed    |
| title      | text      | indexed    |
| date_added | timestamp |            |
| test       | uint      |            |
+------------+-----------+------------+
mysql> alter table rt add column title text attribute;
mysql> desc rt;
+------------+-----------+------------+
| Field      | Type      | Properties |
+------------+-----------+------------+
| id         | bigint    |            |
| text       | text      | indexed    |
| title      | text      | indexed    |
| date_added | timestamp |            |
| test       | uint      |            |
| title      | string    |            |
+------------+-----------+------------+
mysql> alter table rt drop column title;
mysql> desc rt;
+------------+-----------+------------+
| Field      | Type      | Properties |
+------------+-----------+------------+
| id         | bigint    |            |
| text       | text      | indexed    |
| title      | text      | indexed    |
| date_added | timestamp |            |
| test       | uint      |            |
+------------+-----------+------------+
mysql> alter table rt drop column title;
mysql> desc rt;
+------------+-----------+------------+
| Field      | Type      | Properties |
+------------+-----------+------------+
| id         | bigint    |            |
| text       | text      | indexed    |
| date_added | timestamp |            |
| test       | uint      |            |
+------------+-----------+------------+

Updating table FT settings in RT mode

ALTER TABLE table ft_setting='value'[, ft_setting2='value']

You can use ALTER to modify the full-text settings of your table in RT mode. However, it only affects new documents and not existing ones. Example:

  • create a table with a full-text field and charset_table that allows only 3 searchable characters: a, b and c.
  • then we insert document 'abcd' and find it by query abcd, the d just gets ignored since it's not in the charset_table array
  • then we understand, that we want d to be searchable too, so we add it with help of ALTER
  • but the same query where match('abcd') still says it searched by abc, because the existing document remembers previous contents of charset_table
  • then we add another document abcd and search by abcd again
  • now it finds the both documents and show meta says it used two keywords: abc (to find the old document) and abcd (for the new one).
‹›
  • Example
Example
📋
mysql> create table rt(title text) charset_table='a,b,c';
mysql> insert into rt(title) values('abcd');
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id                  | title |
+---------------------+-------+
| 1514630637682688054 | abcd  |
+---------------------+-------+
mysql> show meta;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 1     |
| total_found   | 1     |
| time          | 0.000 |
| keyword[0]    | abc   |
| docs[0]       | 1     |
| hits[0]       | 1     |
+---------------+-------+
mysql> alter table rt charset_table='a,b,c,d';
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id                  | title |
+---------------------+-------+
| 1514630637682688054 | abcd  |
+---------------------+-------+
mysql> show meta
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 1     |
| total_found   | 1     |
| time          | 0.000 |
| keyword[0]    | abc   |
| docs[0]       | 1     |
| hits[0]       | 1     |
+---------------+-------+
mysql> insert into rt(title) values('abcd');
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id                  | title |
+---------------------+-------+
| 1514630637682688055 | abcd  |
| 1514630637682688054 | abcd  |
+---------------------+-------+
mysql> show meta;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 2     |
| total_found   | 2     |
| time          | 0.000 |
| keyword[0]    | abc   |
| docs[0]       | 1     |
| hits[0]       | 1     |
| keyword[1]    | abcd  |
| docs[1]       | 1     |
| hits[1]       | 1     |
+---------------+-------+

Renaming a real-time table

You can change the name of a real-time table in RT mode.

ALTER TABLE table_name RENAME new_table_name;

NOTE: Renaming a real-time table requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.

‹›
  • Example
Example
📋
ALTER TABLE table_name RENAME new_table_name;
‹›
Response
Query OK, 0 rows affected (0.00 sec)

Updating table FT settings in plain mode

ALTER TABLE table RECONFIGURE

ALTER can also reconfigure an RT table in the plain mode, so that new tokenization, morphology and other text processing settings from the configuration file take effect for new documents. Note, that the existing document will be left intact. Internally, it forcibly saves the current RAM chunk as a new disk chunk and adjusts the table header, so that new documents are tokenized using the updated full-text settings.

‹›
  • Example
Example
📋
mysql> show table rt settings;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| settings      |       |
+---------------+-------+
1 row in set (0.00 sec)
mysql> alter table rt reconfigure;
Query OK, 0 rows affected (0.00 sec)
mysql> show table rt settings;
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| settings      | morphology = stem_en |
+---------------+----------------------+
1 row in set (0.00 sec)

Rebuilding a secondary index

ALTER TABLE table REBUILD SECONDARY

You can also use ALTER to rebuild secondary indexes in a given table. Sometimes, a secondary index can be disabled for the entire table or for one or multiple attributes within the table:

  • When an attribute is updated, its secondary index gets disabled.
  • If Manticore loads a table with an old version of secondary indexes that is no longer supported, the secondary indexes will be disabled for the entire table.

ALTER TABLE table REBUILD SECONDARY rebuilds secondary indexes from attribute data and enables them again.

Additionally, an old version of secondary indexes may be supported but will lack certain features. REBUILD SECONDARY can be used to update secondary indexes.

‹›
  • Example
Example
📋
ALTER TABLE rt REBUILD SECONDARY;
‹›
Response
Query OK, 0 rows affected (0.00 sec)

Rebuilding a KNN index

ALTER TABLE table REBUILD KNN

The command reprocesses all vector data in the table and rebuilds the KNN index from scratch.

‹›
  • Example
Example
📋
ALTER TABLE rt REBUILD KNN;
‹›
Response
Query OK, 0 rows affected (0.00 sec)

Rebuilding embeddings

ALTER TABLE table REBUILD EMBEDDINGS column_name

This command regenerates embeddings for one target float_vector column that has MODEL_NAME and FROM configured.

Use it when you want to rebuild vectors for an existing embedding column, for example when you want to reprocess rows after adding the column later with ALTER TABLE ... ADD COLUMN, or when you want to force regeneration for all rows.

Important behavior:

  • The column name is mandatory. The command rebuilds one embedding column at a time.
  • It regenerates embeddings for all rows in that column, not only rows with zero vectors.
  • It also overwrites rows whose vectors were inserted manually, and rows where () was used to skip generation and store a zero vector.
  • The target column must be an indexed float_vector with an embedding model configured.
  • FROM='' is allowed and means "use all text fields and string attributes".

Manticore does not persist whether the current vector in that column was generated automatically, provided explicitly by the user, or created from (). If you run REBUILD EMBEDDINGS, the stored values are regenerated from the configured FROM source for every row in the column, including rows whose current value is an all-zero vector.

‹›
  • Example
Example
📋
ALTER TABLE products ADD COLUMN embedding FLOAT_VECTOR KNN_TYPE='hnsw' HNSW_SIMILARITY='l2' MODEL_NAME='sentence-transformers/all-MiniLM-L6-v2' FROM='title';
ALTER TABLE products REBUILD EMBEDDINGS embedding;
‹›
Response
Query OK, 0 rows affected (0.00 sec)

Updating attribute API key (for embeddings generation) in RT mode

ALTER can be used to modify an API key when a remote model is used for auto-embeddings:

ALTER TABLE table_name MODIFY COLUMN column_name API_KEY='key';
‹›
  • Example
Example
📋
ALTER TABLE rt MODIFY COLUMN vector API_KEY='key';

Changing a distributed table

To change the list of local or remote nodes in a distributed table, follow the same syntax you used to create the table. Just replace CREATE with ALTER in the command and remove type='distributed':

ALTER TABLE `distr_table_name` [[local='local_table_name'], [agent='host:port:remote_table'] ... ]

NOTE: Changing the schema of a distributed table online requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.

‹›
  • Example
Example
📋
ALTER TABLE local_dist local='index1' local='index2' agent='127.0.0.1:9312:remote_table';
K-nearest neighbor vector search
Last modified: March 11, 2026