0

I have a table with 12 million rows. There are 16 columns on the table, one of which is a boolean.

After a certain business process, a large chunk of these rows will have this boolean set to false (80%). I want to update all of these values back to true.

Query would be simple enough: UPDATE table SET my_boolean=TRUE

This query takes a very long time though on my development server (quad core, SSD, 8GB ram). The last time it took over 35 minutes.

I've already deleted all indices before executing the query as I've read that could be a major slowdowner.

I've already analyzed and vacuumed the table, doesn't seem to improve.

I'm quite new to Postgres and am kind of lost as to why this simple query is taking SO long. Any pointers would be greatly appreciated.

  • 1
    Did you drop even the indices implicitly used to support constraints? Do you have any triggers or foreign key constraints? – jjanes Jan 5 at 4:50
  • 35 minutes seems egregiously slow for updating 12 million rows. How large is the table, in MB? Can you turn track_io_timing on and then run the update with EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)? Can you use system tools like 'top', 'vmstat', and 'sar' to see what the bottleneck is? – jjanes Jan 5 at 5:00
  • Hmmm... there appears to be definite agreement that > 0.5hr is way too much for your update - indeed, it appears this way to me too, especially with an SSD! Can you provide the DDL of the table? Are you doing this update programatically - the only thing that I can think of is that each update is being committed individually? How much space is free on the disk? p.s. welcome to the forum! :-) – Vérace Jan 5 at 7:40
  • 1
    You can improve that a bit by only updating those that need the update, UPDATE table SET my_boolean=TRUE where not my_boolean. If an update takes that long, maybe the table is already bloated. Did you run vacuum full _before` running the update? Breaking it up into chunks will not improve the overall speed (if at all it will make things slower) – a_horse_with_no_name Jan 5 at 7:48
  • I would suspect your UPDATE to wait for a lock, rather than just being slow. Did you check pg_stat_activity to see if this is the case? – a_horse_with_no_name Jan 5 at 7:53
0

this is one of the big weaknesses of Postgresql and MVCC (multi-version concurrency control) design. when doing a simple update on a table does not just update this column but creates entirely new rows for each updated record. this solves lots of other issues specifically reducing record locking.

My suggestion would be to break the update into to smaller chunks 10 TO 20k records to cut on IO issues with other users, and if the update rolls back for whatever reason you do not have to start at the beginning just restart on the last successful update

parallelism most likely will not help in this case as the update will be IO bound not CPU bound. throwing more threads or processes will just fill up the IO buffers faster

| improve this answer | |
  • 1
    Not sure that I agree here - I remember a Tom Kyte (of Oracle fame) being asked if an update should be "chunked" and he said no - the new records will basically be pointed to until committed and it doesn't matter if the update is 1 record or 1 million. AFAIK, the MVCC (record-shadowing, record-versioning) strategy of PostgreSQL is also that of Oracle, so maybe the same answer applies - but I am open to correction here! I know that MySQL has utilities from Percona and Shlomi Noach which help chunking updates, but I thought this was an InnoDB thing. Be interested to find out! – Vérace Jan 5 at 7:47
  • @Vérace: I too doubt this. Splitting up a large transaction typically does not speed up things in Postgres (and Oracle as you mentioned) – a_horse_with_no_name Jan 5 at 7:49
  • @Vérace I did not say it would speed things up, i said to break it up for concurrency for other user and if the exception occurred one is not starting at the beginning – zsheep Jan 5 at 11:29
  • The beauty of the MVCC system is that writers don't block readers so that unless other users are also updating the same field on the same table, this shouldn't be an issue. Optimistic concurrency is generally considered the best approach for optimal performance. As for an exception occurring, it could well be that one would want all of the updates to either succeed or fail as a unit (definition of a transaction). I never mentioned speed either, so that's not an issue between us but it is the reason for the OP's question! – Vérace Jan 5 at 11:57
  • I'm sure we agree, but with update taking so long it will cause other connections to slow waiting for IO buffers to clear if the other users do updates... Plus shared buffers will cleared out by the update, so even selects will be slowed. The OP actually gave allot information without stating it, one this is development machine, vacuumed table, and deleted the indexes, so clearly has exclusive access and the update is taking forever, odds are this is strictly IO issues, unless there is trigger that is killing it. that goes and updates other tables. – zsheep Jan 5 at 12:13
0

There is a lot of data missed in the question. There are several conditions have to be taken into account:

  1. Is table fully vacuumed before update?.
    • If table heavily fragment the update will take longer.
  2. Is storage busy with other tasks?
  3. Any concurrent activity on same table?
    • Check waits of your session.
  4. Are other columns holds too much data?
  5. Is table partitioned by updated column?
| improve this answer | |
0

I have similar problem. If you have primary key in your table you can to something like that:

with table as (
select id from sourcetable where mybool = false
)

update sourcetable st 
set mybool = true
from table tt
where tt.id = st.id

surcetable - your table; column "id" - your primary key; column "mybool" - your bool column;

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.