Improving Postgres Text Search Speed 7x On Millions of Records | CharityAPI.org

This article discusses how CharityAPI.org improved the speed of their Postgres text search by 760% on millions of records without using Elastic Search or other external tools. They initially added and queried a tsvector field on the organizations table but found that joining this table with another resulted in slower query times. By refactoring their search logic into two separate queries, one to collect organization IDs from the organization_search table and another to retrieve those records from the main organizations table, they were able to achieve a 230% speed increase compared to the previous iteration. This new approach significantly improved search performance, with searches consistently resolving in under 1 second even for the slowest 1% of searches. The article emphasizes that this improvement was achieved entirely within Postgres without needing to introduce another dependency.