0

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 xx@xx LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp1350170.54", size 564228392

2022-03-13 00:59:51.692 UTC 1350170 xx@xx 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,

1 Answer 1

2

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?

2
  • aha, that explains why it's behaving like that nicely, thanks! they are, indeed, using WITH HOLD. I'm not sure if the warnings against using symlinks were for any specific reason or just a general discouraging of messing with things under base/ Commented Mar 15, 2022 at 0:46
  • I ended up symlinking base/pgsql_tmp to another volume and it appears to be working well. Commented Mar 28, 2022 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.