Handling of images via Postgressql

--=-ao1qcOrDTPbsNvK2j78A
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

How does PostGreSQL perform in case we have to store and use huge no of
images:

Eg 4000 =E2=80=93 10000 images, each approx 2 MB size.

The Questions that we have are:

How do we handle such huge no of images so that the application does not
slow down?

How does PostGreSQL use caching? In case of images does it cache?

Do you suggest having the images in a different database by itself?

Regards,

Suresh

--=-ao1qcOrDTPbsNvK2j78A
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.14.1">
</HEAD>
<BODY>
 
 
How does PostGreSQL perform in case we have to store and use huge no of images: 
 
 Eg 4000 – 10000 images, each approx 2 MB size. 
 
 
 
The Questions that we have are: 
 
How do we handle such huge no of images so that the application does not slow down? 
 
How does PostGreSQL use caching? In case of images does it cache? 
 
Do you suggest having the images in a different database by itself? 
 
 
 
Regards, 
 
Suresh
</BODY>
</HTML>

--=-ao1qcOrDTPbsNvK2j78A--

Suresh Borse <s.borse [at] direction.biz> wrote:

> How does PostGreSQL perform in case we have to store and use huge
> no of images:
>
> Eg 4000 * 10000 images, each approx 2 MB size.

We have a database with a table containing 7.5 million rows, each
having an image in a bytea column. While the average size is only
15.2 kB, 69,000 of the rows are 2 MB or larger. Performance is
fine, but this is a pretty large machine.

> How do we handle such huge no of images so that the application
> does not slow down?

I would avoid doing a select of all 10,000 rows in a single result
set. If you're getting multiple images in a result set, you might
consider using a cursor.

> How does PostGreSQL use caching?

It has its own cache in shared memory, and also tends to use the
OS's cache -- just by virtue of doing the disk I/O through the OS.

> In case of images does it cache?

Yes.

> Do you suggest having the images in a different database by
> itself?

No. Well, not unless it is unrelated to other data in the database.
If it's related, you're going to want to define foreign keys, join
images to other data, and be able to have transactional integrity
between the images and other data. If you want those things, keep
them in the same database.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--0-1407091019-1270657288=:22026
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Suresh,
The real question is: =C2=A0does manipulation of the images have to be tran=
sactional? =C2=A0If so, store them in the database. =C2=A0If not, store the=
images in the file system and put the file name in the database, thereby a=
voiding unecessary WAL overhead.
Bob Lunney

--- On Wed, 4/7/10, Suresh Borse <s.borse [at] direction.biz> wrote:

From: Suresh Borse <s.borse [at] direction.biz>
Subject: [ADMIN] Handling of images via Postgressql
To: pgsql-admin [at] postgresql.org
Date: Wednesday, April 7, 2010, 8:50 AM

=0A=0A=0A =0A =0A=0A =0A
=0A
=0AHow does PostGreSQL perform in case we have to store and use huge no of =
images:
=0A
=0A=C2=A0Eg 4000 =E2=80=93 10000 images, each approx 2 MB size.
=0A
=0A=C2=A0
=0A
=0AThe Questions that we have are:
=0A
=0AHow do we handle such huge no of images so that the application does not=
slow down?
=0A
=0AHow does PostGreSQL use caching? In case of images does it cache?
=0A
=0ADo you suggest having the images in a different database by itself?
=0A
=0A
=0A
=0ARegards,
=0A
=0ASuresh=0A =0A=0A=0A=0A=0A
--0-1407091019-1270657288=:22026
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<table cellspacing=3D"0" cellpadding=3D"0" border=3D"0" ><tr><td valign=3D"=
top" style=3D"font: inherit;">Suresh,<div> </div><div>The real question =
is: does manipulation of the images have to be transactional? I=
f so, store them in the database. If not, store the images in the fil=
e system and put the file name in the database, thereby avoiding unecessary=
WAL overhead.<div> </div><div>Bob Lunney --- On Wed, 4/7/10, =
Suresh Borse <s.borse [at] direction.biz> wrote: <blockquote=
style=3D"border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; paddin=
g-left: 5px;"> From: Suresh Borse <s.borse [at] direction.biz> Subje=
ct: [ADMIN] Handling of images via Postgressql To: pgsql-admin [at] postgresq=
l.org Date: Wednesday, April 7, 2010, 8:50 AM <div id=3D"yiv12133=
55121">=0A=0A=0A =0A =0A=0A =0A =0A =0AHow does Po=
stGreSQL perform in case we have to store and use huge no of images:=
 =0A =0A Eg 4000 =E2=80=93 10000 images, each a=
pprox 2 MB size. =0A =0A =0A<=
br>=0AThe Questions that we have are: =0A =0A=
How do we handle such huge no of images so that the applic=
ation does not slow down? =0A =0AHow does Pos=
tGreSQL use caching? In case of images does it cache? =0A =0A<=
font size=3D"2">Do you suggest having the images in a different database by=
itself? =0A =0A =0A =0ARegards,=
 =0A =0ASuresh=0A =0A=0A</div></blockquote></=
div></div></td></tr></table> =0A=0A
--0-1407091019-1270657288=:22026--

Suresh Borse skrev 2010-04-07 14.50:
>
>
> How does PostGreSQL perform in case we have to store and use huge no of
> images:
>
> Eg 4000 =E2=80=93 10000 images, each approx 2 MB size.
>
>
>
> The Questions that we have are:
>
> How do we handle such huge no of images so that the application does no=
t
> slow down?
>
> How does PostGreSQL use caching? In case of images does it cache?
>
> Do you suggest having the images in a different database by itself?

I'd suggest storing meta information in PostgreSQL and the images in the
filesystem.

Regards,
roppert

>
>
>
> Regards,
>
> Suresh
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=-sVue1Csr9pUe5P5SOU3a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, 2010-04-07 at 16:01 +0200, Robert Gravsj=C3=B6 wrote:
> > Do you suggest having the images in a different database by itself?
>
> I'd suggest storing meta information in PostgreSQL and the images in
> the filesystem.

Is this transaction safe?

--
Devrim G=C3=9CND=C3=9CZ
PostgreSQL Dan=C4=B1=C5=9Fman=C4=B1/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

--=-sVue1Csr9pUe5P5SOU3a
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iEYEABECAAYFAku/kiIACgkQtl86P3SPfQ5ObwCggu7LtdrghLjeHAp65Z4o 93uM
mS8AoOr4eaiTB7KWOrM97TqbSr0UxC78
=oL07
-----END PGP SIGNATURE-----

--=-sVue1Csr9pUe5P5SOU3a--

Devrim G=DCND=DCZ<devrim [at] gunduz.org> wrote:
> Robert Gravsjö wrote:

>> I'd suggest storing meta information in PostgreSQL and the images
in
>> the filesystem.
>
> Is this transaction safe?

No.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Handling of images via Postgressql

Re: Handling of images via Postgressql

Re: Handling of images via Postgressql

Re: Handling of images via Postgressql

Re: Handling of images via Postgressql

Re: Handling of images via Postgressql