Skip to main content

pytest-pghealth

PostgreSQL schema health checks in your test suite. Your tests already build the real schema from your migrations; pytest-pghealth reads the catalog of that test database and fails the run when it finds a structural problem:

  • a foreign key with no index behind it,
  • duplicate, overlapping and invalid indexes,
  • tables without a primary key,
  • integer primary keys and sequences that will run out,
  • foreign keys whose column type differs from the column they reference,
  • multi-column unique constraints that NULLs slip through,
  • constraints added NOT VALID and never validated,
  • json columns that should be jsonb, and unlogged tables.

Every finding names the table and the object, says why it matters, and gives the SQL that fixes it. No extension to install, no production statistics, no running database beyond the one your tests use. Tables that belong to Django, Alembic and other packages are skipped. The same checks run against staging or a production snapshot with the read-only pghealth command.

=================================== pghealth ===================================
PostgreSQL 18.1, all user schemas: 13 checks, 3 problems (fixture engine)

foreign_key_without_index: public.order_items.order_items_order_id_fkey
  Foreign key order_items_order_id_fkey (order_id) has no index: every delete or key update in orders scans public.order_items, and joins on these columns cannot use an index.
  fix: CREATE INDEX CONCURRENTLY ON public.order_items (order_id);

overlapping_index: public.orders.ix_orders_customer_id
  Index ix_orders_customer_id is a leading prefix of ix_orders_customer_id_created_at, which serves the same lookups; the shorter index only adds write cost.
  fix: DROP INDEX CONCURRENTLY public.ix_orders_customer_id;

int4_primary_key: public.events.id
  Primary key public.events.id is integer fed by a sequence: inserts start failing after 2,147,483,647 rows, and widening it later rewrites the table.
  fix: ALTER TABLE public.events ALTER COLUMN id TYPE bigint;
       -- and every foreign key column that references it

To accept a finding, add an ignore with a reason to pyproject.toml:
  [[tool.pghealth.ignore]]
  check = "foreign_key_without_index"
  object = "public.order_items.order_items_order_id_fkey"
  reason = "why this is fine here"
========================= 42 passed in 3.17s =========================

Why in the tests

These problems are invisible in code review. An unindexed foreign key is one missing line in a migration; it shows up months later as a DELETE on the parent table that scans a child table of ten million rows while holding locks. An integer key fails on row 2,147,483,648, when widening it means rewriting the table under load. A failed CREATE INDEX CONCURRENTLY leaves an invalid index that every write still maintains.

All of them are visible in the catalog as soon as the migrations have run, and the test database is the cheapest place where that happens on every pull request.

Install

uv add --dev pytest-pghealth   # or: pip install pytest-pghealth

pytest-pghealth needs pytest 8+ and Python 3.11+. It uses whatever PostgreSQL driver you already have (psycopg, psycopg2 or pg8000, directly or through SQLAlchemy); it does not install one. PostgreSQL 14 to 18 are tested.

Use it

A test that checks the schema

The pghealth fixture takes whatever your suite already has: a SQLAlchemy engine, connection or session (async engines too), a Django connection, a DB-API connection or a URL.

def test_schema_is_healthy(pghealth, engine):
    pghealth.assert_healthy(engine)

assert_healthy fails the test with the report above; check returns the Report without failing. Both take overrides: pghealth.assert_healthy(engine, schemas=["billing"], disable=["int4_primary_key"]).

A check at the end of the session

Turn on the session check and tell it where the database is:

# pyproject.toml
[tool.pghealth]
session = true
fixture = "engine"      # a session-scoped fixture that returns an engine, a connection or a URL

or pass --pghealth on the command line (and --no-pghealth to turn it off). The check runs after the last test and before the session fixtures are torn down, so a schema created by a fixture and dropped at teardown is still there. A finding fails the run (exit code 1) unless --pghealth-warn or warn_only = true.

The session check finds the database, in this order:

  1. --pghealth-url, url in [tool.pghealth], or $PGHEALTH_URL;

  2. targets registered from a fixture:

    @pytest.fixture(scope="session")
    def engine(pghealth_session):
        engine = create_engine(TEST_DATABASE_URL)
        run_migrations(engine)
        pghealth_session.register(engine)
        yield engine
        engine.dispose()
    
  3. the session-scoped fixture named by fixture (if a test used it);

  4. with pytest-django, the Django test database (django_alias, default "default"), if a test used it.

Under pytest-xdist a URL is checked once, by the controller; otherwise each worker checks its own database and the controller reports the findings once.

A run that checks nothing fails when --pghealth was typed on the command line, so a CI job that asks for the check cannot pass without it; enabled in pyproject.toml (or addopts), a run of a few tests that never touch the database just says so.

pytest --pghealth-list-checks lists the checks and which of them run with your configuration, and every active ignore with the file it comes from.

From any code

from pytest_pghealth import run_checks

report = run_checks("postgresql://localhost/app_test")
print(report.format())

Against staging or a production snapshot

Invalid indexes, sequences near their limit and tables left UNLOGGED show up in databases that have run for years, not in a fresh test database. The pghealth command runs the checks against any database, outside pytest:

pghealth "postgresql://pghealth@staging-db/app"                 # text report
pghealth "$DSN" --format github --json pghealth.json            # GitHub annotations
pghealth "$DSN" --format json                                   # the JSON report

It runs in one read-only transaction and refuses to run if PostgreSQL does not confirm it is read-only; every check is a catalog SELECT with a statement and lock timeout, and fixes are printed, never run. It reads the ignores in ./pyproject.toml. Exit status 0 healthy, 1 findings, 2 an error. Details, the privileges it needs, and a GitHub Actions example: docs/cli.md.

The checks

Check Finds Fix
foreign_key_without_index foreign keys whose columns are not the leading columns of a valid, non-partial index CREATE INDEX CONCURRENTLY
duplicate_index indexes identical to another one (columns, operator classes, collations, order, expressions, predicate) drop the copy (or its constraint)
overlapping_index B-tree indexes that are a leading prefix of another index with the same predicate; unique and constraint indexes are never reported DROP INDEX CONCURRENTLY
invalid_index invalid indexes, usually left by a failed CREATE INDEX CONCURRENTLY REINDEX INDEX CONCURRENTLY
table_without_primary_key tables (and partitioned tables) without a primary key ADD PRIMARY KEY USING INDEX when a unique NOT NULL index exists, else an identity column
int4_primary_key integer/smallint primary keys fed by a sequence or identity ALTER COLUMN ... TYPE bigint
sequence_near_overflow sequences with 20% or less of their range left (useful on a restored production snapshot) widen the column and sequence
foreign_key_type_mismatch foreign key columns whose type or length differs from the referenced column ALTER COLUMN ... TYPE
nullable_unique_column multi-column unique indexes and constraints with a nullable column (not NULLS NOT DISTINCT) SET NOT NULL or NULLS NOT DISTINCT
not_valid_constraint CHECK and foreign key constraints added NOT VALID and never validated VALIDATE CONSTRAINT
duplicate_foreign_key foreign keys identical to another one DROP CONSTRAINT
json_column json columns, which cannot be compared, made unique or GIN-indexed ALTER COLUMN ... TYPE jsonb
unlogged_table unlogged tables: emptied by a crash, never on a standby SET LOGGED
timestamp_without_time_zone (off by default) timestamp without time zone and timetz columns ALTER COLUMN ... TYPE timestamptz
btree_index_on_array (off by default) B-tree indexes on array columns, which cannot serve @>, <@ or && a GIN index

docs/checks.md explains each check, what it skips and why. Objects that belong to extensions and PostgreSQL's own schemas are never reported.

Tables that frameworks and packages create (django.contrib, django-taggit, Celery, Wagtail, Alembic's alembic_version, ...) are skipped by default, since their migrations are not yours to change; invalid_index and sequence_near_overflow still report them. The list and how to turn it off: framework-owned tables.

Django note: Django indexes foreign keys by itself, so foreign_key_without_index mostly catches db_index=False and raw SQL migrations there. What Django schemas do have is the reverse: a per-ForeignKey index next to a unique constraint that starts with the same column, which overlapping_index reports and names the foreign key (db_index=False removes it).

On real projects

The checks ran against the migrated schemas of four open-source projects; every finding was read and classified (details):

Project Findings True Debatable False positive Skipped (framework-owned)
full-stack-fastapi-template (SQLModel) 1 1 0 0 0
Prefect 3.9.3 (SQLAlchemy) 31 27 4 0 0
NetBox (Django) 208 164 44 0 9
Wagtail 8.1 (Django) 0 0 0 0 69

The FastAPI template's item.owner_id has no index, so deleting a user scans every item; Prefect has nine unindexed foreign keys, a duplicate index, twelve redundant ones and five json columns. A new Wagtail site has nothing of its own to report: all 69 findings of 0.2.0 were on Wagtail's and Django's own tables. "Debatable" findings are right about the catalog but may be intended: NULL meaning "not applicable" in a unique constraint, integer keys on small lookup tables.

Configuration

[tool.pghealth]
session = true                      # the end-of-session check (or --pghealth)
url = "postgresql://localhost/test" # or fixture = "engine", or $PGHEALTH_URL
schemas = ["public", "billing"]     # default: every non-system schema
disable = ["int4_primary_key"]      # or --pghealth-disable
enable = ["btree_index_on_array"]   # or --pghealth-enable; "all" for every check
sequence_remaining_percent = 10     # default 20
ignore_framework_tables = true      # default; false, or a list of packages
warn_only = false                   # report without failing (or --pghealth-warn)
json = "pghealth.json"              # JSON report (or --pghealth-json)

[[tool.pghealth.ignore]]
check = "foreign_key_without_index" # a pattern; omit for every check
object = "public.audit_log"         # schema.table or schema.table.object, a pattern
reason = "append-only; rows in the parent table are never deleted"

An ignore needs a reason, so the exception stays documented next to the code. Unknown keys and check names are an error, not silently ignored. All options: docs/configuration.md.

The JSON report (--pghealth-json) lists the findings with their fix SQL, the ignored findings with their reasons and any check that failed to run; its format key is versioned.

Compared with

pytest-pghealth pg-index-health pglinter squawk
Language / runs in Python, pytest Java, JUnit / Spring Boot PostgreSQL extension (Rust) CLI (Rust)
Looks at the migrated test database, or any database with the pghealth command a live database a live database migration SQL files
Needs installed in the database nothing nothing the extension no database
Works with the stock postgres image in CI yes yes no n/a
Output findings with fix SQL: text, JSON, GitHub annotations Java objects for assertions; a generator of foreign key index migrations SARIF rule violations with advice
Production statistics (bloat, unused indexes) no, by design yes n/a no

pg-index-health is the mature tool for this on the JVM, and twelve of the queries here are ported from its SQL (see Credits). squawk lints the migration statements (locks, unsafe ALTERs) and complements this: squawk asks "is this migration safe to run?", pytest-pghealth asks "is the schema it produces sound?". Production-only checks (bloat, unused indexes) are left out on purpose: on a test database their numbers mean nothing.

Pairs with pytest-queryguard

pytest-queryguard watches the queries your tests send: N+1 lazy loads, repeated statements, query budgets, and (opt-in) filters no index can serve. pytest-pghealth watches the schema those queries run against. Neither sees what the other does: an unindexed foreign key costs nothing in a test that never deletes a parent, and an N+1 loop looks fine to the catalog.

Both run in the same pytest session, against the same test database, with no code changes beyond configuration:

[tool.pytest.ini_options]
addopts = "--pghealth"          # schema checked once, at the end of the run

[tool.pghealth]
fixture = "engine"              # the session-scoped engine your tests use

pytest-queryguard needs no configuration for its default checks; its missing-index check and pytest-pghealth's foreign_key_without_index overlap only in spirit: one looks at the queries that ran, the other at foreign keys whatever the tests ran.

Credits

The SQL of foreign_key_without_index, duplicate_index, invalid_index, table_without_primary_key, sequence_near_overflow, foreign_key_type_mismatch, not_valid_constraint, duplicate_foreign_key, json_column, unlogged_table, timestamp_without_time_zone and btree_index_on_array is ported from pg-index-health-sql by Ivan Vakhrushev and others, under the Apache License 2.0. Each ported file keeps its copyright line and says what was changed; see NOTICE and LICENSES/Apache-2.0.txt. The rest of pytest-pghealth is under the MIT License.

Metadata

Release files for pytest-pghealth 0.3.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pytest-pghealth 0.3.0
File Size Uploaded Provenance Actions
pytest_pghealth-0.3.0.tar.gz 77.2 kB With provenance Details for pytest_pghealth-0.3.0.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for pytest-pghealth 0.3.0
File Interpreter ABI Platform Actions
pytest_pghealth-0.3.0-py3-none-any.whl Python 3 none any Details for pytest_pghealth-0.3.0-py3-none-any.whl

Total release size: 131.9 kB

Release files / pytest_pghealth-0.3.0.tar.gz

Download URL pytest_pghealth-0.3.0.tar.gz
Size 77.2 kB
Tags Source
SHA-256 checksum
How to use checksums
8e45336e6f03005b4cf841b468cfe3b1dbbb33bb8b7e6a4648d6763791565ae8
BLAKE2b-256 checksum
How to use checksums
888cf2e61a13f17a71757790092c0f3b4737bf3aca1f628da4efdc5c3007964b
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 15, 2026.

Transparency log

Release files / pytest_pghealth-0.3.0-py3-none-any.whl

Download URL pytest_pghealth-0.3.0-py3-none-any.whl
Size 54.7 kB
Tags Python 3
SHA-256 checksum
How to use checksums
0956d69162cfb3c7fe3bb158a2f8a6326dedb7fbd6abfef131e0634e411153b1
BLAKE2b-256 checksum
How to use checksums
b31321138a023664653ff253f899af73e7cbb9df560bd06039e42cf82995d28c
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 15, 2026.

Transparency log

Security for pytest-pghealth 0.3.0

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for pytest_pghealth-0.3.0.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 15, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Release history Release notifications | RSS feed

This release

0.3.0 This release

2 release files Full provenance

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page