Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)

On Wed, Jun 3, 2015 at 11:56 AM, Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> On 06/03/15 16:06, ben.play wrote:
>>
>> The query is (unfortunately) generated by Doctrine 2 (Symfony 2).
>> We can’t change the query easily.
>
>
> Well, then you'll probably have to buy more RAM, apparently.

There's an easy way to add disk space for this kind of thing.

Add a big fat rotational HD (temp tables are usually sequentially
written and scanned so rotational performs great), format it of
course, and create a tablespace pointing to it. Then set it as default
in temp_tablespaces (postgresql.conf) or do it in the big query's
session (I'd recommend the global option if you don't already use a
separate tablespace for temporary tables).

Not only it will give you the necessary space, but it will also be
substantially faster.

You'll have to be careful about backups though (the move from one
filesystem to two filesystems always requires changes to backup
strategies)