PostgreSQL Full-Text Search in Django: From 45ms to 1ms with SearchVectorField and GIN Indexes

Good evening. I understand you have a search feature.

You followed the Django full-text search documentation. You annotated your queryset with SearchVector. You filtered. Results appeared. The feature shipped. The pull request was approved with a comment that said "looks good to me," and for a time, all was well.

Then somebody ran it against the production table with 200,000 rows and noticed that every search request takes 45 to 90 milliseconds. The database is computing to_tsvector() from scratch on every row, for every query, for every user who types into that search bar. A user searching twice per session means 400,000 to_tsvector() calls just for that user. Multiply by your concurrent users. I shall give you a moment with the arithmetic.

This is the default path. The documentation shows it first because it requires the fewest lines of code. It is also, regrettably, the slowest possible approach to full-text search in PostgreSQL. Not by a small margin. By a factor of 45 to 100.

I do not blame the documentation. Its purpose is to introduce the feature, not to optimize it. But I have observed, across many Django applications, that the introductory example becomes the production implementation — and then remains the production implementation until someone asks why the search page is the slowest page in the application.

What follows is the optimization path — from naive annotation to sub-millisecond search — using tools that Django and PostgreSQL already provide. No Elasticsearch. No external search service. No new dependency in your requirements.txt. Just a stored column, an index, and a trigger.

Why the naive approach is so slow

Here is what most Django full-text search tutorials show you:

from django.contrib.postgres.search import SearchVector
from myapp.models import Article

# The approach most tutorials show you
results = Article.objects.annotate(
    search=SearchVector('title', 'body'),
).filter(search='postgresql optimization')

# What this generates:
# SELECT *, to_tsvector('english', "title" || ' ' || "body") AS "search"
# FROM articles
# WHERE to_tsvector('english', "title" || ' ' || "body")
#       @@ plainto_tsquery('english', 'postgresql optimization')
#
# to_tsvector() is recomputed for EVERY row. Every single one.
# On 100K rows, this takes 45-90ms. On 500K rows, 200-400ms.

The generated SQL calls to_tsvector() on every row. This function is not a simple string comparison. It parses the text into tokens, classifies each token (word, email, URL, number), applies the language-specific stemmer to reduce words to their root forms, removes stop words ("the," "and," "is"), records the position of each surviving term, and produces a tsvector document — a sorted list of lexemes with their positions.

That is a substantial amount of work for a single row. On a single row, it takes 0.3 to 0.5 microseconds. That sounds trivial. On 100,000 rows, it adds up to 30-50 milliseconds of pure CPU work — before PostgreSQL even begins matching against the query. On 500,000 rows, you are looking at 200-400 milliseconds. On a million rows, the search bar becomes a loading spinner.

To appreciate what to_tsvector actually produces:

-- What to_tsvector actually does to your text:
SELECT to_tsvector('english', 'PostgreSQL full-text search optimization techniques');

-- Result:
-- 'full':2 'optim':5 'postgresql':1 'search':4 'techniqu':6 'text':3
--
-- Each word is stemmed: "optimization" → "optim", "techniques" → "techniqu"
-- Stop words removed: no articles, prepositions, or conjunctions survive
-- Position recorded: 'postgresql':1 means it appeared first
-- This is the document representation that gets compared against the query

SELECT plainto_tsquery('english', 'postgresql optimization');
-- Result: 'postgresql' & 'optim'
-- The query terms are stemmed the same way — that is how matching works

Every word stemmed. Every position recorded. Every stop word removed. This is precisely the kind of computation you want to do once and store, not repeat on every query.

Here is the EXPLAIN output that confirms the damage:

EXPLAIN (ANALYZE, BUFFERS) SELECT *, to_tsvector('english', title || ' ' || body) AS search
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', 'postgresql optimization');

-- Seq Scan on articles  (cost=0.00..28491.00 rows=500 width=312)
--   (actual time=12.841..47.223 rows=127 loops=1)
--   Filter: (to_tsvector('english', (title || ' ' || body)) @@ '''postgresql'' & ''optim'''::tsquery)
--   Rows Removed by Filter: 99873
--   Buffers: shared hit=8847
-- Planning Time: 0.182 ms
-- Execution Time: 47.291 ms

-- Sequential scan. 99,873 rows examined to find 127 matches.
-- to_tsvector() called 100,000 times. Every query. Every request.

Sequential scan. 99,873 rows examined to find 127 matches. 8,847 buffer pages read. 47 milliseconds. The database read through every row in the table, computed a tsvector for each one, compared it against the query, and discarded 99.87% of the results.

This is not a missing index problem in the usual sense. There is no index that can help here, because the expression being searched — to_tsvector('english', title || ' ' || body) — is computed at query time. PostgreSQL cannot index something it has not yet computed. You might as well ask a librarian to file books by a classification system that has not been invented yet.

The solution is to compute it in advance.

The expression index alternative — and why it falls short

Before I introduce SearchVectorField, allow me to address the approach you may encounter in PostgreSQL-focused tutorials: a GIN index on the to_tsvector() expression itself.

-- Alternative to SearchVectorField: a GIN index on an expression.
-- No stored column required. PostgreSQL computes the tsvector at index time.

CREATE INDEX idx_articles_fts ON articles
  USING gin (to_tsvector('english', title || ' ' || body));

-- This index ONLY works when the WHERE clause matches the expression exactly:
-- WHERE to_tsvector('english', title || ' ' || body) @@ query
--
-- Change the expression even slightly — different separator, different config —
-- and the planner will not recognise it. Silent sequential scan.
--
-- The index is also recomputed on every INSERT or UPDATE to title or body,
-- which means the same CPU cost as a trigger, but without the flexibility
-- of weighted fields (A, B, C, D weights require setweight, which requires
-- a stored column).

This works. PostgreSQL will use this index when it encounters a WHERE clause with the exact same expression. The keyword is exact. If your Django code generates to_tsvector('english', "title" || ' ' || "body") but the index was created on to_tsvector('english', title || ' ' || body) — with or without quotes, with different whitespace handling, with a different separator — the planner will not match them. It will fall back to a sequential scan, silently, without complaint.

Expression indexes also cannot use weighted fields. If you want title matches to score higher than body matches — and you do, because a user searching for "PostgreSQL optimization" should see the article titled "PostgreSQL Optimization" before one that merely mentions the phrase in paragraph nineteen — you need setweight(), and setweight() requires a stored tsvector column.

The expression index is a reasonable choice for simple, single-field search on a table where you cannot add columns. For everything else, the stored column approach is superior. It is more flexible, more predictable, and more maintainable. Allow me to show you.

SearchVectorField and GIN: the 45x speedup

Django provides SearchVectorField — a dedicated column type that stores the pre-computed tsvector. Combined with a GIN (Generalized Inverted Index) index, this moves the cost from query time to write time. You pay fractions of a millisecond on each INSERT or UPDATE. You save 45 milliseconds on every search query. The economics of this trade are, if I may say so, rather favourable.

from django.contrib.postgres.search import SearchVectorField
from django.contrib.postgres.indexes import GinIndex
from django.db import models

class Article(models.Model):
    title = models.CharField(max_length=300)
    body = models.TextField()
    author = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    published_at = models.DateTimeField(null=True, blank=True)

    # Pre-computed tsvector column — updated on save, indexed with GIN
    search_vector = SearchVectorField(null=True)

    class Meta:
        indexes = [
            GinIndex(fields=['search_vector']),
        ]

The search_vector column stores the tsvector representation of each row's title and body. The data is computed once and written alongside the row. No runtime computation. No repeated work.

The GIN index builds an inverted index over that column — mapping each lexeme (stemmed word) to the sorted list of row IDs containing it. When you search for "postgresql optimization," PostgreSQL looks up two posting lists, intersects them, and returns the matching row IDs. Lookups become a set intersection, not a table scan.

You need to populate the column for existing data and keep it updated:

from django.contrib.postgres.search import SearchVector

# One-time backfill for existing rows
Article.objects.update(
    search_vector=SearchVector('title', weight='A')
                + SearchVector('body', weight='B')
)

# For new/updated articles, update in your save logic
from django.db.models.signals import post_save
from django.dispatch import receiver

@receiver(post_save, sender=Article)
def update_search_vector(sender, instance, **kwargs):
    # Use .update() to avoid re-triggering post_save
    Article.objects.filter(pk=instance.pk).update(
        search_vector=SearchVector('title', weight='A')
                    + SearchVector('body', weight='B')
    )

Now your queries hit the index:

from django.contrib.postgres.search import SearchQuery

# Now queries hit the GIN index — no to_tsvector() recomputation
results = Article.objects.filter(
    search_vector=SearchQuery('postgresql optimization')
)

# What this generates:
# SELECT * FROM articles
# WHERE "search_vector" @@ plainto_tsquery('english', 'postgresql optimization')
#
# GIN index lookup. No function call per row.

And the EXPLAIN tells a different story entirely:

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql optimization');

-- Bitmap Heap Scan on articles  (cost=12.08..1620.73 rows=500 width=320)
--   (actual time=0.089..0.412 rows=127 loops=1)
--   Recheck Cond: (search_vector @@ '''postgresql'' & ''optim'''::tsquery)
--   Heap Blocks: exact=118
--   Buffers: shared hit=125
--   ->  Bitmap Index Scan on articles_search_vector_idx
--       (cost=0.00..11.96 rows=500 width=0)
--       (actual time=0.067..0.068 rows=127 loops=1)
--       Index Cond: (search_vector @@ '''postgresql'' & ''optim'''::tsquery)
--       Buffers: shared hit=7
-- Planning Time: 0.154 ms
-- Execution Time: 0.439 ms

-- 0.4ms. Down from 47ms. The GIN index found 127 rows by scanning 7 buffer pages
-- instead of 8,847. That is a 99.9% reduction in I/O.

0.4 milliseconds. Seven buffer pages. Down from 47 milliseconds and 8,847 buffer pages. The GIN index identified the 127 matching rows by consulting its inverted index — a handful of pages — and then fetched only those rows from the table. The other 99,873 rows were never touched. Never read. Never computed against. They simply did not participate.

The numbers speak plainly:

The pattern is consistent across every approach: without an index, latency scales linearly with table size. Double the rows, double the query time. With a GIN index, latency scales with the result set size — which is almost always orders of magnitude smaller than the table. A table that grows from 100,000 to 10 million rows does not produce a 100x slowdown. It produces roughly the same query time, because the number of rows matching "postgresql optimization" does not grow linearly with the table.

This is the fundamental insight. The index changes the scaling characteristic of your search from O(n) to O(k), where k is the number of matching rows. For most search queries, k is negligibly small relative to n.

How GIN indexes work, briefly

I should like to spend a moment on the mechanism, because understanding how GIN operates will inform several decisions you will make later — about index sizing, maintenance, and write performance.

-- A GIN index is an inverted index — like the index at the back of a book.
-- For each unique lexeme (stemmed word), it stores a sorted list of row IDs.
--
-- Conceptual structure for our articles table:
--
-- Lexeme          → Row IDs (posting list)
-- ─────────────────────────────────────────
-- 'postgresql'    → {1, 4, 17, 42, 89, 127, ...}
-- 'optim'         → {4, 12, 42, 56, 89, ...}
-- 'index'         → {1, 3, 4, 17, 42, ...}
-- 'search'        → {4, 12, 17, 42, 127, ...}
--
-- Query: 'postgresql' & 'optim'
-- → Intersect the posting lists for 'postgresql' and 'optim'
-- → Result: {4, 42, 89, ...}
-- → Bitmap Heap Scan fetches only those rows

-- Check the actual size of your GIN index:
SELECT pg_size_pretty(pg_relation_size('articles_search_vector_idx'));
-- Typical: 15-30 MB for 500K articles with average-length content

-- Compare to the table size:
SELECT pg_size_pretty(pg_relation_size('articles_article'));
-- The index is usually 5-15% of the table size — a modest overhead

A GIN index is, at its core, the same data structure as a search engine's inverted index. For each unique term (lexeme), it maintains a sorted list of row IDs — called a posting list — that contain that term. A search query is decomposed into terms, each term's posting list is retrieved, and the lists are intersected (for AND) or unioned (for OR) to produce the result set.

This is why GIN is fast: it never touches a row that does not match. It never reads the table data to determine whether a row matches. The posting lists are the answer.

The trade-off is write amplification. When you insert or update a row, GIN must update the posting list for every lexeme in that row's tsvector. An article with 200 unique stemmed words means 200 posting list updates. PostgreSQL mitigates this with a "pending list" — new entries are batched in an unsorted buffer and flushed into the main index structure during VACUUM or when the buffer reaches gin_pending_list_limit (default 4 MB). This makes writes fast at the cost of occasional slow queries that must scan the pending list linearly.

For most applications, this trade-off is invisible. Your table receives dozens or hundreds of writes per second, not thousands. The pending list is flushed often enough that it never grows large. But if you are doing bulk imports — loading 100,000 articles from a CSV — the pending list will grow, and search queries during the import will be slower than usual. The remedy is a VACUUM after the import completes, which flushes the pending list into the main index.

Database triggers: updates that never get forgotten

The Django signal approach shown above works. I should be more precise: it works for the code paths that flow through the Django ORM's .save() method. It has gaps everywhere else.

# The gaps in Django signals — every one of these skips your signal handler:

# 1. Bulk updates
Article.objects.filter(category='news').update(body='[redacted]')
# search_vector is now stale for every affected row

# 2. Raw SQL
from django.db import connection
with connection.cursor() as cursor:
    cursor.execute("UPDATE articles_article SET title = %s WHERE id = %s", ['New Title', 42])
# search_vector: still the old title's tsvector

# 3. Django admin bulk actions (uses .update() internally)
# 4. Management commands that use .update() for performance
# 5. Database-level imports (pg_restore, COPY, etc.)
# 6. Other applications or services writing to the same table

# A PostgreSQL trigger covers ALL of these. Every write path.
# The only way to bypass a trigger is to explicitly disable it:
# ALTER TABLE articles_article DISABLE TRIGGER articles_search_vector_trigger;
# — and if someone does that, they deserve what happens next.

Bulk updates via .update() skip signals. Raw SQL skips signals. Admin panel edits skip signals. Management commands skip signals. Database-level imports skip signals. Other services writing to the same table skip signals. Each of these creates stale search vectors — rows where the search_vector column no longer reflects the current title and body. Users search for content that exists and find nothing. Worse: they do not know they found nothing incorrectly. They assume the content does not exist.

A PostgreSQL trigger has no gaps. It fires on every INSERT and UPDATE, regardless of what initiated the write — Django, raw SQL, psql, another application, a migration, a bulk import. The trigger lives in the database, and the database sees every write.

-- Database trigger: keeps search_vector updated without Python involvement.
-- More reliable than Django signals — works for bulk updates, raw SQL, admin edits.

CREATE OR REPLACE FUNCTION articles_search_vector_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_vector_trigger
    BEFORE INSERT OR UPDATE OF title, body ON articles_article
    FOR EACH ROW
    EXECUTE FUNCTION articles_search_vector_update();

-- Now every INSERT or UPDATE to title/body automatically recomputes
-- the search_vector. No Django signal required. No forgotten code paths.

The trigger runs BEFORE INSERT OR UPDATE OF title, body. Two details here are worth noting. First, the BEFORE timing means the trigger modifies the row before it is written to disk — there is no second write, no additional I/O. The search vector is computed inline with the original INSERT or UPDATE. Second, the OF title, body clause means the trigger only fires when those specific columns change. An update to published_at or author_id does not trigger a recomputation. This matters for tables that receive frequent metadata updates — you do not want to recompute a tsvector because someone corrected a typo in the author's email address.

The cost is negligible: to_tsvector() on a single row takes a fraction of a millisecond. You pay that once per write to save 45 milliseconds on every search query that follows. If your table receives 100 writes per hour and 1,000 search queries per hour, you are spending 30 milliseconds of trigger time to save 45,000 milliseconds of query time. A ratio of 1,500 to 1.

To add this trigger in a Django migration:

# In a Django migration, add the trigger via RunSQL
from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0005_article_search_vector'),
    ]

    operations = [
        migrations.RunSQL(
            sql="""
            CREATE OR REPLACE FUNCTION articles_search_vector_update() RETURNS trigger AS $$
            BEGIN
                NEW.search_vector :=
                    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
                    setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'B');
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;

            CREATE TRIGGER articles_search_vector_trigger
                BEFORE INSERT OR UPDATE OF title, body ON myapp_article
                FOR EACH ROW
                EXECUTE FUNCTION articles_search_vector_update();
            """,
            reverse_sql="""
            DROP TRIGGER IF EXISTS articles_search_vector_trigger ON myapp_article;
            DROP FUNCTION IF EXISTS articles_search_vector_update();
            """,
        ),
    ]

With the trigger in place, you can remove the Django signal entirely. The database handles it. This is one of those cases where pushing logic into the database is unambiguously the right call — the database sees every write, and it cannot be bypassed by application code that forgot to import the right module. I find that reliability of this nature — the kind that does not depend on developer memory — to be the most satisfying kind.

SearchRank: relevance ordering that does not kill performance

Filtering is half the problem. Finding the 127 rows that match "postgresql optimization" is necessary but insufficient. The other half is ordering those results so the best matches appear first. An article titled "PostgreSQL Query Optimization" should rank above one that mentions optimization once in the fourteenth paragraph.

from django.contrib.postgres.search import SearchQuery, SearchRank
from django.db.models import F

query = SearchQuery('postgresql optimization')

results = (
    Article.objects
    .filter(search_vector=query)
    .annotate(rank=SearchRank(F('search_vector'), query))
    .order_by('-rank')
)

# SearchRank uses the A/B/C/D weights you assigned.
# Title matches (weight A, default 1.0) score higher than body matches (weight B, default 0.4).
# The GIN index handles the filtering. SearchRank only runs on the matched rows.

SearchRank computes a relevance score based on the weights assigned to each field. Weight A (title) has a default multiplier of 1.0. Weight B (body) has 0.4. A match in the title scores 2.5 times higher than a match in the body. This weighting is why we use setweight() in the trigger — it embeds the importance hierarchy directly into the tsvector, where SearchRank can use it without any additional computation.

The critical performance point: SearchRank only runs on the rows that pass the GIN filter. If the index narrows 100,000 rows down to 127 matches, SearchRank computes scores for 127 rows, not 100,000. The cost is proportional to the result set, not the table. This is the difference between "ranking adds 1-2ms" and "ranking adds 40ms." The GIN index does the expensive work of elimination. SearchRank merely sorts the survivors.

You can customise the weight multipliers to suit your content:

from django.contrib.postgres.search import SearchQuery, SearchRank
from django.db.models import F

query = SearchQuery('postgresql optimization')

# Custom weight multipliers: [D, C, B, A]
# Default is [0.1, 0.2, 0.4, 1.0]
results = (
    Article.objects
    .filter(search_vector=query)
    .annotate(
        rank=SearchRank(
            F('search_vector'),
            query,
            weights=[0.1, 0.2, 0.4, 1.0],
        )
    )
    .filter(rank__gt=0.01)
    .order_by('-rank')
)

# Weight guide:
# A (1.0) — title, headings: the most important signal
# B (0.4) — body text, descriptions: the bulk of the content
# C (0.2) — tags, categories: supplementary metadata
# D (0.1) — comments, footnotes: least important

# To use C and D weights, add more fields to your trigger:
# NEW.search_vector :=
#     setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
#     setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'B') ||
#     setweight(to_tsvector('english', COALESCE(NEW.tags, '')), 'C');

If you need ranking but want to keep latency tight, add a .filter(rank__gt=0.01) to drop very low relevance matches, and always apply LIMIT through Django's slicing. A search query that returns 3,000 results is not a good search query — it is a browsing exercise. Cap the result set, and the ranking cost stays proportional to the cap, not to the match count.

TrigramSimilarity: handling typos without Elasticsearch

Full-text search is stem-based. It handles "optimize" matching "optimization" because both stem to "optim." It handles "running" matching "run" because the English stemmer knows they share a root. What it does not handle is "postgrsql" matching "postgresql," because those are not linguistic variants — they are typos. Different character sequences. The stemmer has no opinion about misspellings.

For typo tolerance, PostgreSQL offers trigram similarity via the pg_trgm extension. A trigram is a sequence of three consecutive characters. The word "postgresql" produces the trigrams: "pos", "ost", "stg", "tgr", "gre", "res", "esq", "sql". The misspelling "postgrsql" produces: "pos", "ost", "stg", "tgr", "grs", "rsq", "sql". Six of seven trigrams overlap. The similarity score is high. The match is found.

Django wraps this in TrigramSimilarity:

# For typo-tolerant search: combine full-text search with trigram similarity.
# Requires: CREATE EXTENSION IF NOT EXISTS pg_trgm;

from django.contrib.postgres.search import TrigramSimilarity

# Fuzzy matching on title — catches "postgrsql" → "postgresql"
results = (
    Article.objects
    .annotate(similarity=TrigramSimilarity('title', 'postgrsql'))
    .filter(similarity__gt=0.3)
    .order_by('-similarity')
)

# For best performance, add a GIN trigram index:
# CREATE INDEX idx_articles_title_trgm ON myapp_article
#   USING gin (title gin_trgm_ops);

# In Django's Meta:
from django.contrib.postgres.indexes import GinIndex

class Meta:
    indexes = [
        GinIndex(fields=['search_vector']),
        GinIndex(
            name='title_trgm_idx',
            fields=['title'],
            opclasses=['gin_trgm_ops'],
        ),
    ]

Without the trigram GIN index, similarity queries are sequential scans — just as slow as naive full-text search. PostgreSQL must compute the trigram set for every row and compare it against the query. With the index, it consults the inverted trigram index and retrieves only the rows with sufficient overlap. The difference is the same order of magnitude as the full-text search case: 80-150ms without the index, 1-4ms with it.

A practical search feature often combines both approaches. Full-text search first for exact and stemmed matches — it is faster and produces more precise results. Then, if full-text returns nothing, a trigram fallback to catch typos and partial matches:

from django.contrib.postgres.search import (
    SearchQuery, SearchRank, TrigramSimilarity,
)
from django.db.models import F, Q, Value
from django.db.models.functions import Greatest

def hybrid_search(user_query, page=1, per_page=20):
    """Full-text search with trigram fallback for typo tolerance."""
    query = SearchQuery(user_query, search_type='websearch')

    # Try full-text search first
    fts_results = (
        Article.objects
        .filter(search_vector=query)
        .annotate(rank=SearchRank(F('search_vector'), query))
        .filter(rank__gt=0.01)
        .order_by('-rank')
    )

    if fts_results.exists():
        return fts_results[(page - 1) * per_page : page * per_page]

    # No full-text matches — fall back to trigram similarity
    # This catches typos, partial words, and non-stemmed matches
    return (
        Article.objects
        .annotate(
            similarity=Greatest(
                TrigramSimilarity('title', user_query),
                TrigramSimilarity('body', user_query) * Value(0.5),
            )
        )
        .filter(similarity__gt=0.15)
        .order_by('-similarity')
        [(page - 1) * per_page : page * per_page]
    )

# The fallback adds ~2-4ms on a well-indexed table.
# Total worst case: one failed FTS query (0.4ms) + one trigram query (3ms) = 3.4ms.
# Still faster than a single naive SearchVector annotation.

This gives you the speed of tsvector matching with the forgiveness of fuzzy matching, without the operational overhead of a separate search engine. The fallback adds 2-4 milliseconds on a well-indexed table. Total worst case: one failed full-text query plus one trigram query, still faster than a single naive SearchVector annotation.

websearch_to_tsquery and unaccented search

Two more capabilities that most Django full-text search implementations miss, and both are straightforward to add once the foundation is in place.

from django.contrib.postgres.search import SearchQuery

# plainto_tsquery: splits on spaces, ANDs everything
# 'postgresql optimization' → 'postgresql' & 'optimization'
basic = SearchQuery('postgresql optimization')

# websearch_to_tsquery: supports quoted phrases, OR, negation
# Like a search engine — users can type natural queries
advanced = SearchQuery(
    '"full text search" OR tsvector -trigram',
    search_type='websearch',
)

# phrase search: words must appear adjacent and in order
phrase = SearchQuery('connection pooling', search_type='phrase')

results = Article.objects.filter(search_vector=advanced)

# websearch_to_tsquery was added in PostgreSQL 11.
# It handles:
#   "quoted phrases"        → 'full' <-> 'text' <-> 'search'
#   OR                      → |
#   -negation               → !
#   unquoted words           → & (AND)

websearch_to_tsquery (available since PostgreSQL 11) gives your users a familiar search syntax: quoted phrases for exact matches, OR for alternatives, minus for negation. No special escaping needed. It handles the kind of queries people are trained to type into search boxes — because it was modelled on search engine syntax. A user who types "connection pooling" -pgbouncer gets results about connection pooling that do not mention PgBouncer. This is remarkably powerful for a feature that requires changing a single argument: search_type='websearch'.

I should note that websearch_to_tsquery is more forgiving than to_tsquery with malformed input. A user who types an unmatched quote or a dangling OR operator will not produce a database error — websearch_to_tsquery handles these gracefully. This matters more than it might appear. Search boxes receive the most creative user input in any application, and a 500 error from a search query is the kind of experience that teaches users not to use your search.

For international content, accent-insensitive search matters. A user searching for "cafe" expects to find "café." A user searching for "resume" expects to find "résumé." PostgreSQL's unaccent extension handles this:

-- Create an unaccented text search configuration for accent-insensitive search.
-- Users searching for "cafe" should find "café".

CREATE TEXT SEARCH CONFIGURATION english_unaccent (COPY = english);

ALTER TEXT SEARCH CONFIGURATION english_unaccent
    ALTER MAPPING FOR hword, hword_part, word
    WITH unaccent, english_stem;

-- Update your trigger to use the custom configuration:
CREATE OR REPLACE FUNCTION articles_search_vector_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english_unaccent', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english_unaccent', COALESCE(NEW.body, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- And in your Django query:
-- SearchQuery('cafe', config='english_unaccent')

The custom text search configuration strips accents during both indexing and querying. Combined with SearchVectorField and the database trigger, this makes accent-insensitive search just as fast as regular full-text search — the unaccent processing happens at write time (in the trigger), not at query time. The user types ASCII, the database matches Unicode, and the GIN index operates at the same speed regardless.

One caveat: the unaccent extension must be installed as a superuser or by a role with CREATE privilege on the database. If your hosting provider restricts extension installation, you may need to request it through their support process. Most managed PostgreSQL services (RDS, Cloud SQL, Supabase) include unaccent in their default extension set.

Searching across multiple models

Most applications do not have a single searchable model. You have articles, documentation pages, help centre entries, product descriptions, user profiles. A site-wide search bar that only searches one table is a search bar that disappoints.

from django.contrib.postgres.search import SearchQuery, SearchRank
from django.db.models import F, Value, CharField
from itertools import chain

def search_all_content(user_query, limit=20):
    """Search across multiple models with unified ranking."""
    query = SearchQuery(user_query, search_type='websearch')

    articles = (
        Article.objects
        .filter(search_vector=query)
        .annotate(
            rank=SearchRank(F('search_vector'), query),
            content_type=Value('article', output_field=CharField()),
        )
        .values('id', 'title', 'rank', 'content_type')
    )

    docs = (
        Documentation.objects
        .filter(search_vector=query)
        .annotate(
            rank=SearchRank(F('search_vector'), query),
            content_type=Value('doc', output_field=CharField()),
        )
        .values('id', 'title', 'rank', 'content_type')
    )

    # Union the querysets — PostgreSQL handles the merge
    combined = articles.union(docs).order_by('-rank')[:limit]
    return combined

# Each model has its own SearchVectorField and GIN index.
# The UNION happens in SQL — PostgreSQL merges and sorts the results.
# No application-level sorting. No fetching all results into Python.

Each model has its own SearchVectorField and its own GIN index. The search queries run against each table independently, and PostgreSQL's UNION merges the results. The ranking is comparable across models because SearchRank uses the same scoring algorithm regardless of the source table.

Two considerations. First, if your models have very different content lengths — articles with 2,000 words versus product names with 5 words — the ranking will naturally favour shorter content because term density is higher. You can counteract this by adjusting the cover_density parameter in SearchRank, or by applying a model-specific boost multiplier in your application code. Second, if you have five or more searchable models, the UNION approach starts to feel unwieldy. At that scale, consider a dedicated search table that aggregates content from all models — a denormalised search index, updated by triggers on each source table. PostgreSQL becomes its own search engine.

Putting it all together: a production search view

Here is a complete search function that combines everything discussed above:

from django.contrib.postgres.search import (
    SearchQuery, SearchRank, SearchHeadline,
)
from django.db.models import F

def search_articles(user_query, page=1, per_page=20):
    """Production-grade full-text search with ranking and highlights."""
    query = SearchQuery(user_query, search_type='websearch')

    results = (
        Article.objects
        .filter(search_vector=query)
        .annotate(
            rank=SearchRank(
                F('search_vector'),
                query,
                weights=[0.1, 0.2, 0.4, 1.0],  # D, C, B, A
            ),
            headline=SearchHeadline(
                'body',
                query,
                start_sel='<mark>',
                stop_sel='</mark>',
                max_words=60,
                min_words=20,
            ),
        )
        .filter(rank__gt=0.01)   # drop very low relevance matches
        .order_by('-rank')
        [(page - 1) * per_page : page * per_page]
    )

    return results

# SearchHeadline generates snippets with highlighted matches.
# The GIN index handles filtering. SearchRank and SearchHeadline
# only process the matched rows — not the full table.

This function gives you:

• Sub-millisecond filtering via the GIN-indexed search_vector
• Relevance ranking via SearchRank with custom weights
• Result highlighting via SearchHeadline with configurable snippet length
• Web-style query syntax via search_type='websearch'
• Pagination via Django queryset slicing

Total query time on 500,000 rows: 3 to 8 milliseconds, including ranking and headline generation. That is fast enough to call on every keystroke for live search suggestions, if you are so inclined — though I would recommend a 150-200ms debounce on the client side, not because the database cannot handle it, but because the network round trip makes sub-100ms keystroke search impractical regardless of backend speed.

A note on SearchHeadline: it is the most expensive component in this stack, because it must re-read the original text column (not the tsvector) to generate the highlighted snippet. The tsvector stores lexemes and positions but not the original words — "optimization" is stored as "optim." To produce a readable snippet with <mark> tags around the matched terms, PostgreSQL must read the body column and scan it. This is still fast — 1-3 milliseconds for typical content — but it does mean that SearchHeadline reads more data than a simple filter-and-rank query. If you are searching a table with very large text columns (10,000+ words per row), benchmark with and without SearchHeadline to see whether the cost is acceptable for your use case.

For additional background on how GIN indexes work alongside B-tree, GiST, and other PostgreSQL index types, The guide to the full index taxonomy covers each type's strengths in the detail they deserve. And if your Django models store structured data in JSONB columns, GIN indexes serve double duty there as well — the same index type, different operator class.

GIN index maintenance: keeping the machinery in order

A GIN index, like any index, requires periodic attention. Not a great deal of it — PostgreSQL's autovacuum handles most of the housekeeping — but enough that you should know what to monitor and when to intervene.

-- Check your GIN index health and size
SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS times_used,
    idx_tup_read AS rows_returned
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%search_vector%'
   OR indexrelname LIKE '%trgm%';

-- Example output:
-- index_name                    | index_size | times_used | rows_returned
-- articles_search_vector_idx    | 22 MB      | 148293     | 1847261
-- title_trgm_idx                | 31 MB      | 4217       | 53012

-- GIN indexes accumulate "pending" entries during fast updates.
-- These are flushed during VACUUM or when gin_pending_list_limit is reached.
-- Check pending entries:
SELECT * FROM pg_stat_user_indexes
WHERE indexrelname = 'articles_search_vector_idx';

-- If search feels intermittently slow, pending entries may be the cause.
-- They require a linear scan until flushed into the main index structure.
-- Force a cleanup:
VACUUM articles_article;

-- Or tune the pending list limit (default 4MB):
ALTER INDEX articles_search_vector_idx SET (gin_pending_list_limit = 2097152);
-- Smaller limit = more frequent flushes = more consistent query speed
-- Larger limit = fewer flushes = better write throughput, but occasional slow queries

Three things to watch:

1. Index size relative to table size. A healthy GIN index on a tsvector column is typically 5-15% of the table size. If it has grown to 30-40%, the index may be bloated from heavy update activity. A REINDEX CONCURRENTLY will compact it.
2. Pending list size. GIN indexes buffer new entries in a pending list before flushing them into the main index structure. While entries are pending, they must be scanned linearly during queries — which means search speed degrades temporarily after a burst of writes. Autovacuum flushes the pending list, but if your write volume outpaces autovacuum, you may see intermittent slow queries. Lowering gin_pending_list_limit forces more frequent flushes at the cost of slightly more write overhead.
3. Usage counts. The idx_scan column in pg_stat_user_indexes tells you how many times the index has been used. If your trigram index shows zero scans after a month, nobody is hitting the fuzzy search fallback — which either means your full-text search is excellent or your fallback code has a bug. Both are worth investigating.

If you need to rebuild an index in production:

-- If your GIN index becomes bloated or corrupted, rebuild it.
-- REINDEX locks the table. Use CONCURRENTLY in production.

-- PostgreSQL 12+:
REINDEX INDEX CONCURRENTLY articles_search_vector_idx;

-- For Django migrations, wrap it in RunSQL:
migrations.RunSQL(
    sql="REINDEX INDEX CONCURRENTLY articles_search_vector_idx;",
    reverse_sql="-- No reverse needed for REINDEX",
)

-- Check bloat before deciding to reindex:
SELECT
    pg_size_pretty(pg_relation_size('articles_search_vector_idx')) AS current_size;

-- Rebuild and compare:
-- If the rebuilt index is 30%+ smaller, bloat was significant.
-- If it is roughly the same size, the index was healthy. Leave it alone next time.

REINDEX CONCURRENTLY builds a new index alongside the old one, then swaps them atomically. No lock on the table. No downtime. The only cost is temporary additional disk space for the new index while it is being built. For a 30 MB GIN index, that means 30 MB of temporary space. The household barely notices.

The honest counterpoint: when PostgreSQL full-text search is not enough

I should be forthcoming about the limits of this approach, because pretending they do not exist would be a disservice to you and an embarrassment to me.

PostgreSQL full-text search is excellent for structured, language-aware search over relational data. It handles stemming, ranking, phrase matching, and boolean queries with sub-millisecond latency on properly indexed tables. For most Django applications — content sites, admin panels, e-commerce catalogs, documentation platforms — it is more than sufficient. It is, in fact, more capable than what most applications need.

But there are domains where it genuinely falls short:

• Faceted search. If your search results page needs to display "127 results in 'Python', 42 in 'JavaScript', 89 in 'DevOps'" alongside the results — those facet counts, computed in real time, are expensive in PostgreSQL. Elasticsearch and Solr were designed for this pattern. You can approximate it with GROUP BY and COUNT on your filtered results, but it requires a separate aggregation query and does not scale as cleanly to dozens of facet dimensions.
• Synonym expansion and custom analyzers. PostgreSQL's text search configurations support synonym dictionaries, but configuring them is manual — you maintain a flat file of synonym mappings. Elasticsearch's synonym filters are more flexible, support multi-word synonyms, and can be updated without rebuilding the index. If your search needs "laptop" to match "notebook" and "MacBook," PostgreSQL can do it, but the ergonomics are notably less pleasant.
• Extremely high query volume on very large corpora. If you are serving 10,000 search queries per second across 50 million documents, you need a distributed search cluster with horizontal scaling, query routing, and result caching built into the architecture. PostgreSQL is a single-node database. It scales vertically (bigger machine) and through read replicas, but it does not shard a GIN index across nodes. At this scale, Elasticsearch or Meilisearch earn their operational complexity.
• Vector search and semantic similarity. If "how to make coffee" should match an article titled "brewing techniques for pour-over enthusiasts," you need vector embeddings, not lexical matching. PostgreSQL supports this through pgvector, but that is a different architecture from full-text search and a different article entirely.

For the vast majority of Django applications, these limits are theoretical, not practical. Your articles table has 50,000 rows, not 50 million. Your search traffic is 10 queries per second, not 10,000. Your users are searching for specific terms, not semantic concepts. PostgreSQL handles this gracefully, without adding another service to your infrastructure, another deployment to your CI pipeline, another dashboard to your monitoring stack, and another line item to your hosting bill.

The advice to add more services arrives most enthusiastically from those who sell them. Elasticsearch is a fine piece of software. It is also a distributed Java application that requires its own cluster, its own monitoring, its own security configuration, and its own team of people who understand why the cluster health turned yellow at 3 AM. If your search problem can be solved with a column and an index on the database you already have, that is not a compromise. That is good engineering. For a thorough comparison of PostgreSQL search capabilities against Elasticsearch, The trade-offs between PostgreSQL and Elasticsearch warrant their own discussion — and have one, in a thorough side-by-side comparison.

"Simplicity is not the absence of capability. It is the discipline to use existing capability before adding new complexity."

— from You Don't Need Redis, Chapter 19: The Case for Simplicity

Common mistakes I have attended to

Having observed many Django applications adopt full-text search, I should like to note the patterns that most frequently require correction.

Forgetting to backfill existing rows. You add the SearchVectorField, create the trigger, deploy. New articles are searchable. Existing articles have NULL search vectors. Your users search for content that existed before the migration and find nothing. The fix is a one-time backfill — Article.objects.update(search_vector=...) — but it must be included in the migration or run immediately after. Do not rely on someone remembering to do it manually.

Using the wrong text search configuration. The default configuration is 'english', which applies English stemming rules. If your content is in German, French, or Spanish, use the appropriate configuration: 'german', 'french', 'spanish'. PostgreSQL ships with configurations for over 15 languages. Using the wrong stemmer does not produce errors — it produces poor search results, because the stemmer will mangle words according to the wrong language's rules. "laufen" (German for "to run") stemmed with the English stemmer produces "laufen" (unchanged), not "lauf" (the correct German stem).

Not indexing the right fields. I have seen applications that index only the title, ignoring the body. And applications that index the body but not the title. And applications that index the title, body, and the raw HTML markup of the body, so that a search for "div" returns every article on the site. Index the fields that contain the words your users will search for. Strip HTML before indexing if your content is stored as rich text. The trigger is the right place for this transformation.

Ignoring NULL handling. If title or body can be NULL, to_tsvector('english', NULL) returns NULL — and NULL || 'anything' is NULL in PostgreSQL. The entire search vector for that row becomes NULL, and the row becomes unsearchable. This is why the trigger uses COALESCE(NEW.title, ''). If you are writing the signal-based approach, apply the same safeguard in Python.

Computing the search vector in the Django view. I have encountered applications that compute the SearchVector annotation in the search view but never store it. Every request recomputes. The view returns correct results. The performance suffers accordingly. The developer does not understand why, because the code "uses SearchVector like the documentation says." This is the naive approach with extra steps, and it is the single most common implementation I attend to.

Where Gold Lapel fits in

Everything above requires you to know the problem exists, diagnose it, add the column, create the index, write the trigger, and update your queries. It is not difficult work — this article contains every line of code you need — but it is work that must be done proactively. And most teams do not discover the problem until the search bar is already slow in production, a support ticket has been filed, and someone is investigating during their Friday afternoon.

Gold Lapel sits between your Django application and PostgreSQL as a transparent proxy. When it observes to_tsvector() calls in WHERE clauses — the pattern produced by naive SearchVector annotations — it recognizes the full-text search pattern and can create appropriate GIN indexes on the tsvector expression. It also detects LIKE '%term%' and ILIKE patterns and creates trigram indexes for those.

This does not replace the SearchVectorField approach. A stored column with a trigger is still the optimal architecture for full-text search — it supports weighted fields, it avoids expression matching fragility, and it produces the fastest possible query plans. But Gold Lapel catches the gap between "we know we should optimize this" and "we have optimized this" — keeping queries fast while the proper fix is being developed, reviewed, tested, and deployed.

The search bar should be fast from day one. Not from the day someone notices it is slow.