Pg_stat_activity
List PostgreSQL Wait Events with the pg_wait_events View
May 25, 2026 / · 8 min read · pg_wait_events wait events system catalog postgresql 17 reference administration pg_stat_activity ·List PostgreSQL Wait Events with the pg_wait_events View PostgreSQL 17 added pg_wait_events, a system view that enumerates every wait event the running server can record. Prior to 17 the same information lived only in the documentation — no in-database way to confirm a wait name or attach a description to it. The view …
Read MoreIdentify Running PostgreSQL Autovacuum Worker Sessions
May 24, 2026 / · 7 min read · pg_stat_activity pg_stat_progress_vacuum autovacuum backend_type vacuum administration pg_stat_user_tables ·Identify Running PostgreSQL Autovacuum Worker Sessions Autovacuum lag is a slow accumulation. Every UPDATE rewrites the row and creates a new HOT chain, the launcher falls behind during write spikes, and over weeks a busy OLTP cluster ends up with three workers stuck on a handful of huge tables while smaller ones wait …
Read MoreMonitor PostgreSQL Wait Events Using pg_stat_activity
May 17, 2026 / · 4 min read · pg_stat_activity wait events performance monitoring session monitoring administration ·Understanding PostgreSQL Wait Events for Performance Analysis Every active PostgreSQL backend is either executing work or waiting. When a session waits, PostgreSQL records the reason in pg_stat_activity using two columns: wait_event_type and wait_event. Aggregating these across all sessions gives a clear picture of …
Read MoreMonitor PostgreSQL Vacuum Progress with pg_stat_progress_vacuum
May 16, 2026 / · 4 min read · pg_stat_progress_vacuum vacuum autovacuum performance monitoring administration pg_stat_activity pg_stat_user_tables ·Tracking Active Vacuum and Autovacuum Workers in PostgreSQL PostgreSQL vacuum is the maintenance process that removes dead tuples left behind by updates and deletes, reclaims storage, and prevents transaction ID wraparound. On active databases, autovacuum runs continuously in the background, but knowing whether it is …
Read MoreCancel vs Terminate PostgreSQL Backends Explained
Cancel vs Terminate PostgreSQL Backends: What Every DBA Should Know PostgreSQL gives you two tools for dealing with problem backends: pg_cancel_backend() and pg_terminate_backend(). They sound similar but behave very differently. Using the wrong one can disrupt users unnecessarily, or leave a problematic connection in …
Read MoreDetect PostgreSQL Transaction ID Wraparound Risk
Apr 3, 2026 / · 5 min read · postgresql administration vacuum monitoring database pg_class pg_database pg_settings pg_stat_activity pg_stat_user_tables ·Detect PostgreSQL Transaction ID Wraparound Before It Causes Downtime PostgreSQL assigns a transaction ID (XID) to every write transaction. These IDs are 32-bit integers, so the counter wraps around after about 4 billion transactions. Because IDs are compared modulo 2^32, each ID has two billion IDs that count as older …
Read MoreFind PostgreSQL Tables That Need VACUUM FREEZE
Apr 2, 2026 / · 3 min read · postgresql administration vacuum maintenance database pg_class pg_settings pg_stat_activity ·Find PostgreSQL Tables That Need VACUUM FREEZE PostgreSQL uses transaction IDs (XIDs) to track which rows are visible to which transactions. Over time, these IDs age. When a table's oldest unfrozen XID gets too old, PostgreSQL triggers an aggressive autovacuum to freeze it. This is controlled by the …
Read MoreMonitor PostgreSQL Index Build Progress with SQL
Mar 30, 2026 / · 4 min read · postgresql administration indexing monitoring database pg_stat_activity pg_stat_progress_create_index ·How to Monitor PostgreSQL Index Build Progress Building an index on a large table can take minutes or even hours. Without progress tracking, it is hard to know if the build is almost done or barely started. PostgreSQL 12 introduced the pg_stat_progress_create_index view to fix this. It shows real-time progress for any …
Read MoreFind Idle in Transaction Sessions in PostgreSQL
Mar 28, 2026 / · 4 min read · postgresql administration monitoring troubleshooting database pg_locks pg_stat_activity ·How to Find Idle in Transaction Sessions in PostgreSQL An idle-in-transaction session is a database connection that has started a transaction but is not currently running a query. It is just sitting there, holding the transaction open. This is different from a regular idle connection, which has no open transaction. …
Read MoreKill Idle PostgreSQL Sessions with SQL
Mar 24, 2026 / · 9 min read · postgresql database administration sql queries connection management performance tuning pg_stat_activity postgres dba ·Kill Idle PostgreSQL Sessions with SQL The connection count sits at max_connections, new clients are being refused, and pg_stat_activity is full of sessions that have run nothing for hours. Freeing the slots takes one statement, pg_terminate_backend(), but the choices around it decide whether the cleanup is safe: which …
Read More