[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: While you're packing (some questions about gb.db2)

On the other hand, I dont think it's such a simple decision.
Most rdbms compress data, some across tables not just within an attribute. So your size info may differ from Christof's. Albeit, the only way to find out is to monitor the database size exactly. Now before you cry but the objects are images so they are already compressed, it depends on the images. I have one database with aerial photographs of crop fields, the db table size is 40% less than the total size of all the images. I guess the green and brown are being compressed across the hundreds of images (blobs) rather than the image files which are just compressed within  themselves. I have heard though that pdf files don't compress much inside databases.
Many databases support incremental backups (and restores are just as complex as file system backups).
Security! If you have multiple users then database storage is secure from Fred changing the blob while Mary changes the accompanying data.
Volatility, if the blob data is reasonably volatile ( say a plot image that is updated daily or even more often ). Many databases offer a mechanism to restore to a particular point in time that is between your backup points.
Searching. (possibly contentious as there are obviously image categorising utilities that are better than the rdbms at searching for something inside an image blob) But this may be worth some consideration.
So, all I'm saying is that there are some instances where storing large object data inside the database is better. 

b

On 16/8/24 2:00 am, Fabien Bodard wrote:

Hi Christof

I've alway done like that :-) (Path storage and little bash for backup)

Never used blob field in my own database.
Le jeu. 15 août 2024 à 00:08, Gianluigi <gradobag@xxxxxxxxxxx <mailto:gradobag@xxxxxxxxxxx>> a écrit : 

    Il 14/08/24 22:44, Christof Thalhofer ha scritto:
    > Am 14.08.24 um 15:04 schrieb Gianluigi:
    >
    >> Hi Fabien,
    >>
    >> In my small database projects, I have always stored file paths
    and not
    >> files (blob).
    >> However, the files are always present in a folder, so they are
    stored on
    >> the computer, with the problem that it is not enough to back up the
    >> database to save the files.
    >> Are you sure this system is more economical?
    >
    > It's the best in most cases. Imagine what you store in a
    database and
    > what on disk if you want to keep documents and their description
    in this
    > example:
    >
    > The database contains the path to the file and the description as
    > strings. If you need 1 kB for that per file and you have 1,000 files
    > then the data in your DB sums up to 1 MByte.
    >
    > If you store the files in the filesystem and each file has 1 MB
    then the
    > files need 1 GB.
    >
    > So you have 1001 MB on disk. 1 MB for the database and 1000 files.
    >
    > Now imagine that every day you add one new file and delete one
    and you
    > want to backup that. On every Sunday you make a full backup and from
    > Monday to Saturday you create incremental backups which store
    only files
    > which had changed.
    >
    > The backup in this scenario:
    >
    > Sunday: 1000 files and database dump = 1001 MB
    > Every Mon to Sat: 1 new file and the database dump changed = 2 MB
    >
    > So you have a total storage of 1001 MB + 6 * 2 MB = 1013 MB for
    one full
    > and six incremental backups.
    >
    > Now imagine that you have everything (content of files and the
    > description) in the database which creates a database dump of
    1001 MB
    > every day because every day one document was deleted and one was
    created
    > new.
    >
    > Sunday: Database dump = 1001 MB
    > Every Mon to Sat: Database dump = 1001 MB
    >
    > So you have a total storage of 7 * 1001 MB = 7007 MB
    >
    > That's the reason why it is better to store only the paths as
    string in
    > the database and the files in the filesystem (in most cases).
    >
    > Alles Gute
    >
    > Christof Thalhofer
    >

    Hi Christof,

    great explanation.

    Thank you very much.

    Good night
    Gianluigi




--
Fabien Bodard