Why does query response time increase under load?

Question

Python

To connect to the database, I use the asyncpg python library, all the settings have been optimized, indexes have been created on all tables, the server is 128GB operative, 128 cores, 1 NVME disk, ubuntu, there is a problem when there are few users, all requests are completed in an average of 60ms, but when users gets bigger, even the simplest queries are completed in 300ms+, why is that? Is it possible to fix this somehow?

asyncpg setup:

async def create(self):
        pool = await asyncpg.create_pool(
            user=config.PGUSER,
            password=config.PGPASSWORD,
            host=config.ip,
            port=5432,
            database=config.DATABASE,
            max_size=300,
            max_inactive_connection_lifetime=60.0,
            min_size=20,
            max_queries=100000,
            statement_cache_size=16000,#9000
            max_cached_statement_lifetime=300,
            max_cacheable_statement_size=0,
            command_timeout=120.0
        )
        self.pool = pool

PostgreSQL config:

https://pastebin.com/PtQTN6Ex



The following errors sometimes occur:

asyncpg.exceptions.DeadlockDetectedError: deadlock detected
DETAIL:  Process 2220108 waits for ShareLock on transaction 14040699; blocked by process 2220462.
Process 2220462 waits for ShareLock on transaction 14040460; blocked by process 2220456.
Process 2220456 waits for ExclusiveLock on tuple (12,51) of relation 16565 of database 16385; blocked by process 2220108.
HINT:  See server log for query details.

Answer 1

Apparently, the problem is not in the server, but in an interesting (in fact, crookedly implemented) business logic that creates deadlocks