
Full-Text Search: Building Search with Just Your Database
You don't always need Elasticsearch. PostgreSQL's built-in Full-Text Search handles most search needs without extra infrastructure.

You don't always need Elasticsearch. PostgreSQL's built-in Full-Text Search handles most search needs without extra infrastructure.
Two ways to escape a maze. Spread out wide (BFS) or dig deep (DFS)? Who finds the shortest path?

Foundation of DB Design. Splitting tables to prevent Anomalies. 1NF, 2NF, 3NF explained simply.

Learn BST via Up & Down game. Why DBs use B-Trees over Hash Tables. Deep dive into AVL, Red-Black Trees, Splay Trees, and Treaps.

Binary Tree is for RAM. Disk is slow. B-Tree minimizes Disk I/O by being short and fat. Difference between B-Tree and B+Tree, and why databases love them.

Every time I needed to add search functionality, my first thought was always "I should use Elasticsearch." It felt like the obvious choice—search needs a search engine, right? But when I actually looked at my requirements, I realized I wasn't building Google. I just needed to search through a few hundred blog posts or a few thousand products.
So I tried implementing search with PostgreSQL, which I was already using. The verdict? Way more powerful than I expected. Without any additional infrastructure, I could build a solid search experience using just my existing database. Sure, I might need Elasticsearch someday, but PostgreSQL's Full-Text Search was more than enough for now.
I started with LIKE queries because, well, that's what everyone does first.
SELECT * FROM posts
WHERE title LIKE '%search term%' OR content LIKE '%search term%';
This worked fine at small scale. But once I had a few hundred records, things got noticeably slower. The reason is simple: LIKE '%search term%' can't use indexes at all. The database has to scan every single row and compare strings. It's like going through every book in a library one by one to find a specific word in the title.
The bigger problem was search quality. Searching for "database" wouldn't find "DB", and searching for "PostgreSQL" would miss "postgres". Case sensitivity and exact spacing mattered. This wasn't search—it was just string matching.
That's when I discovered Full-Text Search. PostgreSQL already had search engine-level features built in.
The core of Full-Text Search is tsvector and tsquery. At first glance they seemed cryptic, but once I understood them, the design made perfect sense.
tsvector transforms a document into a searchable format. Instead of storing raw text, it extracts and normalizes words. Think of it like an index at the back of a book—a pre-organized list of what words exist and where.
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
Notice how words are stemmed to their root form. "jumps" becomes "jump", "lazy" becomes "lazi". Stopwords like "the" and "over" are removed entirely. The numbers after each word indicate their position in the document.
tsquery represents a search query. It transforms user input into a format that can be matched against tsvector.
SELECT to_tsquery('english', 'fox & dog');
-- 'fox' & 'dog'
SELECT to_tsquery('english', 'quick | slow');
-- 'quick' | 'slow'
The & is AND, | is OR. Now we can match these together:
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'fox');
-- true
The @@ operator checks if they match. That's the heart of it.
Theory aside, I needed to implement this for my blog's posts table.
First, I added a search column:
ALTER TABLE posts
ADD COLUMN search_vector tsvector;
-- Combine title and content into tsvector
UPDATE posts
SET search_vector =
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));
The coalesce handles NULL values—titles or content might be empty.
Now the search query looks like this:
SELECT title, content, ts_rank(search_vector, query) AS rank
FROM posts, to_tsquery('english', 'database & search') query
WHERE search_vector @@ query
ORDER BY rank DESC;
Here's where ts_rank comes in. It calculates relevance scores for search results. If a search term appears multiple times or in important positions (like the title), the score is higher. Just like Google, I can now sort by relevance.
This was already faster than LIKE, but with lots of data it could still slow down. That's where GIN (Generalized Inverted Index) comes in.
CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);
GIN is an index type specialized for Full-Text Search. It uses an inverted index approach—pre-organizing which words appear in which documents. Elasticsearch uses the same technique.
After adding the index, search speed on 10,000 records went from 200ms → 15ms. That's a difference you can feel.
The problem was that every time I created or edited a post, I had to manually update search_vector. Easy to forget and annoying. So I automated it with a trigger.
CREATE FUNCTION posts_search_vector_update() RETURNS trigger AS $
BEGIN
NEW.search_vector :=
to_tsvector('english', coalesce(NEW.title, '') || ' ' || coalesce(NEW.content, ''));
RETURN NEW;
END
$ LANGUAGE plpgsql;
CREATE TRIGGER posts_search_vector_trigger
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION posts_search_vector_update();
Now whenever a post is inserted or updated, search_vector updates automatically. One less thing to worry about.
Everything above was for English. Korean introduced new problems.
PostgreSQL's Full-Text Search doesn't do morphological analysis out of the box. Searching for "데이터베이스" (database) only finds exact matches, not "데이터" (data) or "베이스" (base). English words are separated by spaces, but Korean has particles and compound words that make it much more complex.
Proper Korean morphological analyzers require external extensions (like pg_mecab), which adds maintenance overhead. Instead, I found pg_trgm (Trigram).
Trigram splits strings into 3-character chunks for indexing. "데이터베이스" becomes "데이터", "이터베", "터베이", "베이스". It's not morphological analysis, but it enables partial matching.
CREATE EXTENSION pg_trgm;
CREATE INDEX posts_title_trgm_idx ON posts USING GIN(title gin_trgm_ops);
CREATE INDEX posts_content_trgm_idx ON posts USING GIN(content gin_trgm_ops);
Now similarity search is possible:
SELECT title, similarity(title, '데이터베이스') AS sim
FROM posts
WHERE title % '데이터베이스'
ORDER BY sim DESC;
The % operator checks similarity, and similarity() calculates the score. This handles typos and spacing variations reasonably well.
I ended up using both. Full-Text Search for English and exact word matching, Trigram for Korean and fuzzy matching.
SELECT
title,
GREATEST(
ts_rank(search_vector, to_tsquery('english', 'database')),
similarity(title, '데이터베이스') * 0.5
) AS final_rank
FROM posts
WHERE
search_vector @@ to_tsquery('english', 'database')
OR title % '데이터베이스'
ORDER BY final_rank DESC;
I take the higher of the two scores. Trigram gets a 0.5 weight to prioritize Full-Text Search results.
My project uses Supabase, which is built on PostgreSQL, so Full-Text Search works out of the box.
I added the same columns, indexes, and triggers in Supabase's SQL Editor, then exposed search through an API.
// Using Full-Text Search from Supabase client
const { data, error } = await supabase
.rpc('search_posts', { search_query: 'database search' });
The RPC function looks like this:
CREATE OR REPLACE FUNCTION search_posts(search_query TEXT)
RETURNS TABLE (
id UUID,
title TEXT,
content TEXT,
rank REAL
) AS $
BEGIN
RETURN QUERY
SELECT
posts.id,
posts.title,
posts.content,
ts_rank(posts.search_vector, to_tsquery('english', search_query)) AS rank
FROM posts
WHERE posts.search_vector @@ to_tsquery('english', search_query)
ORDER BY rank DESC
LIMIT 20;
END;
$ LANGUAGE plpgsql;
On the frontend, I just call the function. Search logic is encapsulated in the database, keeping the code clean.
After using Full-Text Search for months, I kept asking myself: "When do I actually need Elasticsearch?" Here's what I figured out.
PostgreSQL Full-Text Search is enough when:For me, with a few thousand blog posts and projects, PostgreSQL was plenty. Having search logic in the database actually made data consistency easier. I'll migrate to Elasticsearch when the scale demands it, but until then, this approach works great.
You don't need new tools just because you need search functionality. PostgreSQL already provides powerful Full-Text Search capabilities, and for most cases, that's enough.
Key takeaways:tsvector and tsquery to transform data into searchable formatts_rankSearch engines aren't complicated—they start with properly using what your database already offers. You'll know when you need Elasticsearch, and when that time comes, you can migrate. For now, PostgreSQL Full-Text Search builds a great search experience with zero extra infrastructure.