PostgreSQL & Database Backends

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Django 6.1 (6.1.1, released 2026-09-02; 6.1.2 is scheduled for 2026-10-06), Python 3.12-3.14 and PostgreSQL 15 or newer, with psycopg 3.3.6, all checked on 2026-10-03; where the 5.2 LTS (5.2.17) behaves differently the text says so. No PostgreSQL server was available while writing, so nothing on this page was run against one: the PostgreSQL examples were validated by manage.py check, by makemigrations, by sqlmigrate and by compiling the queries to SQL with the real PostgreSQL backend and its field, index, constraint and function classes, with the server round-trips stubbed out, and the portable SQLite code was executed and tested. The section What was run, and what was not lists, example by example, what was executed and what was not. Choosing PostgreSQL is the most common decision a Django project makes about its database, and the reason is not only quality: django.contrib.postgres exposes PostgreSQL’s arrays, ranges, hstore, exclusion constraints, GIN/GiST indexes and full-text search as ordinary fields, lookups and expressions. In the Bookshelf application that grows across this section, PostgreSQL stores the catalogue, tags books with arrays, prevents two book-club meetings from overlapping in a room with an exclusion constraint and powers the catalogue search with a stored, weighted full-text vector. This page covers the backend notes for every officially supported database, then django.contrib.postgres (fields, indexes, constraints, migration operations, aggregates), then search (full-text and trigram), and ends with pitfalls, what is outdated in older books and what changed recently.

In a hurry: what goes where

Need Use Section

Which database and driver

PostgreSQL 15+ with psycopg 3 (psycopg[binary], psycopg[pool] for a pool); MariaDB 10.11+/MySQL 8.4+ through the mysql backend; SQLite 3.37+; Oracle 19c+ with oracledb.

The supported backends

Connection reuse

CONN_MAX_AGE for WSGI threads; OPTIONS["pool"] (5.1, psycopg only) for ASGI or many short requests; CONN_HEALTH_CHECKS after restarts.

Connections: lifetime, health checks and pools

A list of tags on a row

ArrayField(base_field, size=None) with a callable default and a GIN index.

ArrayField

Free-form string key/values

HStoreField plus HStoreExtension() in a migration; prefer models.JSONField for nested data.

HStoreField

A period of time or a numeric interval

The five range fields; psycopg_any range classes in Python.

Range fields

"No two active rows may overlap"

ExclusionConstraint with RangeOperators.OVERLAPS and BtreeGistExtension().

ExclusionConstraint

Add an index to a live table

AddIndexConcurrently in a migration with atomic = False.

Migration operations: extensions, collations, concurrent indexes

Add a check constraint to a big table

AddConstraintNotValid, then ValidateConstraint in a separate migration.

Adding a constraint without scanning the table

Search text

A stored GeneratedField with SearchVector and a GIN index; SearchQuery (websearch, Lexeme), SearchRank, SearchHeadline.

Full-text search

Typo-tolerant name search

pg_trgm through TrigramExtension(), TrigramSimilarity, trigram_similar, and a trigram GIN index.

Trigram similarity and unaccent

Same code on SQLite in tests

Branch on connection.vendor, or run the tests on PostgreSQL (preferred).

Writing code that survives two backends

What was run, and what was not

Every claim about behaviour comes from the Django 6.1 documentation; the examples were exercised in a scratch project with Django 6.1.1, Python 3.13, psycopg 3.3.6 and psycopg-pool 3.3.3. The honest summary:

Example Needs a live PostgreSQL What was done

DATABASES for PostgreSQL with a pool and health checks

yes

Loaded and passed manage.py check once the server-version query was stubbed. Without the stub check tried to connect and raised PoolTimeout; without psycopg-pool installed it raised ImproperlyConfigured. Never connected.

django-environ DATABASE_URL parsing, IsolationLevel import, backend subclass

no

Executed: the parsed dictionary, the import and the subclass (DatabaseWrapper with a custom features_class) were printed and checked.

SQLite init_command PRAGMAs, transaction_mode, timeout

no

Executed on SQLite 3.53: PRAGMA synchronous returned 3 and PRAGMA cache_size returned 2000.

search_books() with a connection.vendor branch

the PostgreSQL branch

The SQLite branch and an async variant ran in a Django TestCase (2 tests, OK). The PostgreSQL branch was compiled to SQL only.

Models with ArrayField, HStoreField, DateRangeField, GeneratedField(SearchVector…​), GinIndex, BTreeIndex, ExclusionConstraint

yes, to create the tables

manage.py check clean; makemigrations generated the migration; makemigrations --check --dry-run reported "No changes detected"; sqlmigrate printed the DDL quoted below.

Migrations with extensions, AddIndexConcurrently, AddConstraintNotValid, ValidateConstraint

yes, to apply

sqlmigrate output shown. migrate was not run. The extension operations print -- (no-op) under the stub, which is an artefact of the stub, not Django’s behaviour.

Lookups, search, trigram and aggregate queries

yes, to return rows

Compiled with str(queryset.query) against the PostgreSQL backend; the SQL fragments below come from that. The result sets in comments are the documentation’s, not observed.

Constraint-violation and search tests

yes

Written but not executed; they are marked "needs PostgreSQL".

MySQL, MariaDB and Oracle OPTIONS

yes (and a driver)

Copied from the documentation; not executed (no mysqlclient or oracledb, no server).

The stubs live only in the scratch project: they replace DatabaseWrapper.get_new_connection with a mock, fix pg_version, and substitute the query parameters textually, which is why quoted SQL below shows parameters unquoted when str(queryset.query) was used (Django documents that this string is not valid SQL). To reproduce the real results, start PostgreSQL 15+ (for example the official container, see Settings & Configuration) and run the tests in Testing on PostgreSQL with DJANGO_SETTINGS_MODULE pointing at PostgreSQL settings.

The supported backends

Django officially supports PostgreSQL, MariaDB, MySQL, Oracle and SQLite, and documents a list of third-party backends. It tries to offer the same ORM everywhere, but "not all database backends are alike": the notes below are the differences that most often matter. They are not a replacement for the database’s own manual.

Backend Minimum version (Django 6.1) Driver ENGINE

PostgreSQL

15 (14 dropped in 6.1; 5.2 LTS needs 14)

psycopg 3.1.12+ (recommended) or psycopg2 2.9.9+; support for psycopg2 is likely to be deprecated and removed

django.db.backends.postgresql

MariaDB

10.11

the MySQL driver (shared backend)

django.db.backends.mysql

MySQL

8.4

mysqlclient 2.2.1+ (recommended) or MySQL Connector/Python

django.db.backends.mysql

Oracle

19c

oracledb 2.3.0+

django.db.backends.oracle

SQLite

3.37.0

the standard-library sqlite3 module

django.db.backends.sqlite3

Source: Django: Databases

Django assumes UTF-8 everywhere; another encoding produces confusing "value too long" errors for valid data. Third-party backends listed in the documentation include CockroachDB, Firebird, Google Cloud Spanner, Microsoft SQL Server, MongoDB, Snowflake, TiDB and YugabyteDB; their supported Django versions and ORM features "vary considerably", so questions go to their own projects. The database-agnostic parts of this section are covered in Models & Fields and Making Queries; the SQL itself (joins, window functions, transactions) is in SQL.

Connections: lifetime, health checks and pools

Django opens a connection the first time a request needs one and, by default, closes it at the end of the request (CONN_MAX_AGE = 0). settings.py configures this per alias; the full table of DATABASES keys is in Settings & Configuration. What matters for the backend choice:

  • CONN_MAX_AGE set to a positive number of seconds (or None for unlimited) keeps one connection per thread alive between requests. Django closes an over-age or broken connection at the start or end of a request. Your database must allow at least as many connections as you have worker threads. The development server creates a thread per request, so persistence does nothing there.

  • CONN_HEALTH_CHECKS = True checks a reused connection once per request, and only if the request touches the database. It prevents errors after the server closed an idle connection or restarted.

  • Under ASGI, persistent connections should be disabled. The async ORM methods run the queries in a thread, so a long lifetime per thread does not help; use the backend’s pool (or an external pooler) and size it to the number of queries you want in flight.

  • PostgreSQL pools (5.1, psycopg only): set "pool" in OPTIONS to True (the ConnectionPool defaults) or to a dictionary of ConnectionPool options. This needs psycopg[pool], and CONN_MAX_AGE must be 0; connection lifetime is the max_lifetime pool option. Django keeps one pool per database alias per process, and closing a Django connection returns it to the pool. Unless you pass a check callable, CONN_HEALTH_CHECKS decides whether connections are checked on checkout.

  • Oracle pools (5.2): "pool": True or a dictionary for the driver’s create_pool().

# config/settings_pg.py -- needs a PostgreSQL server and psycopg[pool]
import os

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.environ.get("POSTGRES_DB", "bookshelf"),
        "USER": os.environ.get("POSTGRES_USER", "bookshelf"),
        "PASSWORD": os.environ.get("POSTGRES_PASSWORD", ""),  # from the secret store
        "HOST": os.environ.get("POSTGRES_HOST", "localhost"),
        "PORT": os.environ.get("POSTGRES_PORT", "5432"),
        "CONN_MAX_AGE": 0,  # must be 0 when "pool" is enabled
        "CONN_HEALTH_CHECKS": True,
        "OPTIONS": {
            "pool": {"min_size": 2, "max_size": 10, "timeout": 10},
        },
    }
}

Source: Django: PostgreSQL connection pool

Executed here only as far as check (with the stubs described in What was run, and what was not). A pool that is configured but cannot reach the server fails on the first query with OperationalError: couldn’t get a connection after 10.00 sec; leaving out psycopg-pool fails with ImproperlyConfigured: Error loading psycopg_pool module. Did you install psycopg[pool]?. The slow-query side of connections (pool sizing, Debug Toolbar) continues in Query Optimization & Fetch Modes and Diagnosing Slow Queries.

When a connection is created outside the request cycle (a script, a thread you start, a management command that runs for hours) nothing closes it for you: call django.db.close_old_connections() or close it explicitly.

A URL-style configuration

Many deployments inject a single DATABASE_URL. django-environ turns it into the dictionary Django expects; the options in the query string become OPTIONS:

import environ

env = environ.Env()
cfg = env.db_url_config("postgres://bookshelf:change-me@db.internal:5432/bookshelf?sslmode=require")
print({k: v for k, v in cfg.items() if k != "PASSWORD"})
{'NAME': 'bookshelf', 'USER': 'bookshelf', 'HOST': 'db.internal', 'PORT': 5432, 'OPTIONS': {'sslmode': 'require'}, 'ENGINE': 'django.db.backends.postgresql'}

Source: Django: PostgreSQL connection settings

This one was executed (django-environ 0.14.0). Read the real value from the environment in production with env.db("DATABASE_URL"); the literal password above is a placeholder. Note that a pool setting is not expressible in the URL and must be added to the dictionary afterwards.

PostgreSQL notes

The PostgreSQL backend passes the contents of OPTIONS to the connection constructor as keyword arguments, so any libpq parameter works (sslmode, connect_timeout, application_name, and so on). The options Django documents itself:

Option Effect Since

service and passfile

Connect through an entry of the connection service file (.pg_service.conf) and a password file. Using a service name for the test database is not supported.

4.0

isolation_level

Defaults to READ COMMITTED, as in PostgreSQL. Set IsolationLevel.REPEATABLE_READ or SERIALIZABLE (import from django.db.backends.postgresql.psycopg_any); the application then has to handle serialization failures.

long-standing

assume_role

Run the connection under a different role than the one used to log in.

4.2

server_side_binding

psycopg 3.1.8+ binds parameters on the client by default; True selects server-side binding. Ignored with psycopg2.

4.2

pool

Connection pooling, see Connections: lifetime, health checks and pools.

5.1

Source: Django: PostgreSQL notes

from django.db.backends.postgresql.psycopg_any import IsolationLevel

DATABASES["default"]["OPTIONS"] |= {
    "isolation_level": IsolationLevel.SERIALIZABLE,
    "assume_role": "bookshelf_app",
    "server_side_binding": True,
}

Source: Django: PostgreSQL isolation level

The import was executed (it printed 4 for IsolationLevel.SERIALIZABLE); the options were not applied to a server. Further PostgreSQL facts from the notes:

  • Fewer round-trips per connection. Django needs client_encoding set to UTF8, the isolation level (read committed by default) and timezone (UTC when USE_TZ is True). If the role already has those values, Django skips the SET statements for each new connection; set them with ALTER ROLE or in postgresql.conf.

  • varchar and text get two indexes with db_index=True. Django adds a second index with the like operator class (varchar_pattern_ops/text_pattern_ops), needed for LIKE-based lookups such as contains and startswith. The sqlmigrate output of a unique=True CharField shows it (pgdemo_room_name…​_like).

  • Server-side cursors and poolers. QuerySet.iterator() opens a server-side cursor, and PostgreSQL assumes only the first 10% of a cursor query will be fetched (cursor_tuple_fraction). Behind a transaction-pooling pooler such as PgBouncer, set DISABLE_SERVER_SIDE_CURSORS = True for that alias, or wrap the iteration in atomic().

  • Identity columns and explicit primary keys. Auto-incrementing keys are identity columns fed by a sequence. Creating a row with an explicit pk does not advance the sequence, so a later insert can raise IntegrityError: duplicate key value violates unique constraint. After loading data with explicit keys, run manage.py sqlsequencereset <app> and execute the output.

  • Extensions such as hstore, pg_trgm or btree_gist are enabled through migration operations, see Migration operations: extensions, collations, concurrent indexes.

  • Tests. TEST["TEMPLATE"] names the template database the test database is created from; the notes also describe speeding the test run with non-durable settings on a throwaway server, which is dangerous on anything you care about.

MySQL and MariaDB notes

MariaDB uses the MySQL backend (ENGINE = "django.db.backends.mysql"), so the notes apply to both. They were not executed here; they are the documentation’s, grouped by what goes wrong:

Topic What to know

Storage engine

Use InnoDB (transactions, foreign keys). MyISAM does not enforce foreign keys and does not support transactions; savepoint methods raise database errors on it. Django does not choose an engine when creating tables, so set the server default.

Character set

Create the database with CHARACTER SET utf8mb4. Columns of type VARCHAR with unique=True are limited to 255 characters (CharField, SlugField); a TextField cannot be unique=True because MySQL needs a key length.

Collation

The default utf8mb4_0900_ai_ci compares strings case-insensitively, so "Fred" and "freD" collide on a unique column. Use db_collation per column or a case-sensitive collation (utf8mb4_0900_as_cs). Model formsets validate unique fields case-sensitively, so the database can still raise IntegrityError at save().

sql_mode

Keep strict mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES) so truncation raises instead of losing data; set it with 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'" in OPTIONS.

Isolation level

Django works best with, and defaults to, read committed; MySQL’s own default repeatable read can make get_or_create() raise IntegrityError for an object that a following get() cannot see. Set OPTIONS["isolation_level"] to 'read committed', 'repeatable read', 'serializable' or 'read uncommitted' (or None for the server’s setting).

Time zones and fractions

Load the time zone tables with mysql_tzinfo_to_sql once per server to use time-zone support. Fractional seconds need DATETIME(6) columns; Django does not upgrade existing columns (use RunSQL in a migration). A legacy database with TIMESTAMP columns needs USE_TZ = False to avoid corrupting values.

Row locking

SKIP LOCKED and NOWAIT work on both; OF is MySQL-only; NO KEY is not supported on either, and unsupported options raise NotSupportedError. Filter select_for_update() on indexed or unique columns, or MySQL locks the whole table for the transaction.

Type coercion

Querying a string column with an integer makes MySQL coerce every row to an integer (so WHERE col = 0 matches 'abc'). Django casts string-field values to strings; if you write custom fields, RawSQL, extra() or raw(), do the same.

Table names

MySQL can alter the case of table names under some conditions; keep db_table lowercase.

Source: Django: MySQL notes

# Not executed: needs mysqlclient and a MySQL 8.4+ or MariaDB 10.11+ server
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "bookshelf",
        "USER": "bookshelf",
        "PASSWORD": os.environ["MYSQL_PASSWORD"],
        "HOST": "127.0.0.1",
        "OPTIONS": {
            "charset": "utf8mb4",
            "isolation_level": "read committed",
            "init_command": "SET sql_mode='STRICT_TRANS_TABLES'",
        },
    }
}

Source: Django: MySQL connection settings

The charset option name is mysqlclient’s; the documentation’s own sample uses a read_default_file option file with default-character-set = utf8mb4. Connection settings are applied in this order: OPTIONS, then NAME, USER, PASSWORD, HOST, PORT, then the option file.

SQLite notes

SQLite is the zero-configuration backend (the standard-library driver, 3.37.0 or newer) and the one every example in this section can fall back to. The differences that surprise people:

  • Substring and case matching. contains and iexact match case-insensitively for substrings, but exact matches on strings with non-ASCII characters are case-sensitive even with iexact.

  • Decimals are floats. DecimalField is stored as REAL, so decimal arithmetic is not exact.

  • "database is locked". Too much write concurrency. Shorten transactions, raise OPTIONS["timeout"] (which only waits longer) or move to another backend.

  • Transaction mode. OPTIONS["transaction_mode"] accepts DEFERRED (default), IMMEDIATE and EXCLUSIVE (5.1); IMMEDIATE waits for the lock up to the timeout. ATOMIC_REQUESTS is discouraged with it.

  • Pragmas. OPTIONS["init_command"] runs statements at connection time (5.1).

  • No row locking. select_for_update() is accepted but has no effect.

  • Mutating a table while iterating. Rows changed inside an iterator() loop may appear zero or two times.

  • JSON. JSONField needs the JSON1 extension, enabled by default from SQLite 3.38; otherwise fields.E180.

# config/settings_lite.py
from .settings import *  # noqa: F403

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "lite.sqlite3",  # noqa: F405
        "OPTIONS": {
            "init_command": "PRAGMA synchronous=3; PRAGMA cache_size=2000;",
            "transaction_mode": "IMMEDIATE",
            "timeout": 20,
        },
    }
}
$ python manage.py shell --settings=config.settings_lite -c "
from django.db import connection
c = connection.cursor()
print(c.execute('PRAGMA synchronous').fetchone(), c.execute('PRAGMA cache_size').fetchone())
print(connection.settings_dict['OPTIONS'], connection.vendor)"
(3,) (2000,)
{'init_command': 'PRAGMA synchronous=3; PRAGMA cache_size=2000;', 'transaction_mode': 'IMMEDIATE', 'timeout': 20} sqlite

Source: Django: SQLite notes

This example was executed. A longer treatment of the same options, in the context of the whole DATABASES setting, is in Settings & Configuration.

Oracle notes

Oracle 19c or newer with oracledb 2.3.0 or newer. Nothing here was executed (no server, no driver). The points that shape a project:

  • Privileges. migrate needs CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE and CREATE TRIGGER. Running a test suite needs additional user and tablespace privileges (CREATE USER, ALTER USER, DROP USER, CREATE TABLESPACE, DROP TABLESPACE and the WITH ADMIN OPTION variants), all included in the DBA role, which is appropriate on a private developer database only. The backend also uses SYS.DBMS_LOB and SYS.DBMS_RANDOM.

  • Connecting. With a service name (or a tnsnames.ora entry) leave HOST and PORT empty; with a SID fill in both. A full DSN or Easy Connect string can go into NAME when HOST and PORT are empty ("NAME": "localhost:1521/orclpdb1").

  • Pool (5.2). "OPTIONS": {"pool": True}, or a dictionary of create_pool() parameters such as min and max.

  • INSERT …​ RETURNING INTO. The backend uses it to read generated keys; disable with "use_returning_into": False in OPTIONS for unusual setups such as remote tables or views with INSTEAD OF triggers.

  • Names. Identifiers are limited to 30 characters (truncated with an MD5 suffix) and upper-cased; a quoted db_table such as '"name_left_in_lowercase"' avoids both. Do not name a field date, timestamp, number or float.

  • NULL and empty strings. Oracle treats them identically, so the backend ignores an explicit null=False on fields that allow '' and reads NULL back as ''.

  • TextField is an NCLOB. It cannot be a primary key, be indexed or appear in SELECT DISTINCT; combine defer() with distinct() as a workaround.

Source: Django: Oracle notes

Subclassing a backend, and third-party backends

To change one behaviour of a built-in backend (a feature flag, an operation) you subclass it: a package with a base.py that defines a DatabaseWrapper subclass, then ENGINE set to the package. This is also the pattern the third-party backends use.

# mydbengine/base.py
from django.db.backends.postgresql import base, features


class DatabaseFeatures(features.DatabaseFeatures):
    def allows_group_by_selected_pks_on_model(self, model):
        return True


class DatabaseWrapper(base.DatabaseWrapper):
    features_class = DatabaseFeatures
DATABASES = {"default": {"ENGINE": "mydbengine", "NAME": "bookshelf"}}

Source: Django: Subclassing the built-in database backends

Executed: with that setting Django resolved the engine, connection.features came from mydbengine.base and connection.vendor was still postgresql. Keep such subclasses small; each Django release can change the feature flags and operation methods you override.

Writing code that survives two backends

The cleanest policy is to test on the database you deploy to (Testing on PostgreSQL). When a project must run on SQLite as well (a tutorial, a laptop without Docker), isolate the PostgreSQL-only code behind connection.vendor, which is "postgresql", "mysql", "sqlite" or "oracle":

# portable/search.py
from django.db import connections
from django.db.models import Q, QuerySet


def search_books(qs: QuerySet, term: str, using: str = "default") -> QuerySet:
    """Full-text search on PostgreSQL, a plain substring match everywhere else."""
    if connections[using].vendor == "postgresql":
        from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector

        vector = SearchVector("title", weight="A") + SearchVector("summary", weight="B")
        query = SearchQuery(term, search_type="websearch")
        return (
            qs.annotate(search=vector, rank=SearchRank(vector, query))
            .filter(search=query)  # the @@ match, not a rank threshold
            .order_by("-rank")
        )
    return qs.filter(Q(title__icontains=term) | Q(summary__icontains=term))
# portable/tests.py
from django.db import connection
from django.test import TestCase

from .models import Book
from .search import search_books


class SearchBooksTests(TestCase):
    def test_substring_fallback_on_non_postgresql(self):
        Book.objects.create(title="Two Cheeses", summary="")
        Book.objects.create(title="Other", summary="all about cheese")
        Book.objects.create(title="Unrelated", summary="nothing")
        found = search_books(Book.objects.all(), "cheese")
        self.assertEqual(connection.vendor, "sqlite")
        self.assertEqual(sorted(b.title for b in found), ["Other", "Two Cheeses"])

    async def test_async_iteration(self):
        await Book.objects.acreate(title="Async Cheese", summary="")
        titles = [b.title async for b in search_books(Book.objects.all(), "cheese")]
        self.assertEqual(titles, ["Async Cheese"])
$ python manage.py test portable --settings=config.settings_lite
Found 2 test(s).
...
Ran 2 tests in 0.002s

OK

Source: Django: Search

Both tests ran on SQLite. The PostgreSQL branch compiled to the following SQL (only the FROM/WHERE shape matters; the parameters are unquoted in the str() rendering):

SELECT ..., (setweight(to_tsvector(COALESCE("portable_book"."title", '')), 'A') || setweight(to_tsvector(COALESCE("portable_book"."summary", '')), 'B')) AS "search",
       ts_rank((...same vector...), websearch_to_tsquery('django -flask')) AS "rank"
FROM "portable_book"
WHERE (...same vector...) @@ (websearch_to_tsquery('django -flask'))
ORDER BY 5 DESC

Source: Django: PostgreSQL notes

The async test shows that the same expressions work with the async ORM (acreate, async for); transactions are the part of the async ORM that does not work yet, so wrap transactional code in one synchronous function and call it with sync_to_async(). Do not try to hide real differences (ranking, stemming, collation) behind such a helper: the fallback only keeps a development environment usable.

django.contrib.postgres

django.contrib.postgres is a Django app that ships PostgreSQL-specific fields, indexes, constraints, lookups, aggregates, expressions, validators, form fields and migration operations. Add it to INSTALLED_APPS to use the lookups and expressions that register themselves (search, trigram_similar, unaccent, OpClass):

INSTALLED_APPS = [
    "django.contrib.postgres",
    "pgdemo",
    # ...
]

Source: Django: django.contrib.postgres

Since Django 6.0, model fields, indexes and constraints from the package carry a system check that fails when the app is not installed, instead of failing later with a database error. With the app removed from INSTALLED_APPS, manage.py check printed (excerpt, executed):

pgdemo.Book.extra: (postgres.E005) 'django.contrib.postgres' must be in INSTALLED_APPS in order to use HStoreField.
pgdemo.Book.print_run: (postgres.E005) 'django.contrib.postgres' must be in INSTALLED_APPS in order to use DateRangeField.
pgdemo.Book.search_vector: (fields.E223) GeneratedField.output_field has errors:
    'django.contrib.postgres' must be in INSTALLED_APPS in order to use SearchVectorField. (postgres.E005)
pgdemo.Book: (postgres.E005) 'django.contrib.postgres' must be in INSTALLED_APPS in order to use GinIndex.
pgdemo.Booking: (postgres.E005) 'django.contrib.postgres' must be in INSTALLED_APPS in order to use ExclusionConstraint.
System check identified 10 issues (0 silenced).

Source: Django: django.contrib.postgres

The rest of this section builds one scratch app (pgdemo) around the Bookshelf catalogue. Its models use every kind of field, index and constraint that follows, so the complete file comes first and the sections after it explain each part:

# pgdemo/models.py
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import (
    ArrayField,
    DateRangeField,
    DateTimeRangeField,
    HStoreField,
    RangeOperators,
)
from django.contrib.postgres.indexes import BTreeIndex, GinIndex, OpClass
from django.contrib.postgres.search import SearchVector, SearchVectorField
from django.db import models
from django.db.models import Q
from django.db.models.functions import Upper


class Author(models.Model):
    name = models.CharField(max_length=200)

    class Meta:
        indexes = [
            # Trigram index: speeds up trigram_similar / TrigramSimilarity
            GinIndex(
                OpClass(Upper("name"), name="gin_trgm_ops"),
                name="author_name_trgm_idx",
            ),
        ]

    def __str__(self) -> str:
        return self.name


class Book(models.Model):
    title = models.CharField(max_length=200)
    summary = models.TextField(blank=True)
    # default must be a callable: a shared mutable list would leak between rows
    tags = ArrayField(models.CharField(max_length=40), default=list, blank=True)
    # keys and values are strings (or None); use JSONField for nested data
    extra = HStoreField(default=dict, blank=True)
    print_run = DateRangeField(null=True, blank=True)
    pages = models.PositiveIntegerField(null=True, blank=True)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    # PostgreSQL maintains this column itself (GeneratedField, Django 5.0)
    search_vector = models.GeneratedField(
        expression=SearchVector("title", weight="A", config="english")
        + SearchVector("summary", weight="B", config="english"),
        output_field=SearchVectorField(),
        db_persist=True,
    )

    class Meta:
        indexes = [
            GinIndex(fields=["search_vector"], name="book_search_vector_idx"),
            GinIndex(fields=["tags"], name="book_tags_gin_idx"),
            BTreeIndex(fields=["title"], fillfactor=80, name="book_title_idx"),
        ]
        constraints = [
            models.CheckConstraint(condition=Q(pages__gt=0), name="book_pages_positive"),
        ]

    def __str__(self) -> str:
        return self.title


class Room(models.Model):
    name = models.CharField(max_length=80, unique=True)


class Booking(models.Model):
    """A book-club meeting: no two active bookings may overlap in the same room."""

    room = models.ForeignKey(Room, on_delete=models.CASCADE)
    timespan = DateTimeRangeField()
    cancelled = models.BooleanField(default=False)

    class Meta:
        constraints = [
            ExclusionConstraint(
                name="exclude_overlapping_bookings",
                expressions=[
                    ("timespan", RangeOperators.OVERLAPS),
                    ("room", RangeOperators.EQUAL),
                ],
                condition=Q(cancelled=False),
            ),
        ]
$ python manage.py check --settings=config.settings_pg_nodb
System check identified no issues (0 silenced).
$ python manage.py makemigrations pgdemo --settings=config.settings_pg_nodb
Migrations for 'pgdemo':
  pgdemo/migrations/0001_initial.py
    + Create model Room
    + Create model Author
    + Create model Book
    + Create model Booking
$ python manage.py makemigrations --check --dry-run --settings=config.settings_pg_nodb
No changes detected

Source: Django: PostgreSQL specific model fields

config.settings_pg_nodb is the scratch settings module that stubs the server round-trips (see What was run, and what was not); with a real database the commands are the same without --settings. The GeneratedField and the two GinIndex entries on Book are explained in Full-text search; ExclusionConstraint in ExclusionConstraint.

Migration operations: extensions, collations, concurrent indexes

django.contrib.postgres.operations provides the operations that make migrations PostgreSQL-aware. Their position in the request cycle is "deployment": they run during migrate, never while serving a request.

Extensions

CreateExtension(name, hints=None) installs a PostgreSQL extension, skipping it if it already exists; subclasses exist for the common ones: BloomExtension, BtreeGinExtension, BtreeGistExtension, CITextExtension, CryptoExtension (pgcrypto), HStoreExtension, TrigramExtension (pg_trgm) and UnaccentExtension. Put the operation before the first operation that uses it: for HStoreField that is the first CreateModel or AddField involving it, for gin_trgm_ops the first index with it, for an exclusion constraint on a scalar column btree_gist. HStoreExtension also sets up the connection to interpret hstore values for later operations in the same migration. The hints argument (6.0) is passed to database routers' allow_migrate(), so routed projects can send extension creation to the right database.

# pgdemo/migrations/0001_initial.py (excerpt: operations list; the rest is generated)
from django.contrib.postgres.operations import (
    BtreeGistExtension,
    HStoreExtension,
    TrigramExtension,
)

operations = [
    # extensions first: later operations use hstore, gin_trgm_ops and btree_gist
    HStoreExtension(),
    TrigramExtension(),
    BtreeGistExtension(),
    # migrations.CreateModel(...), generated by makemigrations
]

Source: Django: Creating extension using migrations

Most extensions need a database user with superuser privileges. If the application’s user does not have it, a privileged user creates the extension once (CREATE EXTENSION IF NOT EXISTS hstore; in the application database) and the operation then finds it. In tests, Django creates the test database by running the migrations, so the extension operations run there too, and the test user needs the same privilege.

sqlmigrate for the whole migration (executed with the stubs; the three extension steps print -- (no-op) there only because the stub pretends they exist):

CREATE TABLE "pgdemo_book" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "title" varchar(200) NOT NULL, "summary" text NOT NULL, "tags" varchar(40)[] NOT NULL,
  "extra" hstore NOT NULL, "print_run" daterange NULL,
  "search_vector" tsvector GENERATED ALWAYS AS ((setweight(to_tsvector('english'::regconfig, COALESCE("title", '')), 'A')
    || setweight(to_tsvector('english'::regconfig, COALESCE("summary", '')), 'B'))) STORED,
  "author_id" bigint NOT NULL);
CREATE TABLE "pgdemo_booking" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "timespan" tstzrange NOT NULL, "cancelled" boolean NOT NULL, "room_id" bigint NOT NULL,
  CONSTRAINT "exclude_overlapping_bookings" EXCLUDE USING GIST ("timespan" WITH &&, "room_id" WITH =) WHERE (NOT "cancelled"));
CREATE INDEX "author_name_trgm_idx" ON "pgdemo_author" USING gin ((UPPER("name")) gin_trgm_ops);
CREATE INDEX "book_search_vector_idx" ON "pgdemo_book" USING gin ("search_vector");
CREATE INDEX "book_tags_gin_idx" ON "pgdemo_book" USING gin ("tags");

Source: Django: sqlmigrate

(Line breaks added for reading; the pages column and the two later indexes arrive in later migrations, shown below.)

Collations

CreateCollation(name, locale, *, provider='libc', deterministic=True) and RemoveCollation manage collations that your operating system or ICU provides but PostgreSQL does not yet list. A collation created with deterministic=False and the icu provider is how you get case-insensitive filtering and ordering on a column through db_collation:

from django.contrib.postgres.operations import CreateCollation

operations = [
    CreateCollation(
        "case_insensitive",
        provider="icu",
        locale="und-u-ks-level2",
        deterministic=False,
    ),
]

Source: Django: Managing collations using migrations

Not executed. This replaces the CICharField, CIEmailField and CITextField of older Django versions and the citext extension: they were deprecated in 4.2 and removed in 5.1. A non-deterministic collation cannot be used with LIKE-style lookups on every PostgreSQL version, so test the queries you need.

Adding an index to a live table

A plain CREATE INDEX blocks writes to the table while it builds. AddIndexConcurrently(model_name, index) and RemoveIndexConcurrently(model_name, name) use PostgreSQL’s CONCURRENTLY option, which does not. That option cannot run inside a transaction, so the migration must set atomic = False:

# pgdemo/migrations/0003_book_title_idx_concurrently.py
from django.contrib.postgres.indexes import BTreeIndex
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations


class Migration(migrations.Migration):
    # CREATE INDEX CONCURRENTLY cannot run inside a transaction block
    atomic = False

    dependencies = [("pgdemo", "0002_book_pages")]

    operations = [
        AddIndexConcurrently(
            "book",
            BTreeIndex(fields=["title"], fillfactor=80, name="book_title_idx"),
        ),
    ]
$ python manage.py sqlmigrate pgdemo 0003 --settings=config.settings_pg_nodb
--
-- Concurrently create index book_title_idx on field(s) title of model book
--
CREATE INDEX CONCURRENTLY "book_title_idx" ON "pgdemo_book" USING btree ("title") WITH (fillfactor = 80);

Source: Django: Concurrent index operations

sqlmigrate output executed; the statement was not run. Note that the output has no BEGIN/COMMIT, unlike the atomic migrations, and that a concurrent build that fails leaves an invalid index that you must drop before retrying. Keep a non-atomic migration to this one operation so a partial failure leaves a known state. The general migration workflow, including squashing and zero-downtime schema changes, is in Migrations and Constraints, Indexes & Composite Keys.

Adding a constraint without scanning the table

AddConstraintNotValid(model_name, constraint) (4.0) adds a check constraint with NOT VALID: new and updated rows are checked, existing rows are not scanned. ValidateConstraint(model_name, name) later scans the table. The documentation requires them to be in two separate migrations: in one atomic migration they are equivalent to a plain AddConstraint, and in one non-atomic migration a failure of the second leaves the database inconsistent.

# pgdemo/migrations/0004_book_pages_positive_not_valid.py
from django.contrib.postgres.operations import AddConstraintNotValid
from django.db import migrations, models
from django.db.models import Q


class Migration(migrations.Migration):

    dependencies = [("pgdemo", "0003_book_title_idx_concurrently")]

    operations = [
        # ADD CONSTRAINT ... NOT VALID: new writes are checked, old rows are not scanned yet
        AddConstraintNotValid(
            "book",
            models.CheckConstraint(condition=Q(pages__gt=0), name="book_pages_positive"),
        ),
    ]
# pgdemo/migrations/0005_validate_book_pages_positive.py
from django.contrib.postgres.operations import ValidateConstraint
from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [("pgdemo", "0004_book_pages_positive_not_valid")]

    operations = [ValidateConstraint("book", name="book_pages_positive")]
-- 0004
ALTER TABLE "pgdemo_book" ADD CONSTRAINT "book_pages_positive" CHECK ("pages" > 0) NOT VALID;
-- 0005
ALTER TABLE "pgdemo_book" VALIDATE CONSTRAINT "book_pages_positive";

Source: Django: Adding constraints without enforcing validation

Executed through sqlmigrate (the stub renders the literal as Int4(0); the real SQL has 0). With the models in django.contrib.postgres declaring the same CheckConstraint, makemigrations --check reported no changes, which is how you know the hand-written pair matches the model state.

ArrayField

ArrayField(base_field, size=None, **options) stores a list. The base field is any field instance except relations and file fields, and arrays can be nested (they must then be rectangular). size is passed to the database but PostgreSQL does not enforce it. Transformation, validation and serialisation are delegated to the base field. The default must be a callable: default=[] is shared between all instances, and the system check says so. Executed against the field in pgdemo/models.py and a variant with default=[]:

baddefault.Post.tags: (fields.E010) ArrayField default should be a callable instead of an instance so that it's not shared between all field instances.
	HINT: Use a callable instead, e.g., use `list` instead of `[]`.

Source: Django: ArrayField

Lookup or transform Meaning SQL operator

contains

All given values are in the array (subset)

@>

contained_by

Every array value is among the given values

<@

overlap

Any value in common (a queryset of values_list is accepted)

&&

len

Array length; further lookups are IntegerField lookups

array_length

0, 1, …​ (index)

Element by position, zero-based like Python; later lookups are the base field’s

[1]

0_1, 0_2 (slice)

Sub-array; later lookups unchanged

[1:2]

Index transforms never raise for an index past the end; they simply do not match. In raw SQL PostgreSQL arrays are one-based, in Django’s lookups they are zero-based. Slices and indexes on multidimensional arrays behave oddly at the database level; only index all the way down to the final element.

Book.objects.filter(tags__contains=["django"])             # has this tag
Book.objects.filter(tags__overlap=["django", "python"])    # has either tag
Book.objects.filter(tags__len=2)
Book.objects.filter(tags__0="django")                      # first tag
Book.objects.filter(tags__0_2__contains=["django"])        # among the first two
WHERE "pgdemo_book"."tags" @> (ARRAY['django'])::varchar(40)[]
WHERE "pgdemo_book"."tags" && (ARRAY['django', 'python'])::varchar(40)[]
WHERE CASE WHEN "pgdemo_book"."tags" IS NULL THEN NULL ELSE coalesce(array_length("pgdemo_book"."tags", 1), 0) END = 2
WHERE ("pgdemo_book"."tags")[1] = 'django'
WHERE ("pgdemo_book"."tags")[1:2] @> (ARRAY['django'])::varchar(40)[]

Source: Django: Querying ArrayField

Compiled, not run against rows (the rendering shows parameters unquoted; the SQL above is that output with quotes added). Array columns want a GIN index (GinIndex(fields=["tags"]) in the model), which is what makes @> and && fast; a plain db_index=True creates a B-tree, which does not help with those operators. A many-to-many relation is still the right model for tags that have their own attributes, are shared across rows or need referential integrity; an array is for simple, self-contained lists that are read and written together with the row.

HStoreField

HStoreField stores a dictionary of string keys and string-or-None values. It needs django.contrib.postgres in INSTALLED_APPS and the hstore extension (HStoreExtension()); missing the first gives can’t adapt type 'dict', missing the second gives type "hstore" does not exist. Use models.JSONField (3.1, portable) for nested or typed data; hstore remains a good fit for flat string attributes with GiST or GIN indexes.

Any lookup name that is not one of the documented lookups is interpreted as a key lookup, with no error, so a typo in a lookup silently queries a key that does not exist. Check queries return what you intend. When a key clashes with a lookup name (such as contains), use the field’s contains lookup explicitly.

Book.objects.filter(extra__has_key="isbn")                     # key present
Book.objects.filter(extra__isbn="978-0-262-03384-8")           # key lookup (value equals)
Book.objects.filter(extra__contains={"format": "hardcover"})   # all pairs present
Book.objects.filter(extra__has_any_keys=["isbn", "doi"])
WHERE "pgdemo_book"."extra" ? 'isbn'
WHERE ("pgdemo_book"."extra" -> 'isbn') = '978-0-262-03384-8'
WHERE "pgdemo_book"."extra" @> '{"format": "hardcover"}'

Source: Django: HStoreField

Compiled only. The other documented lookups are contained_by, has_keys, and the keys/values transforms (arrays you combine with ArrayField lookups). inspectdb introspects HStoreField since 6.1, when psycopg 3.2+ is installed and django.contrib.postgres is installed. To require or restrict keys, use KeysValidator(keys, strict=False, messages=None) from django.contrib.postgres.validators.

Range fields

PostgreSQL range types store an interval as one value, and Django maps them to five fields. In Python they are psycopg Range objects (import the classes from django.db.backends.postgresql.psycopg_any, which works with psycopg 2 and 3), and the fields also accept tuples.

Field PostgreSQL type Python class

IntegerRangeField

int4range

NumericRange

BigIntegerRangeField

int8range

NumericRange

DecimalRangeField(default_bounds='[)')

numrange

NumericRange

DateTimeRangeField(default_bounds='[)')

tstzrange

DateTimeTZRange

DateRangeField

daterange

DateRange

Bounds default to lower-inclusive, upper-exclusive ([)). The default_bounds argument (4.1) changes this for list and tuple input on the two non-discrete fields; discrete types (integers, dates) are always returned in the canonical [) form. A range with equal inclusive-lower and exclusive-upper values has no points; PostgreSQL normalises it to empty and the original bound values are lost.

import datetime

from django.db.backends.postgresql.psycopg_any import DateRange

jan_2026 = DateRange(datetime.date(2026, 1, 1), datetime.date(2026, 2, 1))

Book.objects.filter(print_run__contains=datetime.date(2026, 1, 15))   # range contains a point
Book.objects.filter(print_run__overlap=jan_2026)                      # ranges overlap
Book.objects.filter(print_run__startswith=datetime.date(2026, 1, 1))  # lower bound equals
WHERE "pgdemo_book"."print_run" @> '2026-01-15'::date
WHERE "pgdemo_book"."print_run" && '[2026-01-01, 2026-02-01)'
WHERE lower("pgdemo_book"."print_run") = '2026-01-01'

Source: Django: Range fields

Compiled only. Other lookups and transforms: contained_by (also available on plain numeric, date and datetime fields), fully_lt, fully_gt, not_lt, not_gt, adjacent_to, isempty, lower_inc, lower_inf, upper_inc, upper_inf, endswith, and RangeOperators/RangeBoundary for expressions and constraints. RangeMinValueValidator and RangeMaxValueValidator bound a range in forms and full_clean(). For a GiST index on a range column use GistIndex(fields=["print_run"]) (see the next section).

PostgreSQL index types

Meta.indexes is the same mechanism as in any Django model (see Constraints, Indexes & Composite Keys); the classes in django.contrib.postgres.indexes choose the PostgreSQL access method. Index and db_index=True create a B-tree, which is not helpful for arrays, ranges, hstore or search vectors.

Class Parameters Typical use

BTreeIndex

fillfactor (10-100), deduplicate_items (5.1)

The default; scalar columns with a tuned fill factor

GinIndex

fastupdate, gin_pending_list_limit

Arrays, hstore, full-text vectors, trigrams; btree_gin extension for other types

GistIndex

buffering, fillfactor

Range fields, hstore, spatial; btree_gist extension for other types

SpGistIndex

fillfactor; covering indexes on PostgreSQL 14+ (4.1)

Space-partitioned data

BrinIndex

autosummarize, pages_per_range

Very large, naturally ordered tables (timestamps in an append-only log)

HashIndex

fillfactor

Equality lookups only

BloomIndex

length (1-4096), columns (up to 32 values of 1-4095); bloom extension

Equality on many columns

Source: Django: PostgreSQL specific model indexes

All accept the generic Index options (name, condition for partial indexes, include for covering indexes, expressions for functional indexes). OpClass(expression, name) attaches an operator class, which is how the trigram index in the models is written (gin_trgm_ops); it also works in functional unique constraints and exclusion constraints.

from django.contrib.postgres.indexes import GinIndex, OpClass
from django.contrib.postgres.search import SearchVector
from django.db.models.functions import Upper

# in Meta.indexes
GinIndex(OpClass(Upper("name"), name="gin_trgm_ops"), name="author_name_trgm_idx")
GinIndex(SearchVector("title", "summary", config="english"), name="book_fts_idx")
CREATE INDEX "author_name_trgm_idx" ON "pgdemo_author" USING gin ((UPPER("name")) gin_trgm_ops);
CREATE INDEX "book_fts_idx" ON "pgdemo_book" USING gin ((to_tsvector('english'::regconfig, COALESCE("title", '') || ' ' || COALESCE("summary", ''))));

Source: Django: OpClass() expressions

The two statements were produced by Index.create_sql() and sqlmigrate and not executed. An index only helps if the query uses the same expression: the functional index on UPPER(name) serves a query that compares Upper("name"), not one that compares name directly. Confirm with QuerySet.explain() on the real data.

ExclusionConstraint

An exclusion constraint generalises a unique constraint to any commutative operator: "no two rows may satisfy this operator on these expressions at the same time". PostgreSQL implements it with an index (GiST by default). The classic use is preventing overlapping bookings, and it is the only reliable way to do that: checking overlap in Python before saving has a race between two concurrent requests.

ExclusionConstraint(*, name, expressions, index_type=None, condition=None, deferrable=None, include=None, violation_error_code=None, violation_error_message=None):

  • expressions: an iterable of (expression_or_field_name, operator) pairs; use RangeOperators instead of raw operator strings. For ("room", RangeOperators.EQUAL) on a scalar column you need the btree_gist extension (BtreeGistExtension()).

  • index_type: GiST (default), Hash (6.1) or SPGiST, case-insensitive.

  • condition: a Q restricting the constraint to a subset of rows (condition=Q(cancelled=False)), with the same database restrictions as a partial index.

  • deferrable: Deferrable.DEFERRED or Deferrable.IMMEDIATE; a deferred constraint is enforced at the end of the transaction, with a performance penalty.

  • include: non-key columns for index-only scans (GiST and SP-GiST).

  • violation_error_code (5.0) and violation_error_message: used when model validation raises ValidationError.

  • OpClass() can set a custom operator class for any expression.

A violation raises IntegrityError at save time, and exclusion constraints are also checked by model validation (full_clean(), ModelForm). The model and DDL are the Booking model and the CREATE TABLE …​ EXCLUDE USING GIST in the earlier listings. When a range is stored as two columns instead of a range field, build the range in the expression:

from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateTimeRangeField, RangeBoundary, RangeOperators
from django.db import models
from django.db.models import Func, Q


class TsTzRange(Func):
    function = "TSTZRANGE"
    output_field = DateTimeRangeField()


class Slot(models.Model):
    room = models.ForeignKey("pgdemo.Room", on_delete=models.CASCADE)
    start = models.DateTimeField()
    end = models.DateTimeField()
    cancelled = models.BooleanField(default=False)

    class Meta:
        constraints = [
            ExclusionConstraint(
                name="exclude_overlapping_slots",
                expressions=[
                    (TsTzRange("start", "end", RangeBoundary()), RangeOperators.OVERLAPS),
                    ("room", RangeOperators.EQUAL),
                ],
                condition=Q(cancelled=False),
            ),
        ]
CONSTRAINT "exclude_overlapping_slots" EXCLUDE USING GIST ((TSTZRANGE("start", "end", '[)')) WITH &&, "room_id" WITH =) WHERE (NOT "cancelled")

Source: Django: ExclusionConstraint

check, makemigrations and sqlmigrate were executed for this model; the constraint itself was not enforced against a server. The test that proves it, which needs PostgreSQL (not run):

# pgdemo/tests.py -- needs PostgreSQL with btree_gist (created by the 0001 migration)
import datetime as dt

from unittest import skipUnless

from django.db import IntegrityError, connection, transaction
from django.db.backends.postgresql.psycopg_any import DateTimeTZRange
from django.test import TestCase

from .models import Booking, Room


def _span(hour_from: int, hour_to: int) -> DateTimeTZRange:
    day = dt.datetime(2026, 11, 5, tzinfo=dt.UTC)
    return DateTimeTZRange(day.replace(hour=hour_from), day.replace(hour=hour_to))


@skipUnless(connection.vendor == "postgresql", "exclusion constraints need PostgreSQL")
class BookingExclusionTests(TestCase):
    def setUp(self):
        self.room = Room.objects.create(name="Reading room")
        Booking.objects.create(room=self.room, timespan=_span(18, 20))

    def test_overlap_in_same_room_is_rejected(self):
        with self.assertRaises(IntegrityError), transaction.atomic():
            Booking.objects.create(room=self.room, timespan=_span(19, 21))

    def test_adjacent_range_is_allowed(self):
        Booking.objects.create(room=self.room, timespan=_span(20, 22))  # [18,20) and [20,22) do not overlap

    def test_cancelled_booking_does_not_block(self):
        Booking.objects.create(room=self.room, timespan=_span(18, 20), cancelled=True)

Source: Django: Writing and running tests

Aggregates, expressions and functions

The aggregates in django.contrib.postgres.aggregates have no default alias, so you always name them (aggregate(arr=ArrayAgg("somefield"))). All accept filter=, and most accept default=.

Class Returns Note

ArrayAgg(expression, distinct=False, filter=None, default=None, order_by=())

A list (including nulls)

Use order_by for ordering; ordering was deprecated in 5.2 and removed in 6.1

JSONBAgg(expressions, distinct=False, filter=None, default=None, order_by=())

A JSON array, queryable with key and index lookups

Combine with JSONObject

StringAgg

Values joined by a delimiter

The PostgreSQL class is deprecated since 6.0 for django.db.models.StringAgg, which takes a Value/expression as delimiter (a plain string is deprecated)

BoolAnd, BoolOr

True if all / at least one value is true

BitAnd, BitOr, BitXor

Bitwise results

Deprecated since 6.1 for the generally available classes in django.db.models

Statistical aggregates (Corr, CovarPop, RegrAvgX, …​)

Correlation, regression statistics

Not detailed here; see the aggregates reference

Source: Django: PostgreSQL specific aggregation functions

Since 5.0, ArrayAgg, JSONBAgg and StringAgg return None instead of [], [] and '' when there are no rows, unless you pass default=. Related expressions:

  • ArraySubquery(queryset) (django.contrib.postgres.expressions) builds an array with PostgreSQL’s ARRAY(…​) constructor from a values() queryset with one column. Unlike ArrayAgg it is not an aggregate, so it needs no GROUP BY.

  • TransactionNow() (django.contrib.postgres.functions) is the time the current transaction started, whereas Django’s Now() is the time of the current statement; only the outermost atomic() sets it.

  • RandomUUID() returns a version 4 UUID; use the cross-database UUID4 (6.1) instead.

from django.contrib.postgres.aggregates import ArrayAgg, JSONBAgg
from django.contrib.postgres.expressions import ArraySubquery
from django.db.models import OuterRef, StringAgg, Value
from django.db.models.functions import JSONObject

Author.objects.annotate(titles=ArrayAgg("books__title", order_by="books__title"))

Author.objects.annotate(
    titles=StringAgg("books__title", delimiter=Value(", "), order_by="books__title")
)

books = Book.objects.filter(author=OuterRef("pk")).values(json=JSONObject(title="title", pages="pages"))
Author.objects.annotate(books_json=ArraySubquery(books))
ARRAY_AGG("pgdemo_book"."title" ORDER BY "pgdemo_book"."title") AS "titles" ... GROUP BY "pgdemo_author"."id"
ARRAY(SELECT JSON_OBJECT('title' VALUE "U0"."title", 'pages' VALUE "U0"."pages" RETURNING JSONB) AS "json"
      FROM "pgdemo_book" "U0" WHERE "U0"."author_id" = ("pgdemo_author"."id")) AS "books_json"

Source: Django: PostgreSQL specific query expressions

Compiled only (django.db.models.StringAgg with a Value delimiter compiled to STRING_AGG(…​, ORDER BY …​) on the PostgreSQL backend). On the 5.2 LTS, django.db.models.StringAgg does not exist yet: import the class from django.contrib.postgres.aggregates there. The portable aggregates and window functions belong to Aggregation, Expressions & Functions.

Full-text search

PostgreSQL’s full-text search compares a tsvector (a document reduced to normalised lexemes, stemmed and without stop words) with a tsquery. Django exposes both through django.contrib.postgres.search. It is "inside your database", so it combines with relational filters and needs no extra service; it is less powerful than a dedicated engine (Elasticsearch, Solr), which you would feed through a translation layer. See the database reference for those engines and PostgreSQL pgvector for semantic search in the same database.

The search lookup and SearchVector

The simplest search is a lookup on one column, which builds a to_tsvector from the column and a plainto_tsquery from the term, both in the database’s default configuration (needs django.contrib.postgres in INSTALLED_APPS):

from django.contrib.postgres.search import SearchVector

Book.objects.filter(summary__search="cheese")

Book.objects.annotate(search=SearchVector("title", "summary")).filter(search="django")
WHERE to_tsvector(COALESCE("pgdemo_book"."summary", '')) @@ (plainto_tsquery('cheese'))
WHERE to_tsvector(COALESCE("title", '') || ' ' || COALESCE("summary", '')) @@ (plainto_tsquery('django'))

Source: Django: The search lookup

Compiled only. SearchVector(*expressions, config=None, weight=None) takes field names or expressions, concatenates several arguments with a space, and vectors can be added (SearchVector("a") + SearchVector("b")). The relation-spanning form (SearchVector("body_text", "blog__tagline")) works too.

SearchQuery, user input and Lexeme

SearchQuery(value, config=None, search_type="plain") turns text into a tsquery. The search_type values:

search_type Meaning

plain (default)

The terms are separate keywords, all of which must match (after stemming).

phrase

The terms form one phrase, in order.

raw

A tsquery with operators you write, for example "'tomato' & ('red' | 'green')".

websearch

Web-search syntax (quoted phrases, - for exclusion, OR); requires PostgreSQL 11 or newer, so always available now.

Queries combine with &, | and ~. Passing raw user input to the raw type lets a user inject operators; plain and websearch are the safe choices for a search box. Since 6.0, Lexeme(value, output_field=None, *, invert=False, prefix=False, weight=None) makes operators safe with untrusted strings: each lexeme is escaped, and lexemes combine with &, | and ~, with prefix matching and weights.

from django.contrib.postgres.search import Lexeme, SearchQuery

Book.objects.filter(search_vector=SearchQuery("django -flask", search_type="websearch", config="english"))
Book.objects.filter(search_vector=SearchQuery(Lexeme("django") & ~Lexeme("flask"), config="english"))
WHERE "pgdemo_book"."search_vector" @@ (websearch_to_tsquery('english'::regconfig, 'django -flask'))
WHERE "pgdemo_book"."search_vector" @@ (to_tsquery('english'::regconfig, ('django' & !'flask')))

Source: Django: SearchQuery

Compiled only. Lexeme exists from 6.0; on a 5.2 LTS project use plain/websearch and do not build raw queries from user input.

Ranking and headlines

SearchRank(vector, query, weights=None, normalization=None, cover_density=False) scores how well a vector matches: it considers frequency, proximity and the weight of the field in which the term appears. normalization is a bit mask (combine with Value(2).bitor(Value(4))); cover_density=True takes the proximity of terms into account. Weights A, B, C, D default to 1.0, 0.4, 0.2, 0.1; pass weights=[d, c, b, a] to change them. SearchHeadline(expression, query, config=None, start_sel=None, stop_sel=None, max_words=None, min_words=None, short_word=None, highlight_all=None, max_fragments=None, fragment_delimiter=None) returns the text with the matched terms wrapped; the defaults are PostgreSQL’s (<b>, </b>). The result of SearchHeadline is an HTML-looking string built from user-controlled text: if you set start_sel and stop_sel to markup, escape the surrounding text yourself before marking it safe in a template.

from django.contrib.postgres.search import SearchHeadline, SearchQuery, SearchRank
from django.db.models import F, Value

query = SearchQuery("django orm", config="english")
results = (
    Book.objects.filter(search_vector=query)
    .annotate(
        rank=SearchRank(F("search_vector"), query, normalization=Value(2).bitor(Value(4))),
        headline=SearchHeadline("summary", query, config="english", start_sel="<mark>", stop_sel="</mark>"),
    )
    .order_by("-rank")
    .defer("search_vector")
)
SELECT ..., ts_rank("pgdemo_book"."search_vector", plainto_tsquery('english'::regconfig, 'django orm'), (2 | 4)) AS "rank",
       ts_headline('english'::regconfig, "pgdemo_book"."summary", plainto_tsquery('english'::regconfig, 'django orm'),
                   <options string carrying StartSel and StopSel>) AS "headline"
FROM "pgdemo_book"
WHERE "pgdemo_book"."search_vector" @@ (plainto_tsquery('english'::regconfig, 'django orm'))
ORDER BY 9 DESC

Source: Django: SearchRank

Compiled only (the options argument of ts_headline is abbreviated here). The defer("search_vector") is deliberate: a GeneratedField is loaded like any other column, so without it every search row drags the tsvector across the wire.

Configurations and weights

config on SearchVector, SearchQuery and SearchHeadline chooses the language parser and dictionaries (config="english"); it can also be an expression such as F("blog__language") so each row is processed in its own language. Vector and query must use the same configuration to match as expected. Weights are set per vector before combining:

vector = SearchVector("title", weight="A", config="english") + SearchVector("summary", weight="B", config="english")
rank = SearchRank(vector, SearchQuery("cheese", config="english"), weights=[0.2, 0.4, 0.6, 0.8])

Source: Django: Weighting queries

A stored vector with GeneratedField

Computing to_tsvector for every row on every query is fine for a few hundred rows. The documentation recommends, once you search more than that, either a functional GinIndex(SearchVector(…​)) (the example in PostgreSQL index types; every query must then use the same expression) or a stored column. The stored column is a SearchVectorField. Older code keeps it populated with a trigger; since 5.0 a GeneratedField lets PostgreSQL maintain it, with no trigger and no signal to forget. That is the search_vector field in the models above; the generated DDL is

"search_vector" tsvector GENERATED ALWAYS AS ((setweight(to_tsvector('english'::regconfig, COALESCE("title", '')), 'A')
    || setweight(to_tsvector('english'::regconfig, COALESCE("summary", '')), 'B'))) STORED
CREATE INDEX "book_search_vector_idx" ON "pgdemo_book" USING gin ("search_vector");

Source: Django: Using GeneratedField

The migration was generated and its SQL printed here; the column was not created on a server. What to know:

  • The column is read-only. Do not assign to it; it is recomputed when title or summary change, including through QuerySet.update() and bulk_update().

  • The configuration is part of the generation expression (config="english" above), so one column serves one language; use several columns, or the per-query config expressions described earlier, for several languages.

  • Since 6.0 Django refreshes generated values from the database after save() on PostgreSQL (through RETURNING), so book.search_vector is current right after saving.

  • Adding a generated column to a large existing table rewrites it; plan the migration like any other table rewrite (see Migrations).

  • Since 6.1 GeneratedField supports virtual columns (db_persist=False) on PostgreSQL 18+. A column that a GIN index covers has to be stored, so this page uses db_persist=True; check PostgreSQL’s documentation for the index support of virtual generated columns before choosing otherwise.

  • Searching against the stored column is just Book.objects.filter(search_vector=query), as in the previous examples; the older Book.objects.update(search_vector=SearchVector("body_text")) pattern is for trigger-maintained columns only.

A test for it, which needs PostgreSQL (not run):

# pgdemo/tests.py -- needs PostgreSQL
from unittest import skipUnless

from django.contrib.postgres.search import SearchQuery, SearchRank
from django.db import connection
from django.db.models import F
from django.test import TestCase

from .models import Author, Book


@skipUnless(connection.vendor == "postgresql", "full-text search needs PostgreSQL")
class BookSearchTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        author = Author.objects.create(name="A. Writer")
        cls.title_hit = Book.objects.create(author=author, title="Django cheese", summary="unrelated")
        cls.summary_hit = Book.objects.create(author=author, title="Unrelated", summary="all about cheese")
        Book.objects.create(author=author, title="Nothing", summary="nothing here")

    def test_title_outranks_summary(self):
        query = SearchQuery("cheese", config="english")
        found = list(
            Book.objects.filter(search_vector=query)
            .annotate(rank=SearchRank(F("search_vector"), query))
            .order_by("-rank")
        )
        self.assertEqual(found, [self.title_hit, self.summary_hit])  # weight A beats weight B

    def test_generated_vector_follows_updates(self):
        Book.objects.filter(pk=self.summary_hit.pk).update(summary="bread")
        query = SearchQuery("cheese", config="english")
        self.assertEqual(list(Book.objects.filter(search_vector=query)), [self.title_hit])

Source: Django: Search

Trigram similarity and unaccent

A trigram is a group of three consecutive characters; two strings are similar when they share many. Trigrams find "Middlesborough" when the row says "Middlesbrough" and suit short strings (names, titles) where users type something close. They need the pg_trgm extension (TrigramExtension() in a migration), and unaccent needs its own (UnaccentExtension()).

Tool Use

name__trigram_similar="…​"

Lookup (%): similarity above the current threshold. On CharField and TextField.

trigram_word_similar (4.0), trigram_strict_word_similar (4.2)

The greatest number of shared trigrams with any substring of the field; the strict variant forces word boundaries.

TrigramSimilarity(expression, string)

Annotation with the similarity (0 to 1) for ordering and thresholds.

TrigramWordSimilarity(string, expression), TrigramStrictWordSimilarity

Word variants; note that the string comes first.

TrigramDistance, TrigramWordDistance, TrigramStrictWordDistance

The complement, for order_by("distance").

nameunaccenticontains="…​"

Accent-insensitive lookup, chainable with other lookups; it generally forces a full table scan.

Source: Django: PostgreSQL specific lookups

from django.contrib.postgres.search import TrigramSimilarity

Author.objects.filter(name__trigram_similar="Irurueta")

Author.objects.annotate(sim=TrigramSimilarity("name", "Irurueta")).filter(sim__gt=0.3).order_by("-sim")

Author.objects.filter(name__unaccent__icontains="helene")
WHERE "pgdemo_author"."name" % 'Irurueta'
SELECT ..., SIMILARITY("pgdemo_author"."name", 'Irurueta') AS "sim" FROM "pgdemo_author"
WHERE SIMILARITY("pgdemo_author"."name", 'Irurueta') > 0.3 ORDER BY 3 DESC
WHERE UPPER(UNACCENT("pgdemo_author"."name")::text) LIKE '%' || UPPER(REPLACE(REPLACE(REPLACE((UNACCENT('helene')), E'\\', E'\\\\'), E'%', E'\\%'), E'_', E'\\_')) || '%'

Source: Django: Trigram similarity

Compiled only. The trigram index shown in the models and PostgreSQL index types (GinIndex(OpClass(Upper("name"), name="gin_trgm_ops"))) serves the % operator on UPPER(name); a lookup on name itself needs an index on name. Trigram similarity is asymmetric in practice: the documentation’s example shows that a short search string matches a longer name poorly, so choose between trigram_similar, the word variants and unaccent against your own data, and tune the similarity threshold in PostgreSQL if the default does not suit.

Sync and async

All of the fields, lookups and expressions above are ordinary query building blocks, so they work with the async ORM (await Book.objects.filter(…​).acount(), async for) exactly as they do synchronously; the compiled SQL is identical, as the portable test showed on SQLite. What differs is the connection model: each async ORM call runs in a thread with the connection of its request, concurrent requests in one process share the connection pool, persistent connections (CONN_MAX_AGE) should be disabled, and transactions do not yet work in async mode. So for an ASGI deployment of Bookshelf: psycopg 3 with "pool" in OPTIONS, CONN_MAX_AGE = 0, a pool sized to the in-flight queries you want, and transactional code kept in a synchronous function called with sync_to_async(). See Introduction & Architecture for where the database sits in the request cycle.

Pitfalls checklist

  • Forgetting the app or the extension. django.contrib.postgres in INSTALLED_APPS for lookups; the extension in a migration for the type or operator class (type "hstore" does not exist, operator class "gin_trgm_ops" does not exist). The extension operation must run before the first thing that needs it.

  • default=[] and default={}. Mutable defaults shared between instances; use list and dict.

  • Typos in hstore lookups become key lookups silently.

  • unaccent and icontains can force full scans; measure before relying on them.

  • A GIN or GiST index that the query cannot use. Plain db_index=True is a B-tree; functional indexes must match the query’s expression; confirm with explain().

  • Checking overlap in Python. Racy; use ExclusionConstraint. And remember it needs btree_gist for scalar columns.

  • CREATE INDEX CONCURRENTLY in an atomic migration fails; set atomic = False and keep the operation alone.

  • AddConstraintNotValid and ValidateConstraint in one migration defeats the purpose.

  • pool without psycopg[pool], with CONN_MAX_AGE other than 0, or with psycopg2.

  • Persistent connections under ASGI, and connections left open by threads or scripts outside the request cycle.

  • Transaction-pooling poolers and iterator(): disable server-side cursors.

  • Explicit primary keys in fixtures or imports without sqlsequencereset afterwards.

  • Searching with different configurations for vector and query, or with raw queries built from user input.

  • Trusting SQLite in tests for PostgreSQL-only features: the code that passes there is the fallback branch, not the feature.

  • StringAgg, ArrayAgg(ordering=…​) and the bit aggregates from django.contrib.postgres.aggregates in new code: deprecated or removed (see below).

Testing on PostgreSQL

Run the suite on the database you deploy to. In a CI job use a PostgreSQL service container (version 15 or newer) and the same DATABASES as production with a different NAME; Django creates and drops the test_ database, running the migrations (and so the extension operations). Mark PostgreSQL-only tests with unittest.skipUnless(connection.vendor == "postgresql", …​) as in the examples above, so the suite still runs on a laptop with SQLite. skipUnlessDBFeature is the alternative for features Django models as database flags. For fixtures, factories and test data, see Fixtures, Serialization & Initial Data.

Where the books are outdated

The four reference books for this section target Django 1.11 to 3.x. Where they touch databases the differences are:

Older practice Current practice Changed in

psycopg2 for PostgreSQL

psycopg 3 (psycopg[binary]/[c]/[pool]); psycopg2 still works but is likely to be removed

4.2

django.contrib.postgres.fields.JSONField and its form field and key transforms

models.JSONField, portable across backends

3.1 added it, 4.0 removed the PostgreSQL class

CICharField, CIEmailField, CITextField for case-insensitive columns

db_collation with a non-deterministic collation created by CreateCollation

deprecated 4.2, removed 5.1

index_together and hand-written SQL for GIN/trigram indexes

Meta.indexes with GinIndex, OpClass, functional expressions

index_together removed in 5.1

A trigger plus SearchVectorField to keep a search column current

A GeneratedField(db_persist=True) with a GIN index

5.0

Unique-together plus Python checks to prevent overlapping periods

ExclusionConstraint with a range field

3.0 (introduced), many options added since (OpClass 4.1, violation_error_code 5.0, Hash 6.1)

ArrayAgg(ordering=…​)

order_by=

deprecated 5.2, removed 6.1

StringAgg(expr, ", ") with a plain delimiter

StringAgg(expr, Value(", ")), from django.db.models

6.0

Building search raw queries from user input

SearchQuery with websearch, or Lexeme

websearch 3.1, Lexeme 6.0

Blocking CREATE INDEX on a live table

AddIndexConcurrently in a non-atomic migration

3.0

The books' blog and search chapters remain a good conceptual guide to SearchVector, SearchRank, weights and trigram similarity; the class names are still current, but check every import and keyword argument against the version of Django you run before copying it, and treat any settings.DATABASES shown without a pool, health checks or an environment-driven password as a teaching simplification.

What changed recently

Version Change

3.0

ExclusionConstraint, RangeOperators, RangeBoundary, AddIndexConcurrently/RemoveIndexConcurrently.

3.1

SearchQuery websearch type, SearchHeadline, cover_density for SearchRank, query expressions in search; models.JSONField.

4.0

Connect through a service name; AddConstraintNotValid/ValidateConstraint; trigram_word_similar; the PostgreSQL JSONField removed.

4.1

Functional unique constraints and OpClass in ExclusionConstraint; BitXor; default_bounds for range fields; covering SpGistIndex and exclusion constraints on PostgreSQL 14+.

4.2

psycopg 3 supported; assume_role and server_side_binding; strict word trigrams; CIText fields deprecated.

5.0

GeneratedField; ExclusionConstraint.violation_error_code; aggregates return None for no rows.

5.1

PostgreSQL connection pools; BTreeIndex(deduplicate_items=…​); SQLite init_command and transaction_mode; CIText fields removed.

5.2 (LTS)

Oracle connection pools; ordering on ArrayAgg/JSONBAgg/StringAgg deprecated; PostgreSQL 14 required.

6.0

Lexeme for safe search operators; system checks that django.contrib.postgres is installed; hints for extension operations; StringAgg available on all backends (PostgreSQL class deprecated); GeneratedField values are refreshed after save() through RETURNING on PostgreSQL.

6.1

PostgreSQL 15 minimum; ExclusionConstraint accepts the Hash index type; inspectdb introspects HStoreField (psycopg 3.2+); GeneratedField virtual columns on PostgreSQL 18+; BitAnd/BitOr/BitXor in django.db.models, the PostgreSQL versions deprecated; UUID4/UUID7 database functions; ordering removed.

Source: Django 6.1 release notes

Before upgrading, run the tests with python -Wd manage.py test so deprecation warnings appear; 6.2 (the next LTS, planned for April 2027) is the last release under the old versioning, and from 2028 releases are named by year.

References