Best way to reduce server rounds getting big BLOBs

From: Jorge Arévalo <jorgearevalo(at)libregis(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Best way to reduce server rounds getting big BLOBs
Date: 2013-05-15 15:08:33
Message-ID: 55DB3F35B0674051883B4E784D73A941@libregis.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'd like to know what's the best way to reduce the number of server rounds in a libpq C app that fetches BLOBs from a remote PostgreSQL server.

About 75% of the time my app uses is spent querying database. I basically get binary objects (images). I have to fetch all the images from a table. This table can be really big (in number of rows) and each image can be big too.

I guess I should go for cursors. If I understood the concept of "cursor", basically the query is executed, a ResultSet is generated inside the database server, and the client receives a "pointer" to this ResultSet. You can get all the rows by moving this pointer over the ResultSet, calling the right functions. But you still have to go to the database for each chunk of data. Am I right?

I also understand that the "perfect" situation would be to have all the table content in memory in just one server round, available for my app. But as that's not scalable at all, I want a "good enough" method to avoid (expensive) travels to database server.

Are cursors my best bet?

Many thanks in advance, and sorry if the question is too naive.

--
Jorge Arevalo
Freelance developer

http://www.krop.com/jorgearevalo
http://about.me/jorgeas80

Enviado con Sparrow (http://www.sparrowmailapp.com/?sig)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jorge Arévalo 2013-05-15 16:31:45 Best way to reduce server rounds getting big BLOBs
Previous Message Tom Lane 2013-05-15 14:50:41 Re: seeming overflow during avg() of intervals without errors/warnings