pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHandling of images via Postgressql
6+ messages / 5 participants
[nested] [flat]
* Handling of images via Postgressql
@ 2010-04-07 12:50 Suresh Borse <s.borse@direction.biz>
0 siblings, 3 replies; 6+ messages in thread
From: Suresh Borse @ 2010-04-07 12:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling of images via Postgressql
@ 2010-04-07 14:01 Robert Gravsjö <tekniksupport@blogg.se>
parent: Suresh Borse <s.borse@direction.biz>
2 siblings, 1 reply; 6+ messages in thread
From: Robert Gravsjö @ 2010-04-07 14:01 UTC (permalink / raw)
To: s.borse@direction.biz; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling of images via Postgressql
@ 2010-04-07 14:26 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Suresh Borse <s.borse@direction.biz>
2 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2010-04-07 14:26 UTC (permalink / raw)
To: s.borse@direction.biz; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling of images via Postgressql
@ 2010-04-07 16:21 Bob Lunney <bob_lunney@yahoo.com>
parent: Suresh Borse <s.borse@direction.biz>
2 siblings, 0 replies; 6+ messages in thread
From: Bob Lunney @ 2010-04-07 16:21 UTC (permalink / raw)
To: pgsql-admin
Suresh,
The real question is: does manipulation of the images have to be transactional? If so, store them in the database. If not, store the images in the file 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>
Subject: [ADMIN] Handling of images via Postgressql
To: pgsql-admin@postgresql.org
Date: Wednesday, April 7, 2010, 8:50 AM
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling of images via Postgressql
@ 2010-04-09 20:46 Devrim GÜNDÜZ <devrim@gunduz.org>
parent: Robert Gravsjö <tekniksupport@blogg.se>
0 siblings, 1 reply; 6+ messages in thread
From: Devrim GÜNDÜZ @ 2010-04-09 20:46 UTC (permalink / raw)
To: robert@blogg.se; +Cc: s.borse@direction.biz; pgsql-admin
On Wed, 2010-04-07 at 16:01 +0200, Robert Gravsjö 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ÜNDÜZ
PostgreSQL Danışmanı/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
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling of images via Postgressql
@ 2010-04-09 20:57 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Devrim GÜNDÜZ <devrim@gunduz.org>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2010-04-09 20:57 UTC (permalink / raw)
To: robert@blogg.se; Devrim GÜNDÜZ <devrim@gunduz.org>; +Cc: s.borse@direction.biz; pgsql-admin
Devrim GÜNDÜZ<devrim@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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-04-09 20:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-07 12:50 Handling of images via Postgressql Suresh Borse <s.borse@direction.biz>
2010-04-07 14:01 ` Robert Gravsjö <tekniksupport@blogg.se>
2010-04-09 20:46 ` Devrim GÜNDÜZ <devrim@gunduz.org>
2010-04-09 20:57 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-07 14:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-07 16:21 ` Bob Lunney <bob_lunney@yahoo.com>
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