Re: BUG #14290: materialized view refresh doesn't usetemp_tablespace - Mailing list pgsql-bugs
From | Rick Otten |
---|---|
Subject | Re: BUG #14290: materialized view refresh doesn't usetemp_tablespace |
Date | |
Msg-id | 8879489a97315500562edd71edc5d1fa@www.windfish.net Whole thread Raw |
In response to | Re: BUG #14290: materialized view refresh doesn't use temp_tablespace (Kevin Grittner <kgrittn@gmail.com>) |
Responses |
Re: BUG #14290: materialized view refresh doesn't use temp_tablespace
|
List | pgsql-bugs |
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
pgsql-bugs by date: