Partitionwise aggregate and runtime partition pruning
Hi All, I have a question about partition pruning. Does runtime partition pruning (ie. pruning performed during execution) work with partition wise aggregates? 1) I have a setup with a mix of foreign (postgres_fdw) and local partitions. 2) I want to perform an aggregate query and I want the aggregates to be pushed down to remote servers. To make it possible I set enable_partitionwise_aggregate to on. My observation is that partition pruning works during planning time But with plan_cache_mode = force_generic_plan, explain analyse execute prepared_stmt(params) shows that _all_ partitions are scanned (and queries sent to remote severs for all remote partitions). When I set enable_hashagg to false runtime partition pruning works but no pushdown is taking place. Am I missing something or runtime partition pruning is not going to work for generic plans and partitionwise aggregates? Thanks, Michal
Suggestion for memory parameters
Hello All, In a RDS postgres we are seeing some select queries when running and doing sorting on 50 million rows(as its having order by clause in it) , the significant portion of wait event is showing as "IO:BufFileWrite" and it runs for ~20minutes+. Going through the document in the link below, it states we should monitor the "FreeLocalStorage" metric and when monitoring that, I see it showing up to ~535GB as the max limit and when these queries run this goes down till 100GB. Note- (it's a R7g8xl instance) https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html We were thinking of bumping up the work_mem to a higher value in database level , which is currently having size 4MB default. But we will also have ~100 sessions running at same time and majority were from other applications which execute other single row "insert" queries and I hope that will not need high "work_mem" . And setting it at database level will consume 100 times that set work_mem value. So how to handle this situation? Or Is it fine to let it use "FreeLocalStorage" unless it goes till zero? Also I am confused between the local storage (which is showing as 535GB) vs the memory/RAM which is 256GB for this instance class with ~128TB max storage space restriction, how are these storage different, (mainly the 535GB space which it's showing vs the 128TB storage space restriction)? Appreciate your guidance. select query looks something as below with no Joins but just single table fetch:- Select from where order by column1, column2 LIMIT $b1 OFFSET $B2 ; Regards Yudhi
Re: Issues with PostgreSQL Source Code Installation
> It looks to me like there is something > wrong with your libicu installation --- perhaps headers out of sync > with shared library? Yes correct, fixing libicu installation resolved the issue.
Re: Suggestion for memory parameters
On Fri, Sep 27, 2024 at 9:11 AM veem v wrote: > > On Thu, 26 Sept 2024 at 16:33, yudhi s > wrote: > >> Hello All, >> >> In a RDS postgres we are seeing some select queries when running and >> doing sorting on 50 million rows(as its having order by clause in it) , the >> significant portion of wait event is showing as "IO:BufFileWrite" and it >> runs for ~20minutes+. >> >> Going through the document in the link below, it states we should monitor >> the "FreeLocalStorage" metric and when monitoring that, I see it showing up >> to ~535GB as the max limit and when these queries run this goes down till >> 100GB. Note- (it's a R7g8xl instance) >> >> >> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html >> >> We were thinking of bumping up the work_mem to a higher value in database >> level , which is currently having size 4MB default. But we will also have >> ~100 sessions running at same time and majority were from other >> applications which execute other single row "insert" queries and I hope >> that will not need high "work_mem" . And setting it at database level will >> consume 100 times that set work_mem value. So how to handle this situation? >> Or >> Is it fine to let it use "FreeLocalStorage" unless it goes till zero? >> >> Also I am confused between the local storage (which is showing as 535GB) >> vs the memory/RAM which is 256GB for this instance class with ~128TB max >> storage space restriction, how are these storage different, (mainly the >> 535GB space which it's showing vs the 128TB storage space restriction)? >> Appreciate your guidance. >> >> select query looks something as below with no Joins but just single table >> fetch:- >> >> Select >> from >> where >> order by column1, column2 LIMIT $b1 OFFSET $B2 ; >> >> >> > My 2 cents > I think you should set the work_mem on specific session level , if your > sorting queries are only from specific handful of sessions, as because > setting it up at database level will eat up your most of RAM(which you said > is 256GB) and you said 100+ sessions getting spawned at any point in time. > Thank you. When I checked pg_stat_statements for this query , and divided the temp_blk_read+temp_blk_written with the "calls", it came as ~1million which means ~7GB. So does that mean ~7GB of work_mem should be allocated for this query?
Request for Insights on ID Column Migration Approach
I am just contacting you to talk about a current issue with our database. We have run out of a positive sequence in one of our tables and are now operating with negative sequences. To address this, we plan to migrate from the int4 ID column to an int8 ID column. The plan involves renaming the int8 column to the id column and setting it as the primary key. However, this process will require downtime, which may be substantial in a production environment. Fortunately, we have noted that other tables do not use the id column as a foreign key, which may help mitigate some concerns. Our Approach: 1. *Create a Unique Index*: We will first create a unique index on the new ID column before renaming it and altering it to be non-nullable. This step will necessitate scanning the entire table to verify uniqueness. 2. *Add Primary Key*: After ensuring the uniqueness, we will add the ID column as the primary key. By doing this, we hope to bypass the additional scanning for uniqueness and nullability, as the column will already be set as not nullable and will have the uniqueness constraint from the unique index. We want to confirm if this approach will work as expected. If we should be aware of any potential pitfalls or considerations, could you please provide insights or point us toward relevant documentation? Thank you so much for your help, and I look forward to your guidance. Best regards, Aditya Narayan Singh Loyalty Juggernaut Inc. -- *Confidentiality Warning:* This message and any attachments are intended only for the use of the intended recipient(s), are confidential, and may be privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or other use of this message and any attachments is strictly prohibited. If received in error, please notify the sender immediately and permanently delete it.
Re: Request for Insights on ID Column Migration Approach
Hi, Your approach to migrating the ID column from int4 to int8 with minimal downtime is generally sound but in my option, consider the following also: - Consider using PostgreSQL's CONCURRENTLY option when creating the unique index to avoid locking the entire table - Make sure to first alter the new column to be non-nullable if it’s not already On Fri, 27 Sept 2024 at 06:57, Aditya Singh wrote: > I am just contacting you to talk about a current issue with our database. > We have run out of a positive sequence in one of our tables and are now > operating with negative sequences. To address this, we plan to migrate from > the int4 ID column to an int8 ID column. > > The plan involves renaming the int8 column to the id column and setting > it as the primary key. However, this process will require downtime, which > may be substantial in a production environment. Fortunately, we have noted > that other tables do not use the id column as a foreign key, which may > help mitigate some concerns. > Our Approach: > >1. > >*Create a Unique Index*: We will first create a unique index on the >new ID column before renaming it and altering it to be non-nullable. This >step will necessitate scanning the entire table to verify uniqueness. >2. > >*Add Primary Key*: After ensuring the uniqueness, we will add the ID >column as the primary key. By doing this, we hope to bypass the additional >scanning for uniqueness and nullability, as the column will already be set >as not nullable and will have the uniqueness constraint from the unique >index. > > We want to confirm if this approach will work as expected. If we should be > aware of any potential pitfalls or considerations, could you please provide > insights or point us toward relevant documentation? > > Thank you so much for your help, and I look forward to your guidance. > > Best regards, > > Aditya Narayan Singh > Loyalty Juggernaut Inc. > > -- > *Confidentiality Warning:* > This message and any attachments are intended only for the use of the > intended recipient(s), are confidential, and may be privileged. If you are > not the intended recipient, you are hereby notified that any disclosure, > copying, distribution, or other use of this message and any attachments is > strictly prohibited. If received in error, please notify the sender > immediately and permanently delete it. >
Re: Suggestion for memory parameters
On Thu, 26 Sept 2024 at 16:33, yudhi s wrote: > Hello All, > > In a RDS postgres we are seeing some select queries when running and doing > sorting on 50 million rows(as its having order by clause in it) , the > significant portion of wait event is showing as "IO:BufFileWrite" and it > runs for ~20minutes+. > > Going through the document in the link below, it states we should monitor > the "FreeLocalStorage" metric and when monitoring that, I see it showing up > to ~535GB as the max limit and when these queries run this goes down till > 100GB. Note- (it's a R7g8xl instance) > > > https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html > > We were thinking of bumping up the work_mem to a higher value in database > level , which is currently having size 4MB default. But we will also have > ~100 sessions running at same time and majority were from other > applications which execute other single row "insert" queries and I hope > that will not need high "work_mem" . And setting it at database level will > consume 100 times that set work_mem value. So how to handle this situation? > Or > Is it fine to let it use "FreeLocalStorage" unless it goes till zero? > > Also I am confused between the local storage (which is showing as 535GB) > vs the memory/RAM which is 256GB for this instance class with ~128TB max > storage space restriction, how are these storage different, (mainly the > 535GB space which it's showing vs the 128TB storage space restriction)? > Appreciate your guidance. > > select query looks something as below with no Joins but just single table > fetch:- > > Select > from > where > order by column1, column2 LIMIT $b1 OFFSET $B2 ; > > > My 2 cents I think you should set the work_mem on specific session level , if your sorting queries are only from specific handful of sessions, as because setting it up at database level will eat up your most of RAM(which you said is 256GB) and you said 100+ sessions getting spawned at any point in time.
