Prisma transactions benchmark 3.13.0

prisma-transactions-benchmark.ts

import { prisma } from "~/config/prisma";

async function executeWithoutTransaction(data: Array<{ name: string }>) {
  return await Promise.all(
    data.map((d) => prisma.projects.create({ data: d }))
  );
}

async function executeWithArrayTransaction(data: Array<{ name: string }>) {
  return await prisma.$transaction(
    data.map((d) => prisma.projects.create({ data: d }))
  );
}

async function executeWithInteractiveTransaction(
  data: Array<{ name: string }>
) {
  // eslint-disable-next-line @typescript-eslint/no-unsafe-return
  return await prisma.$transaction((tx) =>
    Promise.all(data.map((d) => tx.projects.create({ data: d })))
  );
}

function elapsedTime(start: number, end: number) {
  return (end - start) / 1000;
}

async function timeTest(name: string, testFunc: () => Promise<unknown>) {
  const start = Date.now();
  try {
    await testFunc();
    const end = Date.now();
    console.log(`${name} PASS: ${elapsedTime(start, end)}s`);
  } catch (err: any) {
    const end = Date.now();
    // eslint-disable-next-line @typescript-eslint/no-unsafe-member-access
    console.log(`${name} FAIL: ${elapsedTime(start, end)}s ${err.message}`);
  }
}

async function runSuite(size: number) {
  console.log("Insert with a single transaction this number of rows: ", size);
  console.log("Generating data");
  const data = Array.from({ length: size }).map(() => ({
    name: "A test project",
  }));
  console.log("Execute projects.create on the dataset: ");
  await timeTest("executeWithoutTransaction", () =>
    executeWithoutTransaction(data)
  );
  await timeTest("executeWithArrayTransaction", () =>
    executeWithArrayTransaction(data)
  );
  await timeTest("executeWithInteractiveTransaction", () =>
    executeWithInteractiveTransaction(data)
  );
  console.log("\n\n ====================================== \n\n");
}

async function runSuiteWithConcurrency(size: number) {
  console.log("Run 2 insert in table with concurrency of size: ", size);
  console.log("Generating data");
  const data = Array.from({ length: 2 }).map(() => ({
    name: "A test project",
  }));
  await timeTest("executeWithoutTransaction", () =>
    Promise.all(
      Array.from({ length: size }).map(() => executeWithoutTransaction(data))
    )
  );
  await timeTest("executeWithArrayTransaction", () =>
    Promise.all(
      Array.from({ length: size }).map(() => executeWithArrayTransaction(data))
    )
  );
  await timeTest("executeWithInteractiveTransaction", () =>
    Promise.all(
      Array.from({ length: size }).map(() =>
        executeWithInteractiveTransaction(data)
      )
    )
  );
  console.log("\n\n ====================================== \n\n");
}

async function main() {
  const TEST_LOADS = [1, 10, 20, 40, 80, 160, 320, 640, 1280];
  for (const load of TEST_LOADS) {
    await runSuite(load);
  }
  console.log("--------------------------");
  console.log("Run suite with concurrency");
  console.log("__________________________");
  const CONCURENCY_LOADS = [1, 10, 20, 40, 80, 160, 320, 640, 1280];
  for (const load of CONCURENCY_LOADS) {
    await runSuiteWithConcurrency(load);
  }

  console.log("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
  console.log("Check if transactions / connections pool recovered");
  await runSuite(10);
  await runSuiteWithConcurrency(10);
}

void main();


/*
** The goal of this script is to check what is the overhead in both performances and stabilities of prisma transactions (without, with array transactions, with interactives ones)
**
** -- runSuite: Will basically create "more and more big transactions", inserting one to thousands of rows into our table
** with a single transaction each time. The goal of this test is to check the performance tradeof of using transactions
** against not using any, from my results, array transactions slightly increase performance when inserting hundreds of rows
** togethers (probably because the database "commit" less operations than without any transactions).
**
** -- runSuiteWithConcurrency: Will create "more and more concurents littles transactions", each time inserting 2 rows into a table.
** The goal of this test is more to test for the "connection pool emptied" bug we had before (failing transactions was closing prisma connections)
** and also to test for something similar to the use case we have with ApolloServer 
** (
**    multiples concurent requests, triggering mutations, each mutation creating a transaction with 
**    (inserting something in a database table, inserting a row in the events table)
** )
** 
** The last part of the benchmark, is just here to ensure that if something errored because of timeout our anything
** else in the previous tests our prisma connection pool recovered and we can still make query to the database.
**
** Running this benchmark on my machine I ended up with the following conclusions:
** - Prisma v3.13.0 fix the "connection pool" can't recover error. This benchmark consistently produced the bug on 3.9.2
** the last part of the bench was not able to recover after failures from the "runSuiteWithConcurrency" tests. In 3.13.0
** it recover properly even after the "runSuiteWithConcurrency" tests fail on the "big loads" tests.
** - Array transactions can handle twice as more load than "interactives" one in both tests:
**     - runSuite:
**             - array transactions (pass in all the bench tests)
**             - interactives transactions (fail due to expired/timeout on the last test with 1280 rows)
**     - runSuiteWithConcurrency:
**             - array transactions works with up to 640 concurents requests
**             - interactives transactions fail between "160" and "320" concurents requests
**
** - The performances cost of using transactions is quite minimal, in fact, when using array transactions
** we gain a little on performances side. With interactives transactions, when they don't fail, the cost is quite minimal.
** - We should never (or almost never) use external api call inside an interactive transaction. An interactive transaction
** should always stay scoped to "only db related" operations, if you need something more heavy than using the result of previous
** db operation into another one, or doing a computation more heavy than iterate over 1k elements, you should do it outside the transaction
** as it'll probably timeout or end up expired before the end of it.
**
** - To run the bench on your machine, copy this script in "server/src/app", run "yarn dev" on "packages/server", then run "yarn run yarn run run:script src/app/prisma-transactions-benchmark.ts"
*/