A few weeks ago, we were tasked with building a robust search feature for a job listing application called Overqualified Housewives. You can check out the platform here: recruit.overqualifiedhousewives.com. Search functionality was critical for this application, as it needed to work across multiple fields for a single applicant. These fields included first name, last name, city, state, country, current job title, industry type, functional areas, work category, resume content, and more.

We also had to assign different weightage to different fields. For instance, when an employer searches for "Chennai," applicants whose city is listed as Chennai should appear before those who only have "Chennai" mentioned in their resume. With a database of approximately 17,000 applicants, the search had to be both optimized and fast.

Why We Chose Typesense Initially

After some initial research, we identified Typesense as a promising solution. It offered several features that seemed well-suited for our use case:

• Blazing-fast performance: Typesense is in-memory, making it extremely fast.
• Multi-field search: It supports searching across multiple fields with ease.
• Weighted search: It allows assigning weightage to fields, which aligned perfectly with our requirements.

We implemented Typesense, and it worked quite well at first. However, as we scaled and refined our requirements, several challenges and limitations became apparent.

Challenges with Typesense

1. Seeding Data Was Time-Consuming
   We deployed Typesense in our small Kubernetes (K8s) cluster. Seeding all 17,000 applicants into Typesense took a significant amount of time. Moreover, if we added a new searchable column, we had to re-seed the entire dataset, which was not efficient.
2. Complex Incremental Syncing
   Our main database was PostgreSQL, and keeping it perfectly synced with Typesense was tricky. We set up a cron job to handle the synchronization, which worked most of the time. However, there were occasional delays and mismatches in the data, which created inconsistencies.
3. High Memory Consumption
   We deployed Typesense in a self-managed K8s cluster on a t3.large EC2 instance. While this instance was initially sufficient for our needs, Typesense's high memory usage often caused resource contention, leaving our backend pod struggling to function properly.

Switching to Postgres Full-Text Search

Given these challenges, we started exploring alternatives. PostgreSQL is known for its versatility and rich feature set, so we investigated whether it could handle our search requirements. We discovered Postgres Full-Text Search, which met all our needs while addressing the issues we faced with Typesense.

Implementation in Postgres

We added a column called search_vector to our applicant table to store the weighted tsvector.

ALTER TABLE applicant ADD COLUMN search_vector tsvector;

Next, we created a trigger to update the search_vector column whenever a new applicant was inserted. The search_vector column was populated using the following update command:

UPDATE applicant
SET search_vector =
    setweight(to_tsvector('english', COALESCE(current_job_title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(experienced_functional_area, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(first_name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(last_name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(previously_worked_industry_type, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(total_years_of_work_experience, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(work_category, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(city, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(state, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(country, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(address, '')), 'B') ||
    setweight(to_tsvector('english',
        COALESCE(
            (SELECT string_agg(value, ' ') FROM resume WHERE resume.applicant_id = applicant.id),
            ''
        )
    ), 'D');

To optimize search performance, we created a GIN index on the search_vector column:

CREATE INDEX applicant_search_vector_index ON applicant USING gin(search_vector);

Search Implementation

In our application, we used the following code to perform searches:

const formattedSearchText = searchText.trim().replace(/\s+/g, ' & ');

if (formattedSearchText) {
  applicantQuery = applicantQuery.whereRaw(
    `"a0"."search_vector" @@ to_tsquery('english', ? || ':*')`,
    [formattedSearchText.trim()],
  );
}

With this setup, the search was both fast and reliable, thanks to the GIN index.

Missing Feature: Typo Tolerance

One limitation of our PostgreSQL implementation was the lack of typo tolerance, which was natively supported in Typesense. However, this could be addressed by leveraging the pg_trgm module. By storing all words in a materialized view and using the similarity function from pg_trgm, we could enhance our search functionality with typo tolerance. For more details, refer to this guide.

Conclusion

While Typesense is a powerful search tool, its overhead and challenges made it less suitable for our specific use case. PostgreSQL Full-Text Search provided all the features we needed, along with better integration, efficiency, and lower resource consumption. For our use case, Postgres Full-Text Search proved to be the perfect solution.

We specialize in creating complex custom software solutions. If you’re looking for expert development services, feel free to reach out to us!