PNGs in Sqlite DB or separate files?

  1. 9 months ago

    Karen A

    11 Nov 2018 Pre-Release Testers

    The app I am working on needs to have around 15,000 pngs on hand, I expect them to be in the 10-20K size range...

    Would I be better off storing themas blobs in the DB in a separate table with an indexed foreign key to the data it is related to, or would I be better off keeping them as separate files with file names made from that key value?

    Thanks,
    -Karen

  2. Dave S

    11 Nov 2018 San Diego, California USA

    personally.. I prefer to store "path" information
    unless the database must be "self-contained"

  3. Emile S

    11 Nov 2018 Europe (France, Strasbourg)
    Edited 9 months ago

    In two projects, I provided a Picture Column to store Photos ID. So, the application is able to read / write the Photos, and my tests proves this works nice (I implemented Open / Export and Drag and drop both directions).

    But: because the client does not use them… I cannot say more (r/w speed, etc.)

    Edit: I forgot to mention that the client does not have (nor think at) so many Records…

  4. Jean-Yves P

    11 Nov 2018 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    I made an account database for a customer. it was installed in january.
    each order, each invoice stores it's own pdf file of the printed order, invoice, and report.
    it has so far around 6000 records and each record has at least one pdf.
    there is a fied for the main pdf in the same table as the invoice, and a linked table to the other pdfs (contractors reports)
    it is a postgres multi-user db, and the database reaches some 2GB today.
    everything is fine. speed is as fast as in january. fingers crossed...
    the server is a mac mini i7 with an ssd drive.

    just don't forget the multiples backups !

  5. Georgios P

    11 Nov 2018 Pre-Release Testers, Xojo Pro Athens, Greece

    ah, the good old "binary data in DB or FS" dilemma!

    I've taken the "link in DB, file in FS" path up to now, and I've faced two annoying issues:

    • sooner or later there are going to be inconsistencies: someone will mess with the files without updating the DB record
    • backing up or migrating even a couple million 50kb-sized files takes f-o-r-e-v-e-r!

    but then again, there are valid reasons for not storing your binary data inside the RDBMS. you really need to know what you're doing and what that means when going down that path.

    I've been working on a document management project that attempts a compromise between the two: store the data in an sqlite-based VFS storage structure that is accessible through a postgresql database session. there's going to be much more, but this part alone might be of interest to some.

    if you're feeling adventurous, you can check out the project here
    you can find an example application for the VFS engine here

    if you decide to use pdstorage for your project, let me know of any questions.

or Sign Up to reply!