I've got three tablespaces. Two of them are on disks with ~400GiB free, and one is on a disk with ~200GiB free. I want to set up temp_tablespaces
to use the latter disk less frequently in case I run out of space on it. How can I do this?
1 Answer
Say your tablespaces are ts1
, ts2
, and ts3
, where ts3
is the smaller one.
ALTER DATABASE your_database SET temp_tablespaces = ts1, ts1, ts2, ts2, ts3
Looks like duplicate values in the temp_tablespaces
list are kept, making Postgres more likely to write a temp file those tablespaces, according to weighted random choice. Transactions use the tablespaces sequentially, which also produces roughly the same weighted distribution (docs). So ts3
should now have about 1/2 the usage as the other two tablespaces.
I'm trying this now for some huge queries. I see 84GiB used on the first two and 40GiB used on the last one.