From: | Jean-Christophe Roux <jcxxr(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing images in PostgreSQL databases (again) |
Date: | 2006-10-05 23:29:01 |
Message-ID: | 20061005232901.58014.qmail@web35311.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Staubo | 2006-10-05 23:35:04 | Re: Storing images in PostgreSQL databases (again) |
Previous Message | AgentM | 2006-10-05 23:27:22 | Re: PostgreSQL Database Transfer between machines(again) |