Thread: Best way to store and retrieve photo from PostGreSQL

Hi,

I would like to store picture in my DB and after to display them on my PHP pages.

What is the best solution for that ?

thanks a lot

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

Alain Roger <raf.news@gmail.com> schrieb:

> Hi,
>
> I would like to store picture in my DB and after to display them on my PHP
> pages.
>
> What is the best solution for that ?

Store the pictures in the filesystem and only the path, description and
other metadata in the database. My suggestion ;-)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

---

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alain Roger
Sent: zondag 25 februari 2007 16:57
To: pgsql-general@postgresql.org
Subject: [GENERAL] Best way to store and retrieve photo from PostGreSQL

Hi,

I would like to store picture in my DB and after to display them on my PHP pages.

What is the best solution for that ?

thanks a lot

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

Andreas Kretschmer wrote:
> Alain Roger <raf.news@gmail.com> schrieb:
>
>
>> Hi,
>>
>> I would like to store picture in my DB and after to display them on my PHP
>> pages.
>>
>> What is the best solution for that ?
>>
>
> Store the pictures in the filesystem and only the path, description and
> other metadata in the database. My suggestion ;-)

One problem with this approach, is that you move the responsibility for
maintaining data integrity from the database, to the application code
using the database. You introduce 2 points of failure, where you risk
adding the image to the filesystem, without it being added to the
database, and the other way around. The same issue appears with
deleting/updating.
In addition, if there is a large amount of images, you probably do not
want to keep all images in the same folder. So you introduce extra
complexity in order to maintain a directory structure, and some sort of
logic for sorting the images in this structure.

--
Tommy

> Store the pictures in the filesystem and only the path, description and
> other metadata in the database. My suggestion ;-)
>
>
> Andreas
>
Don't do that - the filesystems are not transactional (at least not the
usual ones), so you'll lose the ability to use transactions. Imagine
what happens when you do an unlink() and then the transaction fails for
some reason - there's no way to 'rollback' the filesystem operation.
I've seen this solution (storing images in filesystem) mostly in MySQL
applications, but that's because of (a) lack of transactions in MySQL
and (b) somehow sub-optimal handling of binary data as MySQL loads all
the data even if it's not needed (this was true for MySQL 3.23 - I'm not
sure about the current releases).

Anyway, I do recommend storing images in the database, using a 'bytea'
column for the binary data (and load them only if reallly needed, using
proper projection). You can do some benchmarks, but I've never head
performance problems with it on PostgreSQL and the ability to use
transactions was invaluable (it saved us hundreds of hours when the
machine went down for some reason).

Tomas

On 2/26/07, Joris Dobbelsteen <Joris@familiedobbelsteen.nl> wrote:

> See the discussion "[GENERAL] Database versus filesystem for
> storing images" earlier on the List.
And man, do I wish people used threading-capable mailers
and didn't do tofu-posts. :/

> - Joris
Cheers,
Andrej

Tomas Vondra <tv@fuzzy.cz> schrieb:

>
> >Store the pictures in the filesystem and only the path, description and
> >other metadata in the database. My suggestion ;-)
> >Andreas
> >
> Don't do that - the filesystems are not transactional (at least not the
> usual ones), so you'll lose the ability to use transactions. Imagine what

There are pros and cons, i know. If someone need transactional control
on pictures, then store them in the database, right. It's also simpler
to do a complete backup, i know.

On the other side, if you have only a desktop-computer and you will be
able to look at the pictures with other programms (for instance), it's
simpler if you have the pictures in the filesystem. And, large binarys
in the database is more overhead for the database, the database itself
stores the binarys also in the filesystem and every read involves the
filesystem and the database.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

This is what i did (to stored pictures in DB)...

but i use the following process :

1.store picture on my localhost db
2. export as SQL statement all pictures from my table :-(  ===> it was 7.4 Mb
3. import to the remote db hosted by a company.

is there an easy way to store image into a hosted DB ? because i can not use any tool to directly insert into DB the pictures :-(
they only accept pgadmin interface...

thanks a lot,

Al.




--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

Hello Tomas,

Tomas Vondra wrote:
>
>> Store the pictures in the filesystem and only the path, description and
>> other metadata in the database. My suggestion ;-)
>>
>>
>> Andreas

> Anyway, I do recommend storing images in the database, using a 'bytea'
> column for the binary data (and load them only if reallly needed, using
> proper projection). You can do some benchmarks, but I've never head


Would you say the same if the images were each 30MB+ and there were
thousands of them, possibly needing to be stored on several hardwares?
Thanks

Tomas Vondra wrote:
>
>> Store the pictures in the filesystem and only the path, description and
>> other metadata in the database. My suggestion ;-)
>>
>>
>> Andreas
>>
> Don't do that - the filesystems are not transactional (at least not
> the usual ones), so you'll lose the ability to use transactions.
> Imagine what happens when you do an unlink() and then the transaction
> fails for some reason - there's no way to 'rollback' the filesystem
> operation. I've seen this solution (storing images in filesystem)
> mostly in MySQL applications, but that's because of (a) lack of
> transactions in MySQL and (b) somehow sub-optimal handling of binary
> data as MySQL loads all the data even if it's not needed (this was
> true for MySQL 3.23 - I'm not sure about the current releases).
>

Dumb question: the picture data is written twice, right?  Once in the
WAL and once into the table?  So the argument can be reduced to:

1)  Load into tables for transactional support, con is the write-time hit
2)  Load into filesystem for faster load, but you have to provide
integrity by another route



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this
::question: do you worry about how to throw away a garbage can?

On Feb 25, 2007, at 9:26 AM, Tomas Vondra wrote:

>
>> Store the pictures in the filesystem and only the path,
>> description and
>> other metadata in the database. My suggestion ;-)
>>
>>
>> Andreas
>>
> Don't do that - the filesystems are not transactional (at least not
> the usual ones), so you'll lose the ability to use transactions.
> Imagine what happens when you do an unlink() and then the
> transaction fails for some reason - there's no way to 'rollback'
> the filesystem operation. I've seen this solution (storing images
> in filesystem) mostly in MySQL applications, but that's because of
> (a) lack of transactions in MySQL and (b) somehow sub-optimal
> handling of binary data as MySQL loads all the data even if it's
> not needed (this was true for MySQL 3.23 - I'm not sure about the
> current releases).

You just need to implement it correctly. I've done this by using an
in-database delete queue that's polled by an external process to
delete the image files.

For external image files you don't need to be perfectly
transactional, as long as the failure mode is occasionally leaving a
file in place when it shouldn't be, as all that does is leak a little
filesystem space which can easily be recovered by a periodic task.

> Anyway, I do recommend storing images in the database, using a
> 'bytea' column for the binary data (and load them only if reallly
> needed, using proper projection). You can do some benchmarks, but
> I've never head performance problems with it on PostgreSQL and the
> ability to use transactions was invaluable (it saved us hundreds of
> hours when the machine went down for some reason).

The overhead of serving images from postgresql is much higher than
serving them directly from the filesystem (as is the overhead of
backing the data up). For simple, static images the advantages of
full transaction support on the data as well as the metadata are
likely to be fairly minimal.

Both approaches work, which is going to be better will depend on the
details of what you're doing with the images and how far you want it
to scale. (But I can't think of any case where *my* preferred
approach would be to keep them in the DB).

Cheers,
   Steve

> Hi,
>
> I would like to store picture in my DB and after to display them on my PHP
> pages.
>
> What is the best solution for that ?
>
> thanks a lot
>
> --
> Alain
> ------------------------------------
> Windows XP SP2
> PostgreSQL 8.1.4
> Apache 2.0.58
> PHP 5
>
>


I do with  ByteA datatype

leonel

We are storing images as files with names/paths, metadata, etc. in
PostGIS (as our images are often associated with a location, being
things like field specimen images, or seabed pictures we use PostGIS to
store & query the location info, and UMN Mapserver as the engine driving
a map based image selection tool - www.atlasmd.com)

Atlas originally used Firebird RDBMS for image data, but was migrated to
PostGIS to fully support OGC compliant spatial functionality.

The additional step we have implemented to ensure a two way link between
the images & the data is to write the db primary key to an EXIF field in
each image as it is uploaded. This means that given an image, we can
access it's data from the DB, (by looking up the key which is embedded
in the image) instead of the usual approach which only permits a link
the other way.

Cheers,

  Brent Wood