RE: Partition column should be part of PK
Hi all, I think that global indexes could be useful sometimes. That is why Oracle implements them. Just to mention two benefits that could be required by a lot of people: - Global uniqueness which shouldn't be in conflict with partitioning - Performance! Well, when index is on a column which is not the partitioning key. A global index would be better for performance... Nevertheless, this doesn't go without any price and you have described this very well. That is why Oracle invalidates global indexes when some partitioning maintenance operations are achieved. These indexes have to be rebuilt. But, anyway, such operations could be done "concurrently" or "online"... Michel SALAIS -Message d'origine- De : David Rowley Envoyé : lundi 12 juillet 2021 02:57 À : Nagaraj Raj Cc : Christophe Pettus ; [email protected] Objet : Re: Partition column should be part of PK On Mon, 12 Jul 2021 at 12:37, Nagaraj Raj wrote: > personally, I feel this design is very bad compared to other DB servers. I'm not sure exactly what you're referring to here as you didn't quote it, but my guess is you mean our lack of global index support. Generally, there's not all that much consensus in the community that this would be a good feature to have. Why do people want to use partitioning? Many people do it so that they can quickly remove data that's no longer required with a simple DETACH operation. This is metadata only and is generally very fast. Another set of people partition as their tables are very large and they become much easier to manage when broken down into parts. There's also a group of people who do it for the improved data locality. Unfortunately, if we had a global index feature then that requires building a single index over all partitions. DETACH is no longer a metadata-only operation as we must somehow invalidate or remove tuples that belong to the detached partition. The group of people who partitioned to get away from very large tables now have a very large index. Maybe the only group to get off lightly here are the data locality group. They'll still have the same data locality on the heap. So in short, many of the benefits of partitioning disappear when you have a global index. So, why did you partition your data in the first place? If you feel like you wouldn't mind having a large global index over all partitions then maybe you're better off just using a non-partitioned table to store this data. David
Re: temporary file log lines
On Thu, 2021-07-08 at 17:22 -0400, MichaelDBA wrote: > I got a question about PG log lines with temporary file info like this: > > case 1: log line with no contextual info > 2021-07-07 20:28:15 UTC:10.100.11.95(50274):myapp@mydb:[35200]:LOG: > temporary file: path "base/pgsql_tmp/pgsql_tmp35200.0", size 389390336 > > case 2: log line with contextual info > 2021-07-07 20:56:18 UTC:172.16.193.118(56080):myapp@mydb:[22418]:LOG: > temporary file: path "base/pgsql_tmp/pgsql_tmp22418.0", size 1048576000 > 2021-07-07 20:56:18 > UTC:172.16.193.118(56080):myapp@mydb:[22418]:CONTEXT: PL/pgSQL function > memory.f_memory_usage(boolean) line 13 at RETURN QUERY > > There are at least 2 cases where stuff can spill over to disk: > * queries that don't fit in work_mem, and > * temporary tables that don't fit in temp_buffers > > Question, if log_temp_files is turned on (=0), then how can you tell > from where the temporary log line comes from? > I see a pattern where work_mem spill overs have a CONTEXT line that > immediately follows the LOG LINE with keyword, temporary. See case 2 above. > > For other LOG lines with keyword, temporary, there is no such pattern. > Could those be the ones caused by temp_buffer spill overs to disk? case > 1 above. > > I really want to tune temp_buffers, but I would like to be able to > detect when temporary tables are spilling over to disk, so that I can > increase temp_buffers. > > Any help would be appreciated. I am not sure if you can istinguish those two cases from the log. What I would do is identify the problematic query and run it with EXPLAIN (ANALYZE, BUFFERS). Then you should see which part of the query creates the temporary files. If it is a statement in a function called from your top level query, auto_explain with the correct parameters can get you that output for those statements too. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: temporary file log lines
hmmm, I think spilling over to disk for temporary tables is handled by an entirely different branch in the PG source code. In fact, some other folks have chimed in and said log_temp_files doesn't relate to temp files at all use by temporary tables, just queries as you mentioned below elsewhere. This seems to be a dark area of PG that is not convered well. Regards, Michael Vitale Laurenz Albe wrote on 7/12/2021 8:01 AM: On Thu, 2021-07-08 at 17:22 -0400, MichaelDBA wrote: I got a question about PG log lines with temporary file info like this: case 1: log line with no contextual info 2021-07-07 20:28:15 UTC:10.100.11.95(50274):myapp@mydb:[35200]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp35200.0", size 389390336 case 2: log line with contextual info 2021-07-07 20:56:18 UTC:172.16.193.118(56080):myapp@mydb:[22418]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp22418.0", size 1048576000 2021-07-07 20:56:18 UTC:172.16.193.118(56080):myapp@mydb:[22418]:CONTEXT: PL/pgSQL function memory.f_memory_usage(boolean) line 13 at RETURN QUERY There are at least 2 cases where stuff can spill over to disk: * queries that don't fit in work_mem, and * temporary tables that don't fit in temp_buffers Question, if log_temp_files is turned on (=0), then how can you tell from where the temporary log line comes from? I see a pattern where work_mem spill overs have a CONTEXT line that immediately follows the LOG LINE with keyword, temporary. See case 2 above. For other LOG lines with keyword, temporary, there is no such pattern. Could those be the ones caused by temp_buffer spill overs to disk? case 1 above. I really want to tune temp_buffers, but I would like to be able to detect when temporary tables are spilling over to disk, so that I can increase temp_buffers. Any help would be appreciated. I am not sure if you can istinguish those two cases from the log. What I would do is identify the problematic query and run it with EXPLAIN (ANALYZE, BUFFERS). Then you should see which part of the query creates the temporary files. If it is a statement in a function called from your top level query, auto_explain with the correct parameters can get you that output for those statements too. Yours, Laurenz Albe
