PostgreSQL Index Advisor for Django

A workload-driven PostgreSQL Index Advisor for Django.

PostgreSQL Index Advisor for Django learns from PostgreSQL's pg_stat_statements data. It identifies repeated query patterns, compares them with existing indexes, and produces recommendations that a developer can understand before changing the database.

The project is distributed on PyPI as django-index-optimizer and installed as the Django app optimizer. Those technical names remain stable for existing users.

Version 0.2.0 is preview-only: it produces evidence and safely quoted SQL for review but never changes the database.

How it works

pg_stat_statements
        │
        ▼
 recurring query patterns
        │
        ▼
 existing-index comparison
        │
        ▼
 explained recommendations
        │
        ▼
 review first ──► migration or database change process

The 0.2.0 workflow:

1. collects slow or frequently executed PostgreSQL statements;
2. parses PostgreSQL filter predicates into schema, table, and column evidence;
3. ignores recommendations already covered by an index prefix;
4. explains the evidence behind each candidate;
5. generates reviewable, safely quoted SQL;
6. never changes the database.

Correctness and database safety take priority over generating a large number of suggestions.

Installation

python -m pip install django-index-optimizer

Add the app:

INSTALLED_APPS = [
    # ...
    "optimizer",
]

Enable pg_stat_statements in PostgreSQL:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Preview recommendations:

python manage.py optimize_indexes

python manage.py optimize_indexes --limit 100 --min-calls 10

For setup and troubleshooting, see the pg_stat_statements guide.

Each recommendation includes the affected table and columns, workload calls, total execution time, the reason it was selected, and safely quoted CREATE INDEX CONCURRENTLY SQL. The SQL is printed for review and is never executed.

For CI, scripts, or a review artifact:

python manage.py optimize_indexes --limit 100 --min-calls 10 --format json \
  > index-recommendations.json

To make recommendations fail an opt-in CI policy check while preserving the complete report:

python manage.py optimize_indexes --format json --fail-on-recommendations \
  > index-recommendations.json

The command exits successfully when the report is empty and unsuccessfully when it contains one or more candidates. Recommendations remain review artifacts: this option does not mean every suggestion should become a migration or be applied automatically.

Normalized SQL text from pg_stat_statements is parsed in memory but is not included in reports. Query IDs are included so a recommendation can be traced back without copying potentially sensitive literals into an artifact.

See Understanding recommendations for the evidence model, existing-index rules, JSON fields, and the checks to perform before using a SQL preview.

Current conservative limits

• Only filter predicates that can be mapped unambiguously to one table are considered.
• Recommendations are currently single-column B-tree candidates.
• PostgreSQL system catalogs and the advisor's own queries are ignored.
• Planner validation, write-overhead scoring, joins, ordering, partial indexes, and multi-column candidates are planned rather than guessed prematurely.

Supported combinations follow Django: Django 5.2 supports PostgreSQL 14+, and Django 6.1 supports PostgreSQL 15+. Python 3.10–3.14 is supported where the selected Django release supports it. PostgreSQL 14–17 are covered by the project's integration-test matrix.

Download statistics

PyPI download counts include automated environments such as CI and are not a count of unique users. View the current breakdown on PyPI Stats.

Contributing

New contributors are welcome, including developers who are still learning Django or PostgreSQL internals.

1. Choose an unassigned good first issue.
2. Comment with the approach you want to take and wait for confirmation.
3. Follow the environment, test, and pull-request steps in CONTRIBUTING.md.

Most beginner issues use unit tests and do not require a local PostgreSQL server unless the issue explicitly says otherwise.

Security

Report database-safety or SQL-injection concerns privately through GitHub security advisories. Never include credentials or sensitive production queries in a public issue.

License

BSD 2-Clause. See LICENSE.