Postgresql how to speed up queries?

Question

PostgreSQL

There is an application built as a database per client.
Each database has its own set of tables with its own fields, i.e. different clients have different data structures.
One client may have 2-3 tables for 10-100 thousand records, another - 5-10 tables for 1-10 million records.
Each table has dozens, sometimes hundreds of columns.
For each column, the client can search. At a time, usually, the client makes a setting for himself, according to which he sees 10-20 columns, but it is impossible to predict what he will need in the future.
Obviously, creating an index on each column to speed up the search will be extremely expensive. At the same time, the search for each type of data, of course, is different. For int <, >, =, for strings contains, not contains, starts with, ends with, etc.

There are a couple of solution ideas:
1. We can collect statistics on which columns the client searches more often and, based on it, automatically create or delete indexes (create index concurrently, so as not to block his work), while taking into account the nature of the requests, so that the correct indexes are put down.
2. Somehow upload all client data to elastic or a similar system and search through it. I would like to avoid this for obvious reasons in the form of expenses for the cluster, for development, for support and additional. hardware

Are there any options how such a problem is solved when the data scheme is not fixed and the search can work on any existing column? At least keywords for Google.

Answer 1

ClickHouse (CH) seems to be good for such purposes.

Answer 2

Search on any columns is solved by inverted indexes.
If you want to stay within PostgreSQL, you can switch to jsonb fields with a GIN index.
Also in elasticsearch, such queries work well, but you need to be able to prepare it.