~5M documents, would love to have ~223M, with the text of ~10-1000 words each. It must be utf8 and support any spoken language. All words in a text are unique and I can also order them or do whatever needed to have better performance. Full text is saved in another location. Data is almost read-only. For performance, I can generate data, add indexes and mark it as read-only.

Data structure:

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


  • Results must come under 0.1s
  • Sample queries:

    1. find text with words: x or y or z
    2. find text with words: x and y and z
    3. would be great to find text with part of the word: hous would find house, housing etc.


  1. Is it possible to make it happen with MySQL/PostgreSQL?
  2. What database solutions you would offer?
  3. What config to look at?
  4. Index type?
  5. Query sample?
  6. Server requirements for RAM/HDD/CPU?
  7. Better more powerful machines or array of simpler?
  • What kind of text generates 1,000 unique words? Feb 18 '17 at 16:56
  • I don't even know where you would get the sample data to test this.. Wikipedia? Feb 18 '17 at 17:01
  • it's all kind of articles and unique words are only in this table to have smaller storage requirements and faster search. gathering data is already solved. full text articles are in another db which is MySQL Feb 18 '17 at 17:28
  • MySQL's FULLTEXT has perhaps all of your requirements.
    – Rick James
    Feb 19 '17 at 4:53
  • See also Solr..
    – Rick James
    Feb 19 '17 at 4:53

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

  id            int PRIMARY KEY,
  reference_id  int, -- no idea what this is
  document      text,
CREATE INDEX ON foo USING GIST (to_tsvector(document));
  • thank you, will definitely try it. reference_id is an id of the data source and also it connects to other bits of data surrounding this entry. It's an integer. Do you think i should check elasticsearch or similar engines for this? Feb 18 '17 at 19:14
  • 1
    Depends, there are a thousand things you could check. I'd start here, if it doesn't work for you post and ask a question. If you can't get it to work time to look elsewhere. You can't just armchair design all this kind of stuff without the data and the application. You gotta try it and bleed a bit. Feb 19 '17 at 1:58
  • That's what I will do. I'll try your method. I've already bled several times with databases, so came here for suggestions :-) I understand that it's hard to tell without the data in place. I found many good tips on the topic and still reading while doing another part of the API. Thanks again. Feb 19 '17 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.