How is the processing of large INSERT queries arranged?

Question

PostgreSQL

There is a large INSERT with a size of 10MB which scatters about ten thousand values ​​across 5 tables.

I suspect that it is not executed instantly. What happens to PostgreSQL if several more queries arrive at the time of its insertion?

Does PostgeSQL have queues by default?

It's just that from time to time my application performing an insert into the database returns me a 504 error and it is very likely that this situation happens.

Are there any settings that affect this? For example, the number of PostgreSQL processes so that while one is busy, the second one works?

Answer 1

It's just that from time to time my application performing an insert into the database returns me a 504 error and it is very likely that this situation happens.

AND? 504 says some kind of error. Open the logs and see what exactly the error is, and you won’t have to guess, maybe it’s not because of the database at all.

Answer 2

I suspect that it is not executed instantly. What happens to PostgreSQL if several more queries arrive at the time of its insertion?

It executes them, if possible in parallel (in PG, by default, a new process is created on the server for each connection). They can block each other, but under certain conditions. You have to try really hard for INSERT to cause some kind of lock.

It's just that from time to time my application performing an insert into the database returns me a 504 error and it is very likely that this situation happens.

Most likely, you just load the machine heavily, everything starts to slow down, memory consumption increases, requests start to run for a long time, they accumulate. Why exactly you have an error, look in the logs. Maybe timeouts, maybe out of memory or running out of connection limits.