How suitable is the following code for heavy database operations?

Question

Dart

I don't think the language matters much. Arrange the advice of Sharpists and those who write on Dart. The original Dart code. But I think it will be very clear to everyone.

Database db;

void main() async {
  db = Database();

  var pidFile = File("pid.txt");
  pidFile.writeAsStringSync(pid.toString());

  connection = PostgreSQLConnection('localhost', 5432, 'db', username: 'postgres', password: '123456', queryTimeoutInSeconds: 30);
  await connection.open();

  app.post('/db', (req, res) async {
    var r = await db.someLongDBOperation(req.body);
    return res.json({"data": r});
  });  

 }

The question is the following. To what extent is this code optimal from the point of view of performing some heavy operations with the database? Does it make sense to use Isolates (or separate threads if we're talking about C#)?

The usage pattern is like this. A huge number of requests to insert into the database flies to the handler. `someLongDBOperation` handles them accordingly.

What bottlenecks can be and how to fix them?

Answer 1

If the operation is very long, then the client may timeout.

Answer 2

You can easily select a bunch of threads. In fact, async is a wrapper over the pool. Is the load steady or bursty?

Answer 3

Bottlenecks - this is actually a lot of queries to the database for insertion (possibly also into one table) - can slow down the database. Collect on the server in packs of the data and do periodically batch insert.
At each insert in the table it is locked and other requests for an insert wait + indexes are still rebuilt.