when we select configure this field is blob and it can be as large as
1k. Is it good practice to get thousands of rows at one time?
There are a few layers to this. When you select a large set of rows from the DB, it doesn't really send you a huge clump of bytes. What happens is that there is a cursor on the DB with a size that you can configure. So if your cursor size is 1000 rows, it's not going to grab all the rows at once. You generally won't need to worry about this except for things I mention later in this answer.
However, the other aspect of this what you do with the rows. If you pull them all into a list on your application (which is what people do with ORMs) you are going to need allocate space for all of these records. I hate this approach. It's the most common cause of bloated Java programs. You really should be looping over the records from the database connection as an iterator.
or it is better to make multiple db call?
This depends on concurrency considerations discussed below.
time and space, which is more important?
Time-space trade-off analysis is something that applies when you have algorithmic complexity. This doesn't seem like one of those cases. If all you are doing is reading a single row and writing an update to it, using more memory won't speed things up. In fact, it will probably slow down your program. It takes time to allocate and manage memory. You try to allocate no more memory than you need to do the next task.
users table has millions rows, we need update perhaps 1 column for 1 million
rows. what is a good practice to make this update? is it better to make 1
million db call? or as little db calls as possible?
I'm assuming here that you need to look at each row and make an update that cannot simply be done in a one-shot SQL statement. There's not a simple answer to this question and that's because it depends on whether there will be other applications interacting with these tables while you are doing this.
If you are doing 'select for update', you probably don't want to do this in one big commit. The reason you would use that is to prevent other applications from modifying the data between the selection and the update. In other words you would be locking all of these records for the duration.
Even if you are not worried about concurrency or dirty reads, you probably don't want to change a million records and then commit at the end because:
- If there is an issue at any point in this process, even a minor one, you have to start over from the beginning.
- Writing a ton of uncommitted changes to a DB puts a lot of stress on it's resources.
So there's a sweet spot for performance for commits. You commit each one and there will be a little overhead. You could commit them in batches but your retry gets a little more complicated. Personally, unless you know there is a performance concern, I'd probably commit each change immediately. It's the easiest to get right. A million records is really not that much per day. And 1K isn't much data. Oracle (for example) won't even bother putting 1K off-table unless you tell it to.