# PostgreSQL Scripts
When working with a database, be that using, developing or supporting, it's often helpful to know what is happening inside the database. This could be investigating performance, seeking to understand data and data distributions, to check for design patterns and standards or lots more.
The postgresql-scripts (opens new window) repo contains various queries to help investigate and diagnose a postgres database. Different branches within the repo support different postgres versions.
The idea for this came from having found the DMV-Queries by Glenn Berry (opens new window) for Microsoft SQL Server helpful. That isn't to say there aren't already lots of resources for postgres available, there are, but not neccessarily easy to find, or exactly the same as these, or grouped all together. Maybe? (opens new window)
# SQL vs Utility Application
Some information with postgresql is obtained from utility applications. The postgresql-scripts repo is delibrately constrained on just the information you can obtain with sql queries.
This isn't to say that other information you would expect (particularly around OS stats, file level information, some backup stats, etc) isn't available, just that you don't obtain that information with SQL queries.
# Policy
In general the queries contained in the scripts are not particularly opinionated. We have included two policy queries:
- The missing-primary-keys query is one that most people would agree with, generally primary keys are a good thing. However there are occasions when you might choose not to create a primary key on a table, those occasions could be handled by exclusions within the query (for instance you might exclude all tables that are on the "staging" schema).
- The primary-key-names query is much more subjective. It's not really meant to suggest any particular naming convention, just that you should have one, and that testing that the implementation matches your convention is pretty easy.
Having standards and conventions is important, exactly what they are is less important. A future blog will look at implementing standards and policy tests.
# Script Index
| Category | File | Notes |
|---|---|---|
| backups | transaction-xid-wraparound | Shows the number of transactions and age of oldest xid. Gives an idea if vacuum is lagging and if there is a risk of a wrap around situation |
| backups | wal-files | Shows the locations of WAL Files, along with which file is being written too, what is being flushed to disk, etc |
| configuration | autovacuum | Shows settings for autovacuum |
| configuration | client-connections | Shows settings for client connections |
| configuration | extensions | Shows in use, and available extensions |
| configuration | file-info | Shows configuration files in use, default data directories, files for table and index data and other file related configuration |
| configuration | general-configuration | Shows version, configuration and settings for the instance |
| configuration | lock-management | Show lock settings |
| configuration | preset-options | Show options set at build (not run) time |
| configuration | query-planning | Shows query settings |
| configuration | replication | Shows replication settings |
| configuration | reporting-and-logging | Shows reporting and logging settings |
| configuration | resource-usage | Shows resource usage settings |
| configuration | statistics | Shows statistics settings |
| configuration | write-ahead-log | Shows WAL settings |
| performance | blocking | Shows currently blocked queries and pids, along with the query & pid that is blocking their execution |
| performance | function-statistics | Show execution counts and times for functions |
| performance | index-statistics | Show heavily used, potentially missing, duplicated or bad indexes, along with some internal index information |
| performance | io-stastitics | Shows tables by disk actiivty, tables with no disk activity at all, cache misses, temporary file IO, and time spent on IO operations |
| performance | memory-statistics | Shows overall buffer cache stats, amount of buffer consumed by each object, dirty pages in the buffer (waiting on write to disk), and unpinned pages that could be released from the buffer |
| performance | statement-statistics | Shows count of statement executions per database, statement execution time per database, buffer & IO per database, buffer used for temporary tables and indexes by statement and database, along with general statistics by query & database (with the aggregation removing individual user data points) |
| performance | table-statistics | Show table sizes and proportion of the total database size, number of rows per table with counters for CRUD operations along with vaccuum statistics, also shows some internal table tuple information |
| performance | waits | Show current backed processes and their wait types |
| policy | missing-primary-keys | Shows tables that do not have a primary key |
| policy | primary-key-names | An example of a naming convention test query |
| security | authentication | Shows authentication configuration ident.conf hba.conf |
| security | connections | Show current connections to the database along with application, user and query |
| security | database-owners-acls | Show acl information for each database along with the database owner |
| security | roles | Shows roles, users and the roles that are granted to users |
| security | server-info | Shows server level info: addresses, ports, versions & load time etc |
# Other Resources
The main two resources in collating all of the above are the provided documentation resources: