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
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@xxxxxxxxxxxxxxx>
To: pgsql-general@xxxxxxxxxxxxxx
Cc: DEV <dev@xxxxxxxxxxx>
Sent: Thursday, October 5, 2006 6:30:07 PM
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)
From: Alexander Staubo <alex@xxxxxxxxxxxxxxx>
To: pgsql-general@xxxxxxxxxxxxxx
Cc: DEV <dev@xxxxxxxxxxx>
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
> 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
