Slow degradation of index

[alesji]

Bug Description
I am creating fulltext index over recipes table on recipe name.
That table is updated frequently, inserted into it frequently too...

CALL paradedb.create_bm25(
        index_name => 'ngram_recipes_name_search',
        schema_name => 'public',
        table_name => 'recipes',
        key_field => 'id',
        text_fields => '{name_search: {tokenizer: {type: "ngram", min_gram: 3, max_gram: 3, prefix_only: false}}}'
);

After few thousands of updates/inserts the search index starts to be incredibly slow to insert incredibly slow to search in (from few ms when fresh to seconds when really let unchecked for a long time)

when i run the create_bm25 function again, it recreates the index and is running fast again... I have no idea what is happening, but this is basically unusable. I tried to do it on fresh docker install (latest version) with simple table and it does it too...
Not sure how can i help, but if i can I will try. I love the speed when the index is fresh, but the degradation of the index is seriously make it unusable for any dynamic data

[neilyio]

Thanks for reporting this. To make sure I understand, you're seeing this slowdown after a few thousand writes to the index.

Could you clarify that means a few thousand separate transactions, each with around one row being updated?

[alesji]

Thanks for reporting this. To make sure I understand, you're seeing this slowdown after a few thousand writes to the index.

Could you clarify that means a few thousand separate transactions, each with around one row being updated?

exactly, it is thousands of separate updates and inserts into the indexed column, very often those updates are with the exactly same data (its just how the SW works, it updates columns even if they didnt change).

I let it run for last few hours just now... doing updates on data without really changing the data and it still got the slow down - so maybe that might be some clue? not sure...

[alesji]

Hello, are you able to replicate this bug or should i prepare exact step by step to replicate it? I might have some time this weekend to do it if needed

[philippemnoel]

Hello, are you able to replicate this bug or should i prepare exact step by step to replicate it? I might have some time this weekend to do it if needed

If you have exact steps to replicate it would be extremely helpful. We haven't managed to replicate just yet

[alesji]

I just tried to do vaccuum full, when the degradation occured and it completely fixed the issue... so I am not sure if it is a bug or just feature, it happens quite fast (only one thousand inserts/updates) and the degradation is quite big (like 10x slower queries)... So I guess I will still prepare the step by step so you can see for yourself, never happened to me before with any other indexes that the slow down would be so fast and so big, so maybe it should be checked anyway.

[rebasedming]

I've replicated this bug. Using the test table and index in the quickstart, if I do

insert into mock_items (description, rating, category) values ('test', 3, 'Electronics');
\watch 0.001

After 1000 rows have been inserted, the insert time seems to have crept up by about 100ms.

I will prioritize fixing this asap.