I have a Django web application that is backed by PostgreSQL 11 and I want to upgrade to PostgreSQL 14. But, when running PostgreSQL 14, the test suite runs more than 2x slower.
I have compared both DB logs for the test run, and the queries appear to be the same, so Django is not constructing different queries for the different DB versions. As such, I think the DBA community is the best place for this question, instead of StackOverflow.
Disabling JIT in PG14 did not resolve the issue. (JIT is off by default in PG11 and on by default in PG14.) Also, I have not been able to isolate the issue to any particular test. When run individually, the setup phase of the tests (fixture provisioning, etc) takes a considerable amount of time, then the tests themselves run quickly. I've tried the normal Python debugging tools like cProfile, etc, but these didn't turn up the issue, either.
I have created a test script that makes 10,000 trivial DB queries (essentially SELECT now();) and this runs about 15% slower on PG 14. This isn't the same performance degradation as the tests, but at least it is a small and reproducible. Also, JIT would not be a factor for small queries like this.
The tests run using pytest in a Docker Compose environment. The PG11 and PG14 containers are postgis/postgis:11-2.5 and postgis/postgis:14-3.1, respectively (cf. postgis/postgis on Docker Hub).
Why does my application perform so much worse with PostgeSQL 14?
SELECT now();is slower.SELECT now();as any regressions you see with that are less likely to be relevant to regressions you see in your application's queries, which are a more typical occurrence between database version upgrades. You would need to zoom in on a specific query that is repeatedly twice as slow in your new instance as it is in your old instance and grab theEXPLAIN ANALYZEplans of both for comparison. Providing that information is what's required on here to be able to help you.SELECT now();is slower, I would seek the blame in a higher network latency or some similar external cause.