Performance

How to Monitor PostgreSQL HOT Updates and Fillfactor When PostgreSQL updates a row, it normally writes a new row version and creates a new index entry pointing to it. This is safe, but it adds overhead — especially on tables with many indexes. HOT updates (Heap Only Tuple) are a smarter path. When a HOT update happens, …

pg_settings — Query PostgreSQL Configuration Parameters The live configuration PostgreSQL is running with and the values written in postgresql.conf are not always the same. A parameter that requires a server restart has already been updated in the file but the old value is still in effect. A reload that produced a …

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 …

PostgreSQL Performance Tuning: Analyze Cache Hit Ratio with pg_stat_statements Purpose This PostgreSQL query empowers you to gain critical insights into the efficiency of your query workload by measuring the cache hit ratio from the pg_stat_statements extension. Cache hit ratio is a crucial metric for evaluating how …

Analyze PostgreSQL Query Times with pg_stat_statements PostgreSQL 13 renamed the timing columns of pg_stat_statements: total_time became total_exec_time, mean_time became mean_exec_time, and the same suffix went onto the minimum, maximum and standard deviation columns. A query copied from an older tutorial fails on a …