[HN Gopher] How to Find and Stop Running Queries on PostgreSQL ___________________________________________________________________ How to Find and Stop Running Queries on PostgreSQL Author : j4mie Score : 45 points Date : 2022-06-20 12:15 UTC (10 hours ago) (HTM) web link (adamj.eu) (TXT) w3m dump (adamj.eu) | aidos wrote: | Ooohh. That pg_blocking_pids(blockedpid) function is a nice one | to add to the arsenal. | SnowHill9902 wrote: | How can you find and stop users which are accessing a certain | table? | superb-owl wrote: | This process has saved me from an outage more than once. | | I keep a dashboard of running pids, and get alerts when any of | them take more than N minutes (excepting autovacuum). Once every | few months I have to psql into prod and cancel one. | nrmitchi wrote: | If you use separate postgres users for different service | components you an also set `statement_timeout` on the user | itself. | CraigJPerry wrote: | Yeah that's a super handy resource for all sorts of stuff, if | you dump a view of pids by age and also a separate view of | pg_locks joined to pg_stat_activity to show blockers and their | blocked pids, and i've always dumped disk and cpu markers too, | then journal that every few seconds to disk, it's super handy | for tailing it in a web dashboard view as you said - which also | has a convenient side benefit of stopping every man and their | granny having duplicate squizzes at the pg_* tables if there's | been a suspected wobble of some sort. | | But it's also really great for providing a cheap historical | snapshot of activity for historical investigations and course | grained analysis. | | There are potential dangers with going overboard in this like | inadvertently asserting troublesome locks or leaking sensitive | data, but fairly easy to avoid. ___________________________________________________________________ (page generated 2022-06-20 23:01 UTC)