Replacing Algolia with Postgres vector search

You can skip to the code example below if you don't like the jibber jabber`

Last week I wanted to play around with Postgrestext search capabilities thanks to a Fireship video. So I tried it out on my own product and was astonished to see how quickly you can add it to your table. So far I've only been indexing single tables as I haven't tried combining indexes from different tables yet. Even on a single table it's super impressive. After improving a couple of searches on my own product. A happy coincidence happened yesterday. My Engineering manager asked me if we could get rid of Algolia as the costs were rising. He asked me if I could maintain the Algolia indexes or try an alternative. Instantly I wanted to try out my new favorite database tool and in less that thirty minutes we got rid of an expense and a dependency. I'm still not fully sure how it works and I only know how to do basic things with it, but I can see it's possibilities and it's been a while since a tool has got me so excited. The best part is that it's built into Postgres. No extensions or installations needed.

I can give you a simple example to get started from there you can push it as far as you like.

Start by adding a vector column to your table with the type TSVECTOR
Example: ADD COLUMN users_search_vector TSVECTOR;`.

That's it really. Note that you need to keep the index updated. You can do this either by adding a trigger or in your insert query. Let's say you want to add indexes by rank for email, first_name and last_name.

    setweight(to_tsvector('english', $1), 'A') ||
    setweight(to_tsvector('english', $2), 'A') ||
    setweight(to_tsvector('english', $3), 'B') ||

Here A, and B are weights and can be used to order the response too. The variables are email, first name and last name respectively. I'm not exactly sure how many there are. So far I've only used upto 'C'. You can add this to the end of your variables in insert before the values. Or you can create a trigger that updates the index when the table is updated.

Finally to search use the @@ symbol to let Postgres know that you'd like to search by the vector. There are some options for this too:

users_search_vector @@ to_tsquery('english', $3);

You can replace $3 with a pattern in combination with the search query.

That's it really. To think that we have been paying for a service to replace about ten lines of code is crazy. But it can happen when you don't know your tools well enough. Better late than never I guess. I'd like to see how I can do more advanced searches with this. I'm not sure how to combine multiple tables and search through them. When I find out I'll be back. But this on it's own has been super useful. I used to despise searches, mainly because I couldn't get them to be good now I can't wait to implement my next one.

This is a full example for those interested:

-- CREATE TABLE
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    users_search_vector tsvector
);

-- INSERT
INSERT INTO users (email, first_name, last_name, users_search_vector) VALUES (
    $1, $2, $3,
    setweight(to_tsvector('english', $1), 'A') ||
    setweight(to_tsvector('english', $2), 'A') ||
    setweight(to_tsvector('english', $3), 'C')
);

-- SEARCH
SELECT id, email, first_name, last_name
FROM users
WHERE search_vector @@ to_tsquery('english', $1) -- can be extended by adding || ':*'
ORDER BY ts_rank(users_search_vector, to_tsquery('english', $1) DESC;