Blob vs File system storage

[Thor]

So following my user authentication learning step the next phase is to allow the user to upload a profile picture, which leads me to this thread.

For Profile images ( 200 x 200 ) which is best; Blob in the DB or File System storage with the path stored in the DB?

Q Why do I think I want to store the profile images in the DB?

A If my understanding is correct weather I backup 10Gb of files or a 10Gb Database 10Gb is 10Gbs none the less?

What would be the best practice here to store user uploaded profile images with a fixed dimension of 200 x 200 pixels

 

[pilks]

I have learnt the hard way that storing pictures in the Database is a bad idea.

Best practice is generally store them on the file system. It reduces strain on your database and allows you to scale better.
It does make it easier to manage your app if there is not a folder full of data you need to migrate.
You are less likely to delete a row than a folder or a file on the file system.

Both of those above points should be mooted in a properly managed environment where your DevOps knows what they are doing.

My advice:File System

 

[gkm]

In general, some kind of file storage is best for images in my opinion. Also, seriously consider offloading this storage to some kind of cloud storage like Amazon S3 or Azure Object Storage. That way you reduce your storage space needs and also load (cpu/memory/bandwidth/threads) on your web server to deal with serving the images. I know there are PHP plugins for S3. Guess there should be ones for Azure as well. Or roll your own from the examples on the internet. Then just store the image locations in your database.

 

[Thor]

I have learnt the hard way that storing pictures in the Database is a bad idea.

^Explain

Best practice is generally store them on the file system. It reduces strain on your database and allows you to scale better
My advice:File System

I think storing in the DB will scale better than the file system. The file system is not build to handle or optimized to handle tens of millions of files and folders, Database systems are optimized for exactly this.

Updating, changing, deleting etc is simply altering a column and not having to do all sorts of file manipulations in the file system.

 

[SauRoNZA]

Why store them at all?

Let uses link them from independent hosts that aren't your problem.

 

[Thor]

In general, some kind of file storage is best for images in my opinion. Also, seriously consider offloading this storage to some kind of cloud storage like Amazon S3 or Azure Object Storage. That way you reduce your storage space needs and also load (cpu/memory/bandwidth/threads) on your web server to deal with serving the images. I know there are PHP plugins for S3. Guess there should be ones for Azure as well. Or roll your own from the examples on the internet. Then just store the image locations in your database.

So from a performance and storage perspective you prefer filesystem, not so sure on the performance part as I trust a DB will be faster concidering it's small images 200 pixels, but from storage yes I can understand.

I can swap out MySQL for something better like Azura or AWS since I am using PDO so storage is not a issue

 

[Thor]

Why store them at all?

Let uses link them from independent hosts that aren't your problem.

That is a hassle for users. I hate it, I know they will too.

 

[gkm]

So from a performance and storage perspective you prefer filesystem, not so sure on the performance part as I trust a DB will be faster concidering it's small images 200 pixels, but from storage yes I can understand.

I can swap out MySQL for something better like Azura or AWS since I am using PDO so storage is not a issue

I think you should still use MySQL for the rest of the data, just use something like an S3 bucket instead of a local directory for the images, and serve the s3 links instead of the local files. (AWS also has MySQL via RDS, but guess that is a subject for another thread.)

Note, 200 pixel square image is still say 200 x 200 x 4 bytes, maybe less with compression, but still a fair bit of data. Performance should probably be similar for DB and file system, for small images. Various caching layers involved in both situations will make the answer very much "it depends".

 

[SauRoNZA]

That is a hassle for users. I hate it, I know they will too.

I see it as a benefit again. Choose Avatar once and just link to it repeatedly.

 

[Thor]

I see it as a benefit again. Choose Avatar once and just link to it repeatedly.

I get the appeal but trust me the majority of people have no clue about hosted file repos or imeger and the likes and want if they are down etc etc.

I want it easy, drag image here.

Done.

 

[Tokolotshe]

This is a difficult one at best. I agree with a small amount of images being stored in the DB. But it also adds other issues.

With separate images I can easily make differential backups. To do so in a DB context I'd need to add a field or do some other fancy footwork. Backups would typically take more resources, take longer as the amount of number of images grows.

However splitting off images to files on disk needs some form of ensuring consistency between what's believed to be on disk from a DB perspective vs what really is. Plus your security mechanisms would be different as you now have to cater for two mechanisms.

Personally my number of images always grow exponentially, so I end up with a file solution. Security is not that difficult.

Which car is the best?

I've seen exspurts use the best tools ending up making one hell of a mess. CoJ? I've seen brilliant stuff made with basic tools. It's a case of using what you have properly. I'd suggest go with what you know and the most comfortable with.

My 5c donated ...

/Edit:
Comment re millions of images vs a file system: Bad idea putting them all in one directory. The old termininfo days of Unix solved that by using sub directories.

Record_ID 12345678
$Image Dir/12/1234/123456/12345678/
No more than a 100 entries per directory works well
Try and keep some metadata in the image for DR and/or naming for the files that may assist. 12345678.png

As for "source images from elsewhere" and not worry about it. You kind of loose sleep (or should) about cross linking images from another untrusted site in your app.

 

[Thor]

This is a difficult one at best. I agree with a small amount of images being stored in the DB. But it also adds other issues.

With separate images I can easily make differential backups. To do so in a DB context I'd need to add a field or do some other fancy footwork. Backups would typically take more resources, take longer as the amount of number of images grows.

However splitting off images to files on disk needs some form of ensuring consistency between what's believed to be on disk from a DB perspective vs what really is. Plus your security mechanisms would be different as you now have to cater for two mechanisms.

Personally my number of images always grow exponentially, so I end up with a file solution. Security is not that difficult.

Which car is the best?

I've seen exspurts use the best tools ending up making one hell of a mess. CoJ? I've seen brilliant stuff made with basic tools. It's a case of using what you have properly. I'd suggest go with what you know and the most comfortable with.

My 5c donated ...

That will be the winning advise and this is what I will go with ( so file system )

<?php
 
class Uploader
{
    public $destFolder = 'uploads';
 
    public $alloweFileTypes = array(
        IMAGETYPE_GIF, IMAGETYPE_JPEG, IMAGETYPE_PNG
    );
 
    public function upload($file)
    {
        $returnFilePath = '';
        if (!$file['error']) {
            $destFile = $this->destFolder . DIRECTORY_SEPARATOR . time() . '-' . $file['name'];
            if (move_uploaded_file($file['tmp_name'], $destFile) || copy($file['tmp_name'], $destFile)) {
                $returnFilePath = $destFile;
            }
        }
        return $returnFilePath;
    }
 
    public function delete($filename)
    {
        if (file_exists($filename)) {
            return unlink($filename);
        }
    }
 
    public function valid($file)
    {
        return in_array(exif_imagetype($file['tmp_name']), $this->alloweFileTypes);
    }
}

Three functions in this class:

public function upload($file): it utilizes PHP functions move_uploaded_file and copy to move uploaded file to desired directory.

public function delete($filename): it deletes a file specified by $filename parameter. Here $filename is actually referring to the file path.

public function valid($file): it validates the file type using PHP function exif_imagetype and you are free to update by add/removing any types by changing variable

 

[_kabal_]

you should look at some form of subfolder segregation.

at some point a human might have to look at the filesystem, and having tons of files in a single folder might not be the best idea

 

[Thor]

you should look at some form of subfolder segregation.

at some point a human might have to look at the filesystem, and having tons of files in a single folder might not be the best idea

Definitely agree

 

[Tokolotshe]

Gave an idea ito of segregation in my pot edit. Unfortunately Thor was too fast.

 

[Tokolotshe]

..in my pot edit.

*post

 

[IndigoIdentity]

Why do I think I want to store the profile images in the DB?

Define DB? I'd go with it's likely not the best thing to do.

If my understanding is correct weather I backup 10Gb of files or a 10Gb Database 10Gb is 10Gbs none the less?

Furthering the above statement, there is a reason why a relational database is called this and it is because it is recommended that the data that you're dealing with is relational. The Blob data type is a binary object so it would not be very efficient to store and process 10GB of non-relational data in something like say, MySQL, no. There are however, better database engines that deal with this type of data specifically.

Just bear in mind too though that when you store data on the disk, you can leverage the speed of SSD's to your advantage and this is not very expensive.

 

[Tokolotshe]

ust bear in mind too though that when you store data on the disk, you can leverage the speed of SSD's to your advantage and this is not very expensive.

^^ NB that. Data - not images.

Disk speeds, CPU speeds, memory speeds ... the net will be the slowest connection normally, but try getting that data flowing asap

Typically a DBS will do many reads for potentially a few writes in rendering a page. Read an image only once. CloudFlare?

So get a good quality good cheap storage for you images (dare I say a 7.2k SATA raid protected or like solution) for your images. Fast SSD disk for your DB.

At the end it all comes together when your user accesses this over a piece of barbed wire in Put Sonder Water or a bit faster link in JHB/CPT (assuming an internet facing solution).

 

[rward]

Images on the filesystem, path in the db.

 

[Beachless]

A file system was designed for storing files. Some of the newer DB designs are much better at storing larger items but at the end of the day I would still pick file storage.