How to understand at what point the database slows down?

Question

PostgreSQL

There is a PostgreSQL database. The results of parsing files are written to it.
Each parser instance generates an SQL INSERT and spits it out to the microservice. The microservice writes the result to the database.

The problem is that everything is written very slowly and as the database grows, the speed of writing to it drops (already about 5 million rows).

From indexes - there is only an index on the main identifier of record MyID.

At the beginning, I thought that everything was slowing down for me. the index is rebuilt on every insert. But then I took one SQL INSERT and fed it into SQL to see how long it takes to insert.

He worked for 23 milliseconds. Those. clearly not in this.

And what I can not understand. There were thoughts that it could slow down on the side of the microservice, but the logic there seems to be extremely simple. Insert transaction. If not inserted, then roll back and delete MyID and re-insert. Cases of duplicates are extremely rare, so it probably slows down not because of them.

Here if that code:

spoiler

Future<dynamic> sqlInsert(Map body) async {
  bool isDataWasInserted = false; // т.к. идет несколько попыток нам нужно внизу знать что возвращать

  try {
      print("Trying to insert data");
      print("sql: ${body['sql']}");
      // данные прилетают как запросы через точку-запятую

      await connection.transaction( (ctx) async {
        for (var s in body['sql']) {
              await ctx.query(s); 
            }  
            isDataWasInserted = true;
            print("INSERT SUCCESS\n");
        });

    }
    on PostgreSQLException catch(e)
    {
        print("FIRST INSERT FAILED: ${e.message} ");
        await connection.cancelTransaction();
       
       try {
            print("There is some duplicates. Removing them");
            await connection.transaction( (ctx) async {
              for (var s in body['sql-remove']) {
                    print(s);
                    await ctx.query(s); 
                }  
            });

          // новая попытка вставить
          try {
            await connection.transaction( (ctx) async {
                for (var s in body['sql']) {
                      await ctx.query(s);  
                    }  
                print("INSERT2 SUCCESS");
                  isDataWasInserted = true;
                }); 

            } on PostgreSQLException catch(e) {
                  print("SECOND INSERT WAS FAILED ${e.message}");
                  await connection.cancelTransaction();
                  writeLog("SECOND INSERT WAS FAILED ", e.message); // нельзя продолжать т.к. попытка вставки провалилась
                }           

        } on PostgreSQLException catch(e) {
             print("Removing duplicates was FAILED: ${e.message}");
            }
    }    

    if (isDataWasInserted) {
      return dbEnums.success; 
    }

    else {
      writeLog("Can't insert data. Something wrong", ""); // нельзя продолжать т.к. попытка вставки провалилась
      return dbEnums.insertFailed; 
    }
  }

}

How to find the cause of the brakes?

Does it make sense to make indexes lazy?

Answer 1

It is necessary to measure calls to the database and compare them with operations. If you use APM, then usually everything will be visually represented.
Wherever possible, add metrics to measure.
Added

took one SQL INSERT and fed it into SQL to see how long it takes to insert.
He worked for 23 milliseconds. Those. clearly not in this.

one insert will show nothing.
I hope the insertion is in the form of a batch of many rows per 1 request?