Learning PostgreSQL 11

PostgreSQL describes all database objects using the meta information stored in database relations. These relations hold information about tables, views, functions, indexes, foreign data wrappers (FDWs), triggers, constraints, rules, users, groups, and so on. This information is stored in the pg_catalog schema, and to make it more readable by humans, PostgreSQL also provides the information_schema schema, in which the meta information is wrapped and organized into views.

The pg_catalog and information_schema schema contain hundreds of views, tables, and administration functions; for this reason, only some of the more common and heavily used catalog tables will be described.

Questions:

1. How can you display the queries that are generated by the psql client to the PostgreSQL server?

   \set ECHO_HIDDEN
   \d
   

2. What is the purpose of the pg_class table? What is the difference between pg_catalog and information_schema?
3. What does the abbreviation TOAST stand for and what is it used for?
4. You work as an administrator and you would like to clean up unused indexes. What are the criteria you can use to find these indexes? Why is it important to monitor usage indexes?
5. Assume that you have a table with duplicate data due to the lack of unique and primary key constraints. Which column can be used to distinguish the rows?
6. Can a table have duplicate data even if it has a primary key?
7. What is the difference between the pg_cancel_backend and pg_terminate_backend functions?
8. Which is easier to find: unused tables or unused views? And why?
9. List the contype constaint types found in the pg_constraint table, and give the full name for each type.