How I reduced the total execution time of backend integration tests by 15x

Get ready to improve your team productivity with the tips presented on this article. In this article I’ll talk about how I reduced the total execution time of all integration tests of a big backend service in a pull-request with less than 200 lines added/removed. The backend is implemented with Python and FastAPI and the database we use is Postgres. Currently we have 1841 tests implemented, divided into integration and unit tests.

For total comprehension of all optimizations discussed here you will need to have previous knowledge on parallel computing, processes, concurrency, and postgres templates. The concepts discussed here are language agnostic, which could be ported to any backend service that is facing similar problems.

Parallel execution example of a given function do_payroll() by 4 processors

1 — Previous scenario:

On our CI/CD pipeline, we run the tests in a GitHub Actions runner with only 2 cores. Previously, the tests took on average 46 minutes to execute in and in average 50 minutes to execute all the job steps.

For the integration tests, we created a postgres docker container with one database and accessed it during the integration tests. For each of the integration tests we executed all of these steps:

1.1 — Before each test:
1.1.1 — Loop through tables and collect their foreign keys to a list. Also collect each table schema.
1.1.2 — Loop through collected foreign keys and drop each.
1.1.3 — Loop through collected tables and drop each.

1.2 — Run the test

1.3 — After each test:
1.3.1 — Drop all tables stored in the metadata
1.3.2 — Create all tables stored in the metadata (recreates the db schema)

Besides that, the postgres container was run without any additional commands that could improve speed.

Previous GitHub actions total job execution time

Previous GitHub actions tests execution time

2 — Optimized scenario:

For the optimized scenario I used python-xdist to enable running tests in parallel using the maximum amount of CPU cores our machine has. In theory, each parallel execution can be run by a thread or a process, but in our case, as we used python-xdist package, we only had the "process" option.

Before entering the steps of the optimized scenario, it's important to understand that, as the tests will be run in parallel now, and because we instantiate X processes to run our tests, there will be X instances of our code running in parallel. This means introducing concurrency issues when trying to access the same resource.

—You may wonder, "What’s the resource shared by all processes in this scenario?"
— The database!

That's why I opted for creating a database for each process, this way each process has their own resource and the concurrency issue is solved.

Here are the steps I did to optimize the integration tests:

2.1 — Spawn your postgres docker container with the commands bellow. You can check how it looks within a full docker-compose-yml file example. These commands will speed up your tests a little bit. In our case they were responsible for almost 2x of speedup.

command: "postgres -c 'shared_buffers=128MB' -c 'fsync=off' -c 'synchronous_commit=off' -c 'full_page_writes=off' -c 'max_connections=100' -c 'client_min_messages=warning'"

2.2 — Run a test setup before all tests, meaning that this should run only once at the start of execution of each process, to create a template database related to that process.

This template database will be used while dropping and recreating the "testable" database of the process, so, in total, each process will have 2 databases. This template database should finish with a number that identifies the process that uses it, for instance, a number from 0 to the max number of CPU cores your machine has, followed by -template. In my opinion these are the best identifiers in this scenario. An example would be db-test-0-template. This way you can easily identify each database’s purpose. Again, and this is important: These template databases should be created only once at the start of execution of each process and shouldn't be changed anymore. For that you will also need a root connection to your postgres service. Finally, create all tables necessary for your integration test but add no data to it. In our case, data is added in each table during each test execution.

If you would run your tests with 4 processes, then you would have all these template databases created: db-test-0-template , db-test-1-template , db-test-2-template and db-test-3-template

Here's how you can create a template db on postgres:

CREATE DATABASE <db-name> IS_TEMPLATE true;

And here’s how I did step 2.2 in a python test:

# conftest.py

def db_engine_root():
    root_connection_string = settings.DB_CONNECTION_URI.replace(f"/{settings.DB_NAME}", "")
    return create_engine(root_connection_string, pool_pre_ping=True, isolation_level="AUTOCOMMIT")

# This function will run concurrently only one time for each xidst worker (aka process)
# and create a template database related to that worker
@pytest.fixture(scope='session', autouse=True)
def setup_template_db(tmp_path_factory):
    template_db_name = f"\"{settings.DB_NAME}-template\""
    template_connection_string = f"{settings.DB_CONNECTION_URI}-template"

    db_root = db_engine_root()
    if database_exists(template_connection_string):
        db_root.execute(f"ALTER DATABASE {template_db_name} WITH is_template false")
        db_root.execute(f"DROP DATABASE {template_db_name} WITH (FORCE)")
    db_root.execute(f"CREATE DATABASE {template_db_name} IS_TEMPLATE true")
    db_root.dispose()

    db_template = create_engine(template_connection_string, pool_pre_ping=True, isolation_level="AUTOCOMMIT")
    db_metadata.create_all(db_template)
    db_template.dispose()

2.3 — For each test, before the test execution, create the database that will be used by this process. The db should be created using the previous template db which is related to the same process that is running the test. If you are creating a db-test-1, you should use the template db-test-1-template for it. This way we assure there will not be any concurrency issues.

If you would run your tests with 4 processes, then you would have all these dbs created: db-test-0 , db-test-1 , db-test-2 and db-test-3

Here's how you can create a db based on another template db:

CREATE DATABASE <db-name> TEMPLATE <db-template-name>;

And here’s how I did step 2.3 in a python test:

# conftest.py

@pytest.fixture(autouse=True)
def setup_db():
    if not database_exists(settings.DB_CONNECTION_URI):
        db_root = db_engine_root()
        db_root.execute(f"CREATE DATABASE \"{settings.DB_NAME}\" TEMPLATE \"{settings.DB_NAME}-template\"")
        db_root.dispose()

2.4 — For each test, after the test execution, drop the process db previously created (not the template one). Here’s how you can drop a db:

DROP DATABASE <db-name> WITH (FORCE);

And here’s how I did step 2.4 in a python test:

# conftest.py

def reset_db():
    db_root = db_engine_root()
    db_root.execute(f"DROP DATABASE IF EXISTS \"{settings.DB_NAME}\" WITH (FORCE)")
    db_root.dispose()

2.5 — How to configure each process to access their related databases?
It's really simple. Each process will have its own variable values, meaning that if you execute the code sum = 8 + 8 in parallel with multiple processes, each process will have a sum variable instance with the value of 16. So you can simply use your already existing db configurations. You will only need to check if you are running inside a test environment, and if so, retrieve the current process identifier (the simplest id would be a number from 0 to the max number of process you are spawning) and concatenate that to the end of your db configuration variables, like db-name and connection-uri.

Here's how I did that on our python backend service:

# When running pytest with xdist, each worker (aka each process) will have its
# own instance of worker_id, routes and settings variables
if "pytest" in sys.modules:
    worker_id = int(os.getenv("PYTEST_XDIST_WORKER", "gw0").replace("gw", ""))
    settings.DB_NAME = f"{settings.DB_NAME}-{worker_id}"
    routes["default"]["name"] = settings.DB_NAME
    settings.DB_CONNECTION_URI = f"{settings.DB_CONNECTION_URI}-{worker_id}"

You can see in the previous steps that I then use these variables to create the template database, the "testable" database, and to drop the "testable" db too.

With this improvements I got a 15x reduction time on GitHub actions 🚀Now the tests execution time were dropped from 45 minutes to under 3 minutes, which represents approximated 15x reduction 🙌

New GitHub actions total job execution time

New GitHub actions tests execution time

3- How can I reduce this even more?

Use more CPU cores and spawn more processes to run your tests!
Our current GitHub action runner only has two cores 😢
Running the same tests on my mac m1 machine with 8 cores and 16GB of ram I was able to get an execution time of almost 1 minute, so there's still room for a 45x reduction with 8 cores.

You could also be in the middle and only use 4 cores. GitHub Actions has the option to use large runners with 4 or 8 cores.

That's it! I hope I was able to help you to also improve your integration tests execution time and improve your team performance. Thanks for reading and see you soon 🚀 🙌.