>----- Mensaje original -----
>De: Jorge Arévalo <jorgearev...@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 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?
------
About cursors, i can't recommend anything, it "depends". But you shouldn't
include the blob in the cursor select. Do another select 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
with 2 column , the ID_PK and the blob itself. Image metadata in other
table/tables with FK to Image_Table. These if you want to store images inside
Postgresql.
You can get more speed storing images at filesystem level, and serving them
with a standard http server. The Image_Table has the image' url. Postgresql DB
will be lighter and need less resources. Http servers can be distributed among
different physical servers.
Using an external filesystem storage breaks ACID, because
inserting/deleting/updating an image in db and filesystem are 2 different
operations and both are not Atomic. You may need to implement some kind of
locking for blob access. Backups are tricky, you need to stop access to
filesystem and db if you don't want to break Consistency (images on fs that
aren't in 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
