postgresql UPDATE query taking forever with 3m rows

Question

This is the table schema:

CREATE TABLE public.page_by_category
(
    id integer NOT NULL DEFAULT nextval('page_by_category_id_seq'::regclass),
    page_id bigint NOT NULL,
    category_id bigint NOT NULL,
    weight integer NOT NULL,
    CONSTRAINT id_pk PRIMARY KEY (id),
    CONSTRAINT category_id_fkey FOREIGN KEY (category_id)
    CONSTRAINT page_id_fkey FOREIGN KEY (page_id)
)

This is the query that takes long: UPDATE page_by_category SET weight=0 on 3m rows.

looking at pg_stat_activity this is the result:

30366   "2 days 18:32:12.141453"    "user"  "UPDATE page_by_category SET weight=0"

how can i check if the query is stuck? since no IO, CPU heavy usage ... on my centos i'm using top, iotop to see if cpu is used or disk but nothing more then 5% usage...

PostgreSQL version: ""PostgreSQL 10.7 on x86_64-pc-linux-gnu"

This query should not take 2 days to run. It sounds like there might be some sort of deadlock or contention. — Gordon Linoff
– Gordon Linoff, Commented Feb 22, 2021 at 11:49
@EmrahMehmedov: it's probably "only" a lock not a deadlock (because if it was a deadlock you would have gotten an error message) — user330315
– user330315, Commented Feb 22, 2021 at 11:57
lock issue appear before the query starts i guess? i can see the query is not blocked by any lock... — Emrah Mehmedov
– Emrah Mehmedov, Commented Feb 22, 2021 at 12:12

Laurenz Albe · Accepted Answer · 2021-02-22 15:02:00Z

2

Since the process ID of your hanging update is 30366, you should look for open transactions that hold a lock that blocks the statement:

SELECT pg_blocking_pids(30366);

Figure out what is wrong with these connections and why they hold locks for such a long time.

To kill them, run

SELECT pg_terminate_backend(?????);

where ????? is one of the blocking process IDs found with the above query.

If it is not a lock that blocks your query, the following possibilities remain:

you have terribly slow storage
you have expensive row level triggers
you have quite a lot of indexes

edited Feb 22, 2021 at 15:02

answered Feb 22, 2021 at 13:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Emrah Mehmedov Over a year ago

executing SELECT pg_blocking_pids(30366); and result is: {}

Laurenz Albe Over a year ago

Then you must have terribly slow storage, or expensive row level triggers, or quite a lot of indexes.

Emrah Mehmedov Over a year ago

to be honest, table has triggers which is synching with another db on another postgresql server... might be a network latency issue as well? what i'm not sure is the trigger is executed for each updated row? like update row then trigger sends the updated row to the other server and again another row ... ?

Laurenz Albe Over a year ago

Yes, that is definitely the problem. For each row, you incur at least twice the network latency. It is almost always a very bad idea to have a trigger that does something complicated or interacts with something outside the database. That should be handled on the application side.

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-02-22 11:59:34Z

1

This should not take much time. Kill the process and execute the query again and check whether it's getting stuck again.

SELECT pg_cancel_backend(<pid of the process>)

If you don't know the pid then you can try below query:

SELECT * FROM pg_stat_activity WHERE state = 'active';

Then find the process you want to kill.

If the process is still there then try:

SELECT pg_terminate_backend(<pid of the process>)

(If possible restart the DB after killing the query and then try it again.)

answered Feb 22, 2021 at 11:59

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

5 Comments

Emrah Mehmedov Over a year ago

well, first im trying to understand what is going on before i go cancel the query or restart db... if query is not stuck i don't wanna interrupt it...

Kazi Mohammad Ali Nur Romel Over a year ago

Though I am not an expert of Postgres rather SQL Server but it seems either there is a deadlock situation or the query is just stuck it is not going anywhere. An update on only 3M rows should not take much time.

Emrah Mehmedov Over a year ago

isn't there any way to check if query i stuck?

Kazi Mohammad Ali Nur Romel Over a year ago

Definitely there is. Let's try to find the way.

Kazi Mohammad Ali Nur Romel Over a year ago

Have you tried SELECT * FROM pg_stat_activity WHERE state = 'active';

Collectives™ on Stack Overflow

postgresql UPDATE query taking forever with 3m rows

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related