Creating a search feature with full-text indexing on PostgreSQL

Photo by Marten Newhall on Unsplash

Creating a search feature with full-text indexing on PostgreSQL

Adi Purwanto's photo
Adi Purwanto
·

6 min read

Beberapa waktu lalu, saya membuat FAQ feature yang sangat sederhana hanya sebatas CRUD feature. Agar mendapatkan gambaran besar feature yang saya buat, berikut user flow-nya secara singkat dalam backoffice.

  • Admin dapat membuat pertanyaan dan jawaban berdasarkan faq category

  • Pertanyaan dan jawaban memiliki dua bahasa (english dan indonesia)

  • Admin dapat melalukan filter berdasarkan category

  • Admin dapat mencari faq berdasarkan pertanyaan dan jawaban -> highlight-nya ini ✨

  • Admin dapat mengubah sorting dengan drag n drop

Dari user flow singkat tersebut, saya dapat men-design table yang saya perlukan untuk feature tersebut. Kebetulan dalam tempat saya bekerja, kita menggunakan https://dbdocs.io/ sebagai database documentation, jadi saya akan menulis dalam DBML (Database Markup Language).

Table faq_categories {
    id uuid [pk]
    name_ind text [not null]
    name_eng text [not null]
    note: "Jangan terlalu fokus pada table ini"
}

Table faq {
    id uuid [pk]
    category_id uuid [not null, ref: > faq_categories.id]
    question_ind text [not null]
    question_eng text [not null]
    answer_ind text [not null]
    answer_eng text [not null]
    index_number bigint [default: '1024', note: 'Index number to sort data']
    note: 'Fokus saja pada table ini'
}

Oke anggap saja saya sudah membuat table tersebut ke dalam postgres database. Sering sekali search feature dengan database sql menggunakan fuzzy searches.

select question_ind from faq where question_ind like '%login%'

Saya sebenarnya bisa saja menggunakan cara ini, namun dalam case saya penggunaan ini sangat buruk. Karena jika saya memerlukan pencarian yang lebih complex ... like '%kenapa saya gak bisa login ketika memasukan password%' Postgres pasti akan mengembalikan result kosong.

Untuk menyelesaikan masalah tersebut saya dapat menggunakan full-text indexing dari postgres, namun untuk membuat feature tersebut perlu beberapa hal yang harus dipahami sebagai berikut.

Tokenize Textual Document

Langkah yang sangat penting untuk dilakukan adalah parses textual document menjadi tokens. Postgres mem-provide function untuk melakukan ini, yaitu to_tsvector. Function to_tsvector sebenarnya melakukan converting sebuah document menjadi tsvector data type.

select to_tsvector('kenapa saya gak bisa login ketika memasukan password');

                                     to_tsvector
--------------------------------------------------------------------------------------
 'bisa':4 'gak':3 'kenapa':1 'ketika':6 'login':5 'memasukan':7 'password':8 'saya':2
(1 row)

Lalu, dengan function tersebut maka saya dapat melakukan tokenize data yang diperlukan.

select to_tsvector(question_ind) from faq limit 2;

                            to_tsvector
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 'assumenda':9 'aut':7 'delectus':8 'ea':11 'eius':12 'praesentium':1 'quam':3 'reprehenderit':10 'sapient':5
 'accusamus':25,33 'ad':11,29 'adipisci':15 'commodi':20 'delen':30 'dolorem':8 'doloremqu':17 'dolorum':23 'fugiat':16 'fugit':1 'impedit':19 'incidunt':10,22 'ist':24 'laborum':12,21 'maxim':31 'mollitia':32 'nam':13 'officia':2 'perspiciati':26 'quasi':27 'quod':28 'rem':6 'repellat':7 'temporibus':3 'vel':14,18 'volupta':9 'voluptatum':4
(2 rows)

Namun bagaimana ketika saya ingin tokenize hal-hal yang simple seperti nama, tag, atau saya ingin melakukan tokenize ke literal value? Sebenarnya to_tsvector memiliki config untuk addressing literal values. Kita dapat menggunakan to_tsvector('simple', document text) untuk simple dictionary (nama, tag, dll) atau to_tsvector('indonesian', document text) literal values.

Querying Tokenized Data

PostgreSQL menyediakan function to_tsquery, plainto_tsquery, phraseto_tsquery, dan websearch_to_tsquery untuk mengonversi sebuah query menjadi tipe data tsquery.

select question_ind from faq where to_tsvector(question_ind) @@ to_tsquery('Deleniti');

                   question_ind
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Deleniti dolorum commodi fuga sunt eos voluptate iure. Quos illo laboriosam omnis dolorem. Hic quas sunt quo mollitia deserunt aperiam corporis reiciendis ullam. Adipisci similique exercitationem provident aspernatur eum. Ducimus quibusdam amet ipsam in incidunt blanditiis aspernatur minus fugit. Repellendus quos aut quo exercitationem nobis ipsum.
 In cupiditate deleniti harum iure dolorum. Aspernatur doloremque provident expedita alias omnis iusto adipisci.
 Facilis et maiores voluptatibus. Vero sint natus illo enim fugiat pariatur magni eveniet. Quis dolor nisi quasi velit. Vitae quis perspiciatis voluptatibus ipsa ducimus. Id molestiae odio sed deleniti eligendi nesciunt. Voluptate illo hic culpa ullam.
 Quo explicabo voluptatem accusantium corporis possimus ducimus corrupti qui. Quas labore perspiciatis cumque debitis vitae odit corporis sint. Velit voluptatem eum autem autem placeat rem et. Aperiam optio magnam deleniti maiores. Dolorem rerum quasi animi aliquid cupiditate quis veritatis ad exercitationem.
 Modi quos illo quasi amet nisi dolore adipisci veniam possimus. Reprehenderit autem accusantium voluptatibus iste magnam sunt deleniti voluptates tempore. Suscipit esse commodi rerum explicabo odio omnis officia quis.
(5 rows)

Kita mendapatkan kata yang kita cari, namun bagaimana jika saya ingin membuat urutan yang lebih relevan, kita dapat mengukur seberapa relevan document dengan query tertentu menggunakan ts_rank.

select question_ind, ts_rank(to_tsvector(question_ind), to_tsquery('Deleniti')) as rank from faq where to_tsvector(question_ind) @@ to_tsquery('Deleniti') order by rank desc;

-[ RECORD 1 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
question_ind | Deleniti dolorum commodi fuga sunt eos voluptate iure. Quos illo laboriosam omnis dolorem. Hic quas sunt quo mollitia deserunt aperiam corporis reiciendis ullam. Adipisci similique exercitationem provident aspernatur eum. Ducimus quibusdam amet ipsam in incidunt blanditiis aspernatur minus fugit. Repellendus quos aut quo exercitationem nobis ipsum.
rank         | 0.06079271
-[ RECORD 2 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
question_ind | In cupiditate deleniti harum iure dolorum. Aspernatur doloremque provident expedita alias omnis iusto adipisci.
rank         | 0.06079271
-[ RECORD 3 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
question_ind | Facilis et maiores voluptatibus. Vero sint natus illo enim fugiat pariatur magni eveniet. Quis dolor nisi quasi velit. Vitae quis perspiciatis voluptatibus ipsa ducimus. Id molestiae odio sed deleniti eligendi nesciunt. Voluptate illo hic culpa ullam.
rank         | 0.06079271
-[ RECORD 4 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
question_ind | Quo explicabo voluptatem accusantium corporis possimus ducimus corrupti qui. Quas labore perspiciatis cumque debitis vitae odit corporis sint. Velit voluptatem eum autem autem placeat rem et. Aperiam optio magnam deleniti maiores. Dolorem rerum quasi animi aliquid cupiditate quis veritatis ad exercitationem.
rank         | 0.06079271
-[ RECORD 5 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
question_ind | Modi quos illo quasi amet nisi dolore adipisci veniam possimus. Reprehenderit autem accusantium voluptatibus iste magnam sunt deleniti voluptates tempore. Suscipit esse commodi rerum explicabo odio omnis officia quis.
rank         | 0.06079271

Dikarenakan saya menggunakan data dummy lorem ipsum generate, maka rank yang diberikan sama, karena kata yang relevan bernilai sama.

Tapi bagaimana jika saya ingin memberikan bobot yang berbeda, contohnya question vs answer? Saya ingin function yang dapat mempengaruhi bobot dan document yang relevan terhadap query saya (mempengaruhi peringkat dalam pencarian). Untuk kasus seperti ini Postgres memiliki setweight function, yang dapat digunakan untuk memberikan bobot tertentu pada entri-entri dari tsvector, di mana bobotnya dapat berupa huruf A, B, C, atau D.

select setweight(to_tsvector(question_ind), 'A') from faq limit 5;

                                                                                                             setweight
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 'adipisci':24A 'amet':32A 'aperiam':20A 'aspernatur':28A,37A 'aut':42A 'blanditii':36A 'commodi':3A 'corpori':21A 'delen':1A 'deserunt':19A 'dolorem':13A 'dolorum':2A 'ducimus':30A 'eo':6A 'eum':29A 'exercitationem':26A,44A 'fuga':4A 'fugit':39A 'hic':14A 'illo':10A 'incidunt':35A 'ipsam':33A 'ipsum':46A 'iur':8A 'laboriosam':11A 'minus':38A 'mollitia':18A 'nobi':45A 'omni':12A 'provid':27A 'qua':15A 'quibusdam':31A 'quo':9A,17A,41A,43A 'reiciendi':22A 'repellendus':40A 'similiqu':25A 'sunt':5A,16A 'ullam':23A 'volupt':7A
 'ab':6A 'accusantium':1A 'alia':12A 'aliquam':10A 'blanditii':19A 'commodi':4A 'debiti':3A 'dicta':22A 'dolorem':24A 'ducimus':14A,21A 'eo':2A 'facer':23A 'iur':20A 'modi':9A 'numquam':18A 'odio':8A 'qua':25A 'quaerat':13A 'quidem':17A 'sint':16A 'vero':11A 'voluptatibus':7A 'voluptatum':5A,15A
 'accusamus':2A 'aliquam':8A 'consequuntur':9A 'debiti':1A 'explicabo':4A 'laboriosam':7A 'natus':5A 'porro':10A 'quidem':6A 'rerum':3A
 'aliquam':32A 'aliquid':15A 'atqu':3A 'blanditii':19A 'consectetur':37A 'culpa':12A 'dolor':8A 'doloremqu':30A 'ea':4A 'eveniet':33A 'iur':24A 'labor':20A 'laborum':38A 'laudantium':11A 'minus':35A 'modi':14A 'necessitatibus':29A 'nesciunt':23A 'nobi':28A 'non':16A,21A 'odit':26A 'optio':9A,27A 'qui':36A,39A 'quibusdam':5A,13A 'rem':34A 'repellat':18A 'repellendus':2A 'saep':7A,31A 'sequi':22A 'sit':6A 'tempor':1A,17A 'und':25A 'volupta':10A
 'aliquid':3A 'aut':11A 'commodi':8A 'cum':5A 'dignissimo':4A 'ex':20A 'ipsam':12A 'iur':1A 'laboriosam':9A 'non':18A 'numquam':13A 'perspiciati':17A 'qui':10A 'ration':6A 'soluta':14A 'sunt':2A,15A 'veritati':16A 'vita':7A,19A
(5 rows)

Result

Dengan mempelajari dua function utama diatas (tokenized dan querying data) kita sudah dapat membuat search feature yang overkill dengan mengkombinasikan keduanya.

Berikut contoh kombinasi yang saya lakukan dengan kedua function utama tersebut.

  • Alter table untuk add column search

      alter table faq
  add search tsvector
  generated always as (
    setweight(to_tsvector('indonesian',question_ind), 'A')  || ' '||
    setweight(to_tsvector('indonesian',answer_ind), 'B') || ' ' ||
    setweight(to_tsvector('english',question_eng), 'A')  || ' ' ||
    setweight(to_tsvector('english',answer_eng), 'B') :: tsvector
  ) stored;

  • Add the index

      create index idx_search on faq using GIN(search);

  • Contoh search query

      select question_ind, question_eng, answer_ind, answer_eng,
    ts_rank(search, websearch_to_tsquery('indonesian','aku dan cow')) +
    ts_rank(search, websearch_to_tsquery('english','aku dan cow')) as rank
  from faq
  where search @@ websearch_to_tsquery('english','aku dan cow')
  or search @@ websearch_to_tsquery('indonesian','aku dan cow')
  order by rank desc;

Kenapa saya menggunakan websearch_to_tsquery, dikarenakan websearch_to_tsquery adalah versi sederhana dari to_tsquery dengan mirip dengan yang digunakan oleh search engines.

Sekian dari saya dan terima kasih telah membaca (=｀ェ´=).

Reference

PostgreSQLSearch EnginesindexingSQL#SQLtutorial