Postgres Full Text Search

Video explores PostgreSQL full text search capabilities, creating and indexing text documents and providing search capabilities.
Postgres introduces two new datatypes to support full test search, tsvector and tsquery.
Tsvector represents document in form suitable for searching and tsquery a predicate that documents you’re searching on needs to satisfy.
Postgres also supports misspelling suggestions with pg_trgm trigram extension and synonyms searches by dictionary and search configuration extension.
To make you searches performant we’ll use indexes on search columns.
Finally, we demonstrate search concepts in action on java spring boot demo web app.

github link:

00:00 - intro
00:27 - full text search
01:45 - lucene index & search
02:16 - sample pg project
03:56 - pg regular text search operators
06:14 - pg full text search parts
07:27 - tsvector
09:30 - stop words
11:02 - unnacent
11:30 - parsing and translating
12:48 - tsquery
14:49 - @@ operator
16:00 - || && operators
16:59 - ranking
18:59 - ts_headline
19:44 - synonyms
21:00 - indexing
25:50 - select for web ui
27:14 - synonyms
30:49 - java spring app example
32:48 - outro

[jahidem]

Amazing! Thank you so much for making this video 🙏

[undefined]

Such an in-depth explanation, very informative, keep it up, sir!

[WIZOVSKI]

Your videos are great! Thank you very much!

[fabsync]

fantastic video! It will be nice to see a practical project built with this.. like searching pdf content or a library.. etc

[lolcatwill]

what happens if you look up "of" or "off"?

[ole-jakobolsen]

If I could give you more than one of both thumbs ups and github stars, I would. Epic.

[gospher]

Man, this is a treasure! please keep doing more advanced stuff like this!

[NguyễnPhương-yh]

hi sir, i'm having a probem with:
alter table book
add column fts_doc tsvector
generated always as (
setweight(to_tsvector('english', title), 'A')
|| setweight(to_tsvector('simple', description), 'A')
)
stored;
=>> And the fts_doc result is: "''educational':6A 'ending':5A 'happy':4A 'motivation':3A 'strong':2A"


but i think it should be "'educ':6A 'end':5A 'happi':4A'motiv':3A 'strong':2A" => This result comes from:
select setweight(to_tsvector('Be Strong, Motivation, Happy Ending, Educational'), 'A')

Why is it? Is there anything wrong that makes the diffrence?

[thrasosthrasos]

great content, thanks for your effort.

[arnaudpoutieu]

Thanks for such insightful content. Real enterprise grade stuff. Gratitudes!!!

[juanalbertoboada]

Fantastic video. This is a masterclass.

I am pretty new to PostgreSQL (I have experience of other SQL engines), I just hit the jackpot with your video.

Keep doing videos like this!

[NguyễnPhương-yh]

In your java spring app example, the search-word is "albert -einstein life" and the result does not match the whole search-word (without the word " -einstein"). Can you please answer why the app still gives back search result? I am having problems with search-word from users that does not have in the tsvector-column (in this case, my app will show zero result). Thank you!