4 ms·

Postgres locks explorer

130 points by leontrolski 3y ago

cfchou360 3y ago
Thanks! Very useful tool. Another one I often use is https://postgres-locks.husseinnasser.com/ https://postgres-locks.husseinnasser.com/
peter_l_downs 3y ago
This is pretty cool as background information, but if you've ever run into a situation where you're not sure why something is locking, this kind of theoretical info isn't that helpful. If you want to know what queries to kill, and what's blocked on what, check out these two very helpful Postgresql wiki pages: - https://wiki.postgresql.org/wiki/Lock_Monitoring https://wiki.postgresql.org/wiki/Lock_Monitoring - https://wiki.postgresql.org/wiki/Lock_dependency_information https://wiki.postgresql.org/wiki/Lock_dependency_information The "Recursive view of blocking" on the second page has been extremely helpful to me. You don't ever want to need this query but if you do, it's great. You should follow the page's recommendation and set it up as a view you can use if shit ever hits the fan. ...although you may want to show all the locks each pid has acquired, instead of the summary the query gives as written, by modifying it to use array_to_string(locks_acquired, E'\n') instead of array_to_string(locks_acquired[1:5] || CASE WHEN array_upper(locks_acquired,1) > 5 THEN '... '||(array_upper(locks_acquired,1) - 5)::text||' more ...' END, E'\n ')
- TranquilMarmot 3y ago
  This is a great tip. There are definitely a lot of times I've had to look this up in the middle of the night after getting paged.
frenchie4111 3y ago
This is very cool, thank you. You should add this to https://github.com/dhamaniasad/awesome-postgres https://github.com/dhamaniasad/awesome-postgres
- peter_l_downs 3y ago
  This is very cool, thank you. You should add this to https://github.com/bayandin/awesome-awesomeness https://github.com/bayandin/awesome-awesomeness
  - thih9 3y ago
    It is there already.
Dwedit 3y ago
Totally parsed the headline incorrectly and thought it was about a bug in Postgres that caused explorer.exe to freeze.
- leosanchez 3y ago
  You are not alone :)
- wilhelmtellavif 3y ago
  So I'm not as segfaulted as my self-check feature has reported.

130 points by leontrolski 3y ago

Thanks! Very useful tool. Another one I often use is https://postgres-locks.husseinnasser.com/ https://postgres-locks.husseinnasser.com/

peter_l_downs 3y ago

This is pretty cool as background information, but if you've ever run into a situation where you're not sure why something is locking, this kind of theoretical info isn't that helpful. If you want to know what queries to kill, and what's blocked on what, check out these two very helpful Postgresql wiki pages: - https://wiki.postgresql.org/wiki/Lock_Monitoring https://wiki.postgresql.org/wiki/Lock_Monitoring - https://wiki.postgresql.org/wiki/Lock_dependency_information https://wiki.postgresql.org/wiki/Lock_dependency_information The "Recursive view of blocking" on the second page has been extremely helpful to me. You don't ever want to need this query but if you do, it's great. You should follow the page's recommendation and set it up as a view you can use if shit ever hits the fan. ...although you may want to show all the locks each pid has acquired, instead of the summary the query gives as written, by modifying it to use array_to_string(locks_acquired, E'\n') instead of array_to_string(locks_acquired[1:5] || CASE WHEN array_upper(locks_acquired,1) > 5 THEN '... '||(array_upper(locks_acquired,1) - 5)::text||' more ...' END, E'\n ')

TranquilMarmot 3y ago
This is a great tip. There are definitely a lot of times I've had to look this up in the middle of the night after getting paged.

frenchie4111 3y ago

This is very cool, thank you. You should add this to https://github.com/dhamaniasad/awesome-postgres https://github.com/dhamaniasad/awesome-postgres

peter_l_downs 3y ago
This is very cool, thank you. You should add this to https://github.com/bayandin/awesome-awesomeness https://github.com/bayandin/awesome-awesomeness
- thih9 3y ago
  It is there already.

Dwedit 3y ago

Totally parsed the headline incorrectly and thought it was about a bug in Postgres that caused explorer.exe to freeze.

leosanchez 3y ago
You are not alone :)
wilhelmtellavif 3y ago
So I'm not as segfaulted as my self-check feature has reported.