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.
- PostgreSQL provides a huge set of administration functions to get information from the database cluster and also to configure its behavior. Some database administration functions can be used to control the database system's behavior.
- One should keep an eye on the
pg_stat_activityfunction to determine the unclosed connections. - The
pg_stat_activityandpg_locksfunctions can be used to find out locks in the database. - Statistic tables and views are useful in determining performance bottlenecks as well as cleaning the database. It enables developers to monitor different aspects of the database, such as unused objects, duplicated data, as well as missing indexes and unique constraints.
- Knowing the database internal structure, table structure, and row structure is useful in understanding the database's behavior and in solving several problems. The
ctidcolumn can be used to clean duplicate data. - Some of the functions and catalog tables shown in this chapter, such as the
current_settingfunction, will be used in the next chapter—Chapter 10, Optimizing Database Performance—to show the effects of different settings on different execution plans.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.