Trouble understanding Database Cursors

Question

With most drivers for most relational databases, the default and preferred way to access results is to use a cursor or iterator.

What I'm guessing is that the database does something like:

1. Runs the query.
2. Prepares the result, stores it in RAM?
3. Returns the cursor for the result to the client.

Whenever the database driver gets a call to fetch the next result, it passes that cursor to the database, which gives the next result.

However, I'm not sure if that's really correct. One thing that stumps me is that if the database client and database server are on different nodes and communicating via the network, isn't this slow? Does it really use such a lazy approach? It makes sense not to return all the data, but is there some middle path it takes?

Answer 1

The database sends the complete result set in one go. The cursor/iterator is in the driver on the client side.

Answer 2

A cursor is a moving placement or pointer that indicates a position. English-speakers have used the term with this meaning since the 16th century, for a wide variety of movable or mobile position-markers. wikipedia description

It is supposed to conjure up the image of a cursor in a text editor. It is (in some contexts) a place holder for where the pointer (cursor) is in a given dataset. A row (i.e. a line) is returned with cursor.fetchone() and the cursor is advanced to the beginning of the next row/line.

The cursor abstracts how many rows are currently buffered at a database client. As the cursor nears the end of a buffer, the underlying framework will fetch more content. The defaults are usually a good guess at a good tradeoff between memory allocation, network latency and other factors.

It becomes a question of optimization. Google maps provides a good comparison. The user can pan around and it seems like the whole country map was downloaded to the client, but in reality it is downloading adjacent panels right before you need them.

Having the database client perform this buffering relieves the programmer from having to think about it before optimization is required.