Re: 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