Did You Know PostgreSQL Does Full-Text Search?

📚 PostgreSQL Mistakes and How to Avoid Them: https://mng.bz/vKd4 Follow me on: 🦣 https://fosstodon.org/@vyruss 🦋 https://bsky.app/profile/vyruss.org   / vyruss   How to navigate the rich but confusing field of (Full) Text Search in PostgreSQL. A short introduction will explain the concepts involved, followed by a discussion of functions, operators, indexes and collation support in Postgres in relevance to searching for text. Examples of usage will be provided, along with some stats demonstrating the differences. Talk from the PostgreSQL devroom at FOSDEM 2020 Slides: https://vyruss.org/computing/slides/f... 0:00 Introduction 2:40 What is Text? 5:19 What is Text Search? 6:18 Text search operators in PostgreSQL 8:43 What is Full Text Search (FTS)? 10:53 Documents? Tokens? 13:20 Stop words 14:44 Stemming 16:28 FTS representation in PostgreSQL 18:53 Dictionaries in PostgreSQL 20:43 Text matching example (3) 24:44 An example table 28:43 Text indexing 32:05 FTS indexing 35:28 GIN, GIST indexed operations 36:28 Super useful modules 38:02 Other index types 39:15 Free text but not natural? 42:23 Did trigrams help? 43:05 Other neat trigram tricks 43:32 Character set support 44:11 Collation in PostgreSQL 45:20 Other types of documents + JSON 45:59 Finally, maintenance 46:53 The curious Case of TEXT NAME