Re: Best way to reduce server rounds getting big BLOBs

>----- Mensaje original -----
>De: Jorge Arévalo <jorgearevalo(at)libregis(dot)org>
>Para: pgsql-general(at)postgresql(dot)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