How do I move PostgreSQL temp_files to a different tablespace/disk for CURSORs

by

The question:

While working on performance improvements on our PostgreSQL database (Ubuntu Focal, PostgreSQL 13.3), I created a “temporary” tablespace on a fast local NVME drive. This works well and both temporary tables and temp_files created by large queries end up there, taking a lot of load off the main data drive.

However, we use server side CURSORS a lot, and it appears that temp_files created by those always end up on the main data volume, causing a lot more I/O than we really want there.

2022-03-13 00:59:51.692 UTC 1350170 [email protected] LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp1350170.54”, size 564228392

2022-03-13 00:59:51.692 UTC 1350170 [email protected] STATEMENT: FETCH FORWARD 5569 FROM “xx”

I’ve done about as much work_mem tuning as I can, but many of our queries are large (temp_files are often over 1GB). Our biggest bottleneck is I/O on the data drive. So, if I can get those temp_files onto the separate local NVME drive instead, that’d do wonders.

I’ve seen a few places suggest replacing the pgsql_tmp dir from ~/main/base/pgsql_tmp with a symlink to the other drive, but also warnings that that isn’t necessarily safe.

Is this a safe way to do this, or is there another, better way? Is there some way to tell PostgreSQL to put temp_files generated by CURSORS onto the temporary drive?

Thanks,

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

If these cursors are declared WITH HOLD, then I think you are running into this bit from src/backend/storage/file/fd.c:

 * BUT: if the temp file is slated to outlive the current transaction,
 * force it into the database's default tablespace, so that it will not
 * pose a threat to possible tablespace drop attempts.
 */
if (numTempTableSpaces > 0 && !interXact)
{

Perhaps this should be documented in user-space, not just the source-code comments.

I don’t see that there is anything you can do about it, other than remove the !interXact test and compile your own binaries.

I might find symlinking ~/main/base/pgsql_tmp to be preferable to that. Did the people warning against it say why?


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment