store images either in database or in Hard Disk

[cover]

The question is,
we have two options to store images, either in a Database (MySQL,
Postgres, ...) like blob data, or in the hard disk the file and the path in database.
Which option is better? When? Why?

Thanks you for your answers.

[Andy Hassall]

Re: store images either in database or in Hard Disk

On Thu, 26 Feb 2004 23:42:48 +0000, "cover" <dustcover@bigf oot.com> wrote:
[color=blue]
>The question is,
>we have two options to store images, either in a Database (MySQL,
>Postgres, ...) like blob data, or in the hard disk the file and the path in database.
>Which option is better? When? Why?
>
>Thanks you for your answers.[/color]

Database:
+ Files get same transactional integrity guarantees as all other data.
+ Only one thing to back up; the database, easy to keep consistent.
- Requires a database that handles BLOB data well. YMMV, depends on DB.
- Greatly increases data throughput in and out the database.

Filesystem:
+ Web server and browsers can use caching more effectively.
+ Less load on the database.
- Complicated to keep consistency; if you rollback an UPDATE of the database,
or it crashes, how do you keep that in sync with the filesystem?
- Similarly with backups - you have to keep them in sync somehow.

Or you could go for a hybrid; the database being the master for the data,
stored in a BLOB, but it's written out to a file on disk on demand for easy
access by the webserver. When doing a backup, ignore the cache, just backup the
database - everything stays consistent. When doing a restore, wipe out the
cache, and let it repopulate itself on the next accesses.

--
Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>

[Hayden Kirk]

Re: store images either in database or in Hard Disk

Very nice

Never thought of the hybrid way. Will have to try this on my own server.

Are databases ment to handle large files like this. Like if you store a
100mb file in a database, is this recommended?

How does it effect the database, im really talking about MySQL as thats the
only DB I deal with.

Thanks

"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:hq1t301l77 ue317s64200f78v hu2rkojq8@4ax.c om...[color=blue]
> On Thu, 26 Feb 2004 23:42:48 +0000, "cover" <dustcover@bigf oot.com> wrote:
>[color=green]
> >The question is,
> >we have two options to store images, either in a Database (MySQL,
> >Postgres, ...) like blob data, or in the hard disk the file and the path[/color][/color]
in database.[color=blue][color=green]
> >Which option is better? When? Why?
> >
> >Thanks you for your answers.[/color]
>
> Database:
> + Files get same transactional integrity guarantees as all other data.
> + Only one thing to back up; the database, easy to keep consistent.
> - Requires a database that handles BLOB data well. YMMV, depends on DB.
> - Greatly increases data throughput in and out the database.
>
> Filesystem:
> + Web server and browsers can use caching more effectively.
> + Less load on the database.
> - Complicated to keep consistency; if you rollback an UPDATE of the[/color]
database,[color=blue]
> or it crashes, how do you keep that in sync with the filesystem?
> - Similarly with backups - you have to keep them in sync somehow.
>
> Or you could go for a hybrid; the database being the master for the data,
> stored in a BLOB, but it's written out to a file on disk on demand for[/color]
easy[color=blue]
> access by the webserver. When doing a backup, ignore the cache, just[/color]
backup the[color=blue]
> database - everything stays consistent. When doing a restore, wipe out the
> cache, and let it repopulate itself on the next accesses.
>
> --
> Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
> <http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>[/color]

[Alvaro G Vicario]

Re: store images either in database or in Hard Disk

*** cover wrote/escribió (Thu, 26 Feb 2004 23:42:48 +0000):[color=blue]
> The question is,
> we have two options to store images, either in a Database (MySQL,
> Postgres, ...) like blob data, or in the hard disk the file and the path in database.
> Which option is better? When? Why?[/color]

Database is often the bottleneck in a busy web site. Try not to overload
it.

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

[Andy Hassall]

Re: store images either in database or in Hard Disk

On Fri, 27 Feb 2004 16:17:14 +1300, "Hayden Kirk" <spam@spam.co m> wrote:
[color=blue]
>Never thought of the hybrid way. Will have to try this on my own server.
>
>Are databases ment to handle large files like this. Like if you store a
>100mb file in a database, is this recommended?
>
>How does it effect the database, im really talking about MySQL as thats the
>only DB I deal with.[/color]

I haven't heard good things about storing big BLOBs in MySQL. Something like
Oracle would take it in its stride. But if you cache in the filesystem, you
should mitigate most of the overhead, assuming you're reading much more than
you're writing. Although you probably have to be pretty clever if you're
storing files that are hundreds of megabytes each, like streaming out of the
database on the first cache 'miss' but also splitting that off to a filesystem
file for subsequent accesses at the same time; wouldn't want to have to wait
for the copy.

--
Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>