>----- Mensaje original -----
>De: Jorge Arévalo <jorgearevalo@libregis.org>
>Para: pgsql-general@postgresql.org
>CC:
>Enviado: Miércoles 15 de Mayo de 2013 17:08
>Asunto: [GENERAL] Best way to reduce server rounds getting big BLOBs
>
>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
remotePostgreSQL server.
>
>About 75% of the time my app uses is spent querying database. I basically get binary objects (images). I have to fetch
allthe 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
isgenerated inside the database >server, and the client receives a "pointer" to this ResultSet. You can get all the
rowsby moving this pointer over the ResultSet, calling the right functions. >But you still have to go to the database
foreach 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)
travelsto database server.
>
>Are cursors my best bet?
------
About cursors, i can't recommend anything, it "depends". But you shouldn't include the blob in the cursor select. Do
anotherselect to retrieve/get the blob data.
Have you disabled the blob compression? Postgresql spent a big % of querying time decompressing the image blob, and
images(in already compressed formats) aren't compressible. For image storage, the best, as far as I know, is a table
with2 column , the ID_PK and the blob itself. Image metadata in other table/tables with FK to Image_Table. These if you
wantto store images inside Postgresql.
You can get more speed storing images at filesystem level, and serving them with a standard http server. The
Image_Tablehas the image' url. Postgresql DB will be lighter and need less resources. Http servers can be distributed
amongdifferent physical servers.
Using an external filesystem storage breaks ACID, because inserting/deleting/updating an image in db and filesystem are
2different operations and both are not Atomic. You may need to implement some kind of locking for blob access. Backups
aretricky, you need to stop access to filesystem and db if you don't want to break Consistency (images on fs that
aren'tin db or viceversa, images with different url in db and fs, etc...).
------
>Many thanks in advance, and sorry if the question is too naive.
>--
>Jorge Arevalo
>Freelance developer
--
Eduardo Morras
