Storing images in PostgreSQL databases (again)

TIJod wrote:

I need to store a large number of images in a
PostgreSQL database. In my application, this
represents a few hundreds of thousands of images. The
size of each image is about 100-200 Ko. There is a
large turnover in my database, i.e. each image stays
about 1 week in the database, then it is deleted.

I see little value to storing the images in the database. For me that's
a general statement (I'm sure others will disagree); but especially in
your case, where you have a high volume and only want to store them for
a couple days. Why incur all the overhead of putting them in the DB?
You can't search on them or sort on them. I would just store them in
the file system and put a reference in the DB.

but this wouldrequire a more tricky implementation, and ACID-ity
would be difficult to ensure -- after all, a database
should abstract the internal storage of data, may it
be images).

I can't get excited about this. First, given the amount of overhead
you'll be avoiding, checking the return code from storing the image in
the file system seems relatively trivial. Store the image first, and if
you get a failure code, don't store the rest of the data in the DB;
you've just implemented data consistency. That assumes, of course, that
the image is the only meaningful data you have, which in most situations
is not the case. Meaning you'd want to store the rest of the data
anyway with a messages saying "image not available."

--
Guy Rouillier

Hello,

I think I'm about to ask a traditional (almost
religious) question, but I haven't been able to find a
crystal clear answer in the mailing lists so far.
Thus, here is my question:

I need to store a large number of images in a
PostgreSQL database. In my application, this
represents a few hundreds of thousands of images. The
size of each image is about 100-200 Ko. There is a
large turnover in my database, i.e. each image stays
about 1 week in the database, then it is deleted.

Of course, I need to have a relatively fast access to
each one of these images. But more importantly, I need
to periodically delete a large number of images in
batch process. Moreover, the disk space that is used
on the hard-disk to store the images should be kept as
small as possible: Precisely, after the aforementioned
batch deletions, the table that contains the images
should be immediately compacted (I cannot afford the
internal use of a "to be deleted" flag, because of the
large amount of disk space my database requires).

I have three possible implementation choices in
PostgreSQL:

1) Storing the images directly on the disk, and
storing an URI in the database tables (but this would
require a more tricky implementation, and ACID-ity
would be difficult to ensure -- after all, a database
should abstract the internal storage of data, may it
be images).

2) Storing the images in a "bytea" column (but what
about the access times, and the batch deletion
process?).

3) Storing the images as large objects (this sounds
like the best solution to me, but the documentation
lacks clarity about the scope of these large objects).

Finally, my question is what method would you
recommend to me?

I thank you much in advance for your answers!

___________________________________________________________________________
Yahoo! Mail r�invente le mail ! D�couvrez le nouveau Yahoo! Mail et son
interface r�volutionnaire.
http://fr.mail.yahoo.com

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

Encode the image on base64 and inseert on a text field

if you use Bytea it needs to be encoded and the size stored will be
more than base64 encoded

if you store the image on disk you need to keep the consistency between
the database and the file system

leonel

On Oct 4, 2006, at 12:56 PM, Guy Rouillier wrote:

TIJod wrote:

I need to store a large number of images in a
PostgreSQL database. In my application, this
represents a few hundreds of thousands of images. The
size of each image is about 100-200 Ko. There is a
large turnover in my database, i.e. each image stays
about 1 week in the database, then it is deleted.

I see little value to storing the images in the database. For me
that's
a general statement (I'm sure others will disagree); but especially in
your case, where you have a high volume and only want to store them
for
a couple days. Why incur all the overhead of putting them in the DB?
You can't search on them or sort on them. I would just store them in
the file system and put a reference in the DB.

but this wouldrequire a more tricky implementation, and ACID-ity
would be difficult to ensure -- after all, a database
should abstract the internal storage of data, may it
be images).

I can't get excited about this. First, given the amount of overhead
you'll be avoiding, checking the return code from storing the image in
the file system seems relatively trivial. Store the image first,
and if
you get a failure code, don't store the rest of the data in the DB;
you've just implemented data consistency. That assumes, of course,
that
the image is the only meaningful data you have, which in most
situations
is not the case. Meaning you'd want to store the rest of the data
anyway with a messages saying "image not available."

Combine that with an on delete trigger that adds the filename
to a deletion queue (within the transaction) and a separate
process that runs through the deletion queue occasionally
and you get something quite useable, while still being able
to use sendfile() to throw the image over the wire rather than
squeezing all that data through the database.

Cheers,
Steve

On 10/4/06, TIJod <tijod@yahoo.fr> wrote:

I think I'm about to ask a traditional (almost
religious) question, but I haven't been able to find a
crystal clear answer in the mailing lists so far.

I think the key in deciding this, in your case, is your requirement for space
reclamation:

There is a
large turnover in my database, i.e. each image stays
about 1 week in the database, then it is deleted.
... But more importantly, I need
to periodically delete a large number of images in
batch process. Moreover, the disk space that is used
on the hard-disk to store the images should be kept as
small as possible: Precisely, after the aforementioned
batch deletions, the table that contains the images
should be immediately compacted (I cannot afford the
internal use of a "to be deleted" flag, because of the
large amount of disk space my database requires).

If I understand what postgresql is doing, then DELETE will not
reclaim the space immediately. What happens internally is not
all that different from marking the space as deleted. A VACUUM
will allow that space to be reused, (assuming your free space map
is big enough), and a VACUUM FULL would be necessary to compress
the space away. All of these seem incompatible with your requirements.

I agree with another responder who suggested using the filesystem
for your images.

Jack Orenstein

Hi.

I can provide some "insight" on the difference between the two interfaces.

AFAIK, the difference is in size of the file you can store, and in the
interface you have when you want to access.

The size is not important (I think), since you are far below the limit.

For the interface, the bytea gives you a "query" based interfaces,
while largeobject are able to provide a file based interface.

With Large Object, you can avoid reading the whole object with one
read, or you can even move inside the Large Object, which can be
useful if you have large files stored.

I think there are differences also in how the space is reclaimed, but
my PostgreSQL - Fu stops here.

Regards
Marco

--
Marco Bizzarri
http://notenotturne.blogspot.com/

On 10/4/06, Guy Rouillier <guyr@masergy.com> wrote:

TIJod wrote:

I need to store a large number of images in a
PostgreSQL database. In my application, this
represents a few hundreds of thousands of images. The
size of each image is about 100-200 Ko. There is a
large turnover in my database, i.e. each image stays
about 1 week in the database, then it is deleted.

I see little value to storing the images in the database. For me that's
a general statement (I'm sure others will disagree); but especially in
your case, where you have a high volume and only want to store them for
a couple days. Why incur all the overhead of putting them in the DB?
You can't search on them or sort on them. I would just store them in
the file system and put a reference in the DB.

no, you can't search or sort on them but you can put metadata on
fields and search on that, and you can do things like use RI to delete
images that are associated with other things, etc. this would
probably fit the OP's methodogy quite nicely.

but this wouldrequire a more tricky implementation, and ACID-ity
would be difficult to ensure -- after all, a database
should abstract the internal storage of data, may it
be images).

I can't get excited about this. First, given the amount of overhead
you'll be avoiding, checking the return code from storing the image in
the file system seems relatively trivial. Store the image first, and if
you get a failure code, don't store the rest of the data in the DB;
you've just implemented data consistency. That assumes, of course, that
the image is the only meaningful data you have, which in most situations
is not the case. Meaning you'd want to store the rest of the data
anyway with a messages saying "image not available."

i think this topic is interesting and deserves better treatment than
assumptions. postgresql will toast all images over a cerain size which
is actually pretty efficient although can be a problem if your images
are really big. on the downside you have more vacuuming overhead and
postgresql can't match filesystem speed for raw writing. also you can
pretty much forget decent performance if your code that does the
actual insertion is not in c/c++ and uses the paramaterized api.

on the flip side, you have a central interface, single point of
failure and you don't have to deal with thousands or millions of image
files which can become it's own problem (although solvable). also you
don't have to write plumbing code to get something like atomicity.
PostgreSQL is getting more and more efficeint at moving large streams
in and out of the database and the answer here is not as cut and try
as you might think (historically, it was insane to even attempt it).

i'm wondering if anybody has ever attempted to manage large
collections of binary objects inside the database and has advice here.

merlin

"Merlin Moncure" <mmoncure@gmail.com> writes:

... postgresql will toast all images over a cerain size which
is actually pretty efficient although can be a problem if your images
are really big.

But any reasonable image format is compressed already (or at least, if
you are using an uncompressed format while worried about disk space then
you need some time at image processing boot camp). TOAST isn't going to
accomplish anything.

I think the arguments for keeping stuff inside the database are
(a) far easier to maintain transactional semantics for insert/delete,
and (b) easier to serve the data out to clients that aren't on the same
machine. You aren't going to find a performance win though.

regards, tom lane

On Oct 5, 2006, at 16:18 , Merlin Moncure wrote:

I see little value to storing the images in the database. For me
that's
a general statement (I'm sure others will disagree); but
especially in
your case, where you have a high volume and only want to store
them for
a couple days. Why incur all the overhead of putting them in the DB?
You can't search on them or sort on them. I would just store them in
the file system and put a reference in the DB.

no, you can't search or sort on them but you can put metadata on
fields and search on that, and you can do things like use RI to delete
images that are associated with other things, etc. this would
probably fit the OP's methodogy quite nicely.

I second this sentiment; there is a lot to be said for keeping your
data together in a unified storage/retrieval system with ACID
semantics. There is nothing inherently wrong about this model.

[...]

i'm wondering if anybody has ever attempted to manage large
collections of binary objects inside the database and has advice here.

We have a production system containing 10,000 images (JPEG and PNG of
various sizes) totaling roughly 4GBs. We have Lighttpd running
against a couple of Rails processes which crop, scale and convert
images on the fly using ImageMagick; converted images are cached in
the file system and subsequently served directly by Lighttpd.
Functionally I have absolutely no quibbles with this system;
PostgreSQL stores the data smoothly and everything works as designed.

Performance-wise, I'm not sure; the amount of data seems to put a
certain load on the database server, though it's impossible to tell
how much. Backups are hell, taking hours and hours to do just a
single dump of the database. Rails' PostgreSQL adapter uses SQL for
inserts and quotes every byte as an octal escape sequence; storing a
single image can take several seconds. Single-image retrieval is
similarly slow, but since the adapter uses bindings that talk
directly to libpq4, I believe it's caused by the overall load on the
database.

Because of this, we see no recourse but to move the images into the
file system. Since our cluster consists of three separate machines
all running the same Rails application, with no dedicated box
handling the image storage, such a solution requires the use of NFS
or other type of shared storage for centralized image storage; we're
not sure yet about what we will end up with.

Alexander.

On 10/5/06, Alexander Staubo <alex@purefiction.net> wrote:

On Oct 5, 2006, at 16:18 , Merlin Moncure wrote:

i'm wondering if anybody has ever attempted to manage large
collections of binary objects inside the database and has advice here.

We have a production system containing 10,000 images (JPEG and PNG of
various sizes) totaling roughly 4GBs. We have Lighttpd running
against a couple of Rails processes which crop, scale and convert
images on the fly using ImageMagick; converted images are cached in
the file system and subsequently served directly by Lighttpd.
Functionally I have absolutely no quibbles with this system;
PostgreSQL stores the data smoothly and everything works as designed.

Performance-wise, I'm not sure; the amount of data seems to put a
certain load on the database server, though it's impossible to tell
how much. Backups are hell, taking hours and hours to do just a

i admit, backups could be a problem. maybe pitr is the answer. (dump
style backups are a problem for any big database)

single dump of the database. Rails' PostgreSQL adapter uses SQL for
inserts and quotes every byte as an octal escape sequence; storing a

ouch...the only way to do this quickly imo is to send in raw binary
data directly to the database using parameterized...this eliminates
both the escaping and the unescaping step. likewise the data should be
pulled out binary (this will liekly be several times faster).

single image can take several seconds. Single-image retrieval is
similarly slow, but since the adapter uses bindings that talk
directly to libpq4, I believe it's caused by the overall load on the
database.

Because of this, we see no recourse but to move the images into the
file system. Since our cluster consists of three separate machines
all running the same Rails application, with no dedicated box
handling the image storage, such a solution requires the use of NFS
or other type of shared storage for centralized image storage; we're
not sure yet about what we will end up with.

cant fault you for that decision, web applications are usually pretty
aggressive on caching. they also ususally fit pretty well in the
various replication technlogies as well...something to consider.

merlin

marco.bizzarri@gmail.com ("Marco Bizzarri") writes:

Hi.

I can provide some "insight" on the difference between the two interfaces.

AFAIK, the difference is in size of the file you can store, and in the
interface you have when you want to access.

The size is not important (I think), since you are far below the limit.

For the interface, the bytea gives you a "query" based interfaces,
while largeobject are able to provide a file based interface.

With Large Object, you can avoid reading the whole object with one
read, or you can even move inside the Large Object, which can be
useful if you have large files stored.

I think there are differences also in how the space is reclaimed, but
my PostgreSQL - Fu stops here.

That seems a reasonable explanation...

There is another thing that is worth observing for the "store data as
an ordinary column" idea...

Very Large Columns are stored in what is called a TOAST table.

Consider the following table:

tbig@[local]:5832=# \d foo
Table "public.foo"
Column | Type | Modifiers
----------+---------+--------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
name | text |
contents | text |
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)

tbig@[local]:5832=# vacuum verbose foo;
INFO: vacuuming "public.foo"
INFO: index "foo_pkey" now contains 3 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "foo": found 0 removable, 3 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 12 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_113203"
INFO: index "pg_toast_113203_index" now contains 54 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_113203": found 0 removable, 54 nonremovable row versions in 14 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 5 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

tbig@[local]:5832=# select id, name, length(contents) from foo;
id | name | length
----+-------+---------
1 | file1 | 1860342
2 | file2 | 1860342
3 | file3 | 1860342
(3 rows)

The "contents" columns contain 1.8MB of data.

Note that if you run a query that doesn't access the "contents"
columns, they never get drawn in. What the table 'physically' looks
like is rather like:

id | name | toast pointer
----+-------+--------------
1 | file1 | 1341234
2 | file2 | 3241341
3 | file3 | 3421892

[where those pointers point into the "toast" table].

You can get decent efficiency out of that...
--
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://cbbrowne.com/info/linuxdistributions.html
"...[Linux's] capacity to talk via any medium except smoke signals."
-- Dr. Greg Wettstein, Roger Maris Cancer Center

"Merlin Moncure" <mmoncure@gmail.com> writes:

... postgresql will toast all images over a cerain size which
is actually pretty efficient although can be a problem if your images
are really big.

But any reasonable image format is compressed already (or at least, if
you are using an uncompressed format while worried about disk space then
you need some time at image processing boot camp). TOAST isn't going to
accomplish anything.

I think the arguments for keeping stuff inside the database are
(a) far easier to maintain transactional semantics for insert/delete,
and (b) easier to serve the data out to clients that aren't on the same
machine. You aren't going to find a performance win though.

(c) easy to replicate
(d) easy to load balancing

leonel

I appologize for duplicate posting, but I am not sure
if this is getting posted to the news group.

Problem:
PostgreSQL "Service" is not runing on a Linux Box,
but I have a database on the Linux Box, that I want
to relocate to another machine, Windows or Linux.

Question:
What files do I need to transfer to get this to work?

Brian J. Erickson <ericksbr@infocon-inc.com> schrieb:

I appologize for duplicate posting, but I am not sure
if this is getting posted to the news group.

Problem:
PostgreSQL "Service" is not runing on a Linux Box,

Why not?

but I have a database on the Linux Box, that I want
to relocate to another machine, Windows or Linux.

Question:
What files do I need to transfer to get this to work?

Start the database, do a pg_dumpall, and build on the new machine the db
from this dump.

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�

Merlin Moncure wrote:

i'm wondering if anybody has ever attempted to manage large
collections of binary objects inside the database and has advice here.

We have designed and built an image library using Postgres and NFS
servers which currently holds 1.4 million images totalling more than 250Gb.

Not having the images in the database keeps the database lightweight,
and allows us to use Slony to replicate - something we could not do with
blob data (I believe).

If you want to scale up, I think it is worth keeping the images out of
the database. Just use the database to store the filename/location and
meta data associated with each image. Otherwise the images bloat the
database...

Backups are small (the meta data is lightweight), we can use slony for
data redundancy. The NFS servers are rsnyc'd to physically back the
images up.

This setup means that we have to have a layer above the database which
keeps the database and images on the file system in sync. The database
and java layer above with the NFS servers have effectively allowed us to
build a virtualized file system for storing images and meta data. We can
plug in more NFS shares as our storage requirements grow, and the
database keeps tracks of disk usage within each physical disk volume.

This setup appears to give us good performance and hasn't been too
difficult to maintain or administer.

For a setup this size which is growing daily, storing the images in the
database was not really a sensible option. Hoever, with a smaller setup,
keeping the images in the database definitely keeps things simpler though...

John

I have seen several posts pertaining to the "overhead" difference in storing
in a db table versus the file system. What is this difference?

I also think the decision as store in a db table or file system is looking
at how the files will be accessed. If all the clients are on the same
network as the server using the servers file system is viable. If though
your clients are on different networks then storing in the db may be a
better option.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Guy Rouillier
Sent: Wednesday, October 04, 2006 3:57 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)

TIJod wrote:

I need to store a large number of images in a
PostgreSQL database. In my application, this
represents a few hundreds of thousands of images. The
size of each image is about 100-200 Ko. There is a
large turnover in my database, i.e. each image stays
about 1 week in the database, then it is deleted.

I see little value to storing the images in the database. For me that's
a general statement (I'm sure others will disagree); but especially in
your case, where you have a high volume and only want to store them for
a couple days. Why incur all the overhead of putting them in the DB?
You can't search on them or sort on them. I would just store them in
the file system and put a reference in the DB.

but this wouldrequire a more tricky implementation, and ACID-ity
would be difficult to ensure -- after all, a database
should abstract the internal storage of data, may it
be images).

I can't get excited about this. First, given the amount of overhead
you'll be avoiding, checking the return code from storing the image in
the file system seems relatively trivial. Store the image first, and if
you get a failure code, don't store the rest of the data in the DB;
you've just implemented data consistency. That assumes, of course, that
the image is the only meaningful data you have, which in most situations
is not the case. Meaning you'd want to store the rest of the data
anyway with a messages saying "image not available."

--
Guy Rouillier

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

Problem:
PostgreSQL "Service" is not runing on a Linux Box,

Why not?

The file "/etc/mtab" has been corrupted, when start
postgresql the "Service" dies because of an "input/output"
error.

Therefore, "pg_dumpall' won't work.

----- Original Message -----
From: "Andreas Kretschmer" <akretschmer@spamfence.net>
To: <pgsql-general@postgresql.org>
Sent: Thursday, October 05, 2006 8:52 AM
Subject: Re: [GENERAL] PostgreSQL Database Transfer between machines(again)

Brian J. Erickson <ericksbr@infocon-inc.com> schrieb:

I appologize for duplicate posting, but I am not sure
if this is getting posted to the news group.

Problem:
PostgreSQL "Service" is not runing on a Linux Box,

Why not?

but I have a database on the Linux Box, that I want
to relocate to another machine, Windows or Linux.

Question:
What files do I need to transfer to get this to work?

Start the database, do a pg_dumpall, and build on the new machine the db
from this dump.

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

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

On 10/05/06 14:50, Brian J. Erickson wrote:

Problem:
PostgreSQL "Service" is not runing on a Linux Box,

Why not?

The file "/etc/mtab" has been corrupted, when start
postgresql the "Service" dies because of an "input/output"
error.

Therefore, "pg_dumpall' won't work.

Doesn't /etc/mtab get recreated at boot?

And since it's a text file, can't someone fix it with $EDITOR?

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

iD8DBQFFJXFbS9HxQb37XmcRAqSZAJsG2iHXySUdiooXVun5hJsX6zfEPwCggAEb
OjDEKQ5VErzaRv+UcH0P/Kk=
=vs7r
-----END PGP SIGNATURE-----

Leonel Nunez wrote:

I think the arguments for keeping stuff inside the database are
(a) far easier to maintain transactional semantics for insert/delete,
and (b) easier to serve the data out to clients that aren't on the
same machine. You aren't going to find a performance win though.

(c) easy to replicate

I don't follow that. Suppose your database minus images is 3 GB, and
your images are another 50 gigabytes. Which is easier to replicate, 3
or 53? Put the images on a file server, separate from the DBs - no need
to replicate them. And if you do want to copy (e.g., your replicated DB
is in a remote location), you can do a simple file system copy to the
corresponding remote file server.

(d) easy to load balancing

If you're load balancing, both databases are in the same location,
right? In which case you only need one set of images on a central file
server.

--
Guy Rouillier

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.