I'm writing an application in C++ using libpq as the interface to postgresql. I'm currently putting together a small library of helper functions to help deal with the conversion from C++ to C for an application to be used on either a PC or an ARM device (2GHz 2GB ram).
With regards to pulling data from the database, I'm concerned as to whether I should pull all the results from the database at once, or use a cursor to read one or a few at a time.
The returned result set could probably be in the hundreds and I'm assuming:
- if I pull all results, it'll be a memory hit for the application
- if I pull one tuple at a time via a cursor, it could be a heavy burden on the database server (currently localhost for development)
I don't understand how I could obtain some suitable metrics to determine which would be best, so my questions are:
- Is there a best practice when it comes to retrieving results from the database in the real world (all at once / a row at a time / a few rows at a time)
- How would I determine some form of metrics to test efficiency? I'm guessing I'd have to take both application memory usage and database load into account along with response time.
- If it is best to pull a few tuples at a time, how do I determine the optimal amount to pull at once?