Push your Django app beyond basic queries. Master B-tree vs GIN indexes, JSONB for semi-structured data, full-text search with ranking, and window functions for analytics.
Most Django apps use PostgreSQL as a plain row store and leave its real power untouched. Yet the difference between a query that scans a million rows and one that seeks straight to ten is a single well-chosen index, and the difference between a slow JSON soup and a fast, queryable document store is knowing how JSONB works. This tutorial goes deep on the PostgreSQL features that matter most to Django developers: indexing, JSONB, full-text search, and the query-planning knowledge that ties them together.
Application code is rarely the bottleneck in a web app — the database is. A view that feels slow is almost always waiting on a query that scans too much, lacks an index, or fires N+1 times. This means the highest-leverage performance skill a Django developer can have is understanding what PostgreSQL is actually doing with your queries. The ORM is a convenient abstraction, but it generates SQL that the database executes according to a plan, and when that plan is bad, no amount of caching or faster hardware fixes the root cause cleanly. Learning to see through the ORM to the SQL and the plan beneath is what turns mysterious slowness into a specific, fixable problem.
The single most important tool is EXPLAIN (ANALYZE, BUFFERS), which shows how PostgreSQL executes a query: the plan it chose, whether it used an index or scanned the whole table, how long each step took, and how its row estimates compared to reality. The cardinal sign of trouble is a sequential scan on a large table where you expected an index scan — PostgreSQL is reading every row because no useful index exists or its statistics are stale. Get into the habit of running EXPLAIN on any query that feels slow; it replaces speculation with a precise account of where the time and IO go, and it tells you exactly which fix — an index, a rewrite, fresher statistics — will help.
You do not need to copy SQL into psql to see a plan. Every QuerySet has an explain() method that passes options through to PostgreSQL, and str(qs.query) shows the SQL Django generated (with parameters interpolated for display only — never execute that string directly):
qs = (Order.objects
.filter(status="pending", created_at__gte=cutoff)
.order_by("-created_at")[:50])
print(qs.query) # the generated SQL
print(qs.explain(analyze=True, buffers=True)) # runs the query and returns the plan
analyze=True really executes the statement; when explaining an UPDATE or DELETE by hand in psql, wrap it in BEGIN; … ROLLBACK;. Look first for a Seq Scan with a large Rows Removed by Filter, and for estimated row counts that differ wildly from actual ones — the latter usually means running ANALYZE on the table.
An index is a data structure that lets PostgreSQL find rows without scanning the whole table, and it is the difference between a query that takes milliseconds and one that takes seconds. The default B-tree index suits equality and range queries on a column — exactly what most filters and joins do. In Django you declare them in Meta.indexes or via db_index=True:
class Order(models.Model):
customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
status = models.CharField(max_length=20)
created_at = models.DateTimeField()
class Meta:
indexes = [models.Index(fields=["status", "created_at"])]
Every column you regularly filter or join on wants an index. The cost is that indexes slow writes slightly and take disk space, so index deliberately for your real query patterns rather than indexing everything.
A plain CREATE INDEX, which is what Django's default AddIndex migration emits, holds a lock that blocks every INSERT, UPDATE and DELETE on the table until the build finishes. PostgreSQL's CREATE INDEX CONCURRENTLY builds the index while writes continue, at the cost of taking longer and not being allowed inside a transaction. Django ships a migration operation for it:
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models
class Migration(migrations.Migration):
atomic = False # CONCURRENTLY cannot run inside a transaction
dependencies = [("shop", "0041_previous")]
operations = [
AddIndexConcurrently(
"order",
models.Index(fields=["customer", "created_at"],
name="order_customer_created_idx"),
),
]
If a concurrent build fails partway (a deadlock, a unique violation, a cancelled deploy), it leaves an invalid index behind that is maintained on every write but never used for reads. Check for leftovers after any failed deploy and drop them with DROP INDEX CONCURRENTLY before retrying:
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;
The matching RemoveIndexConcurrently operation drops indexes the same way. Remember to also keep the index in the model's Meta.indexes so the migration autodetector stays in sync with the database.
A composite index covers multiple columns, and column order matters: an index on (status, created_at) efficiently serves queries filtering on status, or on status and a created-at range, but not one filtering on created_at alone — the leading column must be used. Order the columns to match how you actually query, most selective first. A partial index covers only a subset of rows defined by a condition, like WHERE status = 'active', which keeps the index small and fast when you only ever query that subset. Partial indexes are a quietly powerful tool: an index on just the active orders is a fraction of the size of one over all orders, and proportionally faster.
Selectivity is not the only factor in column order. A B-tree can only use the columns after a range condition as a filter, not to narrow the scan. For WHERE status = 'pending' AND created_at >= …, the index (status, created_at) jumps to the first pending row in the range and reads contiguously; the reverse order (created_at, status) walks every row in the date range and discards the non-pending ones. The practical rule: columns compared with = go first, then the one column used for a range or ORDER BY.
Both are declared with ordinary models.Index arguments. A partial index takes a condition; a covering index takes include, which stores extra columns in the index leaf pages so the query can be answered by an index-only scan without visiting the table. Both require an explicit name:
from django.db.models import Q
class Order(models.Model):
# ... fields as above, plus:
total = models.DecimalField(max_digits=12, decimal_places=2)
class Meta:
indexes = [
# Only the rows the dashboard ever looks at
models.Index(
fields=["created_at"],
condition=Q(status="pending"),
name="order_pending_created_idx",
),
# Answers "orders for a customer, newest first, with totals"
# without touching the heap
models.Index(
fields=["customer", "-created_at"],
include=["total", "status"],
name="order_customer_recent_idx",
),
]
The partial index is only used when the query's WHERE clause provably implies the index condition. filter(status="pending") qualifies; filter(status__in=["pending", "held"]) does not, and the planner will silently ignore the index. Index-only scans also rely on a current visibility map, so a large Heap Fetches count in the plan usually means vacuum is lagging on that table.
PostgreSQL's JSONB type stores JSON in a binary, indexed, queryable form, giving you schemaless flexibility inside a relational database. It is ideal for data whose shape varies or evolves — settings, metadata, event payloads, flexible product attributes — without forcing a migration for every new field. Django exposes it as JSONField:
class Product(models.Model):
attributes = models.JSONField(default=dict)
# query into the JSON
Product.objects.filter(attributes__color="red")
Product.objects.filter(attributes__dimensions__width__gte=10)
You query nested keys with the double-underscore syntax, and it all happens in the database, not in Python. JSONB is the pragmatic middle ground between rigid columns and a separate document database.
JSONB queries can be slow without the right index, because by default the database must inspect each row's JSON. A GIN index on a JSONB column makes containment and key-existence queries fast, letting PostgreSQL find matching documents without scanning. For queries that always hit a specific key, an expression index on just that key path is even tighter. The lesson mirrors ordinary columns: JSONB is only as fast as its indexing, so if you query a JSONB field at scale, add a GIN index or you have built a slow full scan. Used with the right index, JSONB delivers document-store query performance without leaving PostgreSQL.
The subtle part is that a GIN index only helps operators it supports, and Django's lookups compile to different operators. A key lookup such as attributes__color="red" becomes (attributes -> 'color') = '"red"', which a GIN index on the whole column cannot serve. The containment lookup attributes__contains={"color": "red"} becomes attributes @> '{"color": "red"}', which it can. The key-existence lookups has_key, has_keys and has_any_keys map to ?, ?& and ?|, also GIN-friendly with the default operator class.
from django.contrib.postgres.indexes import GinIndex
from django.db.models.fields.json import KeyTextTransform
class Product(models.Model):
attributes = models.JSONField(default=dict)
class Meta:
indexes = [
# Smaller and faster for @> only; does not support has_key
GinIndex(fields=["attributes"], name="product_attrs_gin",
opclasses=["jsonb_path_ops"]),
# B-tree on one extracted key, for equality and ordering
models.Index(KeyTextTransform("sku_family", "attributes"),
name="product_sku_family_idx"),
]
# Uses product_attrs_gin
Product.objects.filter(attributes__contains={"color": "red"})
# Uses product_sku_family_idx: the expression matches the index exactly
Product.objects.annotate(
sku_family=KeyTextTransform("sku_family", "attributes")
).filter(sku_family="TX-200")
The choice between the two GIN operator classes is a real trade-off: the default jsonb_ops indexes every key and value separately and supports existence operators; jsonb_path_ops hashes whole paths, producing an index often a third of the size that is faster for containment but useless for has_key. If your code only uses contains, choose jsonb_path_ops. Always confirm with explain() — a Bitmap Index Scan on product_attrs_gin in the plan is the proof.
JSONB is powerful but not a default. Data you query, filter, join, and constrain heavily belongs in proper columns, where types, foreign keys, and constraints protect integrity and the planner optimizes best. JSONB shines for the genuinely variable or sparse — attributes that differ per row, payloads you store but rarely query deeply, evolving structures you do not want to migrate constantly. A common and sound pattern is hybrid: the core, queried fields as columns, and a JSONB column for the long tail of flexible attributes. Reaching for JSONB to avoid designing a schema is a trap; using it where flexibility is genuinely needed is excellent engineering.
Before adding Elasticsearch, know that PostgreSQL has capable built-in full-text search — enough for most applications. It tokenizes text into lexemes, handles stemming so "running" matches "run", ranks results by relevance, and Django wraps it cleanly:
from django.contrib.postgres.search import SearchVector, SearchQuery, SearchRank
vector = SearchVector("title", weight="A") + SearchVector("body", weight="B")
query = SearchQuery("django performance")
Article.objects.annotate(rank=SearchRank(vector, query)).filter(
rank__gte=0.1).order_by("-rank")
Weighting lets a title match outrank a body match. For real use, store the search vector in a column with a GIN index and keep it updated with a trigger, so search does not recompute vectors on every query.
The annotation above calls to_tsvector for every row on every search, which is fine for a few thousand articles and hopeless for a few million. The production shape is a SearchVectorField with a GIN index, maintained by a database trigger so every writer — the ORM, bulk imports, a manual psql fix — keeps it correct:
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVectorField
class Article(models.Model):
title = models.CharField(max_length=300)
body = models.TextField()
search = SearchVectorField(null=True, editable=False)
class Meta:
indexes = [GinIndex(fields=["search"], name="article_search_gin")]
Then a migration installs the trigger and backfills existing rows. Using an explicit text search configuration ('english') matters: without one, results depend on the server's default_text_search_config setting, and a query built with a different configuration than the stored vector will stem words differently and miss matches.
TRIGGER_SQL = """
CREATE FUNCTION article_search_update() RETURNS trigger AS $$
BEGIN
NEW.search :=
setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER article_search_trigger
BEFORE INSERT OR UPDATE OF title, body ON blog_article
FOR EACH ROW EXECUTE FUNCTION article_search_update();
UPDATE blog_article SET title = title; -- backfill via the trigger
"""
REVERSE_SQL = """
DROP TRIGGER IF EXISTS article_search_trigger ON blog_article;
DROP FUNCTION IF EXISTS article_search_update();
"""
class Migration(migrations.Migration):
dependencies = [("blog", "0012_article_search")]
operations = [migrations.RunSQL(TRIGGER_SQL, REVERSE_SQL)]
On a large table, do the backfill in batches outside the migration instead of the single UPDATE, which rewrites every row in one transaction. Querying the stored column is then a plain filter that uses the index:
from django.contrib.postgres.search import SearchQuery, SearchRank
from django.db.models import F
query = SearchQuery(user_input, search_type="websearch", config="english")
results = (Article.objects
.filter(search=query)
.annotate(rank=SearchRank(F("search"), query))
.order_by("-rank")[:20])
search_type="websearch" accepts the syntax users know from search engines — quoted phrases, OR, a leading minus to exclude a word — and never raises a syntax error on odd input, so it is the safe choice for a search box.
Full-text search handles words; it does not handle typos or partial matches well. For "did you mean" behavior, autocomplete, and fuzzy matching, the pg_trgm extension breaks text into three-character chunks and measures similarity, so "djnago" still matches "django". A GIN or GiST trigram index makes these similarity queries fast. Trigram matching complements full-text search: use full-text for relevance-ranked document search, and trigrams for fuzzy name lookups, autocomplete, and tolerating the misspellings real users type. Together they cover most search needs without a separate search engine and its operational burden.
Validation in your application is necessary but not sufficient — a bug, a shell, or a second writer can bypass it, so your most important invariants belong in the database where nothing can escape them. PostgreSQL offers check constraints, unique constraints (including partial and multi-column), and exclusion constraints, all expressible in Django:
class Booking(models.Model):
class Meta:
constraints = [
models.CheckConstraint(condition=models.Q(end__gt=models.F("start")), # "check=" before Django 5.1
name="end_after_start"),
models.UniqueConstraint(fields=["room", "date"],
name="one_booking_per_room_per_day"),
]
A constraint guarantees the rule holds for every row, no matter how the data got there. Treat the database as the final guardian of integrity, not just a passive store.
Transactions group operations so they all commit or all roll back, and getting them right is essential for correctness under concurrency. Wrap multi-step changes in transaction.atomic so a partial failure leaves no half-written state. For data that concurrent requests might modify together — inventory, balances — use select_for_update to lock the rows for the transaction's duration, so two requests cannot both read, both decide, and both write a conflicting result. Understanding when you need a lock, and keeping the locked section short to avoid contention, is the difference between a system that stays correct under load and one that silently corrupts data when two users act at once.
The classic bug reads a value into Python, changes it, and saves it back. Two requests interleave, both read 5, both write 4, and one sale disappears. There are two correct fixes, and they suit different situations:
from django.db import transaction
from django.db.models import F
# 1. Atomic update: no lock held in Python, one statement, no race
def reserve_simple(product_id, qty):
updated = (Product.objects
.filter(pk=product_id, stock__gte=qty)
.update(stock=F("stock") - qty))
if not updated:
raise OutOfStock(product_id)
# 2. Row lock: when the decision needs more logic than one UPDATE can express
def reserve_with_rules(order):
with transaction.atomic():
ids = sorted(line.product_id for line in order.lines) # fixed lock order
products = {p.pk: p for p in
Product.objects.select_for_update().filter(pk__in=ids).order_by("pk")}
for line in order.lines:
product = products[line.product_id]
if product.stock < line.qty and not product.allow_backorder:
raise OutOfStock(product.pk)
product.stock -= line.qty
product.save(update_fields=["stock"])
Prefer the first form whenever the whole rule fits in a WHERE clause: it holds a row lock only for the duration of one statement. In the second form, locking rows in a consistent order (order_by("pk")) matters: two transactions locking the same rows in different orders is the textbook deadlock, which PostgreSQL resolves by aborting one of them.
Each PostgreSQL connection costs memory and a backend process, and at scale connection count itself becomes a bottleneck. Django's CONN_MAX_AGE reuses connections across requests rather than opening one each time, but the real answer at scale is a pooler like PgBouncer sitting between your app and the database, multiplexing many client connections onto a few real ones. Knowing how your connections are managed — persistent connections, a pooler, and the interaction between them — prevents the surprising failure where a perfectly fast database refuses new connections because too many are open. Connection management is invisible until it is suddenly the thing that takes you down.
PostgreSQL's design leaves dead row versions behind after updates and deletes, and VACUUM reclaims that space. On busy tables, autovacuum can fall behind, causing bloat — tables growing on disk, scans slowing, indexes losing efficiency — until performance mysteriously degrades over weeks. Monitor dead-tuple counts and tune autovacuum to run more aggressively on your hottest tables. This is invisible maintenance that quietly determines whether a write-heavy database stays fast, and a surprising number of "we need to scale the database" situations are really "autovacuum has been losing the race." Keeping tables healthy is as important as indexing them.
Two SQL features unlock queries that are awkward or impossible with the basic ORM. Common table expressions (CTEs, the WITH clause) let you name and compose query steps, and support recursion for hierarchical data like category trees or org charts. Window functions compute values across a set of rows related to the current one — running totals, rankings, moving averages — without collapsing them into a group. Django exposes window functions natively through Window expressions (CTEs need raw() SQL or a third-party package), letting you push analytical queries into the database instead of computing them in Python. Reaching for these when a query gets convoluted often replaces a tangle of Python loops with one efficient SQL statement the database executes optimally.
At scale, how connections and statements are managed matters as much as the queries themselves. A connection pooler like PgBouncer multiplexes many client connections onto a few backend ones, preventing the connection exhaustion that kills a database long before query load would. Prepared statements let PostgreSQL reuse a query plan across executions, saving planning overhead for hot queries — though their interaction with transaction-mode pooling needs care. Understanding the layer between your app and the database engine is part of advanced PostgreSQL: the fastest queries still fail if connections are mismanaged, and a database that seems slow is sometimes simply starved of available connections.
Transaction pooling gives the best multiplexing — a server connection is lent to a client only for the length of one transaction — but it breaks anything that relies on session state surviving between transactions. For Django that means three settings deserve attention:
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "myproject",
"USER": "deploy",
"HOST": "127.0.0.1",
"PORT": "6432", # PgBouncer, not PostgreSQL
"CONN_MAX_AGE": 60, # keep the client-side connection
"CONN_HEALTH_CHECKS": True,
"DISABLE_SERVER_SIDE_CURSORS": True, # .iterator() cursors are session state
"OPTIONS": {
"prepare_threshold": None, # psycopg 3: no server-side prepares
},
}
}
QuerySet.iterator() normally uses a named server-side cursor, which lives in a specific backend session; under transaction pooling, a later fetch can land on a different backend and fail with "cursor does not exist". DISABLE_SERVER_SIDE_CURSORS makes iterator() fetch through a regular cursor instead, so iterate over large tables in explicit primary-key batches to bound memory. Psycopg 3 automatically prepares statements that are executed several times on a connection; setting prepare_threshold to None disables that, avoiding "prepared statement does not exist" errors. Recent PgBouncer releases (1.21 and later) can track protocol-level prepared statements themselves via max_prepared_statements, which lets you keep automatic preparation — verify the version you run before relying on it. Also avoid session-level SET commands, advisory locks taken with pg_advisory_lock (use the _xact_ variants), and LISTEN/NOTIFY through the pooled connection.
Finally, point migrations at PostgreSQL directly rather than through PgBouncer. Long-running DDL such as a concurrent index build can take far longer than any normal query, and a pooler's query or idle timeouts may cut it off partway — leaving exactly the kind of invalid index described earlier.
You cannot tune what you cannot see, and PostgreSQL exposes rich introspection. The pg_stat_statements extension records which queries consume the most total time — usually a small number of culprits dominate — pointing you straight at what to optimize. pg_stat_activity shows current connections and what they are waiting on, revealing lock contention and long-running queries. Watching cache hit ratios, index usage, and table bloat tells you whether the database is healthy. Making these signals visible turns database performance from a black box into something you can observe and reason about, so you optimize the queries that actually matter rather than guessing.
When a single table grows into hundreds of millions of rows, even good indexes strain and maintenance slows. Table partitioning splits one logical table into many physical pieces, commonly by time range for append-heavy data like events or logs, so a query filtered on the partition key touches only the relevant partitions rather than scanning everything. Old partitions detach and archive cheaply, keeping the active set small. Partitioning is more operational work than indexing, so it is reserved for genuinely huge, naturally-ranged tables — but for those it is transformative, turning queries that would crawl over a monolithic table into fast scans of a small, relevant slice.
PostgreSQL is far more than a row store, and using it well is the highest-leverage performance work a Django developer can do. Learn to read query plans with EXPLAIN (ANALYZE, BUFFERS) so slowness becomes diagnosable, and index deliberately for your real query patterns — composite indexes ordered to match your filters, partial indexes for hot subsets. Reach for JSONB where flexibility is genuinely needed, always with a GIN index, but keep queried data in proper columns. Use built-in full-text search and trigram matching before adding a separate search engine, push your critical invariants into database constraints where nothing can bypass them, and handle concurrency with atomic transactions and row locks. Finally, mind connection management and table health, because a fast database undone by connection exhaustion or autovacuum bloat is just as down as a slow one. Master these and PostgreSQL will carry your app far further than most teams ever realize.