Storing icons in Room Database

Question

I want to store some 512x512 icons for my Android app in a Room database. This is so each item in a list can have all the text data along with the image data stored in a single record.

The answers to this question https://stackoverflow.com/questions/46337519/how-insert-image-in-room-persistence-library indicate this is not a good practice, however the images I'm saving aren't even 1 kilobyte.

Under these circumstances would it be acceptable to store images in a database like this?

I want the database to come preloaded with certain default list items, but the users will also be able to add their own items with custom icons. Given that this is the case it may be better to just store URIs in the database but I would like some insight on this.

Answer 1

would it be acceptable to store images in a database like this?

Yes, of course.

The standard advice about "keep pixels out of the DB, just store image file pathnames" is all about playing to the DB's strengths, and making sensible use of cache memory.

It sounds like you have maybe 1000 bytes of image and 100 bytes of text for each item, a 10:1 ratio, and when you need one you always need the other. So sure, put both in a single row.

When would we not do that?

Well perhaps you frequently tablescan looking for text matches, and for the < 1% matching rows you display the corresponding image. In that case a columnstore, or segregated sqlite rowstore tables, would let you conserve 90% of cache memory, yielding better cache hit ratio and faster queries. So one narrow text table would have (id, description) while the other wider table has (id, image_blob). That lets us efficiently tablescan the text descriptions, and only bring in the handful of needed images for the matches. I/O takes time, and reading lots of big images takes lots of time. This becomes a more pressing concern when images are megabytes rather than a kilobyte.

For your use case, as I understand it, when you read a description you always need the corresponding image BLOB. So there's no need for extra complexity or JOINs; a simple (id, description, image_blob) row will suffice.