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,
integerprimary 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 VALIDand never validated, jsoncolumns that should bejsonb, 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:
-
--pghealth-url,urlin[tool.pghealth], or$PGHEALTH_URL; -
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()
-
the session-scoped fixture named by
fixture(if a test used it); -
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)
| 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)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| 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 logRelease 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 logSecurity 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.
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 logA 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.