NodeJS & postgres - How to improve integration testing speed

[LoganTann]

In this space, you can find some errors I faced and how I solved them.
If this helped you, react to this post :D

Context

NestJs is a robust back-end framework for node.js. Jest is the default test framework.

You might test your code via integration tests and a temporary database. The code is tightly coupled to postgres so you can't test using an in-memory database.

Issue faced

Database operations take most of the running time, especially at the cleanup process.

Truncating all tables takes in average 500ms.

Investigation

• The use of TRUNCATE over DELETE offers greater efficiency.
• During my school project involving Citus, I found out that it's possible to truncate several tables within a single TRUNCATE query, which is even more efficient than executing multiple truncate queries.
• PostgreSQL's documentation provides a dedicated page for databases with short lifespans, which can be found at: https://www.postgresql.org/docs/current/non-durability.html
• A response in a StackOverflow thread suggests that the database should be kept up-to-date with the latest version, as maintainers continually introduce optimizations over time.

Solution

• First, ensure that your js files are not being converted to typescript (refer to this knowledge post), and configure a cache for jest.
• Instead of looping over entities to truncate them, create a single truncate query that includes multiple tables. This optimization reduced the cleanup time from 300ms to approximately 150ms.

    async purgeDatabase() {
        const tablesToPurge = this.entities
            .map((entity) => this.entityManager.getRepository(entity).metadata.tableName)
            .map((tableName) => `"${tableName}"`)
            .join(', ');
        await this.executeQuery(`TRUNCATE TABLE ${tablesToPurge} CASCADE`);
    }

• In your docker-compose.yml file, upgrade PostgreSQL and apply the configuration recommended by the PostgreSQL documentation.
  • Add the following environment variable: POSTGRES_INITDB_ARGS=--no-sync -c fsync=false -c full_page_writes=false -c synchronous_commit=false.
  • This change resulted in a significant improvement in execution performance, from 200ms to approximately 50ms.

Manual benchmarks for 13 tests:

• Runs before : 11,710 ms (avg 650ms) ; 10,110 ms (avg 600ms) ; 10,180 ms (avg 600ms) ;
• Runs after : 2,919ms (avg 70ms) ; 2,776ms (avg 60ms) ; 2,762ms (avg 60ms)

Overall : 10x faster !!

--

Tags : nestjs, ts-jest, faster, postgres, fsync=false, truncate multiple