database - Postgres SQL Update JDBC Stuck

Question

I'm running the below query through jdbcTemplate.update()

"UPDATE <tblnme>  as aa set _r=b._r from(SELECT id,parent,LEAD(id,1) OVER (partition by parent ORDER BY id) _r from <tblnme>) as b where aa.id=b.id"

This update (1 table - 28 million records) runs fine around 15 min on below m.c config

SSD 32 GB RAM Intel(R) Core(TM) i7-7700HQ CPU @ 2.80GHz 2.80 GHz

but gets stuck in below m.c config

HHD 32 GB RAM Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz 3.41 GHz

There's no message in postgres server log during the stuck phase. Had to terminate after 4 hrs wait.

have set below in postgresql.conf in both m/c:

a. max_connections = 375 b. shared_buffers = 2500MB c. max_wal_size = 16GB d. min_wal_size = 4096MB

What could be done to resolve this issue?

question from:https://stackoverflow.com/questions/65945461/postgres-sql-update-jdbc-stuck

Answer 1

Waitting for answers