Django unit tests with postgres can take an exceeding long time

[Rich Rauenzahn]

I've had this happen with two different projects now and both I was able to resolve with the same "hack".

I don't completely understand the actual mechanism for the degenerate case (I do have some explain plans I can share, but I'm not sure is relevant given my solution).

This was reproducible in my environment repeatedly: some queries in postgres during unit tests hit a degenerate case (with relatively small amounts of data!) where they take 100 of times longer than normal (production seems fine.) I'm talking about 2.5 minute queries that takes seconds normally.

postgresql11-server-11.12-1PGDG.rhel7.x86_64

The solution in both projects is the following mixin I add to my test case classes:

def _vacuum():
    # Some unit test queries seem to take a much longer time.
    # Let's try vacuuming.
    # https://stackoverflow.com/a/13955271/2077386
    with connection.cursor() as cursor:
        logging.info("Vacuum: begin")
        cursor.execute("VACUUM ANALYZE")
        logging.info("Vacuum: complete")

class VacuumMixin:
    @classmethod
    def setUpClass(cls):
        _vacuum()
        return super().setUpClass()

    @classmethod
    def tearDownClass(cls):
        ret = super().tearDownClass()
        _vacuum()
        return ret

I realize this is more of a postgres problem than a Django problem, but I wanted to raise awareness of it in case people have similar problem. I'm not sure its practical to run vacuum in the underlying Django code given that there may be more than one database, etc.?

[Mariusz Felisiak]

I don't think it's a bug in Django. Please reopen the ticket if you can debug your issue and provide details about why and where Django is at fault. Also, you should be able to enable ​Automatic Vacuuming on your database.

[Rich Rauenzahn]

Autovacuum is the default in postgres already -- suggesting to turn it on is not a helpful suggestion. I don't feel like you're really understanding the issue as I've reported it. Let me know if there is anything I can do to further clarify.

This is more of an integration issue between the two than a bug in Django. Transactional unit tests apparently can cause degenerate query cases in postgres (at least 11) even though the db is a freshly created db every test run.

Regardless, I think the only action is to give a heads up to the relevant django devs in case they see something else like it in the future. It has taken me days to troubleshoot and I'd like to prevent anyone else having to go through this debug process.

[Mariusz Felisiak]

Autovacuum is the default in postgres already -- suggesting to turn it on is not a helpful suggestion.

I don't know your database configurations and assumed that if VACUUM helps in your case then maybe it's turn off.

I don't feel like you're really understanding the issue as I've reported it. Let me know if there is anything I can do to further clarify.

You've not explained the issue in enough detail to confirm a bug in Django. Please reopen the ticket if you can debug your issue and provide details about why and where Django is at fault.