Text search at scale with ClickHouse ®

TheirStack.com is a market intelligence tool designed for B2B SaaS companies to identify potential buyers by analyzing technologies mentioned in job postings from numerous companies. Initially using PostgreSQL for text search, the team encountered limitations due to case sensitivity, tokenization, and lack of custom dictionary support, leading them to explore alternatives such as regex matching and trigram indices. Despite some improvements, data growth rendered these methods inefficient, prompting a switch to ClickHouse® via Tinybird, providing faster pattern matching on larger datasets. The use of the multiMatchAllIndices function allowed for efficient parsing of numerous patterns in a single query, significantly reducing data read and query count. This approach facilitated the transition from 180,000 monthly queries to a drastically lower number, enhancing performance and efficiency in identifying relevant technologies for TheirStack's clients.