SQLite save images in database

[newbie2]

Hello SQLite experts
I was using access as database and I was just saving the path of pictures
Now I'm using SQLite3 and I'm thinking of saving pictures in database instead of the picture's path.
My question to experts of SQLite:
Are there any serious consequences on thevperformance of the applicaion in long term?
your help is much appreciated.

[techgnome]

If you can help it... don't. Do what you can to avoid it. If you can't make sure you're using the smallest images with high compression and making it as small as possible before storing it.
Trust me.

If you don't it will bloat your db.

-tg

[newbie2]

If you can help it... don't. Do what you can to avoid it. If you can't make sure you're using the smallest images with high compression and making it as small as possible before storing it.
Trust me.

If you don't it will bloat your db.

-tg

thank you very much

[Schmidt]

Do what you can to avoid it.

That's a bit too general IMO.

Here's an article from the SQLite-folks, where they compare DB- vs. FileSystem-performance on different platforms:
https://www.sqlite.org/fasterthanfs.html

Olaf

[Zvoni]

There are pro's and con's to each approach.

as tg said:
blobs in db:
con: it increases the size of your db
pro: you're not dependent on a folder-tree being in the correct place (As i've often said: There is always a smartass who thinks, that folder or that file should have another name.....)

blobs not in db, but in a folder
con: you have to take care of creating/maintaining/checking if Folder-Tree exists and/or you have to distribute the folder-tree
pro: smaller size of db

We've had a similiar thread here some months ago:
If you go the route storing blobs in the database, never fire a "SELECT * FROM MyTable" against a table having blobs

[techgnome]

We've had a similiar thread here some months ago:
If you go the route storing blobs in the database, never fire a "SELECT * FROM MyTable" against a table having blobs

Yes. Yes. Yes. A million times yes.

-tg

[techgnome]

That's a bit too general IMO.

Here's an article from the SQLite-folks, where they compare DB- vs. FileSystem-performance on different platforms:
https://www.sqlite.org/fasterthanfs.html

Olaf

Agreed it was a general blanket statement, I just want to make sure that the OP does it with their eyes wide open. I've been in too many situations where blinders were on and this was "the solution" and despite our pleas to the contrary... 6 months in, the client called billing to find out why their bill exploded... turned out their db usage exploded... a dba spent three days digging into their data to find out why (that was another added expense)... andd found out that a feature that they had paid for - they wanted to be able to include images in some of their mailings... but rather than storing small wallet-sized, reasonably compressed file images, they were storing the digital equivalent of 8x10 high-def images.... and then multiple copies (it's complicated, I can't get into specifics w/o revealing the client) ... over time... before we new it, the blew past a terabye... lat I heard they were on their own server fram with well over 10 terrabytes and still growing...

So when ever I hear someone storing images in the db, I say, don't (because sometimes there are better ways - in the case above we would have been better served with a file-based system)... BUT... if you have to, or need to... then... make sure you go in with your eyes open and with full understanding.

So I like to throw a blanket over it, then pull it back, to make sure it is the right solution.

-tg

[Zvoni]

tg,

btw: Out of curiosity: Do you have any numbers on overhead for db-stored blobs vs. stored as files in a filesystem, and in the db just storing the links/paths?

Kinda like:
i have 1000 pictures (jpg), each 1 MB in size on a disk --> 1 GB required diskspace (Don't start with 1GB<>1000MB), and in the db i just store the filename incl. path
i have 1000 pictures (jpg), each 1 MB in size stored as blobs in a DB --> required diskspace for, say, sqlite?

because regarding required diskspace, i wouldn't expect there to be a huge difference, e.g. 1GB as files in Folder-Tree vs. 1.05GB as db-file

If there is no big difference, then we can say: diskspace is diskspace, irrespective what it's used for, and all pro's and con's above apply.

[jpbro]

Might be of interest: 35% Faster Than The Filesystem*

[jpbro]

The choice of where to store the files depends on some other factors. In my app, I store PDFs and Images on the file system as opposed to in BLOB fields because:

• I have multiple databases (one per "project") that can reference the same PDF/Image. Rather than store multiple copies of the same file in multiple databases, I calculate the hash of the PDF/Image and save it to the filesystem with the file name as the hash value. In the database(s) I store the referenced hash value in an "attachments" table.
  
• The above has an added benefit that duplicate files will never be saved to the file system, saving space in scenarios where you may have a lot of duplicates. Of course, you could do the same when storing in a database (have an attachments table that stores the blob and a hash of the blob, then reference that table in other tables by hash. The Hash column in the attachments table should have a unique constraint).
  
• Incremental backups are easier at the file system level. Rather than backing up a potentially huge database, I can backup only the files that have been added since the last backup run using a tool like rsync/rsnapshot.

There are downsides of course - as Zvoni mentioned you run the risk of users trying to tinker with files on the file system. For that reason I store the attachments in a folder with the Hidden+System attributes set. Seems to have kept everyone out so far (after 15 or so years, I've had no tamperers anyway).

[techgnome]

tg,

btw: Out of curiosity: Do you have any numbers on overhead for db-stored blobs vs. stored as files in a filesystem, and in the db just storing the links/paths?

Kinda like:
i have 1000 pictures (jpg), each 1 MB in size on a disk --> 1 GB required diskspace (Don't start with 1GB<>1000MB), and in the db i just store the filename incl. path
i have 1000 pictures (jpg), each 1 MB in size stored as blobs in a DB --> required diskspace for, say, sqlite?

because regarding required diskspace, i wouldn't expect there to be a huge difference, e.g. 1GB as files in Folder-Tree vs. 1.05GB as db-file

If there is no big difference, then we can say: diskspace is diskspace, irrespective what it's used for, and all pro's and con's above apply.

But it isn't just diskspace... though is it? It's pagespace... it's the allocation of rowspace by the DBMS too. Because there's always more than just the image. There's the metadata that goes with it. Name, attributes, etc. Fragmentation happens. And then you get into odd-shaped files, varying resolutions. Yeah storing the files as blobs was a bad idea in this case. Unfortunately we weren't able to take advantage of SQ Server's FILESYSTEM Stream at the time... that would have been ideal. At least then we would have been able to compress /decompress the files on their way in/out. But it didn't arrive until a year after the client implementation, and then it was too late.

And yes, that pagespace is going to vary from DBMS to DBMS and hopeffully SQLite does it better than SQLServer, which isn't that high of a bar to achieve. And if it does, great. By all means, go for it. I'm hoping that my answer is in that link fomr Schmit there,I haven't had a chance to check it out yet, I plan to come back to it at lunch.

-tg

[jpbro]

Oops, just noticed Olaf posted the link I did before me! Anyway, it's an interesting read if you are using (or considering using) SQLite.