Re: Store images on database ou in external files?

Andre Lopes wrote on 01.11.2009 15:20:
> I need to choose a way to store image files.
>
> This database will take care of an Add Classifieds website with low
> ammount of Ads, maximum of 20 new ads per month.
>
> It is good idea for this kind of website to store image files in the
> database?

This is a question that pops up regularly and is equivalent to the question "What's your favorite color?" (i.e. it depends largely on personal taste)

I prefer having (small to medium) binaries/images in the database because

- I get transactional consistency when storing the binaries
- I don't have to worry about cleaning upt the filesystem when deleting rows from the table
- I don't have to worry about duplicating access privileges between the DB and the file system
- I don't have to worry about the filesystem layout (you do *not* want 100.000 files in a single directory)
- I don't have to worry what else to backup once the DB backup is done

Having said that, there are also some very good reasons to *not* store images in the database:

- in a web-application environment those images cannot be served by the web-server (e.g. Apache) directly, which might be a killer for a high traffic web site
- backup & restore might get very annoying if the database size increases. file system tools are usually a *lot* better in doing incremental backups
- if you have applications that cannot deal with images in the database e.g. if you want to manipulate the images using ImageMagick

You need to judge for yourself which of those reasons apply to your environment.

> Another question... How to do inserts by the command line to insert
> images files to a database field?

That depends on your command line client.

With psql I think the only option is the copy command having encoded binaries in the input file. But as I'm far from being a psql expert, there might be other options that I'm not aware of (I very rarely use psql)

I'm using my own tool [1] that allows the following syntax:
INSERT INTO the_table
(id, image_data)
VALUES
(42, {$blobfile=c:/temp/postgres.gif});

Regards
Thomas

[1] http://www.sql-workbench.net