CrateDB need timeout to see changes from insert/update/delete queries

Hi, I am using the CrateDB community edition to build a pilot project. Currently, I have this problem, if I make a select query right after making insert/update/delete ones, the select will return nothing changes. However, if I wait for few seconds, the select query will return changes in DB. This problem degrades the speed of our application dramatically.
How can we fix this problem?
Thank you in advance!

Hi @minh86

Welcome to the Crate Community.

Because of the underlying append only index / data structure of CrateDB / Lucene, changes are only available after a Refresh or a Flush. By default the refresh_interval - a table level setting - is set to 1000ms. Meaning that after max 1s the newest changes are guaranteed to be available. You can manually trigger a refresh on a table with the following SQL command:

REFRESH TABLE (table_ident [ PARTITION (partition_column=value [ , ... ])] [, ...] )

If you are doing lots of updates / deletes or instantly need the newest changes to always be available, there might be better options than CrateDB :wink:


REFRESH https://crate.io/docs/crate/reference/en/4.5/sql/statements/refresh.html
refresh_interval https://crate.io/docs/crate/reference/en/4.5/sql/statements/create-table.html#sql-create-table-refresh-interval

2 Likes

Thank you very much for your detailed answer!
Best Wish!

2 Likes