[PR #3900] [MERGED] refactor: use trigram search index instead of full text search #4578

New issue

Closed

opened 2026-03-17 02:06:07 +03:00 by kerem · 0 comments

kerem commented

2026-03-17 02:06:07 +03:00

Owner

📋 Pull Request Information

Original PR: https://github.com/hoppscotch/hoppscotch/pull/3900
Author: @AndrewBastin
Created: 3/14/2024
Status: ✅ Merged
Merged: 3/15/2024
Merged by: @AndrewBastin

Base: release/2024.3.0 ← Head: refactor/text-search-trigram

📝 Commits (2)

2a76af2 refactor: use trigram search index instead of FTS
53beb35 chore: fixed return type mismatch with generateParentTree function

📊 Changes

3 files changed (+113 additions, -52 deletions)

View changed files

📝 packages/hoppscotch-backend/prisma/migrations/20240226053141_full_text_search_additions/migration.sql (+18 -13)
📝 packages/hoppscotch-backend/src/team-collection/team-collection.service.ts (+58 -39)
📝 packages/hoppscotch-backend/src/utils.ts (+37 -0)

📄 Description

This PR intends to refactor the upcoming Team Collection Search mechanism to use Postgres Trigram Indices instead of Full Text Search for their implementation.

What's changed

Updated FTS migration to setup and build trigram extension and GIN trigram indices.
Introduce escapeSqlLikeString function to create escaped strings for usage in SQL LIKE statements.
Updated existing SQL search queries to use ILIKE queries instead of old FTS vector lookups.

Notes

Since the old migration is overwritten, you will have to start the DB again from scratch to test this as there will be mismatches between the generated DB. Due to change 1.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/hoppscotch/hoppscotch/pull/3900 **Author:** [@AndrewBastin](https://github.com/AndrewBastin) **Created:** 3/14/2024 **Status:** ✅ Merged **Merged:** 3/15/2024 **Merged by:** [@AndrewBastin](https://github.com/AndrewBastin) **Base:** `release/2024.3.0` ← **Head:** `refactor/text-search-trigram` --- ### 📝 Commits (2) - [`2a76af2`](https://github.com/hoppscotch/hoppscotch/commit/2a76af29d3af483bfac854d57619eea17123c6fe) refactor: use trigram search index instead of FTS - [`53beb35`](https://github.com/hoppscotch/hoppscotch/commit/53beb356839e99b6ae1ccc67abc542bebdd50892) chore: fixed return type mismatch with generateParentTree function ### 📊 Changes **3 files changed** (+113 additions, -52 deletions) <details> <summary>View changed files</summary> 📝 `packages/hoppscotch-backend/prisma/migrations/20240226053141_full_text_search_additions/migration.sql` (+18 -13) 📝 `packages/hoppscotch-backend/src/team-collection/team-collection.service.ts` (+58 -39) 📝 `packages/hoppscotch-backend/src/utils.ts` (+37 -0) </details> ### 📄 Description This PR intends to refactor the upcoming Team Collection Search mechanism to use Postgres Trigram Indices instead of Full Text Search for their implementation. ## What's changed 1. Updated FTS migration to setup and build trigram extension and GIN trigram indices. 2. Introduce `escapeSqlLikeString` function to create escaped strings for usage in SQL `LIKE` statements. 3. Updated existing SQL search queries to use `ILIKE` queries instead of old FTS vector lookups. ## Notes - Since the old migration is overwritten, you will have to start the DB again from scratch to test this as there will be mismatches between the generated DB. Due to change 1. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>