A collection of SQL utilities for PostgreSQL database administration, monitoring, and performance analysis.
pg-utils is a collection of SQL utility scripts for PostgreSQL database administration and performance monitoring. It provides DBAs with tools to analyze query performance, monitor database activity, optimize indexes, check configurations, and manage permissions. The project solves the problem of needing ready-made, production-tested SQL for common PostgreSQL maintenance and troubleshooting tasks.
PostgreSQL database administrators and developers who need to monitor, tune, and maintain PostgreSQL databases in production environments. It's particularly useful for DBAs who prefer SQL-based tools over graphical interfaces.
Developers choose pg-utils because it offers a comprehensive set of battle-tested SQL scripts that provide deep insights into PostgreSQL performance without requiring additional software installation. The utilities work directly with PostgreSQL's system catalogs and extensions like pg_stat_statements.
Useful PostgreSQL utilities
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.
Creates views like create_db_activity_view.sql that provide detailed metrics on active sessions, query states, and ages, requiring track_activities enabled for accurate reporting.
Includes scripts to identify slow queries, unused indexes, and redundant indexes with specific thresholds, such as queries running >= 0.02 seconds in query_stat_time_view.sql.
Provides scripts like index_candidates_to_ssd.sql that recommend moving tables and indexes between SSD and HDD based on read/write ratios, using clear conditions like d_w_rat > 10.
Offers check_config.sql to report non-default PostgreSQL parameters and highlight changes, with aligned fields for easy comparison and debugging.
Some scripts, such as create_xlog_math_procedures.sql, have unclear documentation noted with 'Usage: ???', making implementation challenging without additional resources.
Requires separate files for different PostgreSQL versions, e.g., create_db_activity_view.sql for <9.2 and a separate file for 9.2+, adding management complexity.
Many scripts rely on extensions like pg_stat_statements and settings like track_io_timings, which must be manually enabled and can impact performance if misconfigured.