2

I want to store images and other documents in a PostgreSQL table, along with a thumbnail of each image. The original document and the thumbnail would be two separate bytea fields. PostgreSQL is running on Linux.

Because the image data could come from several different applications, I'd like to have the image processing code (for creating the thumbnail) within PostgreSQL as a function, rather than each individual application having to create the thumbnail. Is there any way for PostgreSQL to be able to create a thumbnail of an image?

Rob McDonell
  • 1,309
  • 9
  • 15

5 Answers5

6

PostPic sounds like the Postgres extension you are looking for.

As described in their wiki you will be able to resize and create thumbnails with ease:

FUNCTION thumbnail(i image, size INT) RETURNS image

FUNCTION resize(i image, w INT, h INT) RETURNS image

ChrisB
  • 2,497
  • 2
  • 24
  • 43
2

May I suggest instead that all your applications instead use a common interface or an API?

For my photography platform, I have an Upload API that everything goes through, although there are about 4 different ways to actually perform an upload (browser, desktop, phone, and software plugin). The Upload API then has the functionality to manipulate the images with some powerful and performant libraries (I'm using Python, so PIL), and then save them to the database (actually, I'm saving to a file system and referencing them in the DB, but the idea is the same).

An alternative is that a thumbnail generator service could reside outside of your database, and then occasionally loop through all your rows that don't yet have a thumbnail generated, generate one, and then store it back into Postgres.

You're asking for a world of performance hurt if you do end up doing image manipulation inside of Postgres, particularly on the memory side.

Jordan
  • 31,971
  • 6
  • 56
  • 67
0

Is there any way for PostgreSQL to be able to create a thumbnail of an image?

No. PostgreSQL is a database engine, it just allows to store and retrieve data, and to some extent manipulate it. But doing some image processing inside it would go way too far. Image resizing should be done outside the database.

And, as other commenter says, consider also the option of not storing the image data inside the database - only some path or locator. This is optional, but frequently it's more practical. Read some related questions: Storing Images in DB - Yea or Nay? , Storing a small number of images: blob or fs?

Community
  • 1
  • 1
leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • 2
    Of course PostgreSQL is a database engine, but you can write functions, using a variety of languages, to do just about anything. And we have considered using a filesystem - indeed that's what we're doing now - but that has a whole lot of different challenges for replication and maintaining referential integrity. – Rob McDonell Apr 29 '11 at 09:18
0

I've only ever hacked a few trivial functions in perl, but chances are there are plenty of appropriate libraries if you install pl/perlu.

If pl/perl2 is not an option, configure pl/perl accordingly:

plperl.use_strict = true
plperl.on_init = 'use stuff1; use stuff2;'
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
-2

Simplest answer: Do not store images in database. It's slow, ineffective, doesn't scale, makes backups take longer.

And when you'll store them on filesystem - just add middleware to resize them, or a simple daemon which will resize all new images.

  • 4
    Our experience in Postgres is that it's not slow, it's quite effective, it does scale, and it makes backups easier. And it maintains the referential integrity of the file data and metadata. – Rob McDonell Apr 29 '11 at 09:22
  • 4
    +1 to Rob's reply. Fast, effective, scales, makes backups *much* easier, and maintains the referential integrity of the file. Just... not in a bytea field in the same table of the metadata. :-) – Denis de Bernardy May 08 '11 at 15:00