Multi-table Full Text Search with Postgres
summary
A real feature evolves from LIKE to Postgres full text search across multiple tables using database views and materialized views; the build uses the Textacular gem for tsquery helpers and Scenic to manage view SQL in migrations. Indexing choices matter — GIN is ~3× faster to query but larger and slower to build than GIST — and raw multi-table queries are an order of magnitude slower than querying a materialized view, which yields large practical speedups and avoids external search services.
AI-written summary · about
chapters (28)
- 0:00Multi-table Full Text Search Postgres
- 1:51I'm going to talk to you about
- 2:31Materialized Views
- 2:43Other Options
- 3:51Exact substrings
- 4:54Search by Author's name
- 5:51Poor results
- 6:17natural language searching
- 8:28That's a lot of SQL.
- 9:02Stored in the Database
- 9:28Multi- source
- 11:45Will it work with full text search?
- 12:01Takes care of the Full Text Search portions of queries
- 14:20CREATE Migration
- 14:50How resistant to change is it?
- 15:39UPDATE Migration
- 16:55Can't be dumped to db/schema.rb
- 17:16Scenic
- 20:14Performance
- 20:33Views can't be indexed
- 20:44btree
- 23:11Pre-populate the results of the view
- 23:35Query against result set
- 24:34What about pre-built solutions?
- 25:33development
- 25:54Needs to be faked in tests
- 26:35Remove a data concern from your database
- 26:56that doesn't require any new dependencies
related talks
34mFull-Text Search in PostgreSQL
43mFull-Text-Search Explained from A to Z with French Food
59mFull-text search - seek and ye shall find
28mElasticsearch-Quality Full-Text Search in Postgres via Tantivy
33mPostgres, the Best Tool You're Already Using
42mPostgreSQL Tips & Tricks For App Devs
24mSearch From the Ground Up
22mFrom Postgres full text search to Retrieval Augmented Generative search