Re: BUG #14290: materialized view refresh doesn't use temp_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 [1]
> The Enterprise PostgreSQL Company

Links:
------
[1] http://www.enterprisedb.com