Create and Drop Indexes Dynamically

CrateDB SQL
1

Hi

Is there anyway to create and drop indexes dynamically?

That is, after the table has been initially created.

Regards

Mark

2

Hi Mark,

That is currently not possible. You can only dynamically add (indexed) columns, after the table has been created, but not add indexes on existing columns. This largely due to how indexing (based on Lucene) works in CrateDB in an append only fashion. Indexes and storage are tightly integrated.

Best practice would be to create a new table with the changes to the index and either use COPY TO | FROM or INSERT INTO newtable SELECT * FROM oldtable .

Best regards
Georg

Creating an index after a table was already created is currently not supported, so think carefully while designing your table definition.

Also see https://crate.io/docs/crate/reference/en/4.5/general/ddl/fulltext-indices.html

3

Hi Georg

OK thanks, we’ll try the other alternatives you suggested.

Regards

Mark