Re: Prepared statements generating a lot of temp files.

Perhaps take the other approach: increase work_mem to make the bulk
inserts fit into memory.  You can easily undo work_mem changes.  It only
requires a sighup: reload, not restart.
What memory do you have now and what is work_mem currently set to? Also,
have many concurrent, active connections do you average at a time?

Regards,
Michael Vitale

Karthik Krishnakumar wrote on 3/23/2023 5:59 AM:
> thanks - checked with the devs and it does look like the application
> is doing some sort of a bulk insert, and at the moment it cannot be
> changed to use "COPY FROM".
> will limiting bulk inserts to match the work_mem(assuming this is the
> guc that is used in this case) reduce this disk activity?
>
> thanks,
> karthik
>
>
>
>
> ---- On Thu, 23 Mar 2023 14:43:19 +0530 *<Michaeldba(at)sqlexec(dot)com>*
> wrote ---
>
> Look at the size of the bind variables and also whether you are
> doing bulk inserts
>
> Sent from my iPad
>
> On Mar 23, 2023, at 5:10 AM, Karthik Krishnakumar
> <karthikk(at)zohocorp(dot)com <mailto:karthikk(at)zohocorp(dot)com>> wrote:
>
> 
> Hi admins,
>
> I am analyzing temp files being generated using pg_badger.
> Under "queries generating the most temporary files", I am
> finding insert/update queries (via prepared statements)
> writing a lot of data to temp files.
> I am trying to figure why inserts are generating such temp files.
> Why are inserts/updates generating temp files?
> what can be done to avoid this ?
>
> thanks,
> karthik
>
>
>

Regards,

Michael Vitale

Michaeldba(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>

703-600-9343