We use the FULLTEXT index to support fast search queries against our keywords database with 150M rows.
To ensure new inserts and updates become available immediately, we use the OPTIMIZE TABLE keywords FLUSH right after any upserts as recommended.
Recently, I became aware that some matches become unavailable from time to time. I suspected the OPTIMIZE command but realized this also happens without manual flushing.
Further testing shows that any updated row becomes unavailable until the index rebuild completes (apparently) within a few minutes.
I would expect the changed rows to remain in the index until the background process overwrites. The changes don’t even modify the indexed column but only some other unindexed columns with metadata.
Is it possible to avoid the unavailability somehow?
hello, this is happening because when you do updates, the row is moving from columnstore to rowstore during the transaction. if these updates are infrequent / batched, you could run the update with Update ... option(COLUMNSTORE_TABLE_LOCK_THRESHOLD = 0); If you do this and you set columnstore_disk_insert_threshold small, you will avoid having any rows in the rowstore segment, which should solve your issue.
However, you should note that this will force table locks which will break concurrent updates and deletes, so you should only do this if the updates are batched.
if this won’t help you, let me know and I’ll see what else we can come up with
Unfortunately, batching is not ideal in this case as upserts are invoked by concurrent on-demand user requests.
The transactions are small with approx. 10-1000 updates/inserts, and we do never delete rows from this table.
We have other background processes upserting rows to this table. I guess the columnstore_disk_insert_threshold option will affect those transactions too even without the COLUMNSTORE_TABLE_LOCK_THRESHOLD query option?
We could have an internal queue batching every five seconds as the last resort and keep the loading indicator present.
If you have any other suggestions, please let be know.
