Performance Tuning

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 …

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 Resource Settings Query Guide This PostgreSQL query retrieves all resource usage configuration settings from the pg_settings system view. Resource settings control how PostgreSQL allocates memory, disk, and CPU resources across queries, background processes, and maintenance operations. Purpose and Overview …

List PostgreSQL Tables by Size with SQL This PostgreSQL query lists all tables in the current database sorted by their total size, largest first. It uses pg_total_relation_size() to include the table data, indexes, TOAST storage, and free space map in the size calculation. Purpose and Overview Disk space pressure is a …

PostgreSQL Table Row Count Estimates with SQL This PostgreSQL query returns estimated row counts for all user tables, sorted by the largest tables first. It reads from PostgreSQL's cumulative statistics rather than scanning the tables, making it near-instant even on tables with billions of rows. Purpose and Overview …

PostgreSQL Memory Settings Query: Monitor Database Memory Configuration This PostgreSQL query helps database administrators monitor and analyze memory-related configuration settings in their database instance. By querying the pg_settings system catalog view, you can quickly identify current memory parameters, their …

PostgreSQL Logging Configuration Query - Monitor Database Settings This PostgreSQL query provides database administrators with essential insights into logging configuration parameters by querying the pg_settings system view. The query specifically targets logging-related settings while filtering out WAL (Write-Ahead …

PostgreSQL Client Connection Settings: Complete Guide to pg_settings Query This PostgreSQL query provides a comprehensive view of client connection default settings by querying the pg_settings system view, allowing database administrators to analyze and understand configuration parameters that affect client behavior. …

PostgreSQL Autovacuum Settings Query: Complete Guide to pg_settings Analysis This PostgreSQL query provides database administrators with essential insights into autovacuum configuration parameters by querying the pg_settings system view. Understanding these settings is crucial for optimal database performance and …

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 …