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.

If you use separate postgres users for different service components you an also set `statement_timeout` on the user itself.

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.

Quote: "Windows does not have a kill command..."

Yes, she does. It's called "taskkill"

Or at least in my experience using pskill from the Sysinternals Suite, even if you kill a process (at least on earlier versions of Windows), it would not do the same as a SIGKILL (as in "tear down the process, don't wait for it whatsoever"), and would otherwise hang or be waiting for something else.

The trick is to run "taskkill" as user SYSTEM, using "run as". It will kill anything and everything, including generating a nice BSOD if you feel like (kill System process).

Or if you're a GUI kind of guy and want a nice interface with plenty of options I recommend "Process Hacker" (https://processhacker.sourceforge.io/).

I've used SuperF4 in the past for this:

https://stefansundin.github.io/superf4/

It seems to use the win32 TerminateProcess() function, I've never seen it fail although apparently according to the doc it does need to wait for pending I/O.

ProcessHacker looks interesting, I used Sysinternals' Process Explorer for the longest time and this appears to be a fork of it?

I learned that from the timeless guide book, 'Windows as she is administered.'

https://github.com/dalibo/pg_activity ('a top-like application for PostgreSQL server activity monitoring') is quite useful.

This is really nice, are there any preventative measures one could take as well? For example setting a max query run time or something like that?

There's a "statement timeout" feature you'd find helpful - https://www.crunchydata.com/blog/control-runaway-postgres-qu...

Ooohh. That pg_blocking_pids(blockedpid) function is a nice one to add to the arsenal.

How can you find and stop users which are accessing a certain table?

The pg_stat_activity table has that info. Just select *

There’s always “sudo reboot”, too :)