Some ideas about message field optimization

[hsuanyi]

For service logs, it is quite common to have a message field that has full, long txt. Clickhouse does have tokenbf_v1 to skip scanning but quesma does not have a good way to support it.

This is how i would think quesma could support it (However, a clean interface needs to be worked out):

---

• Users need to have another field that mirrors message field but this field needs to be in all lower case. Let's call it _lower_case_messge
• tokenbf_v1 needs to be built against this _lower_case_messge.
• At transformation time. for filter such as message ILIKE 'token'. rewrite it to _lower_case_messge LIKE '%searchword%' AND hasToken(_lower_case_messge, searchword)

---

Does this make sense? Is this something quesma would be interested to have. I have a hacky implementation and i want to get signals before moving to a PR/

[jakozaur]

Great idea. Feel free to implement.

Some points, though. You do not need to duplicate data. You can just do an index on lowercased data, e.g.:

    INDEX body_idx lower(body) TYPE ngrambf_v1(4, 60000, 5, 0) GRANULARITY 1,

One example: https://signoz.io/docs/userguide/logs_clickhouse_queries/

Currently, for some users, we just manually issued some ALTER TABLE, but built in support would be nice.

Moreover, ngram are quite popular choice in many use cases. In observability and Security, the user expects InvalidPassword to match InvalidPasswordError, which breaks a lot of tokenizations.