In addition to the PostgreSQL catalog, there are several tools and extensions that provide monitoring information, such as pg_stat_statements
, which shows statistics about the queries executed in the database server, and pg_buffercache
, which is used to get deeper insight into caching.
The PostgreSQL catalog contains meta information about PostgreSQL databases and objects. This information can be retrieved and manipulated using SQL statements. However, it is not recommended to manipulate the data directly in a catalog schema. Also, a more user friendly version of this meta information can be found in the information_schema
schema.
pg_stat_activity
function to determine the unclosed connections.pg_stat_activity
and pg_locks
functions can be used to find out locks in the database.ctid
column can be used to clean duplicate data.current_setting
function, will be used in the next chapter—Chapter 10, Optimizing Database Performance—to show the effects of different settings on different execution plans.18.224.58.182