4 ms·
Cursors are stateful. This greatly complicates the design of the backend. First, you have to maintain state in some kind of session on the app server. Second,
by ccleve 6y ago
Cursors are stateful. This greatly complicates the design of the backend.
First, you have to maintain state in some kind of session on the app server.
Second, if you have more than one app server, you'll have to share the sessions across them using Redis or Hazelcast or something.
Third, cursors have to be closed, which means you have to know when the user is done with the result set. This is impossible to know. The user could go to page 2 and then go to lunch. So you have to leave it open for a while, and then have some kind of timeout. Have a lot of users doing the same thing? Memory fills, both on the app server and in the database server.
Fourth, your cursor is going to leave a transaction open, which interferes with updates and all kinds of other things.
Fifth, most users never go to page 2 anyway. They run another search.
Keeping cursors open in a web app is a spectacularly bad idea.
UPDATE -- I am in error. The article does not recommend a normal database cursor. Disregard my comment.
- yencabulator 6y agoNot that kind of cursor. There is no state on the server / in the database and nothing to close. > Generally, you have some ordering criteria, for example, product id. In this case, you’ll encode your product id with some reversible algorithm (let’s say hashids). And on receiving a request with the cursor you decode it and generate a query like `WHERE id > :cursor LIMIT 100`.
- ccleve 6y agoOops -- this is embarrassing -- I did not read the article closely. You're right, the article does not recommend using a normal database cursor. Never mind.
- deleted 6y ago[deleted]
- dudul 6y agoThis is not about database cursors.
- crb002 6y agoYou could enable a batch hint in the API. The server has the option to allocate a cursor if one is available. Best of both worlds.