On 27/11/2009 7:04 PM, Thom Brown wrote: > But now we wish to redesign our various image upload systems into one > system which will also make images available across various > sites/companies and therefore servers. So one solution is to store > images in a database. What I'm wondering is if this is an appropriate > solution? What are the downsides? Could we expect severe performance > hits? It depends a bit on your needs. If you need facilities provided by the database, such as transactional updates of images and associated metadata, then it's probably a good choice. Otherwise, you'll be paying the costs associated with storing blobs in a database without gaining much. There *are* costs to using a database over a network file system - mostly increased management. It's harder to do incremental backups and harder to work with those incremental backups (see PITR and continuous archiving) than it is with a regular file system. Storage requirements may be slightly increased. Deletion of images requires additional cleanup work (VACUUM) to free the space. Etc. With a database, you can't use the sendfile(...) system call or the like to transmit the data, so there's more work for the system when transmitting query results. It's also not generally transmitted as raw binary, but hex- or octal- encoded, which can be ugly to work with. You can request it as pure binary instead if you're using libpq directly, though. Why don't you use a HTTP-based image server? It's pretty low overhead, is easily cached by commonly available tools, etc. Failing that, there are plenty of network file systems out there (NFSv4 and CIFS come to mind) that would do the job. > To help mitigate the issue of many sites continuously querying a > database for images, we'd implement a caching system, so images would > only be queried from the database probably around once an hour. Memcached ? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
