Re: UPDATE many records

> On Jan 8, 2020, at 7:52 AM, stan wrote: > > On Tue, Jan 07, 2020 at 12:20:12PM -0900, Israel Brewster wrote: >>> On Jan 7, 2020, at 12:15 PM, Alan Hodgson wrote: >>> >>> On Tue, 2020-01-07 at 11:58 -0900, Israel Brewster wrote: > Really? Why? With the update I am only changing data

Re: UPDATE many records

> > On Jan 7, 2020, at 12:57 PM, Adrian Klaver wrote: > > On 1/7/20 1:43 PM, Israel Brewster wrote: >>> On Jan 7, 2020, at 12:21 PM, Adrian Klaver >> > wrote: >>> >>> On 1/7/20 1:10 PM, Israel Brewster wrote: > On Jan 7, 2020, at 12:01 PM, Adrian Klaver >>>

Re: UPDATE many records

On 1/7/20 1:43 PM, Israel Brewster wrote: On Jan 7, 2020, at 12:21 PM, Adrian Klaver > wrote: On 1/7/20 1:10 PM, Israel Brewster wrote: On Jan 7, 2020, at 12:01 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 1/7/20 12:47 PM, Israel Brewster wr

Re: UPDATE many records

> On Jan 7, 2020, at 12:21 PM, Adrian Klaver wrote: > > On 1/7/20 1:10 PM, Israel Brewster wrote: >>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver >>> wrote: >>> >>> On 1/7/20 12:47 PM, Israel Brewster wrote: One potential issue I just thought of with this approach: disk space. Will I

Re: UPDATE many records

On 1/7/20 1:10 PM, Israel Brewster wrote: On Jan 7, 2020, at 12:01 PM, Adrian Klaver wrote: On 1/7/20 12:47 PM, Israel Brewster wrote: One potential issue I just thought of with this approach: disk space. Will I be doubling the amount of space used while both tables exist? If so, that would

Re: UPDATE many records

> On Jan 7, 2020, at 12:15 PM, Alan Hodgson wrote: > > On Tue, 2020-01-07 at 11:58 -0900, Israel Brewster wrote: >>> >> Really? Why? With the update I am only changing data - I’m not adding >> any additional data, so the total size should stay the same, right? >> I’m obviously missing something…

Re: UPDATE many records

On Tue, 2020-01-07 at 11:58 -0900, Israel Brewster wrote: > > > Really? Why? With the update I am only changing data - I’m not adding > any additional data, so the total size should stay the same, right? > I’m obviously missing something… :-) > PostgreSQL keeps the old row until it gets vacuumed

Re: UPDATE many records

> On Jan 7, 2020, at 12:01 PM, Adrian Klaver wrote: > > On 1/7/20 12:47 PM, Israel Brewster wrote: >> One potential issue I just thought of with this approach: disk space. Will I >> be doubling the amount of space used while both tables exist? If so, that >> would prevent this from working -

Re: UPDATE many records

On 1/7/20 12:58 PM, Israel Brewster wrote: On Jan 7, 2020, at 11:56 AM, Alan Hodgson wrote: On Tue, 2020-01-07 at 11:47 -0900, Israel Brewster wrote: One potential issue I just thought of with this approach: disk space. Will I be doubling the amount of space used while both tables exist? If so

Re: UPDATE many records

On 1/7/20 12:47 PM, Israel Brewster wrote: One potential issue I just thought of with this approach: disk space. Will I be doubling the amount of space used while both tables exist? If so, that would prevent this from working - I don’t have that much space available at the moment. It will def

Re: UPDATE many records

On Tue, 2020-01-07 at 11:47 -0900, Israel Brewster wrote: > One potential issue I just thought of with this approach: disk space. > Will I be doubling the amount of space used while both tables exist? > If so, that would prevent this from working - I don’t have that much > space available at the mo

Re: UPDATE many records

> On Jan 7, 2020, at 11:56 AM, Alan Hodgson wrote: > > On Tue, 2020-01-07 at 11:47 -0900, Israel Brewster wrote: >> One potential issue I just thought of with this approach: disk space. >> Will I be doubling the amount of space used while both tables exist? >> If so, that would prevent this from

Re: UPDATE many records

One potential issue I just thought of with this approach: disk space. Will I be doubling the amount of space used while both tables exist? If so, that would prevent this from working - I don’t have that much space available at the moment. --- Israel Brewster Software Engineer Alaska Volcano Obse

Re: UPDATE many records

Nice approach! Using that method, I can do the select to generate the new table in only 6 minutes! I’m sure it helps that through a slightly creative use of Joins, I can re-write my function to actually be part of the select, just using bult-in trig functions, rather than having to called a stor

RE: UPDATE many records

You don’t tell us if other users will be concurrently changing any of the records involved. If you could guarantee that the table won’t be changed, you might be better off doing a CREATE TABLE table_new as SELECT … FROM table_old, dropping table_old, and finally renaming table_new. Given the

Re: UPDATE many records

system monitor i like is glances, have to have python installed to run, it can run in command console or present a web interface. its a very nice to get a quick detail few what is going on https://nicolargo.github.io/glances/ just monitoring the system CPU utilization does not give enough infor

Re: UPDATE many records

On Mon, 6 Jan 2020 at 17:38, Israel Brewster wrote: > > Sure. But I feel we are getting a bit off track. Optimizing the runtime of > the update is great, but this is a one-off (hopefully) event. I want to > accomplish it as quickly as possible, of course, but at the same time it > doesn’t make se

Re: UPDATE many records

> On Jan 6, 2020, at 12:49 PM, Justin wrote: > > What was the HD wait time ? What tool is being use to monitor the server > resources?? No idea on the HD wait time - how would I best monitor that? That said, this machine does have NVMe drives, so the speed should be fairly high/wait time fa

Re: UPDATE many records

What was the HD wait time ? What tool is being use to monitor the server resources?? It appears based on this information there is allot more going on than a simple Update command Moving code out of the trigger probably not going to improve performance, unless there is allot of code that does

Re: UPDATE many records

On Mon, Jan 6, 2020 at 2:34 PM Mark Zellers wrote: > Just out of curiosity, what kind of trigger are you using, a row level > trigger or a statement level trigger? If you are using a row level > trigger, see if you can achieve your requirements using a statement level > trigger instead. I’m rel

RE: UPDATE many records

. Regards, Mark Z. From: Israel Brewster Sent: Monday, January 6, 2020 1:24 PM To: Michael Lewis Cc: Rob Sargent ; Alban Hertroys ; Christopher Browne ; pgsql-generallists.postgresql.org Subject: Re: UPDATE many records On Jan 6, 2020, at 11:54 AM, Michael Lewis mailto:mle...@entrata.com>>

Re: UPDATE many records

> On Jan 6, 2020, at 11:54 AM, Michael Lewis wrote: > > I’m thinking it might be worth it to do a “quick” test on 1,000 or so records > (or whatever number can run in a minute or so), watching the processor > utilization as it runs. That should give me a better feel for where the > bottleneck

Re: UPDATE many records

> > I’m thinking it might be worth it to do a “quick” test on 1,000 or so > records (or whatever number can run in a minute or so), watching the > processor utilization as it runs. That should give me a better feel for > where the bottlenecks may be, and how long the entire update process would > t

Re: UPDATE many records

> On Jan 6, 2020, at 11:40 AM, Rob Sargent wrote: > > > >> On Jan 6, 2020, at 1:29 PM, Alban Hertroys > > wrote: >> >> I think you’re overcomplicating the matter. >> >> I’d just do it as a single update in one transaction. It’s only 50M rows. It >> may take half

Re: UPDATE many records

> On Jan 6, 2020, at 11:38 AM, Christopher Browne wrote: > > > > On Mon, Jan 6, 2020, 3:15 PM Israel Brewster > wrote: >> On Jan 6, 2020, at 10:08 AM, Christopher Browne > > wrote: >> >> On Mon, 6 Jan 2020 at 13:36, Israel Brewster >

Re: UPDATE many records

> On Jan 6, 2020, at 1:29 PM, Alban Hertroys wrote: > > I think you’re overcomplicating the matter. > > I’d just do it as a single update in one transaction. It’s only 50M rows. It > may take half an hour or so on decent hardware, depending on how > resource-intensive your function is. > I

Re: UPDATE many records

On Mon, Jan 6, 2020, 3:15 PM Israel Brewster wrote: > On Jan 6, 2020, at 10:08 AM, Christopher Browne > wrote: > > On Mon, 6 Jan 2020 at 13:36, Israel Brewster > wrote: > >> Thanks to a change in historical data, I have a need to update a large >> number of records (around 50 million). The upda

Re: UPDATE many records

As you have access to Procedure, you can create a loop then issue an Begin Update Commit so something like this should work plpgsql declare icount int = 0; new_count int = 0; begin select count(*) into icount from mytable; loop begin ; Update mytable set myvalue = newvalue() where id

Re: UPDATE many records

> On 6 Jan 2020, at 21:15, Israel Brewster wrote: > >> On Jan 6, 2020, at 10:08 AM, Christopher Browne wrote: >> >> On Mon, 6 Jan 2020 at 13:36, Israel Brewster wrote: >> Thanks to a change in historical data, I have a need to update a large >> number of records (around 50 million). The upd

Re: UPDATE many records

> On Jan 6, 2020, at 10:08 AM, Christopher Browne wrote: > > On Mon, 6 Jan 2020 at 13:36, Israel Brewster > wrote: > Thanks to a change in historical data, I have a need to update a large number > of records (around 50 million). The update itself is straight forwar

Re: UPDATE many records

Good information. I did forget to mention that I am using PostgreSQL 11.5. I also was not aware of the distinction between PROCEDURE and FUNCTION, so I guess I used the wrong terminology there when stating that new_value is the result of a stored procedure. It’s actually a function. So would yo

Re: UPDATE many records

There are several ways to actually do this If you have Postgresql 11 or higher we now have Create Procedure that allows committing transactions, one draw back is it can not parallel from inside the procedure https://www.postgresql.org/docs/11/sql-createprocedure.html https://severalnines.com/dat

Re: UPDATE many records

On Mon, 6 Jan 2020 at 13:36, Israel Brewster wrote: > Thanks to a change in historical data, I have a need to update a large > number of records (around 50 million). The update itself is straight > forward, as I can just issue an "UPDATE table_name SET > changed_field=new_value();" (yes, new_valu

Re: UPDATE many records

On 1/6/20 10:36 AM, Israel Brewster wrote: Thanks to a change in historical data, I have a need to update a large number of records (around 50 million). The update itself is straight forward, as I can just issue an "UPDATE table_name SET changed_field=new_value();" (yes, new_value is the result