From s.borse@direction.biz Wed Apr 7 13:01:12 2010 Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D6AA663351F for ; Wed, 7 Apr 2010 10:01:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 44831-07 for ; Wed, 7 Apr 2010 13:00:53 +0000 (UTC) X-Greylist: delayed 00:06:39.301146 by SQLgrey-1.7.6 Received: from ns1.corpmailserver.com (ns1.corpmailserver.com [72.35.70.2]) by mail.postgresql.org (Postfix) with ESMTP id 91E6D63344C for ; Wed, 7 Apr 2010 10:01:01 -0300 (ADT) Received: (qmail 4175 invoked by uid 89); 7 Apr 2010 12:46:29 -0000 Received: by simscan 1.3.1 ppid: 4102, pid: 4163, t: 0.5790s scanners: attach: 1.3.1 clamav: 0.95.3/m: Received: from unknown (HELO direction.biz) (postmaster@direction.biz@59.163.21.131) by ns2.corpmailserver.com with ESMTPA; 7 Apr 2010 12:46:29 -0000 Received: from [172.16.2.20] ([172.16.2.20]) (authenticated user s.borse@direction.biz) by direction.biz (direction.biz) (MDaemon PRO v9.0.0) with ESMTP id md50000973959.msg for ; Wed, 07 Apr 2010 18:24:39 +0530 Subject: Handling of images via Postgressql From: Suresh Borse Reply-To: s.borse@direction.biz To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary="=-ao1qcOrDTPbsNvK2j78A" Organization: DSS Date: Wed, 07 Apr 2010 18:20:13 +0530 Message-Id: <1270644614.2390.44.camel@dss-052.dsslp.com> Mime-Version: 1.0 X-Mailer: Evolution 2.10.3 (2.10.3-9.fc7) X-Authenticated-Sender: s.borse@direction.biz X-Spam-Processed: direction.biz, Wed, 07 Apr 2010 18:24:39 +0530 (not processed: spam filter heuristic analysis disabled) X-MDRemoteIP: 172.16.2.20 X-Return-Path: s.borse@direction.biz X-MDaemon-Deliver-To: pgsql-admin@postgresql.org X-MDAV-Processed: direction.biz, Wed, 07 Apr 2010 18:24:39 +0530 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/65 X-Sequence-Number: 34159 --=-ao1qcOrDTPbsNvK2j78A Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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 --=-ao1qcOrDTPbsNvK2j78A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit

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 --=-ao1qcOrDTPbsNvK2j78A-- From tekniksupport@blogg.se Wed Apr 7 14:01:19 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D9D09632325 for ; Wed, 7 Apr 2010 11:01:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 56981-02 for ; Wed, 7 Apr 2010 14:01:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.domeneshop.no (smtp.domeneshop.no [194.63.248.54]) by mail.postgresql.org (Postfix) with ESMTP id 58187632315 for ; Wed, 7 Apr 2010 11:01:09 -0300 (ADT) Received: from [194.237.103.50] (helo=charliex.local) by smtp.domeneshop.no with esmtpa (Exim 4.71) (envelope-from ) id 1NzVoX-0003Bq-Jy; Wed, 07 Apr 2010 16:01:05 +0200 Message-ID: <4BBC9020.8090209@blogg.se> Date: Wed, 07 Apr 2010 16:01:04 +0200 From: =?UTF-8?B?Um9iZXJ0IEdyYXZzasO2?= Reply-To: robert@blogg.se Organization: Blogg Esse AB User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; sv-SE; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4 MIME-Version: 1.0 To: s.borse@direction.biz CC: pgsql-admin@postgresql.org Subject: Re: Handling of images via Postgressql References: <1270644614.2390.44.camel@dss-052.dsslp.com> In-Reply-To: <1270644614.2390.44.camel@dss-052.dsslp.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201004/120 X-Sequence-Number: 34214 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 – 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? I'd suggest storing meta information in PostgreSQL and the images in the filesystem. Regards, roppert > > > > Regards, > > Suresh > From Kevin.Grittner@wicourts.gov Wed Apr 7 14:27:53 2010 Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id ADC856348C8 for ; Wed, 7 Apr 2010 11:27:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 59634-02 for ; Wed, 7 Apr 2010 14:27:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id A0256634DB5 for ; Wed, 7 Apr 2010 11:26:53 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Wed, 07 Apr 2010 09:26:51 -0500 Message-Id: <4BBC4FDA0200002500030555@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Wed, 07 Apr 2010 09:26:50 -0500 From: "Kevin Grittner" To: , Subject: Re: Handling of images via Postgressql References: <1270644614.2390.44.camel@dss-052.dsslp.com> In-Reply-To: <1270644614.2390.44.camel@dss-052.dsslp.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.52 tagged_above=-10 required=5 tests=AWL=-0.780, BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 201004/67 X-Sequence-Number: 34161 Suresh Borse 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 From bob_lunney@yahoo.com Wed Apr 7 16:21:45 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0E6A6634DAC for ; Wed, 7 Apr 2010 13:21:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81354-04-6 for ; Wed, 7 Apr 2010 16:21:34 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web39702.mail.mud.yahoo.com (web39702.mail.mud.yahoo.com [209.191.106.48]) by mail.postgresql.org (Postfix) with SMTP id C154C634A2F for ; Wed, 7 Apr 2010 13:21:29 -0300 (ADT) Received: (qmail 22329 invoked by uid 60001); 7 Apr 2010 16:21:28 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1270657288; bh=bsGWEm4SzQMt1/gXJPoIyxZvHKACoQuJFhbi0qve9VU=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=0+PKUorQlhqisFPhTqAREMihPJc44/ilsvuwZibRmjhZbd70xPX51VMpTjVdGLlP24Pk+Ql1ner6dOMws5d884B8KVSUD/ZtS86cEDTcK6L7Dpla2JPzCwoIlXJnsrDAdso4Nu0rGqVtueR0b1Svpn9Kioybv08P8RU0wY90hyc= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=hWH+EtePn4xYzrl/XD2yhKOq8uC1TNQCQf39Ev7wRH54DVref2zHFOipyYL7Kt4OrTv68giS/uB14tBFiVcpk5+/sdBKYqqLxn31EPnqNusdWFvJbosfawMfbFTcrbTsfwc1K45nnrtHdPfGXKmTOPxDSgKn9fUJ32Ya/+nAPz0=; Message-ID: <333114.22026.qm@web39702.mail.mud.yahoo.com> X-YMail-OSG: TqnGLUAVM1nYUB53IluwdqugLGKBiIO9PdCz.M0xoLEjANc WDGzlXbSCDMTvQ3RN6pXNGSeWEAQlk0kuSUcqVk5tbeZvh48cwdiJE0BTIVb ekanm2iqLqZQRpnd0hXHoac4.xes8dKHvGuTV4zsF5KpERtAxZo_rXMnAtc1 TZnWrZTITR5oEIY99GIPPLyrMsX43CoGdcwnBbkDK7NLoXSiNUzB8VX61acG pegGVpNgEOZ0dQGrVgvA4tlIpaotgH8Dtacdd1szvuOWRPa1alLsGRyPXARU 558lenOI3kY_az04tZj6wNY3N2RKgPJibEFtEddVXn9lpdt5WRLtRT844iMT ims6yK8warlan3ARuW9QoszVjQwsKuqTu Received: from [64.126.29.122] by web39702.mail.mud.yahoo.com via HTTP; Wed, 07 Apr 2010 09:21:28 PDT X-Mailer: YahooMailClassic/10.0.8 YahooMailWebService/0.8.100.260964 Date: Wed, 7 Apr 2010 09:21:28 -0700 (PDT) From: Bob Lunney Subject: Re: Handling of images via Postgressql To: pgsql-admin@postgresql.org In-Reply-To: <1270644614.2390.44.camel@dss-052.dsslp.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1407091019-1270657288=:22026" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.184 tagged_above=-10 required=5 tests=BAYES_40=-0.185, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/68 X-Sequence-Number: 34162 --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 wrote: From: Suresh Borse Subject: [ADMIN] Handling of images via Postgressql To: pgsql-admin@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
Suresh,

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.

Bob Lunney

--- On Wed, 4/7/10, = Suresh Borse <s.borse@direction.biz> wrote:

From: Suresh Borse <s.borse@direction.biz>
Subje= ct: [ADMIN] Handling of images via Postgressql
To: pgsql-admin@postgresq= l.org
Date: Wednesday, April 7, 2010, 8:50 AM

=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

=0A=0A --0-1407091019-1270657288=:22026-- From devrim@gunduz.org Fri Apr 9 20:47:34 2010 Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 1599C6339EB for ; Fri, 9 Apr 2010 17:47:34 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 76335-03 for ; Fri, 9 Apr 2010 20:47:18 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gunduz.org (ns1.gunduz.org [77.79.103.58]) by mail.postgresql.org (Postfix) with ESMTP id 4EC656324A1 for ; Fri, 9 Apr 2010 17:47:23 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.gunduz.org (Postfix) with ESMTP id 92FD7400049; Fri, 9 Apr 2010 23:48:37 +0300 (EEST) X-Virus-Scanned: amavisd-new at mail.gunduz.org Received: from mail.gunduz.org ([127.0.0.1]) by localhost (mail.gunduz.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Qoon0hPRxkXO; Fri, 9 Apr 2010 23:48:02 +0300 (EEST) Received: from [188.58.140.242] (unknown [188.58.140.242]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gunduz.org (Postfix) with ESMTPSA id 56F68400046; Fri, 9 Apr 2010 23:48:02 +0300 (EEST) Subject: Re: Handling of images via Postgressql From: Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= To: robert@blogg.se Cc: s.borse@direction.biz, pgsql-admin@postgresql.org In-Reply-To: <4BBC9020.8090209@blogg.se> References: <1270644614.2390.44.camel@dss-052.dsslp.com> <4BBC9020.8090209@blogg.se> Content-Type: multipart/signed; micalg="pgp-sha1"; protocol="application/pgp-signature"; boundary="=-sVue1Csr9pUe5P5SOU3a" Date: Fri, 09 Apr 2010 23:46:27 +0300 Message-ID: <1270845987.9084.45.camel@hp-laptop2.gunduz.org> Mime-Version: 1.0 X-Mailer: Evolution 2.28.3 (2.28.3-1.fc12) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.01 tagged_above=-10 required=5 tests=T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/121 X-Sequence-Number: 34215 --=-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? >=20 > I'd suggest storing meta information in PostgreSQL and the images in > the filesystem.=20 Is this transaction safe? --=20 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/kiIACgkQtl86P3SPfQ5ObwCggu7LtdrghLjeHAp65Z4o93uM mS8AoOr4eaiTB7KWOrM97TqbSr0UxC78 =oL07 -----END PGP SIGNATURE----- --=-sVue1Csr9pUe5P5SOU3a-- From Kevin.Grittner@wicourts.gov Fri Apr 9 20:58:10 2010 Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id C0E18633EEF for ; Fri, 9 Apr 2010 17:58:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 78031-06 for ; Fri, 9 Apr 2010 20:57:55 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 0F37F633934 for ; Fri, 9 Apr 2010 17:57:59 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 09 Apr 2010 15:57:58 -0500 Message-Id: <4BBF4E8002000025000306A1@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Fri, 09 Apr 2010 15:57:52 -0500 From: "Kevin Grittner" To: , "=?ISO-8859-15?B?RGV2cmltIEfcTkTcWg==?=" Cc: , Subject: Re: Handling of images via Postgressql References: <1270644614.2390.44.camel@dss-052.dsslp.com> <4BBC9020.8090209@blogg.se> <1270845987.9084.45.camel@hp-laptop2.gunduz.org> In-Reply-To: <1270845987.9084.45.camel@hp-laptop2.gunduz.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.136 tagged_above=-10 required=5 tests=AWL=-1.126, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/122 X-Sequence-Number: 34216 Devrim GÜNDÜZ wrote: > Robert Gravsjö wrote: >> I'd suggest storing meta information in PostgreSQL and the images in >> the filesystem. > > Is this transaction safe? No. -Kevin