performance - Query hundreds millions rows using partial searches in PostgreSQL -

considering simple database containing table foo (name text, description text) , table bar (key text, other_info text) share many-to-many relationship (foo_bar (foo_id, bar_id) ).

the fact is, want obtain foo rows when bar.key contains partial pattern example 'a' or '1234'. want join , like '%a%'. , end of story...

however, table foo contains 10 millions rows , queries take time... moreover, table have hundreds millions of rows! in production environnement, database read-only mode.

so started thinking materialized view as:

foo_view (name text, description text, bar_key text)

which saves me join.

and started using trigram indexes have faster partial matches, , works.

but it's not enough...

i started thinking array saves me join and distinct part as

foo_view (name text unique, description text, bar_keys text[])

i used parray_gin extension have partial searches , indexes arrays. now, faster solution.

but not enough... (for 1 million rows it's not more...)

i started thinking partionnement ten millions rows don't see significative difference...

my database not complex 1 , can create table/view necessary. problem is: i have query hundreds millions of rows using partial search , can't manage fast.

i know hardware has crucial part in performance, that's not can change. maybe later. now, question strictly software-side.

i need know if solutions tried ones or if missed something.