Search 200M documents of text by keyword in text
Just think about your requirements and what you're asking and if an answer can even been given here. Wikipedia has 5.3 M documents. You're talking about a load on that scale. And you want to know whether or not you can query it in 0.1 sec? You're going to have provide the sample text. I'm all about making sample data, but how am I even going to generate sample data for this question/

What you want is enterprise Full-Text Search (FTS): PostgreSQL has that. Whether or not it will perform as well as you intend, I have no idea. I've used it for reasonably large projects, it's worked fantastically.


  would be great to find text with part of the word: hous would find house, housing etc.


It also supports :* which does what you want. so just use hous:*

It is indexable using a GIST index. All of that is in the docs.

{
  id: 1,
  reference_id: 1,
  text: 'text of unique. 10-1000 words each ',
}


So just

CREATE TABLE foo (
  id            int PRIMARY KEY,
  reference_id  int, -- no idea what this is
  document      text,
);
CREATE INDEX ON foo USING GIST (to_tsvector(document));