Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [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 > > I do with ByteA datatype leonel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
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? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
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.4Mb 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. On 2/25/07, Tomas Vondra <[EMAIL PROTECTED]> 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). 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Tomas Vondra <[EMAIL PROTECTED]> 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° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
On 2/26/07, Joris Dobbelsteen <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Andreas Kretschmer wrote: Alain Roger <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
See the discussion "[GENERAL] Database versus filesystem for storing images" earlier on the List. It started at 31 december 2006 and ended 9 januari 2007. It goes trough all/most pro/con arguments for different options. - Joris From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Alain Roger <[EMAIL PROTECTED]> 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° ---(end of broadcast)--- TIP 6: explain analyze is your friend
[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
