I heard about two types of requests: a buffered and unbuffered, buffered request is that the result of the request to the database is completely copied into the application memory (namely, copied into the library memory that the application uses to communicate with the database), and the unbuffered request does not copy the result into memory of the application, but remains on the side of the database and pulled out by the application as needed.

1) What are the advantages and disadvantages of these two approaches? In the case of a buffered query, the flaw is more or less clear, it just consumes a large amount of RAM with large result sets, and what about an unbuffered query, what is its flaw?

2) Which databases support non-buffered query? I know only one database: Mysql, but can there be other subd supporting nonbuffered queries?

  • What about an unbuffered request, what is its drawback? Blocking a connection is one hundred percent. And also - problems with moving across a data set, unreliability of information about the total number of records. - Akina
  • Akina, and the remaining connections will be blocked at this moment, for example, we have a lot of streaming application in which several clients are working, client 1 decided to request data using an unbuffered query whether client 2 will have access to the same data in this case (for example to the same table), will not client 2 requests be blocked to the same table while client 1 is doing its job? - cvxbcvbsd fsddfgdfg
  • Not. This connection cannot be used. If you need to perform another request, you will have to use another connection. That can cause difficulties, for example, when using temporary tables and user variables. - Akina
  • Requests of the second client will not be blocked by reading, but they may be blocked by writing if the first client asked for this when he began to read (see select for update). - Mike

1 answer 1

Almost all modern DBMSs support unbuffered queries. And for most of them this is a completely normal mode of operation. Buffering of all data on the client is usually done by the driver on a separate request.

Specifically, in MySQL, this mechanism is implemented in such a way that its use is quite rare. The problem is that within a single connection only one request data can be transmitted simultaneously. Because of this, during the execution of an unbuffered query, you cannot quickly refer to the same database for some other information, and then return to reading from the first query.

In other DBMS (Posgresql, Oracle, MS SQL) this mechanism is implemented and much better than in MySQL. They do not even have to switch the operating mode at the driver level, because by default it is already in the unbuffered state. For most drivers, you can specify the amount of data that the driver can receive in advance from the server and store in its buffers. There is no need to get all the data at once. Because these databases can simultaneously keep several requests open within one connection and remember for each request what they stopped at. True, in most cases, all these requests will be executed within a single transaction. If the transaction is in a consistent read mode, then all these requests will see the same data at the time they are executed. As for resource locks in such a state, they are solved in their own way in each DBMS.