Pg_stat_activity

Count PostgreSQL Sessions by State This PostgreSQL query returns a count of all server sessions grouped by their current state. It gives a fast at-a-glance view of how connections are distributed across active, idle, and problematic states. Purpose and Overview PostgreSQL tracks the state of every backend connection in …

PostgreSQL Query Planning Settings Query Guide This PostgreSQL query retrieves all query tuning and statistics collection settings from the pg_settings system view. These parameters control how the query planner selects execution strategies, estimates costs, and collects runtime statistics. Purpose and Overview The …

PostgreSQL Statistics Settings Query Guide This PostgreSQL query retrieves all statistics collection configuration settings from the pg_settings system view. These parameters control what runtime data PostgreSQL collects about query execution, table activity, and performance, feeding into the pg_stat_* monitoring …

Monitor PostgreSQL Active Sessions with pg_stat_activity pg_stat_activity is the system view that shows every backend process running against a PostgreSQL server right now — one row per server process, with the client address, the query it is running, how long it has been running, and what kind of backend it is. A …

Monitor Running Queries in PostgreSQL with pg_stat_activity Where pg_stat_statements aggregates queries that already finished and pgBadger reads logs after the fact, pg_stat_activity is the real-time snapshot: one row per server process, showing what each one is doing at the moment the query runs. It is the right …

Monitor Slow Running Queries in PostgreSQL with pg_stat_activity An application hangs, the connection pool fills up, and somewhere a statement has been running for minutes. pg_stat_activity shows it for every backend on the server: how long the current query has been going, which role and database it belongs to, and …

PostgreSQL Lock Monitoring: Gain Insights into Locks with Query and Age Purpose This PostgreSQL SQL query equips you to monitor locks held by active database sessions along with their associated queries and age. This detailed information proves invaluable for performance tuning, troubleshooting lock conflicts, and …

PostgreSQL Lock Monitoring: Identify Blocking Locks and Resolve Application Conflicts Purpose In a busy PostgreSQL environment, transactions can sometimes become blocked, waiting for other transactions to release locks. This can slow performance and even cause stalls. This article dissects a powerful PostgreSQL script …

PostgreSQL: Identify Slow Running Queries in Real-Time Purpose This PostgreSQL SQL query provides a real-time snapshot of sessions whose current query (or, for idle sessions, most recent query) started more than 30 seconds ago. It acts as a powerful alternative to a traditional slow query log, helping you proactively …

Mastering PostgreSQL Locks: Troubleshooting and Performance Insights In a busy PostgreSQL database, multiple transactions might try to access the same resources simultaneously. Locks are PostgreSQL's way of handling concurrency, ensuring data consistency and preventing conflicts. This article dives into PostgreSQL lock …