Observed Signal · Jun 11, 2026 · Technical Guide · Source: DEV Community · Impact: 1/5 · Sentiment: Neutral
View and Kill Running Postgres Queries
This technical guide explains how to inspect and terminate running PostgreSQL queries using the pg_stat_activity system view and backend control functions. It provides SQL examples to list non-idle connections (including pid, state, query text, and duration), explains why "idle in transaction" sessions are dangerous, and shows how to cancel a query with pg_cancel_backend(pid) or forcibly drop a connection with pg_terminate_backend(pid). The post includes a bulk-termination example for idle-in-transaction sessions older than five minutes. For Supabase users, the same commands work in the SQL Editor with the default postgres role, but the article warns not to terminate Supabase background processes (usernames like supabase_admin or authenticator).
Practical operational guidance for managing PostgreSQL queries; useful to engineers but not industry-shifting for AdTech/MarTech.
Track Supabase Signals & Market Shifts in Real-Time
Polaris7 autonomous intelligence agents track regulatory filings, primary sources, executive changes, and deal flow 24/7. Create your free Explorer workspace to monitor these entities.
Key Takeaways & Evidence Grounding
- Postgres exposes active connections and their activity via the pg_stat_activity system view.
- Example query lists pid, state, query, and duration for all non-idle processes, ordered by duration.
- Use SELECT pg_cancel_backend(pid) to request cancellation of a running query and SELECT pg_terminate_backend(pid) to forcibly terminate a backend connection.
- A sample bulk command terminates connections WHERE state = 'idle in transaction' AND query_start < now() - interval '5 minutes'.
- On Supabase the SQL Editor supports pg_stat_activity, pg_cancel_backend and pg_terminate_backend; avoid killing Supabase background processes (e.g., supabase_admin, authenticator).
Connected Companies & Entities
5 Entities mappedOntology Mapping & Concepts
Related Market Signals & Shifts
Recent verified developments and strategic activity across this market segment.
Postgres Health Monitor with Kill Button in SQL Client
A developer added a Connection Health Monitor feature to the open-source SQL client data‑peek. The Health Monitor is a dedicated tab that polls PostgreSQL system views at configurable intervals and displays four panels: Active Queries (with duration, wait events, and a kill button), Table Sizes, Cache Hit Ratios, and Locks & Blocking (blocked vs. blocking queries). The kill button issues pg_cancel_backend(pid). The article publishes the exact SQL queries used, describes a thin IPC handler → adapter architecture, calls out implementation details (filters, CASE guards, IS NOT DISTINCT FROM for null-safe joins), and links to the code paths and datapeek.dev. The project is MIT-licensed and intended as a pragmatic DB troubleshooting tool rather than a destructive control (pg_terminate_backend is intentionally not exposed).
PostgreSQL Connection Pooling: PgBouncer vs Supavisor
This technical guide explains why PostgreSQL connection overhead matters (each client connection spawns an OS process using ~5–10 MB) and shows how connection pooling prevents max_connections and memory exhaustion. It provides diagnostic SQL queries to find idle and idle-in-transaction connections, a practical pool-sizing heuristic (optimal_pool_size = (CPU_cores * 2) + number_of_disks), and concrete configuration examples for PgBouncer (transaction pool_mode, pool sizing, timeouts). The article describes Supavisor — Supabase’s Elixir pooler — as a cloud-native, multi-threaded alternative that supports named prepared statements in transaction mode and per-tenant isolation. It also recommends small application-level pools when used alongside an external pooler, and operational controls (idle_in_transaction_session_timeout, statement_timeout) to reclaim wasted connections. The post notes PostgreSQL (as of v17) has no built-in connection pooling, so external poolers are essential for production workloads with significant concurrency.
How to Perform Online Bulk Deletes in PostgreSQL
This technical guide explains the full-system mechanics and risks of large-scale DELETE operations in PostgreSQL. The author argues that the SQL DELETE is the easy part; the real challenges are managing the downstream subsystems the delete feeds — MVCC tuple versioning, WAL generation (including full-page images), autovacuum, replication (physical and logical) and replication slots. Recommended practices include tiny transactional batches with per-batch COMMITs to advance the xmin horizon, keyset pagination, proactive vacuum/autovacuum tuning, monitoring and gating on both physical replica lag (seconds) and logical slot WAL retention (bytes), committing before pauses to avoid pinning restart_lsn, and preferring partition DETACH/DROP when possible. The post gives concrete SQL/psql patterns, monitoring queries, and operational checklists for resumable, observable, and safe large purges.
Track Real-Time Market Signals & Shifts
Set up custom watchlists to receive automated, evidence-grounded executive digests whenever material signals or shifts occur across your tracked landscape.