PostgreSQL Scripts for Database Administration

Restarting All PostgreSQL Sequences with SQL Script Database administrators often face challenges with PostgreSQL sequences, especially after data has been deleted or when tables have been re-imported. Sequences drive the generation of auto-incrementing values (like primary keys using SERIAL or BIGSERIAL), and …

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 …

How to Analyze PostgreSQL Query Cache-Hit Ratio Using pg_stat_statements Purpose Measuring the cache-hit ratio of your PostgreSQL queries is essential for understanding and optimizing how efficiently your database serves data from memory versus disk. This article explains how to use the pg_stat_statements extension to …

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 …

Monitor PostgreSQL Recovery Status with pg_is_in_recovery Is this server a standby, and is it still applying WAL? One SELECT answers both questions. It calls pg_is_in_recovery() and the functions that report how far a standby has received and replayed the write-ahead log (WAL), and it adds the two legacy …

Prioritize PostgreSQL Table Analysis: Unlocking Performance Insights Maintaining optimal PostgreSQL database performance requires regular table analysis. This process lets the query planner make informed decisions based on accurate table statistics. This article dives into a valuable PostgreSQL script revealing tables …