Re: BUG #14290: materialized view refresh doesn't usetemp_tablespace

The distinction between temporary objects that write to disk, and temporary files, never occurred to me before.  I had always thought they were the same thing.  I think it is a pretty confusing fine point.

Yes, I do have the temporary tablespace on the fastest disk since I thought that when I spilled out of memory, I'd still want the best performance I could get during those operations.

The temporary files generated during the refresh are several times larger than the final materialized view.  This tells me they are probably doing much more I/O than the final (concurrent) write into the materialized view.  It made sense to put those temporary writes onto the faster disk.

Additionally, in a tiered storage environment, I might put my regular tablespace on a highly redundant (ie, expensive) disk array, and then put my temporary tablespace on a fast, but cheaper disk array because I don't really need the redundancy in the temporary tables the way I do with my regular tables.

Another issue is a psychology of disk management.   When I look at a 1000G "regular" tablespace that only has 200G of tables on it, I might think "oh, there is plenty of space there for more tables" or "oh, look at all the space I'm wasting, I should pare that back and save a few dollars".

Whereas, when I see an 800G temp tablespace on the database, even if it isn't full _at_the_moment_, I think to myself "wow, there must be some big transient queries that need that space which must be why it is there".

I now know that I need all that extra space in the "regular tablespace" to support refreshing the materialized views and that in general I should plan for lots of empty space in my regular tablespaces to support that.  As I mentioned, I discovered this when a refresh view concurrently failed because it ran the regular tablespace out of disk, even though the disk was only at 40% capacity (when the refresh wasn't running) and I had a large temporary tablespace set up to support things like that.  Fortunately I had everything on logical volume managers so it was easy to extend the regular tablespace on the fly once the issue became apparent.   This could have been a much more painful thing to discover.

Lastly, if I had several tablespaces with these sorts of materialized views in them, they could all share the same temporary tablespace for the refresh.  Which means I wouldn't need so much extra space in every tablespace  (as long as they didn't all refresh at the same time).

On 2016-08-24 13:46, Kevin Grittner wrote:

You are confusing two completely different things: temporary

objects (created by statements starting with CREATE TEMPORARY) and
temporary files (created when data spills to disk during, for
example, a sort, hash, or materialization of data internal to
processing some statement).  The former are placed based on
temp_tablespaces; the latter are normally placed in the
base/pgsql_tmp/ subdirectory.

It would be possible to place them underneath a tablespace
specified by temp_tablespaces, and it might even be a good
enhancement to implement, but that is not the normal or default
location for temporary files.

Out of curiosity, and to help justify this as a feature request
worth pursuing, can you explain why you want to do this?  For
example, have you placed your temporary tablespace on a faster
medium?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company