Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Optimal implementation of storing single strings in postgresql for search speed

I have a database that will have 1m+ records of the following shape:

create table simplified(
    url text primary key,   -- google.com/search?=something
    url_host text not null, -- google.com
    url_path text not null  -- /search?=something 
)

I understand that to search for a urls substr a simple query could be:

select * from simplified where url like '%google%'

I am trying to optimize for speed of search and am willing to form the data in anyway. Some questions I have:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

  1. Is the LIKE operator really what I want for speed? or is there a faster alternative?
  2. Can tsvector or tsquery be implemented with a GIN index in a meaningful way for single string search?
  3. If not #2 is there something similar?

Thank you for your time in advance.

I have tried tsvector implementations to no avail, and some research on the topic.

>Solution :

Check the pg_trgm extension, that supports similarity searches using LIKE, ILIKE and (some) regexes.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading