Learn SQL with PostgreSQL - Full-Text Search (FTS) & Vector Extension (pgvector)

#Introduction

Welcome to episode 17 of the Learn SQL with PostgreSQL series! In episode 5 we used ILIKE '%kata%' for text search. It works — but it has limits: it's slow on big tables, can't sort by relevance, and doesn't understand that "berlari" and "berlari cepat" are related concepts. For serious search, PostgreSQL has a built-in and very mature Full-Text Search (FTS) engine.

And there's one more thing making PostgreSQL increasingly relevant in the AI era: the pgvector extension. It turns PostgreSQL into a vector database — a place to store embeddings and perform similarity search for RAG (Retrieval-Augmented Generation) applications, recommendations, and semantic search. You don't need a separate database to build AI applications.

In this episode, we'll cover native FTS with to_tsvector and to_tsquery, the concepts of stemming and stop words, ranking search results with ts_rank, accelerating with a GIN index, then get to know pgvector with the L2 distance and cosine distance operators for similarity search.

#Native Full-Text Search (FTS) in PostgreSQL

#The Concept: Documents Become Searchable Vectors

The core of FTS is turning raw text into a tsvector — a normalized word representation (lowercase, root words, common words removed). Search then matches a tsquery against that tsvector.

SELECT to_tsvector('indonesian',
    'Saya berlari sangat cepat di pagi hari');

The result is a list of lexemes — cleaned-up root word forms. The words "berlari" and "berlari cepat" become the same lexeme. That's the power of FTS that LIKE doesn't have.

#to_tsquery: Building the Search Query

to_tsquery converts a query string into a tsquery with the logical operators & (AND), | (OR), and ! (NOT):

SELECT
    to_tsvector('indonesian', 'Saya belajar SQL dan database') @@
    to_tsquery('indonesian', 'sql & database') AS cocok;

The @@ operator returns TRUE if the tsvector matches the tsquery. For keyword search without strict logic, the plainto_tsquery function converts ordinary user input into a tsquery with automatic AND.

SELECT id, title
FROM articles
WHERE to_tsvector('indonesian', title || ' ' || body)
      @@ plainto_tsquery('indonesian', 'cara belajar postgresql');

#Stemming and Stop Words

Two concepts that make FTS intelligent:

• Stemming: reducing words to their root form. "Berlari", "berlari", "lari" are treated as the same concept.
• Stop words: common words removed because they carry no meaning — "yang", "di", "ke", "dari", and so on.

PostgreSQL has built-in language configurations (english, indonesian, and others). With 'indonesian', Indonesian stop words are automatically dropped and Indonesian stemming is applied. This is why FTS far outperforms character-based LIKE searches.

#Ranking Search Results with ts_rank

FTS search results are a set — but users need ordering by relevance. The ts_rank function (or ts_rank_cd for coverage density) computes a score for how well a document matches the query:

SELECT
    title,
    ts_rank(
        to_tsvector('indonesian', title || ' ' || body),
        plainto_tsquery('indonesian', 'belajar postgresql')
    ) AS skor
FROM articles
WHERE to_tsvector('indonesian', title || ' ' || body)
      @@ plainto_tsquery('indonesian', 'belajar postgresql')
ORDER BY skor DESC;

Documents that contain the words more often and earlier get a higher score. Storing the tsvector as a generated column (episode 12) makes this query much faster and cleaner:

ALTER TABLE articles
ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
    to_tsvector('indonesian', title || ' ' || body)
) STORED;

#Accelerating FTS with a GIN Index

Without an index, every search scans all rows. With a GIN index on the tsvector column, FTS search becomes an Index Scan (the episode 14 concept):

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

Now the query WHERE search_vector @@ plainto_tsquery(...) uses the GIN index and runs in milliseconds even on a table with millions of articles.

#Introduction to the pgvector Extension

Now the most exciting part in the AI era: pgvector. This extension adds the vector data type to PostgreSQL along with operators for similarity search — turning a relational database into a vector database for AI embeddings.

#Enabling and Storing Embeddings

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    embedding VECTOR(1536) NOT NULL
);

VECTOR(1536) denotes the embedding dimension — matching the embedding model used (e.g. OpenAI text-embedding-3-large produces 3072 dimensions, other common models produce 768 or 1536). This column is filled by the application from the embedding model's output.

#Similarity Search with Distance Operators

pgvector provides three main distance operators:

SELECT id, content, embedding <=> $1 AS jarak
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

$1 is the placeholder for the query embedding from the application. The result: the 5 documents with the smallest distance — that is, the most semantically similar to the user's question.

#How RAG Works with pgvector

The complete pipeline of a RAG (Retrieval-Augmented Generation) application uses pgvector as follows:

1. Documents are split into chunks and converted into embeddings by an AI model.
2. The embeddings are stored in the VECTOR(1536) column.
3. When a user asks, the question is also converted into an embedding.
4. PostgreSQL finds the most similar chunks using cosine distance.
5. The relevant chunks are handed to the LLM as context to generate the answer.

All of these retrieval steps happen inside PostgreSQL — without separate vector database infrastructure.

#When FTS and When pgvector?

Many modern applications combine both: FTS for precise keyword search, pgvector for "what's similar" — then blend the scores with ts_rank and cosine distance.

#Closing

Key takeaways:

• FTS turns text into normalized lexemes — far smarter than LIKE.
• Always specify the language in FTS functions ('indonesian').
• Store the tsvector as a generated column + GIN index for lightning-fast search.
• pgvector turns PostgreSQL into a vector database for AI embeddings.
• <-> (L2) and <=> (cosine) are the main similarity operators; cosine is best suited for text.

In the next episode, episode 18, we handle giant data: Table Partitioning for Large-Scale Data — from when partitioning is needed, declarative partitioning with Range, List, and Hash, to partition pruning that makes queries touch only the relevant partitions.