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.
One Postgres rule shapes the whole design: every primary key and unique constraint on a partitioned table must include the partition key, so the real key is (id, created). Create the table with RunSQL inside SeparateDatabaseAndState, and let the Django model keep declaring id as its primary key — the identity sequence keeps ids unique in practice, which is all the ORM needs. The cost is that nothing can hold a foreign key to the table, because id alone carries no unique constraint. Event tables are usually leaves in the schema; decide this before partitioning, not after.
CREATE TABLE analytics_event (
id bigint GENERATED BY DEFAULT AS IDENTITY,
created timestamptz NOT NULL,
name text NOT NULL,
PRIMARY KEY (id, created)
) PARTITION BY RANGE (created);
-- created on every current and future partition automatically
CREATE INDEX ON analytics_event (name, created);
An insert outside every partition fails with no partition of relation found for row, so create partitions several months ahead from a scheduled task. A DEFAULT partition catches strays, but it is a safety net, not a destination: Postgres refuses to create a partition whose range already has rows sitting in the default one. For retention, PostgreSQL 14+ offers ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY followed by an instant DROP TABLE; the concurrent form cannot run inside a transaction block (use atomic = False on the migration) and is not allowed while a default partition exists.
Pruning only happens when the planner sees the raw partition key in the filter. filter(created__gte=start, created__lt=end) prunes; created__date=day casts the column and may not; no created filter scans every partition. .explain() shows which partitions a query touches.
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.
REFRESH ... CONCURRENTLY computes a diff between old and new contents, so it requires a unique index covering all rows (no WHERE clause), and it cannot run on a view that was never populated. Add the index in the same migration that creates the view:
CREATE UNIQUE INDEX daily_sales_day_uidx ON daily_sales (day);
Watch the time zone: date_trunc('day', created) on a timestamptz uses the session's TimeZone. Django's connections use UTC when USE_TZ = True, but a refresh run by hand from psql may not, silently shifting day boundaries — write created AT TIME ZONE 'UTC' in the definition. The view also pins its source columns: a later migration changing orders.total fails with cannot alter type of a column used by a view or rule, so that migration must drop, alter, and recreate the view and its unique index together. Finally, a concurrent refresh is more expensive than a plain one; for small views, the plain form's brief exclusive lock is often the better trade.
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.
from django.db.models import Sum
from django.db.models.functions import Lag
w = dict(partition_by=[F("customer_id")],
order_by=[F("created").asc(), F("id").asc()])
orders = Order.objects.annotate(
running_total=Window(expression=Sum("total"), **w),
previous_total=Window(expression=Lag("total"), **w),
)
top3 = ranked.filter(rank__lte=3) # filtering on windows: Django 4.2+
The id tie-breaker is not decoration. With an ORDER BY and no explicit frame, SQL's default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with equal sort keys as peers — two orders with the same timestamp both get the combined running total. A unique tie-breaker, or an explicit RowRange frame, makes results deterministic. Filtering on a window makes Django wrap the query in a subquery (SQL evaluates WHERE before windows), so the inner query still ranks every row; narrow it with a date range on large tables. A composite index on (customer_id, created) can let Postgres read rows already in window order instead of sorting.
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.
NOTIFY is transactional: a notification is delivered only when the sending transaction commits and discarded on rollback, so a listener that re-reads the row never sees uncommitted data. The payload must stay under 8000 bytes by default — send ids, not rows. The function form SELECT pg_notify(%s, %s) takes channel and payload as ordinary parameters regardless of how your driver binds them.
The listener needs its own long-lived autocommit connection, typically a management command under a process supervisor. Because a disconnected listener loses messages, treat NOTIFY as a wake-up call and the table as the source of truth: on every (re)connect, process anything still pending, then listen.
while True:
try:
with psycopg.connect(dsn, autocommit=True) as conn:
conn.execute("LISTEN orders")
catch_up_pending_orders()
for note in conn.notifies():
process_order(int(note.payload))
except psycopg.OperationalError:
time.sleep(5)
LISTEN does not work through PgBouncer in transaction-pooling mode, so connect the listener directly or through a session-mode pool. Unconsumed notifications accumulate in a server-wide queue; SELECT pg_notification_queue_usage() belongs on your monitoring. And commits that issued NOTIFY serialize briefly on a global lock, so at thousands of notifying commits per second a real broker is the better tool.
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.
def descendants(self):
sql = """
WITH RECURSIVE tree AS (
SELECT id, 1 AS depth FROM shop_category WHERE parent_id = %s
UNION ALL
SELECT c.id, t.depth + 1
FROM shop_category c JOIN tree t ON c.parent_id = t.id
WHERE t.depth < 50
)
SELECT id FROM tree
"""
return Category.objects.filter(id__in=RawSQL(sql, [self.pk]))
Returning a normal QuerySet (with RawSQL from django.db.models.expressions) keeps the result chainable — callers can still add .filter() or .count(). The depth guard stops runaway recursion if bad data ever makes a category its own ancestor; PostgreSQL 14+ also offers a CYCLE clause for explicit cycle detection.
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(condition=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.
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import RangeOperators
ExclusionConstraint(
name="no_overlapping_bookings",
expressions=[("room", RangeOperators.EQUAL),
("during", RangeOperators.OVERLAPS)],
condition=models.Q(cancelled=False),
)
Here during is a DateTimeRangeField. The constraint is backed by a GiST index, which cannot compare a plain integer like room_id with = unless btree_gist is installed — add BtreeGistExtension() from django.contrib.postgres.operations to an earlier migration. Ranges default to half-open [), so back-to-back bookings do not conflict. Model validation checks constraints for a friendly form error; the database constraint still catches the concurrent race that validation cannot see. On Django 5.1+, write new check constraints with condition=; the check= keyword is deprecated.
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.
One trap deserves a mention: with a GIN index on a JSONField, filter(attributes__contains={"color": "red"}) uses the index, while the equivalent-looking filter(attributes__color="red") compiles to key extraction plus equality and does not. For one hot key, an expression index or a real column beats both.
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.
Pass update_conflicts=True with unique_fields (which must match a real unique constraint) and update_fields. De-duplicate the input first: if one key appears twice in a batch, Postgres aborts the whole statement with ON CONFLICT DO UPDATE command cannot affect row a second time.
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.
Django 5.0+ exposes this as GeneratedField(expression=..., output_field=..., db_persist=True); PostgreSQL 16–17 support only stored generated columns, so db_persist=True is required. The expression must be immutable, and an instance does not see the computed value after save() until you call refresh_from_db().
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.
Behind a transaction-mode pooler such as PgBouncer, prefer pg_try_advisory_xact_lock(key) inside transaction.atomic(): it is released at commit or rollback, whereas a session lock may be taken on one server connection and the unlock sent on another. The trade-off is that the guarded work runs in one transaction, and a long one holds back vacuum cleanup.
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.
These features are Postgres-specific, so tests must run on Postgres, ideally the production major version. The test database is built by running your migrations, so raw-SQL partitions, views and extensions exist in tests as in production. A pruning test is cheap insurance against someone quietly turning a one-partition read into a full scan:
def test_month_query_touches_one_partition(self):
plan = Event.objects.filter(created__gte=JAN_1, created__lt=FEB_1).explain()
self.assertIn("analytics_event_2026_01", plan)
self.assertNotIn("analytics_event_2026_02", plan)
For constraint tests, wrap the expected IntegrityError in its own transaction.atomic(), or the test's transaction is unusable afterwards. NOTIFY is delivered only on commit and TestCase never commits, so use TransactionTestCase for the one end-to-end delivery test and test the sender and the processing function separately. For window functions and views, use fixtures with ties and missing days — exactly where SQL semantics surprise readers of the Python code.
| Symptom | Likely cause | Fix |
|---|---|---|
| no partition of relation ... found for row | No partition for that range | Create partitions ahead; alert on the schedule |
| Plan lists every partition | Filter does not constrain the raw partition key | Filter on a created range, no casts |
| cannot refresh materialized view ... concurrently | No full unique index, or never populated | Add the index; run one plain refresh |
| cannot alter type of a column used by a view or rule | A view depends on the column | Drop, alter, recreate in one migration |
| Running total jumps on ties | Default RANGE frame | Add a unique tie-breaker or RowRange |
| Listener stops receiving | Dropped connection or transaction pooler | Direct connection, reconnect loop, catch-up |
| Advisory lock stuck after worker exits | Session lock through a pooler | Use the transaction-scoped lock |
For anything else, pg_stat_activity plus pg_blocking_pids(pid) answers the two incident questions that matter: which session is waiting, and which one holds what it needs.
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.