Django and PostgreSQL in 2026: Indexing, Full-Text Search and Interview Questions

Django PostgreSQL optimization is the difference between an application that scales past its first thousand users and one that grinds to a halt on every list view. PostgreSQL ships with an indexing engine and a full-text search subsystem that most Django projects leave untouched, wasting query performance that costs nothing to reclaim. This guide covers the indexing patterns, django.contrib.postgres search tools, and the database interview questions that separate mid-level from senior Django engineers in 2026.

PostgreSQL Indexing Fundamentals in Django

By default, PostgreSQL creates a B-tree index only on primary keys and columns with unique=True. Every other filtered or sorted column runs a sequential scan until an index exists. Django exposes indexes through Meta.indexes, which is preferred over the older db_index=True because it supports composite, partial, and covering indexes in one place.

Column order inside a composite index is not cosmetic. A B-tree can use an index for a query only when the filter uses a leading prefix of the indexed columns, a rule explained in depth on Use The Index, Luke. An index on (status, created_at) serves WHERE status = ? ORDER BY created_at, but the same index cannot accelerate a query that filters on created_at alone.

# models.py
from django.db import models

class Order(models.Model):
    customer_email = models.EmailField()
    status = models.CharField(max_length=20)
    total = models.DecimalField(max_digits=10, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            # Single-column B-tree for exact and range lookups on status
            models.Index(fields=["status"]),
            # Composite index: serves WHERE status = ? ORDER BY created_at DESC
            # Leading column (status) must appear in the filter to be used
            models.Index(fields=["status", "-created_at"]),
        ]

The composite index makes a dashboard query like "latest pending orders" a single index scan instead of a filter-then-sort over the whole table.

Partial and Covering Indexes for Targeted Queries

A partial index covers only rows matching a condition, so it stays small and stays in memory. When most rows share a value and queries only ever touch the minority, a partial index is dramatically cheaper than a full one. A covering index goes further: by adding non-key columns with include, PostgreSQL answers the query directly from the index without touching the table heap, an access pattern called an index-only scan.

# models.py
from django.db import models
from django.db.models import Q

class Order(models.Model):
    # fields as defined above

    class Meta:
        indexes = [
            # Partial index: only indexes pending rows, ignoring completed history
            models.Index(
                fields=["created_at"],
                name="pending_orders_idx",
                condition=Q(status="pending"),
            ),
            # Covering index: total is read straight from the index (index-only scan)
            models.Index(
                fields=["status"],
                include=["total"],
                name="status_total_covering_idx",
            ),
        ]

On an orders table where 2% of rows are pending, the partial index can be fifty times smaller than a full index on the same column, and the planner keeps it hot in the buffer cache. The include clause is documented under PostgreSQL index-only scans and is the single most overlooked win in Django applications that aggregate a numeric column behind a status filter.

Django Full-Text Search With PostgreSQL

Reaching for title__icontains=query feels convenient, but it forces a sequential scan and cannot rank results by relevance. Django full text search wraps PostgreSQL's native tsvector and tsquery types through SearchVector, SearchQuery, and SearchRank. A search vector normalizes text into lexemes, stripping stop words and reducing words to their stems, so a search for "running" matches "run" and "ran".

# views.py
from django.contrib.postgres.search import (
    SearchVector, SearchQuery, SearchRank,
)
from .models import Article

def search_articles(query_text):
    query = SearchQuery(query_text, config="english")
    # Weight A ranks title matches above body matches (weight B)
    vector = (
        SearchVector("title", weight="A")
        + SearchVector("body", weight="B")
    )
    return (
        Article.objects.annotate(rank=SearchRank(vector, query))
        .filter(rank__gte=0.1)
        .order_by("-rank")
    )

The weight parameter assigns priority buckets from A (highest) to D. A title match ranks above a body match even when both contain the search term, which matches how users expect a search to behave. The full API and the four-tier weighting model are covered in the Django full-text search reference.

GIN Indexes and SearchVectorField for Fast Search

The query above recomputes the search vector for every row on every request, which is fine for a few thousand rows and unacceptable for a million. The production pattern stores the vector in a SearchVectorField and indexes it with a GIN index, the index type PostgreSQL uses for composite values like full-text documents and JSONB.

# models.py
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=255)
    body = models.TextField()
    # Stored, pre-computed search document
    search_vector = SearchVectorField(null=True)

    class Meta:
        indexes = [
            # GIN turns full-text lookups into logarithmic index scans
            GinIndex(fields=["search_vector"]),
        ]

The stored field needs to stay in sync with title and body. A post_save signal that writes the vector with .update() avoids triggering the signal recursively, since QuerySet.update() does not emit save signals.

# signals.py
from django.contrib.postgres.search import SearchVector
from django.db.models.signals import post_save
from django.dispatch import receiver
from .models import Article

@receiver(post_save, sender=Article)
def sync_search_vector(sender, instance, **kwargs):
    Article.objects.filter(pk=instance.pk).update(
        search_vector=SearchVector("title", weight="A")
        + SearchVector("body", weight="B"),
    )

For teams on Django 5.0 or newer, a database-computed GeneratedField or a PostgreSQL trigger removes the extra UPDATE entirely by maintaining the column inside the database. Whichever mechanism keeps it populated, the query then filters the indexed field directly, and the GIN index turns what was a full table scan into a logarithmic lookup.

Choosing the Right PostgreSQL Index Type

B-tree is the correct default, but PostgreSQL exposes several index types through django.contrib.postgres.indexes, and picking the wrong one wastes disk without improving a single query. The choice follows the shape of the data and the operator the query uses, as catalogued in the PostgreSQL index types documentation.

| Index type | Best for | Django class | |------------|----------|--------------| | B-tree | Equality and range on scalar columns | models.Index | | GIN | Full-text search, JSONB, array containment | GinIndex | | BRIN | Huge append-only tables ordered by a column | BrinIndex | | Hash | Equality-only lookups on large columns | HashIndex |

BRIN deserves special attention for time-series data. On a table of tens of millions of rows inserted in timestamp order, a BrinIndex on created_at occupies a few kilobytes where a B-tree would need hundreds of megabytes, because BRIN stores only the min and max value per block range. The tradeoff is that BRIN only helps when physical row order tracks the indexed column, which naturally holds for append-only logs and event tables.

# models.py
from django.contrib.postgres.indexes import BrinIndex
from django.db import models

class Event(models.Model):
    payload = models.JSONField()
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            # Tiny index for range scans on an append-only, time-ordered table
            BrinIndex(fields=["created_at"]),
        ]

Matching the index type to the access pattern is a recurring theme in senior interviews, because it proves a candidate reasons about storage layout rather than reflexively adding B-trees everywhere.

Django Database Interview Questions in 2026

Database questions dominate senior Django interviews because ORM fluency does not imply an understanding of what the ORM emits. The questions below reflect what hiring panels actually probe in 2026, and they pair naturally with the query-level tuning covered in the Django ORM query optimization guide.

When does a composite index on (a, b) fail to help a query? When the query does not filter on a. A B-tree is ordered by its leading column first, so a filter on b alone cannot use the index. This is the leftmost-prefix rule, and candidates who explain it demonstrate they understand index structure rather than memorized recipes.

What is the difference between select_related and prefetch_related? select_related performs a SQL JOIN and works for foreign-key and one-to-one relationships in a single query. prefetch_related runs a second query and joins the results in Python, which is required for many-to-many and reverse foreign-key relationships. Reaching for the wrong one either misses the optimization or triggers an unnecessary second query.

How does icontains differ from full-text search at the database level? icontains compiles to ILIKE '%term%', which cannot use a standard B-tree index and scans every row. Full-text search matches against a pre-computed tsvector backed by a GIN index and ranks results by relevance. The distinction is a reliable signal that a candidate has moved beyond toy datasets.

Why can count() be slow on a large table, and what are the alternatives? PostgreSQL has no cached row count for a table, so COUNT(*) scans every visible row under MVCC. For approximate counts, querying pg_class.reltuples returns an estimate instantly, and for pagination, keyset pagination on an indexed column avoids counting altogether. Structured practice on these patterns is available in the Django caching interview module, where cache-backed counters are a recurring theme, and across the full Django learning track.

What does EXPLAIN ANALYZE reveal that EXPLAIN alone does not? EXPLAIN prints the planner's estimated cost; EXPLAIN ANALYZE actually executes the query and reports real timings and row counts. A large gap between estimated and actual rows points to stale table statistics, fixable with ANALYZE, and is a common root cause of the planner ignoring an index that should obviously apply.

Conclusion

• Define indexes in Meta.indexes rather than db_index=True so composite, partial, and covering indexes live in one auditable place.
• Order composite index columns by the leftmost-prefix rule: the leading column must appear in the query filter for the index to apply.
• Use partial indexes when queries only ever touch a minority of rows, such as pending orders in a table dominated by completed history.
• Add covering indexes with include to enable index-only scans and skip heap access on read-heavy aggregate queries.
• Replace icontains search with SearchVector, SearchQuery, and SearchRank to get relevance ranking and stemming for free.
• Store the search document in a SearchVectorField backed by a GinIndex, kept in sync by a signal, GeneratedField, or database trigger, before full-text search hits production scale.
• Always validate index decisions with EXPLAIN (ANALYZE, BUFFERS); an index the planner never selects is a write-time tax with no read-time benefit.