The best solution heavily depends on the definition of "best". You may be looking for simplicity, reliability, performance or disk space optimization or other things.
infinite amount of stories, and about ~40 sentences per story
Few sentences, possibly many stories.
user can input a word or phrase
We need phrase search. (Maybe across sentence boundaries?)
- "Sentences" are substrings simply separated by dot and space(
- Resources (RAM, disk space) may be a limiting factor.
- Retrieving the whole body and searching for individual sentences are equally frequent tasks.
Postgres 9.6 introduced exactly what you need. The release notes:
Full-text search can now search for phrases (multiple adjacent words)
The latest Postgres release and a text search index will go a long way for performance in either case.
You already considered to:
slap the body of the story onto a 4th column in the Story model
But an array adds 24 bytes + 1 or more bytes per sentence and makes indexing much more complicated. (As well as pretty much all other operations.) I would exclude the array solution.
storyline table with one row per sentence makes indexing simple again, but adds ~ 40 bytes per sentence. Finding and retrieving sentences is simple. But all write operations are more complicated.
About storage size in Postgres:
Consider a single table with
body as plain
text column. Splitting the string into sentences on the fly is fast (with a simple definition of "sentence"). Smaller total storage size may be more important for performance with big tables (fewer pages to fetch). And we only need to index one column. In short:
- dead simple storage and handling
- minimum disk space
- still a hot contender for best performance
CREATE TABLE story (
story_id serial PRIMARY KEY
, author_id int REFERENCES author -- NOT NULL?
, title text -- NOT NULL?
, body text -- NOT NULL?
FTS index, assuming English language:
CREATE INDEX story_body_english_fts_idx ON story USING GIN (to_tsvector('english', body));
Query to return whole story:
WHERE to_tsvector('english', body) @@ phraseto_tsquery('english', 'Lewis Carroll');
Query to return sentences:
SELECT story_id, sentence_nr, sentence
FROM story, unnest(string_to_array(body, '. ')) WITH ORDINALITY x(sentence, sentence_nr)
WHERE to_tsvector('english', body) @@ phraseto_tsquery('english', 'Lewis Carroll')
AND to_tsvector('english', sentence) @@ phraseto_tsquery('english', 'Lewis Carroll');
The query demonstrates a phrase example. But it works for single words as well.
WHERE condition is logically redundant but necessary to use the index on
body. This retrieves only stories with qualifying sentences, then the recheck on unnested sentences (repeating the same condition) is cheap.
unnest(string_to_array(body, '. ')) WITH ORDINALITY:
Note: The check on the whole text body can find phrases across sentence boundaries - unlike searching individual sentences. But the recheck on
sentence excludes such matches. You can fine-tune one or the other way ...
There are many more options with FTS: prefix matching, other languages, no language (no stemming or thesaurus), phrases with words in between ...