Implementing Search: PostgreSQL vs Elasticsearch

When you need search in your Django app, two options stand out: PostgreSQL Full-Text Search (FTS) and Elasticsearch. This guide helps you decide which fits your project and shows you how to implement both.

1. Quick Comparison: When To Use Which

Choose PostgreSQL Full-Text Search If:

Your dataset is under 1-2 million rows
You already use PostgreSQL
You want simplicity and low maintenance
You need real-time consistency (search results match database state)
Budget is tight (no extra infrastructure)

Choose Elasticsearch If:

You have millions of documents or large text bodies
You need advanced features: fuzzy search, synonyms, autocomplete, aggregations
Search performance is critical at scale
You can invest time in setup and maintenance

Reference: Django PostgreSQL Full-Text Search Documentation

2. Performance Benchmarks

Based on real-world testing:

PostgreSQL FTS: ~30-50ms for 1.5M records (with GIN index)
Elasticsearch: ~5-30ms for 1.5M records

For under 500K records, PostgreSQL FTS is often fast enough. Elasticsearch shines when you need consistent sub-30ms responses at scale or advanced search features.

Reference: Full-Text Search: PostgreSQL vs Elasticsearch (Fueled)

3. PostgreSQL Full-Text Search in Django

3.1 Setup

# settings.py
INSTALLED_APPS = [
# ...
'django.contrib.postgres',
]

3.2 Basic Search

from django.contrib.postgres.search import SearchVector
# Search single field
results = Article.objects.filter(body__search='django')
# Search multiple fields
results = Article.objects.annotate(
search=SearchVector('title', 'body')
).filter(search='django tutorial')

3.3 Ranked Search Results

from django.contrib.postgres.search import (
SearchVector, SearchQuery, SearchRank
)
query = SearchQuery('django rest api')
vector = SearchVector('title', weight='A') + SearchVector('body', weight='B')
results = Article.objects.annotate(
rank=SearchRank(vector, query)
).filter(rank__gte=0.1).order_by('-rank')

Weights (A, B, C, D) control importance. A is highest priority.

3.4 Optimize with SearchVectorField

Pre-computing search vectors dramatically improves performance:

from django.contrib.postgres.search import SearchVectorField
from django.contrib.postgres.indexes import GinIndex
class Article(models.Model):
title = models.CharField(max_length=200)
body = models.TextField()
search_vector = SearchVectorField(null=True)
class Meta:
indexes = [
GinIndex(fields=['search_vector']),
]

Update the search vector when content changes:

from django.contrib.postgres.search import SearchVector
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):
Article.objects.filter(pk=instance.pk).update(
search_vector=SearchVector('title', weight='A') + 
SearchVector('body', weight='B')
)

Query using the indexed field:

from django.contrib.postgres.search import SearchQuery
query = SearchQuery('django')
results = Article.objects.filter(search_vector=query)

Reference: Efficient Postgres Full Text Search in Django (pganalyze)

3.5 Trigram Similarity (Fuzzy Search)

For typo-tolerant search, use trigram similarity:

# First, enable the extension in PostgreSQL:
# CREATE EXTENSION pg_trgm;
from django.contrib.postgres.search import TrigramSimilarity
results = Article.objects.annotate(
similarity=TrigramSimilarity('title', 'djnago')  # typo
).filter(similarity__gt=0.3).order_by('-similarity')

Warning: Trigram searches are 3-5x slower than standard FTS. Use sparingly.

4. Elasticsearch in Django

4.1 Setup

pip install django-elasticsearch-dsl elasticsearch

# settings.py
INSTALLED_APPS = [
# ...
'django_elasticsearch_dsl',
]
ELASTICSEARCH_DSL = {
'default': {
'hosts': 'localhost:9200'
},
}

4.2 Define a Document

from django_elasticsearch_dsl import Document, fields
from django_elasticsearch_dsl.registries import registry
from .models import Article
@registry.register_document
class ArticleDocument(Document):
class Index:
name = 'articles'
settings = {
'number_of_shards': 1,
'number_of_replicas': 0
}
class Django:
model = Article
fields = ['title', 'body', 'created_at']

4.3 Build the Index

python manage.py search_index --rebuild

4.4 Basic Search

from .documents import ArticleDocument
# Simple search
search = ArticleDocument.search()
results = search.query('match', title='django')
# Multi-field search
results = search.query(
'multi_match',
query='django api',
fields=['title^2', 'body']  # title has 2x weight
)
# Execute and get Django objects
articles = results.to_queryset()

4.5 Fuzzy Search and Autocomplete

# Fuzzy search (handles typos)
results = ArticleDocument.search().query(
'match',
title={
'query': 'djnago',  # typo
'fuzziness': 'AUTO'
}
)
# Autocomplete with edge_ngram
@registry.register_document
class ArticleDocument(Document):
title = fields.TextField(
analyzer='autocomplete',
search_analyzer='standard'
)
class Index:
name = 'articles'
settings = {
'analysis': {
'analyzer': {
'autocomplete': {
'type': 'custom',
'tokenizer': 'standard',
'filter': ['lowercase', 'autocomplete_filter']
}
},
'filter': {
'autocomplete_filter': {
'type': 'edge_ngram',
'min_gram': 2,
'max_gram': 10
}
}
}
}

Reference: django-elasticsearch-dsl Documentation

4.6 Highlighting Search Matches

search = ArticleDocument.search()
search = search.query('match', body='django')
search = search.highlight('body', fragment_size=150)
for hit in search:
print(hit.meta.highlight.body)  # highlighted snippets

4.7 Aggregations

from elasticsearch_dsl import A
search = ArticleDocument.search()
search.aggs.bucket('by_category', A('terms', field='category.keyword'))
response = search.execute()
for bucket in response.aggregations.by_category.buckets:
print(bucket.key, bucket.doc_count)

5. Running Elasticsearch

5.1 Docker (Recommended for Development)

docker run -d --name elasticsearch \
-p 9200:9200 \
-e "discovery.type=single-node" \
-e "xpack.security.enabled=false" \
elasticsearch:8.11.0

5.2 Verify Connection

curl http://localhost:9200
# Should return cluster info

Reference: Elasticsearch Docker Guide

6. Trade-offs Summary

Feature	PostgreSQL FTS	Elasticsearch
Setup complexity	Low (built-in)	High (separate service)
Maintenance	Minimal	Requires monitoring, tuning
Data consistency	Real-time (same DB)	Eventually consistent (sync lag)
Speed at scale (1M+ docs)	Good (~30-50ms)	Excellent (~5-30ms)
Fuzzy search	Trigram (slow)	Native (fast)
Synonyms	Manual	Built-in
Autocomplete	Limited	Excellent
Aggregations	Use SQL	Native, fast
Cost	Free (use existing DB)	Extra infrastructure

7. Hybrid Approach

You can use both: PostgreSQL FTS for simple, real-time searches and Elasticsearch for complex queries.

def search_articles(query, use_elasticsearch=False):
if use_elasticsearch and len(query) >= 3:
# Complex search with fuzzy matching
search = ArticleDocument.search()
return search.query(
'multi_match',
query=query,
fields=['title^2', 'body'],
fuzziness='AUTO'
).to_queryset()
else:
# Simple, real-time search
return Article.objects.filter(search_vector=SearchQuery(query))

8. New Option: ParadeDB (pg_search)

ParadeDB offers Elasticsearch-quality search inside PostgreSQL using BM25 ranking:

-- Elasticsearch-like syntax in PostgreSQL
SELECT * FROM articles
WHERE articles @@@ 'django OR python'

Features:

20x faster than native PostgreSQL tsquery
BM25 relevance scoring (same as Elasticsearch)
Fuzzy search, highlighting, aggregations
No separate service to maintain

Reference: ParadeDB Official Site

9. Recommendation

Start with PostgreSQL FTS. It's free, built-in, and fast enough for most apps.
Add GIN indexes and SearchVectorField for performance.
Move to Elasticsearch when you need: sub-10ms responses at scale, advanced fuzzy/autocomplete, or complex aggregations.
Consider ParadeDB if you want Elasticsearch features without the operational overhead.