Postgres Full Text Search
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- 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:
github.com/kanezi/postgres-fts
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
fantastic video! It will be nice to see a practical project built with this.. like searching pdf content or a library.. etc
If I could give you more than one of both thumbs ups and github stars, I would. Epic.
much appreciated!!!
Man, this is a treasure! please keep doing more advanced stuff like this!
thanks, I'll try! I'm also open for topic suggestions...
Such an in-depth explanation, very informative, keep it up, sir!
Thanks for such insightful content. Real enterprise grade stuff. Gratitudes!!!
Glad you find it helpful!
Your videos are great! Thank you very much!
great content, thanks for your effort.
Thanks man!
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?
in your fts_doc column part "to_tsvector('simple', description)" uses "simple" configuration that leaves the words as is, and just tokenizes description. Try using to_tsvector('english', description) instead.
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!
is your question why search: "albert -einstein life" on the beginning of tutorial gives results? Why do I get results Albert + life, but also only Albert and only life?
Thanks for your reply. Yes, that is my question, my Postgres FTS only gives results if all the search words are in ts_vector. What i want is to still recieve results even if there are some missing keywords like your example (2/3 searchword not 3/3). Thanks.
how are you searching on ts_vector column / converting to ts_query? In my case I'm using "document@@ websearch_to_tsquery(:q)". I'm using postgres build in function "websearch_to_tsquery" to convert user input to ts_query. Have a look at repository in video description for query example - FtsDocumentRepository class.