SEO

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 filesystemroute. 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@purefiction.net>
To: pgsql-general@postgresql.org
Cc: DEV <dev@umpa-us.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 thisdifference?

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

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

On my MacIntel with PostgreSQLfrom 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

On Oct 6, 2006, at 01:29 , Jean-Christophe Roux wrote:

> 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.

Why would you? It's possible, but completely impractical, since image
data typically exceeds the index page size. Moreover, are you really
going to retrieve an image row by its image data?

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

With Python and the python imaging library you can do this :

image is a bytea field

curs = conn.cursor ()
curs.execute( "select image from images where name = %s" ,(thename, ))
row = curs.fetchone()
if row:
im = Image.open (StringIO.StringIO(row[0]))
im.thumbnail (160,120 )
imagetmp = StringIO.StringIO()
im.save ( imagetmp , "JPEG")
print ("Content-type: image/jpeg\n\n")
print ( imagetmp.getvalue())




with this you get your image to the browser thumbnailed
without touch the filesystem

that's all


Leonel






---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Leonel Nunez" <lnunez@enelserver.com> wrote:
>
> > 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.

<snip>

> With Python and the python imaging library you can do this :
>
> image is a bytea field
>
> curs = conn.cursor ()
> curs.execute( "select image from images where name = %s" ,(thename, ))
> row = curs.fetchone()
> if row:
> im = Image.open (StringIO.StringIO(row[0]))
> im.thumbnail (160,120 )
> imagetmp = StringIO.StringIO()
> im.save ( imagetmp , "JPEG")
> print ("Content-type: image/jpeg\n\n")
> print ( imagetmp.getvalue())

I think part of the point, which you missed, is the convenience of having
the thumbnailing as part of the SQL language by making it a stored
procedure.

I did a presentation for WPLUG not too long ago where I created C
functions in Postgres compiled against the GSOAP library that allowed
you to make simple SOAP calls in SQL within PostgreSQL. Neat stuff.

The problem with creating those kinds of functions is the CPU overhead.
We'll be generating the thumbnails and storing them in a "thumbnail"
field in the record, so we don't have to regenerate the thumbnail each
time it's needed.

BTW: our reason for keeping the thumbnails in fields is so they can be
replicated with Slony along with the rest of the database.

--
Bill Moran

We meddle. People don't like to be meddled with. We tell them what to do,
what to think. Don't run, don't walk. We're in their homes and in their
heads and we haven't the right.

River Tam


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> "Leonel Nunez" <lnunez@enelserver.com> wrote:
>>
>> > 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.
>
> <snip>
>
>> With Python and the python imaging library you can do this :
>>
>> image is a bytea field
>>
>> curs = conn.cursor ()
>> curs.execute( "select image from images where name = %s" ,(thename, ))
>> row = curs.fetchone()
>> if row:
>> im = Image.open (StringIO.StringIO(row[0]))
>> im.thumbnail (160,120 )
>> imagetmp = StringIO.StringIO()
>> im.save ( imagetmp , "JPEG")
>> print ("Content-type: image/jpeg\n\n")
>> print ( imagetmp.getvalue())
>
> I think part of the point, which you missed, is the convenience of having
> the thumbnailing as part of the SQL language by making it a stored
> procedure.
>
> I did a presentation for WPLUG not too long ago where I created C
> functions in Postgres compiled against the GSOAP library that allowed
> you to make simple SOAP calls in SQL within PostgreSQL. Neat stuff.
>
> The problem with creating those kinds of functions is the CPU overhead.
> We'll be generating the thumbnails and storing them in a "thumbnail"
> field in the record, so we don't have to regenerate the thumbnail each
> time it's needed.
>
> BTW: our reason for keeping the thumbnails in fields is so they can be
> replicated with Slony along with the rest of the database.
>
> --
> Bill Moran
>
> We meddle. People don't like to be meddled with. We tell them what to
> do,
> what to think. Don't run, don't walk. We're in their homes and in their
> heads and we haven't the right.
>
> River Tam
>
>



you are 100% right

Leonel


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/05/06 19:41, Bill Moran wrote:
> "Leonel Nunez" <lnunez@enelserver.com> wrote:
>>> 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.
>
> <snip>
>
>> With Python and the python imaging library you can do this :
>>
>> image is a bytea field
>>
>> curs = conn.cursor ()
>> curs.execute( "select image from images where name = %s" ,(thename, ))
>> row = curs.fetchone()
>> if row:
>> im = Image.open (StringIO.StringIO(row[0]))
>> im.thumbnail (160,120 )
>> imagetmp = StringIO.StringIO()
>> im.save ( imagetmp , "JPEG")
>> print ("Content-type: image/jpeg\n\n")
>> print ( imagetmp.getvalue())
>
> I think part of the point, which you missed, is the convenience of having
> the thumbnailing as part of the SQL language by making it a stored
> procedure.

Would untrusted pl/python be able to do this?

> I did a presentation for WPLUG not too long ago where I created C
> functions in Postgres compiled against the GSOAP library that allowed
> you to make simple SOAP calls in SQL within PostgreSQL. Neat stuff.
>
> The problem with creating those kinds of functions is the CPU overhead.
> We'll be generating the thumbnails and storing them in a "thumbnail"
> field in the record, so we don't have to regenerate the thumbnail each
> time it's needed.
>
> BTW: our reason for keeping the thumbnails in fields is so they can be
> replicated with Slony along with the rest of the database.
>


- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFJbVOS9HxQb37XmcRAoFXAKCy8+MIMuWCAaxeJyvijA VGP/RwhACgzO3T
Q1pruQgrFSvsdiUEwtLvgDk=
=XZD2
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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@postgresql.org on behalf of Jean-Christophe Roux
Sent: Thu 10/5/2006 4:29 PM
To: pgsql-general@postgresql.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@purefiction.net>
To: pgsql-general@postgresql.org
Cc: DEV <dev@umpa-us.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@globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=452593f911951950113718&user= gsw@globexplorer.com&retrain=spam&template=history &history_page=1"
!DSPAM:452593f911951950113718!
-------------------------------------------------------




---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Am 2006-10-05 21:22:04, schrieb Gregory S. Williamson:

> a sufficiently large hash (MD5 for instance). Of course, there's

I do this already but have problems since I have
stored arround 130 million files on a server...

> time to create the hash which might be an issue in a high volume
> system. Extending a hash with some other data (date ?) can

MD5 hashes are 32 Bytes long, maybe they change
it to 64 Bytes?

I have already over 2000 collisions and checked
it, that the files are NOT the same.

> considerably decrease the chance of collisions. It's still a
> longish key, but workable I suspect (untested, we used an
> artificial key, a serial).

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSM LinuxMichi
0033/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

In response to Michelle Konzack <linux4michelle@freenet.de>:

> Am 2006-10-05 21:22:04, schrieb Gregory S. Williamson:
>
> > a sufficiently large hash (MD5 for instance). Of course, there's
>
> I do this already but have problems since I have
> stored arround 130 million files on a server...
>
> > time to create the hash which might be an issue in a high volume
> > system. Extending a hash with some other data (date ?) can
>
> MD5 hashes are 32 Bytes long, maybe they change
> it to 64 Bytes?
>
> I have already over 2000 collisions and checked
> it, that the files are NOT the same.

Try sha1 or sha256.

--
Bill Moran
Collaborative Fusion Inc.

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

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