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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
