# Updating table schema

## Updating table schema in RT mode

<!-- example ALTER -->

```sql
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`](Creating_a_table/Data_types.md#Float-vector)
* adding `engine='columnar'` to any attribute (except for json) will make it stored in the [columnar storage](Creating_a_table/Data_types.md#Row-wise-and-columnar-attribute-storages)

#### Important notes:
* ❗It's recommended to **backup table files** before `ALTER`ing 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](Read_this_first.md#Real-time-mode-vs-plain-mode).

<!-- request Example -->
```sql

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      |            |
+------------+-----------+------------+
```

<!-- end -->

## Updating table FT settings in RT mode

<!-- example ALTER FT -->

```sql
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](Read_this_first.md#Real-time-mode-vs-plain-mode). However, it only affects new documents and not existing ones. To apply the new settings to existing documents, [reindex them](Updating_table_schema_and_settings.md#Reindexing-existing-documents-after-changing-FT-settings).
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).

<!-- request Example -->
```sql
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     |
+---------------+-------+
```

<!-- end -->

## Renaming a real-time table

<!-- example Renaming RT tables -->

You can change the name of a real-time table in RT mode.
```sql
ALTER TABLE table_name RENAME new_table_name;
```

> NOTE: Renaming a real-time table requires [Manticore Buddy](Installation/Manticore_Buddy.md). If it doesn't work, make sure Buddy is installed.

<!-- request Example -->
```sql
ALTER TABLE table_name RENAME new_table_name;
```

<!-- response Example -->

```sql
Query OK, 0 rows affected (0.00 sec)
```

<!-- end -->

## Updating table FT settings in plain mode

<!-- example ALTER RECONFIGURE -->
```sql
ALTER TABLE table RECONFIGURE
```

`ALTER TABLE ... RECONFIGURE` reloads the full-text settings of an RT table in [plain mode](Creating_a_table/Local_tables.md#Defining-table-schema-in-config-%28Plain-mode%29). The new tokenization, morphology, and other text-processing settings from the configuration file apply to documents inserted or replaced after the command. Existing documents retain the full-text index built with the previous settings. To apply the new settings to those documents, [reindex them](Updating_table_schema_and_settings.md#Reindexing-existing-documents-after-changing-FT-settings). The command flushes the current RAM chunk to a new disk chunk and updates the table header.

<!-- request Example -->
```sql
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)
```
<!-- end -->

## Reindexing existing documents after changing FT settings

Changing a full-text setting affects indexing when a document is inserted or replaced. To apply a new setting to existing documents, reinsert them after changing the setting. This applies to wildcard indexing, tokenization, morphology, wordforms, and other full-text settings.

In plain mode, update the table configuration and run `ALTER TABLE <table_name> RECONFIGURE` first. In RT mode, change the setting with `ALTER TABLE` as described above. Pause application writes before creating the dump and keep them paused until the replay completes; otherwise, the replay can overwrite newer values written after a document was exported.

Create a data-only dump that emits `REPLACE` statements, then replay it into the same table:

```bash
mysqldump -h0 -P9306 --replace -t -c -e --net-buffer-length=16m manticore '<table_name>' > '<table_name>-reindex.sql'
mysql -h0 -P9306 < '<table_name>-reindex.sql'
```

`-t` omits `DROP` and `CREATE TABLE` statements, while `--replace` makes the dump replace documents with matching IDs. Each replayed document is indexed with the current full-text settings.

If you pipe the dump directly into `mysql` instead of saving it to a file, `--skip-lock-tables` is required:

```bash
mysqldump -h0 -P9306 --skip-lock-tables --replace -t -c -e --net-buffer-length=16m manticore '<table_name>' | mysql -h0 -P9306
```

Without `--skip-lock-tables`, `mysqldump` holds a read lock while exporting, so concurrent `REPLACE` statements can fail with `table '<table_name>' is locked` when the dump spans multiple batches. The option is not required for the two-step file workflow because `mysqldump` exits and releases the lock before the replay starts.

The table must store every full-text field: `mysqldump` cannot back up tables with non-stored fields. For replicated tables, use the [replication-mode backup instructions](Securing_and_compacting_a_table/Backup_and_restore.md#Backup-and-restore-with-mysqldump).

## Rebuilding a secondary index

<!-- example ALTER REBUILD SECONDARY -->
```sql
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.

<!-- request Example -->
```sql
ALTER TABLE rt REBUILD SECONDARY;
```

<!-- response Example -->

```sql
Query OK, 0 rows affected (0.00 sec)
```

<!-- end -->


## Rebuilding a KNN index

<!-- example ALTER REBUILD KNN -->
```sql
ALTER TABLE table REBUILD KNN
```

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

<!-- request Example -->
```sql
ALTER TABLE rt REBUILD KNN;
```

<!-- response Example -->
```sql
Query OK, 0 rows affected (0.00 sec)
```

<!-- end -->

## Rebuilding embeddings

<!-- example ALTER REBUILD EMBEDDINGS -->
```sql
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.

<!-- request Example -->
```sql
ALTER TABLE products ADD COLUMN embedding FLOAT_VECTOR KNN_TYPE='hnsw' HNSW_SIMILARITY='l2' MODEL_NAME='Xenova/all-MiniLM-L6-v2' FROM='title';
ALTER TABLE products REBUILD EMBEDDINGS embedding;
```

<!-- response Example -->
```sql
Query OK, 0 rows affected (0.00 sec)
```

<!-- end -->

## Updating attribute API parameters (for embeddings generation) in RT mode

<!-- example api_key -->

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

```sql
ALTER TABLE table_name MODIFY COLUMN column_name API_KEY='key';
ALTER TABLE table_name MODIFY COLUMN column_name API_URL='url';
ALTER TABLE table_name MODIFY COLUMN column_name API_TIMEOUT='seconds';
```

<!-- request Example -->
```sql
ALTER TABLE rt MODIFY COLUMN vector API_KEY='new-key';
ALTER TABLE rt MODIFY COLUMN vector API_URL='https://custom-api.example.com/v1/embeddings';
ALTER TABLE rt MODIFY COLUMN vector API_TIMEOUT='30';
```

**Notes:**
- `API_KEY`: The new API key is validated during the ALTER operation by making a real API request.
- `API_URL`: Set to an empty string (`''`) to revert to the default provider endpoint.
- `API_TIMEOUT`: Set to `'0'` to use the default timeout (10 seconds). Must be a non-negative integer.

<!-- end -->

## Changing a distributed table

<!-- example local_dist -->

To change the list of local or remote nodes in a distributed table, follow the same syntax you used to [create the table](Creating_a_table/Creating_a_distributed_table/Creating_a_local_distributed_table.md#Creating-a-local-distributed-table). Just replace `CREATE` with `ALTER` in the command and remove `type='distributed'`:

```sql
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](Installation/Manticore_Buddy.md). If it doesn't work, make sure Buddy is installed.

<!-- request Example -->
```sql
ALTER TABLE local_dist local='index1' local='index2' agent='127.0.0.1:9312:remote_table';
```

<!-- end -->
<!-- proofread -->
