Re: Storing images in PostgreSQL databases (again)

FWIW, the company I work for stores its terrabytes of imagery on disk, using a database to track them (spatial coordinates, metadata, location, etc.); I have worked on projects in which we stored images in a database (blobs in Informix) and it worked fine. Both approaches can have their merits.

Personally, I'd do thumbnails on intake and handle them on their own, either on disk on in the db. But I have preference for a preprocessing data so runtime response is maximized.

Assuming you don't have access to a blade/suite of functions that allow you to use the image in the database as a useful data type (Informix at least used ot have a blade that did this), you can still use informtation about the image as a primary key, to wit, a sufficiently large hash (MD5 for instance). Of course, there's time to create the hash which might be an issue in a high volume system. Extending a hash with some other data (date ?) can considerably decrease the chance of collisions. It's still a longish key, but workable I suspect (untested, we used an artificial key, a serial).

$0.02 worth ...

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org on behalf of Jean-Christophe Roux
Sent: Thu 10/5/2006 4:29 PM
To: pgsql-general(at)postgresql(dot)org
Cc:
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)

Hi,
If the database had built-in functions to manipulate images (make a thumbnail, add text ont it.., make a montage of two pictures) and I could write something like
select thumbnail(image_field, 100, 100) from images_table
that would be a good reason to go the db route versus the filesystem route. A database does more then storing data, it makes convenient to play with them. Once my pictures are stored in the database, how do I make thumbnails for instance? Maybe the solution already exists; I am curious here. Is there a way to integrate ImageMagick into a PostgreSQL workflow?
By the way, is it practical to set a bytea column (containing pictures) as primary key? That would severely slow down many operations I guess.
JCR

----- Original Message ----
From: Alexander Staubo <alex(at)purefiction(dot)net>
To: pgsql-general(at)postgresql(dot)org
Cc: DEV <dev(at)umpa-us(dot)com>
Sent: Thursday, October 5, 2006 6:30:07 PM
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)

On Oct 5, 2006, at 19:47 , DEV wrote:

> I have seen several posts pertaining to the "overhead" difference
> in storing
> in a db table versus the file system. What is this difference?

Well, there's not much space overhead to speak of. I tested with a
bunch of JPEG files:

$ find files | wc -l
2724
$ du -hs files
213M files

With an empty database and the following schema:

create table files (id serial, data bytea);
alter table files alter column data set storage external;

When loaded into the database:

$ du -hs /opt/local/var/db/postgresql/base/16386
223M /opt/local/var/db/postgresql/base/16386

On my MacIntel with PostgreSQL from DarwinPorts -- a configuration/
port where PostgreSQL performance does *not* shine, incidentally --
PostgreSQL can insert the image data at a pretty stable 2.5MB/s. It's
still around 30 times slower than the file system at reading the
data. (I would love to run a benchmark to provide detailed timings,
but that would tie up my laptop for too long.)

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

-------------------------------------------------------
Click link below if it is SPAM gsw(at)globexplorer(dot)com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=452593f911951950113718&user=gsw(at)globexplorer(dot)com&retrain=spam&template=history&history_page=1"
!DSPAM:452593f911951950113718!
-------------------------------------------------------