Django Module 4 – The ORM in depth: performance, integrity and PostgreSQL

PostgreSQL features in Django

Run Django on PostgreSQL with psycopg 3: connection pools and health checks, JSONField and ArrayField lookups, ranked full-text search and generated columns.

Advanced
20 minutes
Examples run with Python 3.14.8, Django 6.1.2 and Pyodide 314.0.7
By MySmartCoPilot

Free: the whole lesson. With a pass: the model answers to its 2 interview questions.

What you will learn

Configure DATABASES for PostgreSQL with psycopg 3, and check a server against the versions Django 6.1 supports
Choose between closing connections, persistent connections with health checks and a pool, from what each did with 20 requests
Query lists and documents with ArrayField and JSONField lookups, and tell SQL NULL from JSON null with JSONNull
Build weighted full-text search that filters on the match before it ranks, in the configuration that fits the text
Decide between a stored and a virtual GeneratedField, and index a stored search column with GIN

Before you start

Custom managers, raw SQL and EXPLAIN

What you’ll learn · Before you start

What you will learn

Configure DATABASES for PostgreSQL with psycopg 3, and check a server against the versions Django 6.1 supports
Choose between closing connections, persistent connections with health checks and a pool, from what each did with 20 requests
Query lists and documents with ArrayField and JSONField lookups, and tell SQL NULL from JSON null with JSONNull
Build weighted full-text search that filters on the match before it ranks, in the configuration that fits the text
Decide between a stored and a virtual GeneratedField, and index a stored search column with GIN

Before you start

Custom managers, raw SQL and EXPLAIN

On this page

Twenty requests in a row to a Django site on PostgreSQL opened twenty database connections, one each, because that is Django’s default; one setting brought it down to 1, another to 2. PostgreSQL also gives Django features of its own: arrays and JSON documents with operators to search them, full-text search with ranking, and, since PostgreSQL 18, generated columns computed when they are read.

You will connect Django 6.1 to PostgreSQL through psycopg 3, choose how connections are kept, query ArrayField and JSONField, build a weighted search that matches before it ranks, and choose between stored and virtual generated columns. Every example ran on Django 6.1.2 and psycopg 3.3.6 against PostgreSQL 18.6, each on a throwaway server of its own.

Django 6.1 needs PostgreSQL 15 or later, reached through psycopg

DATABASES names the backend and the server, and psycopg: The PostgreSQL driver for Python that Django's postgresql backend uses to talk to the server. Django 6.1 needs psycopg 3.1.12 or later, or the older psycopg2 2.9.9 or later, and recommends psycopg 3., the driver, does the talking. This program reports what the connection found:

What Django 6.1 connects with

nivas/connect.py

"""What Django 6.1 connects with: the server's version against the oldest one this release of Django accepts, the
psycopg package, the cursor class Django chose, and two settings Django gives every session. PostgreSQL runs on a
throwaway server of its own (pgserver.py), with the DATABASES of config/settings.py.
"""

import pgserver

pgserver.start()

import seed  # noqa: E402,F401  (starts Django, creates the tables, adds the rows)
import django  # noqa: E402
import psycopg  # noqa: E402
from django.db import connection  # noqa: E402


def version(number):
    """180006 -> '18.6', the way PostgreSQL and libpq number their releases."""
    return f'{number // 10000}.{number % 10000}'


with connection.cursor() as cursor:
    cursor.execute('SHOW transaction_isolation')
    isolation = cursor.fetchone()[0]
    cursor.execute('SHOW TimeZone')
    zone = cursor.fetchone()[0]
    cursor_class = type(cursor.cursor)

print(f'Django {django.get_version()}, ENGINE {connection.settings_dict["ENGINE"]}')
print(f'server: PostgreSQL {version(connection.pg_version)}, get_database_version() {connection.get_database_version()}')
print(f'the oldest this Django accepts: {connection.features.minimum_database_version}')
print(f'psycopg {psycopg.__version__}, implementation {psycopg.pq.__impl__}, libpq {version(psycopg.pq.version())}')
print(f'cursor: {cursor_class.__name__}, a psycopg.ClientCursor: {issubclass(cursor_class, psycopg.ClientCursor)}')
print(f'transaction_isolation: {isolation} | TimeZone: {zone}')

Output

Django 6.1.2, ENGINE django.db.backends.postgresql
server: PostgreSQL 18.6, get_database_version() (18, 6)
the oldest this Django accepts: (15,)
psycopg 3.3.6, implementation binary, libpq 18.6
cursor: Cursor, a psycopg.ClientCursor: True
transaction_isolation: read committed | TimeZone: UTC

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 connect.py

config/settings.py · nivas/config/settings.py

"""
Django settings for config project.

Generated by 'django-admin startproject' using Django 6.1.2.

For more information on this file, see
https://docs.djangoproject.com/en/6.1/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/6.1/ref/settings/
"""

import os
from pathlib import Path

# Build paths inside the project like this: BASE_DIR / 'subdir'.
BASE_DIR = Path(__file__).resolve().parent.parent


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/6.1/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = 'django-insecure-this-key-is-for-this-lesson-only-make-your-own'

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = []


# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'django.contrib.postgres',
    'notices.apps.NoticesConfig',
    'society.apps.SocietyConfig',
]

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
]

ROOT_URLCONF = 'config.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'config.wsgi.application'


# Database
# https://docs.djangoproject.com/en/6.1/ref/settings/#databases

# This project uses PostgreSQL-only fields (ArrayField), so it has no SQLite database. The examples start a throwaway
# PostgreSQL 18 server first (pgserver.py), which sets NIVAS_PG_PORT.
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '127.0.0.1',
        'PORT': os.environ.get('NIVAS_PG_PORT', '5432'),
        'NAME': 'postgres',
        'USER': 'postgres',
    },
}


# Password validation
# https://docs.djangoproject.com/en/6.1/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]


# Internationalization
# https://docs.djangoproject.com/en/6.1/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_TZ = True


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/6.1/howto/static-files/

STATIC_URL = 'static/'

nivas/pgserver.py

"""A throwaway PostgreSQL 18 server for one example: start() creates a database cluster in a folder of its own, starts
the server listening only on 127.0.0.1, an address no other computer can reach, and tells Django its port through
NIVAS_PG_PORT (config/settings.py reads it). The server is stopped and its folder deleted when the example ends, also
when it fails. Autovacuum is off, so the table statistics the plans use are the ones the example gathers itself, with
ANALYZE, and every run plans the same way.

It needs PostgreSQL's server programs, initdb and pg_ctl, on PATH (or in the mise installation beside this Python),
and the psycopg package, which Django uses to talk to PostgreSQL.
"""

import atexit
import os
import shutil
import socket
import subprocess
import sys
import tempfile
from pathlib import Path


def _postgres_bin():
    on_path = shutil.which('pg_ctl')
    if on_path:
        return Path(on_path).parent
    installs = Path(sys.executable).resolve().parents[3]  # <mise>/installs/python/3.14.8/bin/python3
    found = sorted(installs.glob('postgres/18.*/bin/pg_ctl'))
    if not found:
        sys.exit('PostgreSQL 18 was not found: install it so that pg_ctl is on PATH')
    return found[-1].parent


def start():
    pg = _postgres_bin()
    work = Path(tempfile.mkdtemp(prefix='pg', dir='.')).resolve()
    data = work / 'data'
    subprocess.run([pg / 'initdb', '-D', data, '-U', 'postgres', '-A', 'trust', '-E', 'UTF8', '--no-locale'],
                   check=True, capture_output=True)
    with socket.socket() as probe:  # ask the operating system for a port that nothing uses
        probe.bind(('127.0.0.1', 0))
        port = probe.getsockname()[1]
    with open(data / 'postgresql.conf', 'a') as conf:
        conf.write(f"listen_addresses = '127.0.0.1'\nport = {port}\nunix_socket_directories = ''\nautovacuum = off\n")
    subprocess.run([pg / 'pg_ctl', '-D', data, '-l', work / 'server.log', '-w', 'start'], check=True,
                   capture_output=True)

    def stop():
        subprocess.run([pg / 'pg_ctl', '-D', data, '-m', 'fast', '-w', 'stop'], capture_output=True)
        shutil.rmtree(work, ignore_errors=True)

    atexit.register(stop)
    os.environ['NIVAS_PG_PORT'] = str(port)

The server was PostgreSQL 18.6, and minimum_database_version says that Django 6.1.2 accepts nothing older than 15: on the first connection of each alias, Django compares the two and raises NotSupportedError for an older server. Django’s documentation requires psycopg 3.1.12 or psycopg2 2.9.9 and recommends psycopg 3; the binary implementation of the run comes with its own libpq, the client library, here 18.6.

Two more lines matter later. The cursor was a psycopg.ClientCursor: with psycopg 3, Django’s default cursors merge the values into the SQL on the client, and 'server_side_binding': True in OPTIONS sends them apart. The session ran at read committed, PostgreSQL’s default and Django’s, and in UTC, which Django sets when USE_TZ is on.

Most releases move the oldest supported PostgreSQL on, and their release notes give the reason: upstream support for that version is ending. Walk through the recent ones:

Timeline · 5 events in order The oldest PostgreSQL each Django release supports Walk through the releases in order. Each names the oldest PostgreSQL it supports and what it changed for PostgreSQL.

Django 5.1

PostgreSQL 13 and later, and a pool pool

Dropped PostgreSQL 12. With psycopg 3, a pool option in OPTIONS gives each database alias a psycopg ConnectionPool.
Django 5.2 LTS

PostgreSQL 14 and later

Dropped PostgreSQL 13. The long-term support release of the 5 series, the one that added CompositePrimaryKey.
Django 6.0

Still PostgreSQL 14 and later

No PostgreSQL release dropped. StringAgg moved from contrib.postgres to every database, and custom expressions return their params as a tuple.
Django 6.1

PostgreSQL 15 and later this lesson

Dropped PostgreSQL 14. Virtual GeneratedFields on PostgreSQL 18, the JSONNull expression, and BitAnd, BitOr and BitXor no longer only in contrib.postgres.
Django 6.2 LTS

PostgreSQL 16 and later

In development: its release notes drop PostgreSQL 15. It is the next long-term support release.

Before you upgrade Django

Check the server first: Django 6.1 refuses PostgreSQL 14, and the 6.2 release notes already drop 15. Upgrading PostgreSQL is the slower job, so it comes first.

Checkpoint

Check: PostgreSQL versions and psycopg

Question 1 of 2 connect.py printed minimum_database_version (15,). What does Django 6.1.2 do when the server is PostgreSQL 14?
Choose one answer.

It downgrades itself to the features of 5.2 It works until a query uses a feature PostgreSQL 14 lacks It raises NotSupportedError on the first connection of the alias It works, with a warning in the logs

Show the answer: Question 1 of 2

Answer: It raises NotSupportedError on the first connection of the alias

Django 6.1's release notes give the reason for the move: upstream support for PostgreSQL 14 ends, and 6.1 supports 15 and higher.
Question 2 of 2 With psycopg 3 and no OPTIONS, how did the values of a query reach PostgreSQL?
Choose one answer.

They were sent in a second network round trip They were sent apart from the SQL, always psycopg merged them into the SQL on the client, through the psycopg.ClientCursor that Django uses by default Django formatted them into the SQL string itself

Show the answer: Question 2 of 2

Answer: psycopg merged them into the SQL on the client, through the psycopg.ClientCursor that Django uses by default

Either way the values are parameters, never pieces of your SQL text; the option only moves where psycopg puts them together.

Twenty requests opened twenty connections, until a setting kept them

A request that opens a connection pays for it before its first query, and Django’s default, CONN_MAX_AGE = 0, closes the connection at the end of every request. This program sends 20 requests through Django’s WSGI handler, the way a server does, to the same database under five aliases with different settings:

20 requests and one restart, under four connection settings

nivas/request_connections.py

"""Requests through Django's WSGI handler, sent the way a server sends them: application(environ, start_response),
then close() on the response, which ends the request. Each alias of config/settings_connections.py is the same
PostgreSQL database with other connection settings; the view of config/urls_connections.py answers with the number of
the server process that ran its query, so the run can count how many connections served 20 requests.

Then a connection is ended from the server's side between two requests, as a database restart or a network fault
would, once without health checks and once with them. PostgreSQL runs on a throwaway server of its own
(pgserver.py). Process numbers change on every run, so the run prints only how many there were.
"""

import logging
import os
from wsgiref.util import setup_testing_defaults

import pgserver

pgserver.start()
os.environ['DJANGO_SETTINGS_MODULE'] = 'config.settings_connections'

import seed  # noqa: E402,F401  (starts Django, creates the tables, adds the rows)
from django.core.exceptions import ImproperlyConfigured  # noqa: E402
from django.core.wsgi import get_wsgi_application  # noqa: E402
from django.db import connections  # noqa: E402

application = get_wsgi_application()
logging.getLogger('django.request').setLevel(logging.CRITICAL)  # the failed request is reported below, not logged


def request(alias):
    environ = {'PATH_INFO': '/board/', 'QUERY_STRING': f'db={alias}'}
    setup_testing_defaults(environ)
    status = []
    response = application(environ, lambda line, headers: status.append(line))
    try:
        body = b''.join(response).decode()
    finally:
        response.close()  # what a server does when it has sent the response: the request ends here
    return status[0], body


def end_connection(pid):
    with connections['default'].cursor() as cursor:
        cursor.execute('SELECT pg_terminate_backend(%s)', [int(pid)])


print('20 requests in a row, one query each:')
connections['pooled'].pool.open(wait=True)  # filled to min_size first, so every run counts the same
for alias in ['closing', 'persistent', 'checked', 'pooled']:
    pids = [request(alias)[1] for _ in range(20)]
    served = len(set(pids))
    print(f'   {alias:<11} {served:>2} connection{"" if served == 1 else "s"} served them')

try:
    connections['pooled_and_persistent'].pool
except ImproperlyConfigured as error:
    print(f'pooled_and_persistent: ImproperlyConfigured: {error}')

print('A connection ended by the server between two requests:')
for alias in ['persistent', 'checked']:
    first = request(alias)
    end_connection(first[1])
    after = [request(alias) for _ in range(2)]
    print(f'   {alias:<11} next request {after[0][0]}, the one after it {after[1][0]}')

Output

20 requests in a row, one query each:
   closing     20 connections served them
   persistent   1 connection served them
   checked      1 connection served them
   pooled       2 connections served them
pooled_and_persistent: ImproperlyConfigured: Pooling doesn't support persistent connections.
A connection ended by the server between two requests:
   persistent  next request 500 Internal Server Error, the one after it 200 OK
   checked     next request 200 OK, the one after it 200 OK

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 request_connections.py

config/settings_connections.py · nivas/config/settings_connections.py

"""The settings of config/settings.py with five more aliases for the same PostgreSQL database, one way to manage
connections each. request_connections.py sends requests through all of them.
"""

from .settings import *  # noqa: F401,F403
from .settings import DATABASES

ROOT_URLCONF = 'config.urls_connections'

SAME_DATABASE = DATABASES['default']

DATABASES = {
    'default': SAME_DATABASE,
    # Django's default: a new connection for every request, closed at its end.
    'closing': {**SAME_DATABASE, 'CONN_MAX_AGE': 0},
    # Persistent: a connection kept for up to 60 seconds and reused by later requests of the same thread.
    'persistent': {**SAME_DATABASE, 'CONN_MAX_AGE': 60},
    # Persistent, and checked once in each request before it is reused.
    'checked': {**SAME_DATABASE, 'CONN_MAX_AGE': 60, 'CONN_HEALTH_CHECKS': True},
    # psycopg's connection pool (Django 5.1 and later): between 2 and 4 connections, shared by all threads.
    'pooled': {**SAME_DATABASE, 'OPTIONS': {'pool': {'min_size': 2, 'max_size': 4}}},
    # Not allowed: a pool with persistent connections.
    'pooled_and_persistent': {**SAME_DATABASE, 'CONN_MAX_AGE': 60, 'OPTIONS': {'pool': True}},
}

config/urls_connections.py · nivas/config/urls_connections.py

from django.db import connections
from django.http import HttpResponse
from django.urls import path


def board(request):
    """One query on the database alias the request names; the answer is the server process that ran it."""
    with connections[request.GET['db']].cursor() as cursor:
        cursor.execute('SELECT pg_backend_pid()')
        return HttpResponse(str(cursor.fetchone()[0]))


urlpatterns = [
    path('board/', board),
]

closing opened 20 connections for 20 requests. persistent, CONN_MAX_AGE = 60, kept one persistent connection: A database connection that Django keeps open after a request, for up to CONN_MAX_AGE seconds, so that later requests of the same thread reuse it instead of opening a new one. and served all 20 with it, and so did checked. pooled used psycopg’s connection pool: A set of open database connections that requests borrow and give back, so that a request does not pay for opening a connection. Django 5.1 and later offer psycopg's pool through the pool option, one pool per alias in each process., available since Django 5.1, with between 2 and 4 connections, and its 2 open connections served all 20. The last alias asked for a pool and a CONN_MAX_AGE together, and Django refused with ImproperlyConfigured; its documentation says to set the pool’s max_lifetime instead.

The second part ended the connection from the server’s side between two requests, as a restart or a failover does. Without health checks, the next request reused the dead connection and failed with a 500; Django closed it at the end of that request, because it was in an error state, and the request after it reconnected. With CONN_HEALTH_CHECKS = True, Django checked the connection before reusing it, found it dead, opened a new one, and the request succeeded. Django’s documentation says the check runs once per request, and only if the request uses the database. Answer three questions about your deployment, and the table marks the settings that fit:

Interactive · answer and compare Which connection setting fits your deployment? Answer three questions about your servers and your database. The table marks the settings that fit.

Answer the questions above: the table then marks what fits your case and says why the rest does not.

Four ways to manage database connections, against what 20 requests and one restart showed
Setting	Connections opened	After a database restart	Connections held open
Close after each request In DATABASES: CONN_MAX_AGE 0, the default	Poor 20 for 20 requests: each one paid for its own connection	Good nothing was open between requests, so nothing broke	Good none between requests
Persistent In DATABASES: CONN_MAX_AGE 60	Good 1 served all 20 requests	Poor the next request failed with a 500; the one after it reconnected	Mixed one per worker thread, open while it is idle
Persistent, health checks In DATABASES: CONN_MAX_AGE 60, CONN_HEALTH_CHECKS	Good 1 served all 20 requests	Good the check found the dead connection and opened a new one: 200	Mixed one per worker thread, open while it is idle
psycopg pool In DATABASES: OPTIONS pool, CONN_MAX_AGE 0	Good 2, the pool's min_size, served all 20 requests	Mixed checked when taken from the pool only with CONN_HEALTH_CHECKS or a check	Good between min_size and max_size, shared by every thread

How the answers decide

“Does opening a connection cost your requests noticeable time, for example to a database on another host?” Yes, it adds up: rules out Close after each request. The default opened 20 connections for 20 requests, so every request paid for a new one.
“Must requests keep working, without errors, when the database restarts or fails over?” Yes: rules out Persistent and adds a caveat to psycopg pool. Without health checks the first request after the restart failed; a pool checks connections only with CONN_HEALTH_CHECKS or a check callback.
“Would one open connection for every worker thread go past the connections the database allows?” Yes, too many threads: rules out Persistent and Persistent, health checks. Each thread keeps its own persistent connection, so the database must allow as many connections as there are worker threads.

Two more limits come from Django’s documentation: each thread keeps its own persistent connection, so the database must allow as many connections as there are worker threads, and under ASGI persistent connections should be disabled.

Key idea

Every request opens a connection unless a setting keeps it: CONN_MAX_AGE with CONN_HEALTH_CHECKS reuses one per thread and survives a restart, and the pool shares a few between all threads.

Checkpoint

Check: Persistent connections and pools

Question 1 of 2 With Django's default CONN_MAX_AGE = 0, how many connections did 20 requests in a row open?
Type a number.
Your answer connections

Show the answer: Question 1 of 2

Answer: 20 connections

The default closes the connection at the end of every request, so each of the 20 opened its own; CONN_MAX_AGE = 60 served the same 20 with 1.
Question 2 of 2 The server ended the persistent connection between two requests, with CONN_HEALTH_CHECKS off. What did the next two requests get?
Choose one answer.

Both waited until the database came back 200 and 200, because Django reconnects by itself 500 Internal Server Error, then 200 OK after Django had closed the broken connection 500 and 500, until the server process restarts

Show the answer: Question 2 of 2

Answer: 500 Internal Server Error, then 200 OK after Django had closed the broken connection

With CONN_HEALTH_CHECKS = True, Django checked the connection once, before the request's first query, found it dead, and opened a new one, so the request succeeded.

JSONField and ArrayField keep a document or a list in one column

Notices carry tags, such as water or repairs, and details whose shape differs from one notice to the next: the hours of a water cut, the vendor of a lift repair. Notice.tags is an ArrayField of short strings and Notice.details a JSONField, and each lookup becomes a PostgreSQL operator:

Lookups on tags and details, and the two kinds of null

nivas/json_and_arrays.py

"""Lookups on Notice.tags, an ArrayField, and Notice.details, a JSONField (notices/models.py), on the eight notices of
seed.py, with the SQL operator each one becomes. Then the two kinds of null a JSONField can hold, and the warning
Django 6.1 gives for the old way of asking for JSON null. PostgreSQL runs on a throwaway server of its own
(pgserver.py).
"""

import warnings

import pgserver

pgserver.start()

import seed  # noqa: E402,F401  (starts Django, creates the tables, adds the rows)
from django.db import connection  # noqa: E402
from django.db.models import JSONNull  # noqa: E402
from django.test.utils import CaptureQueriesContext  # noqa: E402

from notices.models import Notice  # noqa: E402
from seed import where  # noqa: E402


def ask(label, **lookup):
    with CaptureQueriesContext(connection) as sent:
        titles = [notice.title for notice in Notice.objects.filter(**lookup).order_by('pk')]
    sql = where(sent[0]['sql'])
    print(f'{label:<38} {len(titles)}: {" | ".join(titles)}')
    print(f'{"":<38} {sql}')


print('ArrayField: tags')
ask("tags__contains=['water']", tags__contains=['water'])
ask("tags__overlap=['lift', 'parking']", tags__overlap=['lift', 'parking'])
ask('tags__len=0', tags__len=0)
ask("tags__0='repairs'", tags__0='repairs')

print('JSONField: details')
ask("details__towers__contains=['C']", details__towers__contains=['C'])
ask("details__vendor__name='Shree Lifts'", details__vendor__name='Shree Lifts')
ask("details__has_key='tanker'", details__has_key='tanker')
ask("details__contains={'from': '10:00'}", details__contains={'from': '10:00'})

print('Two kinds of null')
ask('details__isnull=True (SQL NULL)', details__isnull=True)
ask('details=JSONNull() (JSON null)', details=JSONNull())
with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter('always')
    old = list(Notice.objects.filter(details=None).values_list('title', flat=True))
print(f'details=None still found {old}, with {caught[0].category.__name__}:')
print(f'   {caught[0].message}')

Output

ArrayField: tags
tags__contains=['water']               3: Water cut in tower B | Water leaking in the B-1203 ceiling | Paani ki tanki ki safai
                                       WHERE notice.tags @> (ARRAY['water'])::varchar(20)[]
tags__overlap=['lift', 'parking']      2: Lift maintenance in tower A | Parking stickers renewal
                                       WHERE notice.tags && (ARRAY['lift', 'parking'])::varchar(20)[]
tags__len=0                            1: Security guard rota
                                       WHERE CASE WHEN notice.tags IS NULL THEN NULL ELSE coalesce(array_length(notice.tags, 1), 0) END = 0
tags__0='repairs'                      1: Plumber visits on Thursday
                                       WHERE (notice.tags)[1] = 'repairs'
JSONField: details
details__towers__contains=['C']        2: Plumber visits on Thursday | Paani ki tanki ki safai
                                       WHERE (notice.details -> 'towers') @> '["C"]'::jsonb
details__vendor__name='Shree Lifts'    1: Lift maintenance in tower A
                                       WHERE (notice.details #> '{vendor,name}') = '"Shree Lifts"'::jsonb
details__has_key='tanker'              1: Water cut in tower B
                                       WHERE notice.details ? 'tanker'
details__contains={'from': '10:00'}    1: Water cut in tower B
                                       WHERE notice.details @> '{"from": "10:00"}'::jsonb
Two kinds of null
details__isnull=True (SQL NULL)        1: Security guard rota
                                       WHERE notice.details IS NULL
details=JSONNull() (JSON null)         1: Parking stickers renewal
                                       WHERE notice.details = 'null'::jsonb
details=None still found ['Parking stickers renewal'], with RemovedInDjango2028Warning:
   Using None as the right-hand side of an exact lookup on JSONField to mean JSON scalar 'null' is deprecated. Use JSONNull() instead (or use the __isnull lookup if you meant SQL NULL).

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 json_and_arrays.py

notices/models.py · nivas/notices/models.py

from django.contrib.postgres.fields import ArrayField
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVector, SearchVectorField
from django.db import models


class Notice(models.Model):
    title = models.CharField(max_length=120)
    body = models.TextField()
    tags = ArrayField(models.CharField(max_length=20), default=list, blank=True)  # PostgreSQL only
    details = models.JSONField(null=True, blank=True)  # jsonb on PostgreSQL
    posted_on = models.DateField()
    # The search document, kept up to date by PostgreSQL itself: the title weighs A, the body B.
    search = models.GeneratedField(
        expression=SearchVector('title', weight='A', config='english')
        + SearchVector('body', weight='B', config='english'),
        output_field=SearchVectorField(),
        db_persist=True,
    )

    class Meta:
        indexes = [
            GinIndex(fields=['tags'], name='notice_tags_gin'),
            GinIndex(fields=['search'], name='notice_search_gin'),
        ]

    def __str__(self):
        return self.title

nivas/seed.py

"""Starts Django for the examples of this lesson, creates every table with migrate, and adds the rows they use: eight
notices with their tags and details, and three bills. The examples start their PostgreSQL server (pgserver.py) before
they import this file.

where() prints only the WHERE clause of a query's SQL, shorter, so that it fits the page: without the double quotes
around names and without the notices_ prefix of the table names. The SQL that was sent is the same, with them.
"""

import os
import re
from datetime import date

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')

import django  # noqa: E402

django.setup()

from django.core.management import call_command  # noqa: E402
from django.db.models import JSONNull  # noqa: E402

from notices.models import Notice  # noqa: E402
from society.models import Bill  # noqa: E402

NOTICES = [  # title, body, tags, details
    ('Water cut in tower B', 'Supply stops from 10:00 to 14:00 while the tank valve is replaced. A tanker comes at noon.',
     ['water', 'tower-b'], {'from': '10:00', 'to': '14:00', 'towers': ['B'], 'tanker': True}),
    ('Lift maintenance in tower A', 'Lift 2 is serviced on Saturday morning; use lift 1 or the stairs.',
     ['lift', 'tower-a'], {'lift': 2, 'towers': ['A'], 'vendor': {'name': 'Shree Lifts', 'ticket': 4471}}),
    ('Water leaking in the B-1203 ceiling', 'The plumber opens the ceiling of B-1203 on Monday to find the leak.',
     ['water', 'repairs'], {'flat': 'B-1203', 'towers': ['B']}),
    ('Cleanliness drive on Sunday', 'Meet at gate 1 at 8:00. Gloves and bags are provided by the committee.',
     ['events'], {'meet_at': 'gate 1'}),
    ('Plumber visits on Thursday', 'Report dripping taps and leaks in bathrooms to the office before Wednesday.',
     ['repairs'], {'towers': ['A', 'B', 'C']}),
    ('Paani ki tanki ki safai', 'Kal subah 9 se 12 baje tak paani nahi aayega. Tanki ki safai hogi.',
     ['water'], {'from': '09:00', 'to': '12:00', 'towers': ['A', 'B', 'C']}),
    ('Security guard rota', 'The new rota starts on the first of the month.',
     [], None),
    ('Parking stickers renewal', 'Old stickers stop working at the end of the month.',
     ['parking'], JSONNull()),
]

call_command('migrate', verbosity=0)
if not Notice.objects.exists():
    Notice.objects.bulk_create([
        Notice(title=title, body=body, tags=tags, details=details, posted_on=date(2026, 9, 20 + n))
        for n, (title, body, tags, details) in enumerate(NOTICES)
    ])
    Bill.objects.bulk_create([
        Bill(flat='B-1203', month='2026-09', amount_paise=350000, paid_paise=350000),
        Bill(flat='B-1204', month='2026-09', amount_paise=350000, paid_paise=100000),
        Bill(flat='A-101', month='2026-09', amount_paise=280000),
    ])


def where(sql):
    """SELECT … FROM "notices_notice" WHERE ("notices_notice"."tags" @> …) ORDER BY … -> WHERE (notice.tags @> …)

    Only the quotes around names go; the text inside SQL string literals, such as '["C"]'::jsonb, stays as it is.
    """
    parts = re.split(r"('(?:[^']|'')*')", sql)
    sql = ''.join(part if part.startswith("'") else re.sub(r'"notices_(\w+)"', r'\1', part).replace('"', '')
                  for part in parts)
    sql = sql[sql.index(' WHERE ') + 1:] if ' WHERE ' in sql else sql
    return sql.split(' ORDER BY ')[0]

Lookup	SQL	Finds notices whose …
`tags__contains=['water']`	`tags @> ARRAY['water']`	tags include every value given
`tags__overlap=['lift', 'parking']`	`tags && ARRAY[…]`	tags include at least one value given
`tags__0='repairs'`	`(tags)[1] = 'repairs'`	first tag is `repairs`
`details__vendor__name='Shree Lifts'`	`details #> '{vendor,name}'`	value at a path is the one given
`details__has_key='tanker'`	`details ? 'tanker'`	top level has the key
`details__contains={'from': '10:00'}`	`details @> '{"from": "10:00"}'`	details hold these keys and values

tags__0 became [1] because PostgreSQL counts array elements from 1 in SQL while Django’s index transforms count from 0, like Python. ArrayField exists only in django.contrib.postgres; JSONField works on every backend, stored as jsonb on PostgreSQL, though Django’s documentation says contains is not supported on SQLite or Oracle. For these operators a GIN index: A PostgreSQL index for values made of many items, such as arrays, jsonb documents and tsvectors. It maps each item to the rows that hold it, so operators such as @>, && and @@ can use it. helps: the migration made notice_tags_gin with GinIndex(fields=['tags']), while the documentation notes that a plain B-tree index isn’t particularly helpful for a JSONField.

default=[] on an ArrayField

A list literal as the default is one list shared by every instance, which Django’s documentation warns about. Pass the callable, default=list, as Notice.tags does, so that each notice starts with a list of its own.

A JSONField with null=True can hold two different nulls. details=None saved SQL NULL, found with details__isnull=True; JSONNull() saved the JSON value null, found with details=JSONNull(). Asking for details=None still found the JSON null, and raised RemovedInDjango2028Warning.

New in Django 6.1

JSONNull() stands for the JSON value null, in saves and in queries. Using None for it in a query is deprecated, and at the end of the deprecation period None will mean SQL NULL there.

Checkpoint

Check: JSONField and ArrayField lookups

Question 1 of 2 Which lookup finds the notices tagged lift or parking, either of them?
Choose one answer.

tags=['lift', 'parking'] tags__overlap=['lift', 'parking'] tags__0__in=['lift', 'parking'] tags__contains=['lift', 'parking']

Show the answer: Question 1 of 2

Answer: tags__overlap=['lift', 'parking']

contains asks for all of the values, contained_by for nothing but these values, overlap for any of them.
Question 2 of 2 The guard rota's details are SQL NULL, the parking notice's details are JSON null. Which lookup finds only the parking notice, in Django 6.1 and after the deprecation period too?
Choose one answer.

details=None details__isnull=True details=JSONNull() details__has_key='null'

Show the answer: Question 2 of 2

Answer: details=JSONNull()

Saving None stores SQL NULL; saving JSONNull() stores JSON null. Asking with the same two keeps queries correct after the deprecation ends.

A search matches the words first and ranks the matches second

A resident types water leak into the notice board. full-text search: Searching text by its words rather than by its exact characters: PostgreSQL turns each document into a tsvector of normalised words and matches it against a tsquery, then can rank the matches. finds the notices that contain those words in any form, leaking included, and ranks a title match above a body match. In Django it is three expressions: SearchVector builds the document, SearchQuery the query, SearchRank the score:

Weighted search over eight notices, and what ranking alone keeps Python · nivas/search_notices.py

"""Full-text search over the eight notices of seed.py, with titles weighing A and bodies B, as Notice.search does
(notices/models.py). First how the weights rank two matches, then what happens to the rows that do not match when a
query of two words is ranked without a filter, then what the 'english' and 'simple' configurations make of two titles,
one of them in Hindi written in Latin letters. PostgreSQL runs on a throwaway server of its own (pgserver.py).
"""

import pgserver

pgserver.start()

import seed  # noqa: E402,F401  (starts Django, creates the tables, adds the rows)
from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector  # noqa: E402
from django.db import connection  # noqa: E402

from notices.models import Notice  # noqa: E402


def vector(config='english'):
    return SearchVector('title', weight='A', config=config) + SearchVector('body', weight='B', config=config)


def ranked(query):
    return Notice.objects.annotate(rank=SearchRank(vector(), query)).order_by('-rank', 'pk')


print("1. 'leaking', filtered on the match and ranked: a title match against a body match")
leaking = SearchQuery('leaking', config='english')
for notice in ranked(leaking).annotate(document=vector()).filter(document=leaking):
    print(f'   {notice.rank:<10.4g} {notice.title}')

print("2. 'water leak', ranked and kept where rank > 0, with no filter on the match")
water_leak = SearchQuery('water leak', config='english')
for notice in ranked(water_leak).filter(rank__gt=0):
    print(f'   {notice.rank:<10.4g} {notice.title}')
matches = Notice.objects.annotate(document=vector()).filter(document=water_leak)
print(f'   filtered on the match instead: {[notice.title for notice in matches]}')

print('3. The same words in two configurations')
with connection.cursor() as cursor:
    for text in ['Water leaking in the B-1203 ceiling', 'Paani ki tanki ki safai']:
        for config in ['english', 'simple']:
            cursor.execute('SELECT to_tsvector(%s::regconfig, %s)::text', [config, text])
            print(f'   {config:<8} {text!r}: {cursor.fetchone()[0]}')
    for config in ['english', 'simple']:
        cursor.execute('SELECT plainto_tsquery(%s::regconfig, %s)::text', [config, 'water leaking'])
        print(f"   {config:<8} the query 'water leaking': {cursor.fetchone()[0]}")

Output

1. 'leaking', filtered on the match and ranked: a title match against a body match
   0.6687     Water leaking in the B-1203 ceiling
   0.2432     Plumber visits on Thursday
2. 'water leak', ranked and kept where rank > 0, with no filter on the match
   0.9911     Water leaking in the B-1203 ceiling
   1e-20      Water cut in tower B
   1e-20      Lift maintenance in tower A
   1e-20      Cleanliness drive on Sunday
   1e-20      Plumber visits on Thursday
   1e-20      Paani ki tanki ki safai
   1e-20      Security guard rota
   1e-20      Parking stickers renewal
   filtered on the match instead: ['Water leaking in the B-1203 ceiling']
3. The same words in two configurations
   english  'Water leaking in the B-1203 ceiling': '-1203':6 'b':5 'ceil':7 'leak':2 'water':1
   simple   'Water leaking in the B-1203 ceiling': '-1203':6 'b':5 'ceiling':7 'in':3 'leaking':2 'the':4 'water':1
   english  'Paani ki tanki ki safai': 'ki':2,4 'paani':1 'safai':5 'tanki':3
   simple   'Paani ki tanki ki safai': 'ki':2,4 'paani':1 'safai':5 'tanki':3
   english  the query 'water leaking': 'water' & 'leak'
   simple   the query 'water leaking': 'water' & 'leaking'

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 search_notices.py

Step through the second search, water leak, from the notices to the one result:

Interactive · stage by stage From 'water leak' to one ranked notice Step through the search for water leak. Watch the match decide which notices come back, before the rank orders them.

The drawing shows the whole scene; its 6 steps are listed below.

Every step as text

The title. SearchVector('title', weight='A', config='english') turns the ceiling notice's title into lexemes, normalised words with their positions: leaking became leak, and in and the were dropped as stop words. Each lexeme carries weight A.
The body. The ceiling notice's body becomes lexemes the same way, with weight B, its positions counted on from the title's last one, 7: plumber at 9, leak at 21. The plumber's own notice has leak in its body too, but no water anywhere.
One document. The + of the two SearchVectors is the || of two tsvectors: one document per notice, with the positions of the body after those of the title.
The query. SearchQuery('water leak', config='english') is plainto_tsquery: each word normalised the same way, joined with &, so a notice must contain both.
The match. filter(document=query) is document @@ query. Only the ceiling notice holds both water and leak, so 1 of the 8 notices matches.
The rank. SearchRank is ts_rank, which weighs A at 1.0 and B at 0.4 by default. The match ranked 0.9911; ranked without the filter, the other 7 notices got 1e-20 each, which is above 0.

Each notice became a tsvector: PostgreSQL's type for a searchable document: its lexemes, the words in normalised form such as leak for leaking, each with its positions and, if one was set, a weight from A to D. of title words weighing A and body words weighing B, and filter(document=query) kept the notices that held every word of the query. In part 1, leaking matched the ceiling notice’s title, ranked 0.6687, and the plumber’s body, ranked 0.2432: by default SearchRank weighs A at 1.0 and B at 0.4. Part 2 is the trap. Ranked with no filter on the match and kept where rank > 0, water leak returned all 8 notices: the 7 that did not match each had a rank of 1e-20, which is above 0. Filtered on the match, the search returned the one notice that holds both words.

The configuration decides what counts as the same word. 'english' stemmed leaking to leak and ceiling to ceil, and dropped stop words such as in and the; 'simple' only turned words into lower case, so in it leaking does not find leak. The Hindi title, written in Latin letters, came out the same in both, but an English stemmer is made for English words: text in another language takes that language’s configuration where PostgreSQL has one, and 'simple' where none fits, as for Hindi written in Latin letters.

rank__gt=0 as the filter

A rank above zero is no proof of a match, as the 7 notices at 1e-20 showed. Filter with filter(document=query), or with the search column of the next section, then annotate the rank and order by it, and cut with rank__gte only among the matches.

Checkpoint

Check: Matching before ranking

Question 1 of 2 'water leak' ranked over the 8 notices with no filter on the match, kept where rank > 0. How many notices came back?
Type a number.
Your answer notices

Show the answer: Question 1 of 2

Answer: 8 notices

The 7 notices that did not match each had a rank of 1e-20, which is above 0. Filtered on the match, the search returned 1 notice.
Question 2 of 2 In the 'simple' configuration, does the query leaking find a notice that only says leak?
Choose one answer.

Only if the notice is in the title Yes, but with a lower rank Yes; every configuration stems English words No; simple only lowers the case, so leaking and leak are different words

Show the answer: Question 2 of 2

Answer: No; simple only lowers the case, so leaking and leak are different words

Choose the configuration by the language of the text: english for English, simple where no stemmer fits, such as Hindi written in Latin letters.

A generated column is stored, or since PostgreSQL 18 virtual

Computing the search document for every row of every search is wasted work: the title and body change rarely, and every search reads them. A GeneratedField makes PostgreSQL keep a column computed from the others. With db_persist=True the generated column: A column whose value the database computes from other columns of the same row: stored, written when the row is written, or virtual, computed whenever it is read. Django declares one with GeneratedField. is stored, written with each row; with db_persist=False it is virtual, computed whenever it is read, which Django 6.1 supports on PostgreSQL 18 and later:

A virtual due amount, a stored search document, and the plans of one search

nivas/generated_columns.py

"""GeneratedField in its two forms on PostgreSQL 18. Bill.due_paise (society/models.py) is virtual, computed when it is
read; Notice.search (notices/models.py) is stored, written with each row, and has a GIN index. The SQL their
migrations make, the virtual value after a save, an index on each, and the plans of one search on 3,008 notices.
PostgreSQL runs on a throwaway server of its own (pgserver.py); autovacuum is off there, so VACUUM runs only when the
example asks for it.
"""

import io
import re

import pgserver

pgserver.start()

import seed  # noqa: E402,F401  (starts Django, creates the tables, adds the rows)
from django.contrib.postgres.search import SearchQuery, SearchVector  # noqa: E402
from django.core.management import call_command  # noqa: E402
from django.db import DatabaseError, connection, transaction  # noqa: E402

from notices.models import Notice  # noqa: E402
from society.models import Bill  # noqa: E402


def column(app, name):
    """The definition of one column in the CREATE TABLE of an app's first migration."""
    out = io.StringIO()
    call_command('sqlmigrate', app, '0001', stdout=out)
    return re.search(rf'"{name}" [^,]*?GENERATED ALWAYS AS \(.*?\) (?:STORED|VIRTUAL)', out.getvalue())[0]


print('1. The columns the migrations create')
print('  ', column('society', 'due_paise'))
print('  ', column('notices', 'search'))

print('2. A virtual column after a save')
bill = Bill.objects.get(flat='B-1204')
print(f'   {bill}: amount {bill.amount_paise}, paid {bill.paid_paise}, due {bill.due_paise}')
bill.paid_paise += 150000
bill.save()
print(f'   after paid_paise += 150000 and save(): due {bill.due_paise} (read back by the save itself)')

print('3. An index on each')
try:
    with transaction.atomic(), connection.cursor() as cursor:
        cursor.execute('CREATE INDEX bill_due_idx ON society_bill (due_paise)')
except DatabaseError as error:
    print(f'   virtual due_paise: {type(error).__name__}: {str(error).strip()}')
with connection.cursor() as cursor:
    cursor.execute("SELECT indexdef FROM pg_indexes WHERE indexname = 'notice_search_gin'")
    print(f'   stored search, from the migration: {cursor.fetchone()[0]}')

print('4. One search on 3,008 notices: the stored column, before and after VACUUM, and the vector computed per row')
TOPICS = ['Lift service in tower {}', 'Parking sticker for tower {}', 'Gym timings for tower {}', 'Garden work in tower {}']
Notice.objects.bulk_create([
    Notice(title=TOPICS[n % 4].format('ABC'[n % 3]), body='Details are with the society office.', posted_on='2026-01-01')
    for n in range(3000)
])
leak = SearchQuery('leak', config='english')
stored = Notice.objects.filter(search=leak)
computed = Notice.objects.annotate(
    document=SearchVector('title', weight='A', config='english') + SearchVector('body', weight='B', config='english'),
).filter(document=leak)


def show(label, search):
    print(f'   {label}: {search.count()} notices')
    for line in search.explain().splitlines():
        print('     ', line)


with connection.cursor() as cursor:
    cursor.execute('ANALYZE notices_notice')
show('filter(search=leak), after ANALYZE', stored)
with connection.cursor() as cursor:
    cursor.execute('VACUUM ANALYZE notices_notice')  # also moves the GIN index's pending entries into the index
show('filter(search=leak), after VACUUM ANALYZE', stored)
show('annotate(document=SearchVector(…)).filter(document=leak)', computed)

Output

1. The columns the migrations create
   "due_paise" integer GENERATED ALWAYS AS (("amount_paise" - "paid_paise")) VIRTUAL
   "search" tsvector GENERATED ALWAYS AS ((setweight(to_tsvector('english'::regconfig, COALESCE("title", '')), 'A') || setweight(to_tsvector('english'::regconfig, COALESCE("body", '')), 'B'))) STORED
2. A virtual column after a save
   B-1204 2026-09: amount 350000, paid 100000, due 250000
   after paid_paise += 150000 and save(): due 100000 (read back by the save itself)
3. An index on each
   virtual due_paise: NotSupportedError: indexes on virtual generated columns are not supported
   stored search, from the migration: CREATE INDEX notice_search_gin ON public.notices_notice USING gin (search)
4. One search on 3,008 notices: the stored column, before and after VACUUM, and the vector computed per row
   filter(search=leak), after ANALYZE: 2 notices
      Seq Scan on notices_notice  (cost=0.00..119.60 rows=2 width=235)
        Filter: (search @@ '''leak'''::tsquery)
   filter(search=leak), after VACUUM ANALYZE: 2 notices
      Bitmap Heap Scan on notices_notice  (cost=8.55..15.64 rows=2 width=235)
        Recheck Cond: (search @@ '''leak'''::tsquery)
        ->  Bitmap Index Scan on notice_search_gin  (cost=0.00..8.55 rows=2 width=0)
              Index Cond: (search @@ '''leak'''::tsquery)
   annotate(document=SearchVector(…)).filter(document=leak): 2 notices
      Seq Scan on notices_notice  (cost=0.00..1653.77 rows=15 width=267)
        Filter: ((setweight(to_tsvector('english'::regconfig, (COALESCE(title, ''::character varying))::text), 'A'::"char") || setweight(to_tsvector('english'::regconfig, COALESCE(body, ''::text)), 'B'::"char")) @@ '''leak'''::tsquery)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 generated_columns.py

society/models.py · nivas/society/models.py

from django.db import models
from django.db.models import F


class Bill(models.Model):
    flat = models.CharField(max_length=10)  # 'B-1203'
    month = models.CharField(max_length=7)  # '2026-09'
    amount_paise = models.IntegerField()
    paid_paise = models.IntegerField(default=0)
    # Computed when read, never stored: a virtual column, which PostgreSQL has from version 18.
    due_paise = models.GeneratedField(
        expression=F('amount_paise') - F('paid_paise'),
        output_field=models.IntegerField(),
        db_persist=False,
    )

    def __str__(self):
        return f'{self.flat} {self.month}'

notices/models.py · nivas/notices/models.py

from django.contrib.postgres.fields import ArrayField
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVector, SearchVectorField
from django.db import models


class Notice(models.Model):
    title = models.CharField(max_length=120)
    body = models.TextField()
    tags = ArrayField(models.CharField(max_length=20), default=list, blank=True)  # PostgreSQL only
    details = models.JSONField(null=True, blank=True)  # jsonb on PostgreSQL
    posted_on = models.DateField()
    # The search document, kept up to date by PostgreSQL itself: the title weighs A, the body B.
    search = models.GeneratedField(
        expression=SearchVector('title', weight='A', config='english')
        + SearchVector('body', weight='B', config='english'),
        output_field=SearchVectorField(),
        db_persist=True,
    )

    class Meta:
        indexes = [
            GinIndex(fields=['tags'], name='notice_tags_gin'),
            GinIndex(fields=['search'], name='notice_search_gin'),
        ]

    def __str__(self):
        return self.title

Bill.due_paise became GENERATED ALWAYS AS (amount_paise - paid_paise) VIRTUAL, and Notice.search the whole weighted document, STORED. After paid_paise went up by 150000, save() read the new due amount, 100000, back by itself, as Django has done for generated fields on PostgreSQL since 6.0. An index on the virtual column failed with NotSupportedError, while the stored search column has its GIN index from the migration.

	Stored, `db_persist=True`	Virtual, `db_persist=False`
Computed	when the row is written	whenever it is read
Takes space	yes, like any column	no
Index on it	yes: `notice_search_gin`	no: `NotSupportedError`
PostgreSQL	every supported version	18 and later, with Django 6.1

The plans of part 4 show what the stored column buys. Searched through search, the 3,008 notices took a Bitmap Heap Scan fed by a Bitmap Index Scan on notice_search_gin, at a total cost of 15.64, while computing the document in the query cost 1653.77, for a scan that runs to_tsvector on every row. Right after the 3,000 new notices, though, PostgreSQL still scanned the table, at 119.60: GIN puts new entries in a pending list until a VACUUM or an autoanalyze moves them into the index, and PostgreSQL’s documentation says searches must scan that list as well. Autovacuum does this on its own; after a bulk load, run VACUUM ANALYZE.

The features of this lesson tie a project to PostgreSQL: ArrayField, the search expressions and GinIndex come from django.contrib.postgres, and a virtual GeneratedField needs PostgreSQL 18. Such a project runs its tests on PostgreSQL too.

Checkpoint

Check: Stored and virtual generated columns

Question 1 of 2 What did PostgreSQL 18 answer to CREATE INDEX on the virtual column due_paise?
Choose one answer.

It created the index It turned the column into a stored one NotSupportedError: indexes on virtual generated columns are not supported It indexed amount_paise instead

Show the answer: Question 1 of 2

Answer: NotSupportedError: indexes on virtual generated columns are not supported

Index a value you search by in a stored column; keep virtual columns for values that are cheap to compute and only shown.
Question 2 of 2 Right after 3,000 notices were added, the search used a Seq Scan although notice_search_gin exists. What made PostgreSQL use the index?
Choose one answer.

Filtering with rank__gt=0 VACUUM ANALYZE, which also moved the GIN index's pending entries into the index ANALYZE alone Recreating the index

Show the answer: Question 2 of 2

Answer: VACUUM ANALYZE, which also moved the GIN index's pending entries into the index

PostgreSQL's documentation says searches must scan GIN's pending list as well as the index. Autovacuum empties it in time; after a bulk load, run VACUUM ANALYZE.

Practise: count the connections and rank the matches

Connections and search, rebuilt in plain Python: first the rules by which one thread opens and reuses its connection, with a restart in between; then a search that matches every word before it ranks, reading words the way the 'simple' configuration does. The tests of both run here, beside the code:

Code lab · 2 steps

Count the connections and rank the matches

Replay how one thread opens and reuses its database connection, then search notices so that only matches are ranked.

Count the connections a thread opens for its requests To do
Search the notices, matching first and ranking second To do

Exercise · Medium · Python

Count the connections a thread opens for its requests

Replay what Django does with one thread's database connection, request after request, the way request_connections.py measured it. serve(times, conn_max_age, health_checks=False, ended=()) gets the second at which each request starts, CONN_MAX_AGE (a number of seconds, or None for no limit) and CONN_HEALTH_CHECKS, and returns (opened, failed): how many connections the thread opened and how many requests failed.

At the start of a request, a connection that has reached its maximum age (now - opened_at >= conn_max_age) is closed.
ended holds the numbers of the requests (0 for the first) before which the server ended the connection, as a restart would. With health checks, a request finds this out before it uses the connection, closes it and opens a new one. Without them, the request fails, and the broken connection is closed at its end.
A request with no open connection opens one, at its start time.
At the end of a request, a connection that has reached its maximum age is closed; with conn_max_age = 0 that is every time.

Starter code · connections.py

def serve(times, conn_max_age, health_checks=False, ended=()):
    """(connections opened, requests failed) for one thread serving requests that start at the given seconds."""
    opened = 0
    connected = False
    for now in times:
        if not connected:
            opened += 1
            connected = True
    return opened, 0

The sample tests · test_connections.py

from connections import serve

EVERY_SECOND = list(range(20))


def test_closing_after_every_request():
    """CONN_MAX_AGE = 0, Django's default: 20 requests, 20 connections, as the run counted"""
    assert serve(EVERY_SECOND, 0) == (20, 0)


def test_persistent_connection_is_reused():
    """CONN_MAX_AGE = 60: one connection for 20 requests in 20 seconds"""
    assert serve(EVERY_SECOND, 60) == (1, 0)


def test_no_limit():
    """None keeps the connection for good"""
    assert serve([0, 100, 1000, 10000], None) == (1, 0)


def test_maximum_age_reached():
    """requests every 30 s with CONN_MAX_AGE = 60: the connection is 60 s old at 60 s and at 120 s, so it is replaced"""
    assert serve([0, 30, 60, 90, 120, 150], 60) == (3, 0)


def test_ended_by_the_server_without_checks():
    """the next request fails, and the one after it opens a new connection"""
    assert serve(EVERY_SECOND[:4], 60, ended={1}) == (2, 1)


def test_ended_by_the_server_with_checks():
    """the health check notices before the query and opens a new connection, so nothing fails"""
    assert serve(EVERY_SECOND[:4], 60, health_checks=True, ended={1}) == (2, 0)


def test_ended_with_no_connection_open_costs_nothing():
    """with CONN_MAX_AGE = 0 nothing is open between requests, so a server restart breaks nothing"""
    assert serve(EVERY_SECOND[:4], 0, ended={2}) == (4, 0)


def test_no_requests():
    """no requests, no connections"""
    assert serve([], 60) == (0, 0)

A hint

Keep two variables for the connection: whether one is open (and whether it is broken) and the second it was opened. Then, for each request, apply the four rules in the order the prompt gives them.

The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Exercise · Medium · Python

Search the notices, matching first and ranking second

Rebuild the notice search of search_notices.py in plain Python, the way the 'simple' configuration reads words. search(notices, query, min_score=0.0) gets the notices as dictionaries, {'title': …, 'body': …}, and the words a resident typed. It returns the titles of the matching notices, best first.

A word is a run of letters or digits, compared in lower case: Leaking, is the word leaking.
A notice matches when every word of the query is in its title or its body, as plainto_tsquery joins words with &. A notice that does not match is never in the result, whatever its score.
The score adds 1.0 for each time a query word appears in the title and 0.4 for each time it appears in the body, the default weights of A and B. Only matches with a score of at least min_score are returned.
Higher scores come first; equal scores keep the order of notices. A query with no words finds nothing.

Starter code · search.py

def search(notices, query, min_score=0.0):
    """The titles of the notices that match every word of the query, best first."""
    words = query.lower().split()
    scored = []
    for notice in notices:
        score = sum(notice['title'].lower().count(word) + 0.4 * notice['body'].lower().count(word) for word in words)
        scored.append((score, notice['title']))
    return [title for score, title in sorted(scored, reverse=True) if score >= min_score]

The sample tests · test_search.py

from search import search

NOTICES = [
    {'title': 'Water cut in tower B', 'body': 'Supply stops from 10:00 to 14:00. A tanker comes at noon.'},
    {'title': 'Water leaking in the B-1203 ceiling', 'body': 'The plumber opens the ceiling to find the leak.'},
    {'title': 'Plumber visits on Thursday', 'body': 'Report dripping taps and leaking pipes to the office.'},
    {'title': 'Paani ki tanki ki safai', 'body': 'Kal subah paani nahi aayega.'},
]


def test_title_match_ranks_above_body_match():
    """'leaking' is in one title and one body: 1.0 against 0.4"""
    assert search(NOTICES, 'leaking') == ['Water leaking in the B-1203 ceiling', 'Plumber visits on Thursday']


def test_every_word_must_match():
    """'water leaking' joins its words with &, so the plumber's notice, without 'water', is out"""
    assert search(NOTICES, 'water leaking') == ['Water leaking in the B-1203 ceiling']


def test_non_matches_never_return_even_with_min_score_zero():
    """the trap of the lesson: a score above 0 is no proof of a match"""
    assert 'Water cut in tower B' not in search(NOTICES, 'water leaking', min_score=0.0)


def test_words_not_substrings():
    """'leak' is a word of its own; 'leaking' does not contain the word 'leak' in the simple configuration"""
    assert search(NOTICES, 'leak') == ['Water leaking in the B-1203 ceiling']


def test_case_and_punctuation_do_not_matter():
    """'Noon.' in the body is the word noon"""
    assert search(NOTICES, 'NOON') == ['Water cut in tower B']


def test_hindi_words_in_latin_letters():
    """'paani' is in the title once and in the body once: 1.4"""
    assert search(NOTICES, 'paani') == ['Paani ki tanki ki safai']


def test_min_score_keeps_only_strong_matches():
    """with min_score=1.0 the body-only match, 0.4, is dropped"""
    assert search(NOTICES, 'leaking', min_score=1.0) == ['Water leaking in the B-1203 ceiling']


def test_title_beats_body_for_any_word():
    """'plumber' scores 1.0 in the third notice's title and 0.4 in the second notice's body"""
    assert search(NOTICES, 'plumber') == ['Plumber visits on Thursday', 'Water leaking in the B-1203 ceiling']


def test_equal_scores_keep_their_order():
    """two titles with 'lift' once each both score 1.0 and come back in the order given"""
    notices = [{'title': 'Lift 1 serviced', 'body': ''}, {'title': 'Lift 2 stopped', 'body': ''}]
    assert search(notices, 'lift') == ['Lift 1 serviced', 'Lift 2 stopped']


def test_empty_query_finds_nothing():
    """no words, no matches"""
    assert search(NOTICES, '  ') == []

A hint

Split the title, the body and the query into lists of lower-case words with one regular expression. Decide whether a notice matches before you compute anything about its score.

Then a round of questions on versions, connections, documents, search and generated columns, ordered so that the idea you are least sure of comes first:

Interview questions

Warm-up (fresher): What does CONN_MAX_AGE control, and what is its default?

CONN_MAX_AGE is how many seconds Django keeps a database connection open. Its default, 0, closes the connection at the end of every request, so each request opens a new one. A positive number keeps the connection for that long, and later requests of the same thread reuse it; None keeps it with no limit. Django closes a connection that has reached its age at the start or the end of a request, and one in an error state at the end. With CONN_HEALTH_CHECKS a reused connection is checked once per request. Under ASGI persistent connections should be off, and with the pool option CONN_MAX_AGE must be 0.

This lesson leads into two design questions of the module: a site that runs out of database connections at its busiest hour, and a notice search that returns everything.

Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.

Choose a pass

Mid to senior

At the evening peak the society portal fails with PostgreSQL's too many clients error. It runs 4 servers, each with 3 worker processes of 8 threads, CONN_MAX_AGE = 600, against max_connections = 100. Explain the failure and redesign the connection settings.

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Mid to senior

The notice board's search annotates SearchRank and keeps rank > 0. Residents complain that a search for two words lists every notice, and that it gets slower each month. Explain both problems and redesign the search for English and Hindi notices.

Model answer, Pro. First step and follow-ups

Key takeaways

Django 6.1 accepts PostgreSQL 15 and later and checks the server on the first connection; with psycopg 3 its default cursors merge the values on the client.
With CONN_MAX_AGE = 0 20 requests opened 20 connections; a persistent connection served them with 1 and the pool with 2, and only a health check kept the request after a restart from failing.
ArrayField and JSONField lookups become PostgreSQL operators such as @>, && and ?, served by GIN indexes, and JSONNull() asks for JSON null where isnull asks for SQL NULL.
A search filters on the match before it ranks: ranked alone, water leak kept 7 notices that did not match, each at 1e-20.
A stored GeneratedField can carry a GIN index and made the search an index scan at a cost of 15.64 against 1653.77; a virtual one, new with PostgreSQL 18, takes no space and cannot be indexed.

Terms in this lesson

connection pool: A set of open database connections that requests borrow and give back, so that a request does not pay for opening a connection. Django 5.1 and later offer psycopg's pool through the pool option, one pool per alias in each process.
full-text search: Searching text by its words rather than by its exact characters: PostgreSQL turns each document into a tsvector of normalised words and matches it against a tsquery, then can rank the matches.
generated column: A column whose value the database computes from other columns of the same row: stored, written when the row is written, or virtual, computed whenever it is read. Django declares one with GeneratedField.
GIN index: A PostgreSQL index for values made of many items, such as arrays, jsonb documents and tsvectors. It maps each item to the rows that hold it, so operators such as @>, && and @@ can use it.
persistent connection: A database connection that Django keeps open after a request, for up to CONN_MAX_AGE seconds, so that later requests of the same thread reuse it instead of opening a new one.
psycopg: The PostgreSQL driver for Python that Django's postgresql backend uses to talk to the server. Django 6.1 needs psycopg 3.1.12 or later, or the older psycopg2 2.9.9 or later, and recommends psycopg 3.
tsvector: PostgreSQL's type for a searchable document: its lexemes, the words in normalised form such as leak for leaking, each with its positions and, if one was set, a weight from A to D.

References

Django documentation: Databases, PostgreSQL notes (Django Software Foundation)
Django documentation: Databases, persistent connections (Django Software Foundation)
Django documentation: PostgreSQL specific model fields (Django Software Foundation)
Django documentation: Making queries, querying JSONField (Django Software Foundation)
Django documentation: Full text search (Django Software Foundation)
Django documentation: Model field reference, GeneratedField (Django Software Foundation)
Django 6.1 release notes (Django Software Foundation)
Django 5.1 release notes (Django Software Foundation)
Django 6.2 release notes (in development) (Django Software Foundation)
Psycopg 3 documentation (The Psycopg Team)
PostgreSQL 18 documentation: Controlling Text Search (The PostgreSQL Global Development Group)
PostgreSQL 18 documentation: GIN Indexes (The PostgreSQL Global Development Group)
PostgreSQL 18 release notes (The PostgreSQL Global Development Group)

Report a problem with this lesson

In this module: The ORM in depth: performance, integrity and PostgreSQL

Aggregation, annotations and expressions Free lesson, Pro answers: 25 minutes
N+1 queries, prefetching and 6.1 fetch modes Free lesson, Pro answers: 20 minutes
Transactions, locking and race conditions Free lesson, Pro answers: 25 minutes
Constraints, indexes and composite primary keys Free lesson, Pro answers: 20 minutes
Custom managers, raw SQL and EXPLAIN Free lesson, Pro answers: 25 minutes
PostgreSQL features in Django Free lesson, Pro answers: 20 minutes
Data migrations and zero-downtime schema changes Free lesson, Pro answers: 20 minutes

Your country

PostgreSQL features in Django

What you will learn

Before you start

What you will learn

Before you start

Django 6.1 needs PostgreSQL 15 or later, reached through psycopg

Twenty requests opened twenty connections, until a setting kept them

JSONField and ArrayField keep a document or a list in one column

A search matches the words first and ranks the matches second

A generated column is stored, or since PostgreSQL 18 virtual

Practise: count the connections and rank the matches

Count the connections a thread opens for its requests

Results of the sample tests

Search the notices, matching first and ranking second

Results of the sample tests

Interview questions

At the evening peak the society portal fails with PostgreSQL's too many clients error. It runs 4 servers, each with 3 worker processes of 8 threads, CONN_MAX_AGE = 600, against max_connections = 100. Explain the failure and redesign the connection settings.

The notice board's search annotates SearchRank and keeps rank > 0. Residents complain that a search for two words lists every notice, and that it gets slower each month. Explain both problems and redesign the search for English and Hindi notices.

Key takeaways

References

Related tools