Re: controlling memory management with regard to a specific query (or groups of connections)

On Wed, 18 Nov 2015 20:10:00 -0500
Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:

> As a temporary fix I need to write some uploaded image files to PostgreSQL until a task server can read/process/delete them.
>
> The problem I've run into (via server load tests that model our production environment), is that these read/writes end up pushing the indexes used by other queries out of memory -- causing them to be re-read from disk. These files can be anywhere from 200k to 5MB.
>
> has anyone dealt with situations like this before and has any suggestions? I could use a dedicated db connection if that would introduce any options.

PostgreSQL doesn't have any provisions for preferring one thing
or another for storing in memory.

The easiest thing I can think would be to add memory to the machine
(or configure Postgres to use more) such that those files aren't
pushing enough other pages out of memory to have a problematic
impact.

Another idea would be to put the image database on a different
physical server, or run 2 instances of Postgres on a single
server with the files in one database configured with a low
shared_buffers value, and the rest of the data on the other
database server configured with higher shared_buffers.

I know these probably aren't the kind of answers you're looking
for, but I don't have anything better to suggest; and the rest
of the mailing list seems to be devoid of ideas as well.

--
Bill Moran