Go beyond CRUD with the Postgres features Django hides: table partitioning, materialized views, window functions, LISTEN/NOTIFY, recursive CTEs, advanced constraints, JSONB/arrays, and safe migrations — with guidance on when to push logic into the database.
The Django ORM makes Postgres look like a plain object store, and most apps use maybe a tenth of what the database can do. Partitioning, materialized views, window functions, LISTEN/NOTIFY, recursive queries, and rich constraints are all available from Django — often through the ORM, sometimes through a thin layer of raw SQL — and reaching for them turns slow application-side workarounds into fast, correct database operations. This tutorial covers the Postgres power-features worth knowing and how to use each one safely from Django.
The instinct when a query is slow or a computation is awkward is to pull data into Python and loop. Frequently the better answer is to push the work into Postgres, which was built for exactly these operations and does them orders of magnitude faster on data it already holds. The skill is knowing what the database offers so you recognize when application code is reimplementing a database feature — badly.
When a table grows into the hundreds of millions of rows — events, logs, time-series — queries and maintenance slow even with good indexes. Declarative partitioning splits one logical table into physical partitions, typically by time range, so a query for last week touches one small partition instead of the whole table, and dropping old data is an instant partition drop rather than a massive DELETE.
-- Postgres side: a range-partitioned table by month
CREATE TABLE events (id bigserial, created timestamptz, ...)
PARTITION BY RANGE (created);
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
Django does not create partitions natively, so you manage the partition DDL through migrations (raw SQL) or a helper library, then use the model normally. The payoff is enormous for append-heavy tables: pruning by dropping partitions and queries that scan only the relevant slice.
Some aggregates are expensive to compute and read far more often than the underlying data changes — a leaderboard, a daily sales rollup, a dashboard metric. A materialized view stores the result of a query physically and serves it instantly, refreshed on a schedule rather than recomputed per request.
-- Create once (in a migration)
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created) AS day, sum(total) AS revenue
FROM orders GROUP BY 1;
-- Refresh periodically; CONCURRENTLY avoids locking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
Map an unmanaged Django model (managed = False) onto the view and query it like any table. Refresh from a Celery beat task, using CONCURRENTLY so readers are never blocked during the refresh. This turns a heavy per-request aggregate into a cheap indexed lookup.
Window functions compute across a set of rows related to the current row without collapsing them like GROUP BY — running totals, row numbers, rank within a group, "this row versus the previous". Django exposes them through the Window expression.
from django.db.models import Window, F
from django.db.models.functions import RowNumber
ranked = Order.objects.annotate(
rank=Window(expression=RowNumber(),
partition_by=[F("customer_id")],
order_by=F("total").desc())
)
This computes each customer's orders ranked by value in one query. The Python alternative — grouping and sorting in application code — is both slower and far more code for something SQL expresses in a line.
Postgres has a built-in publish/subscribe channel: a transaction can NOTIFY a channel and other connections LISTEN for it. For modest real-time needs — invalidate a cache when a row changes, wake a worker when a job is queued — this delivers event-driven behavior without adding Redis or a message broker.
with connection.cursor() as cur:
cur.execute("NOTIFY orders, %s", [str(order.id)])
It is not a durable queue — a listener that is disconnected misses the notification — so use it for best-effort signalling, not guaranteed delivery. Within that boundary it is a wonderfully lightweight way to make Postgres itself the event bus.
Hierarchies — category trees, org charts, threaded comments — are awkward in flat tables and terrible to walk with per-level queries in Python. A recursive CTE traverses the whole tree in one query. The ORM does not express recursion directly, so this is a case for well-contained raw SQL, wrapped in a model method so callers never see it. One recursive query replaces a loop that issues a query per level and falls apart on deep trees.
Enforce rules in the database, not just application code, because the database is the last line that no code path can bypass. Django supports rich constraints directly on models.
class Meta:
constraints = [
models.CheckConstraint(check=models.Q(total__gte=0), name="total_nonneg"),
models.UniqueConstraint(fields=["slug"], condition=models.Q(active=True),
name="unique_active_slug"),
]
The partial unique constraint here — unique slug only among active rows — is impossible with a plain unique=True and exactly the kind of integrity rule you want the database to guarantee. Postgres also offers exclusion constraints (e.g. no two bookings overlapping in time) that catch conflicts application code routinely races on.
Postgres has rich column types Django maps directly: JSONField for semi-structured data you can index and query into, ArrayField for lists without a join table, and range types for intervals. JSONB is powerful but easy to abuse — it is for genuinely variable-shaped data, not an excuse to avoid schema design, and it needs a GIN index to query efficiently. Used with discipline, these types let you model things cleanly that would otherwise need extra tables or application-side serialization.
Power-features often mean bigger tables, where migrations get dangerous — a naive index creation or column change locks the table and takes the site down. Create indexes CONCURRENTLY, add columns without volatile defaults, and backfill in batches. Django's AddIndexConcurrently and SeparateDatabaseAndState give you the tools; the discipline is to treat every migration on a large table as a potential lock and plan it, rather than discovering the lock in production.
None of this is worth much if you cannot see where time goes. EXPLAIN (ANALYZE, BUFFERS) shows the real plan of a slow query — whether an index is used, where the rows come from — and the pg_stat_statements extension ranks your queries by total time so you optimize what actually costs, not what you guess. Django's QuerySet.explain() puts the plan a keystroke away. Optimization without these is guessing; with them it is targeted.
Saving objects one at a time in a loop is the most common Django performance mistake — a thousand save() calls are a thousand round trips. bulk_create and bulk_update collapse them into a handful of statements, and for truly large loads Postgres COPY (via the driver) ingests hundreds of thousands of rows in seconds.
Event.objects.bulk_create(
[Event(name=n, created=t) for n, t in rows], batch_size=1000)
Reach for bulk operations any time you touch more than a handful of rows; the difference between per-row and batched writes is often two orders of magnitude.
When multiple workers might grab the same row — processing a queue, decrementing stock — you need locking to prevent races. select_for_update locks the selected rows until the transaction ends, and skip_locked lets each worker claim different rows without blocking, which is exactly how you build a simple, correct job queue on top of a table.
with transaction.atomic():
job = (Job.objects.select_for_update(skip_locked=True)
.filter(status="pending").first())
if job:
job.status = "running"; job.save()
This pattern turns a plain table into a safe work queue without any external broker, and the skip_locked is what lets many workers drain it concurrently.
"Insert, or update if it already exists" is a race in application code — check-then-insert lets two requests both think the row is missing. Postgres does it atomically with ON CONFLICT, exposed in Django through bulk_create's update_conflicts option. Let the database resolve the conflict in one statement rather than catching integrity errors and retrying, which is both slower and easy to get subtly wrong under concurrency.
Some values are always derived from others — a full name from parts, a search vector from text, a total from line items. A generated column has Postgres compute and store it automatically on write, so it is always consistent and can be indexed, with no application code to keep it in sync. This moves an invariant into the database where it cannot drift, instead of relying on every write path remembering to recompute it.
Postgres ships capabilities behind extensions you enable once: pg_trgm for trigram similarity and fast ILIKE, citext for case-insensitive text (great for emails), pgcrypto for hashing and encryption in the database, and uuid-ossp for UUID generation. Each replaces a pile of application code or a slow query with a database primitive. Knowing they exist is half the battle — the other half is enabling them through a migration so the dependency is versioned with your schema rather than configured by hand on each server.
Sometimes you need to serialize work that is not tied to a specific row — ensure only one worker runs a nightly job, or that a section of code executes once cluster-wide. Postgres advisory locks are application-defined locks on an arbitrary integer key, held for the session or transaction, that coordinate processes without a dedicated lock table. They are perfect for "only one of these should run at a time" guards across your fleet, and because Postgres releases them automatically when the connection drops, a crashed worker does not leave a lock stuck forever the way a homemade flag column would.
Indexes need not cover a whole column. A partial index indexes only the rows matching a condition — just the status = 'active' records — so it is smaller, faster, and cheaper to maintain when your queries always filter that way. An expression index indexes the result of a function, like LOWER(email), so a case-insensitive lookup uses an index instead of scanning. Both are declared from Django with condition and expression indexes in Meta.indexes. They are among the highest-leverage tuning tools precisely because they match the index to how the data is actually queried, rather than blindly indexing everything.
Postgres chooses query plans from statistics about your data's distribution, and when those statistics are stale — after a big load, say — it can choose a bad plan and a fast query suddenly crawls. Usually autovacuum keeps stats fresh, but after bulk changes run ANALYZE to update them immediately, and for skewed columns consider extended statistics so the planner understands correlations. Knowing that plans come from statistics explains a whole category of "the same query got slow for no reason" mysteries: the query did not change, the planner's picture of the data did.
Reach for these features when the database can do a job dramatically better than application code — aggregations over huge tables, hierarchy traversal, integrity rules that must never be bypassed, time-series at scale. Keep logic in the application when it is genuinely business logic that changes often, needs to be unit-tested in isolation, or would scatter your domain across SQL nobody maintains. The judgment is to use Postgres for what it is unbeatable at — set operations on data it holds — while keeping your core domain logic in code where it belongs.