Learn how to dramatically speed up PostgreSQL full-text search by persisting TSVectors and using GIN indexes in Rails apps.

The RubyLA blog offers insights, tutorials, and community updates for Ruby developers in Los Angeles and beyond. Developers can explore Ruby language features, web development frameworks, and best practices for building Ruby applications. Additionally, the blog covers Ruby meetups, events, and job opportunities in the Los Angeles area, fostering collaboration and networking within the local Ruby community.

RUBYLAND

PostgreSQL full-text search can become slow at scale when tsvectors are computed on-the-fly during queries. By persisting tsvectors in a dedicated column with a GIN index, query performance improved 118× (from ~283ms to ~2.4ms) on 100k rows. The optimization shifts PostgreSQL from sequential scans to bitmap index scans, reducing work from processing all rows to only matching ones. Implementation involves adding a tsvector column, creating a GIN index concurrently, setting up a database trigger for automatic updates, and configuring pg_search to use the persisted column instead of computing vectors at query time.

Speeding Up PostgreSQL Full-Text Search with Persistent TSVectors