From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: s.borse@direction.biz
To: pgsql-admin@postgresql.org
Subject: Re: Handling of images via Postgressql
Date: Wed, 07 Apr 2010 09:26:50 -0500
Message-ID: <4BBC4FDA0200002500030555@gw.wicourts.gov> (raw)
In-Reply-To: <1270644614.2390.44.camel@dss-052.dsslp.com>
References: <1270644614.2390.44.camel@dss-052.dsslp.com>
Suresh Borse <s.borse@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, s.borse@direction.biz
Subject: Re: Handling of images via Postgressql
In-Reply-To: <4BBC4FDA0200002500030555@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox