[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)