PostgreSQL Query Catalog

Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Exclude constraints where in case of the subtable there is a CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation)

Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) and have been redefined in its subtable.

Find indexes that support a uniqueness constraint and have the same leading column.

Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table.

Find for each base table the list of routines (functions and procedures) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from at least one routine.

Get overview of used data types in the columns of base tables. If the selection is very small then this is a warning sign that perhaps unsuitable types have been used.

Find all deferrable constraints.

Find the derived tables (views and materialized views) that have a column with the xid type, i.e., these use the data from the hidden xmin column of a base table. If one uses optimistic approach for dealing with the concurrent data modifications, then xmin values should be presented by views and used in routines that modify or delete rows.

Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables.

Find derived tables (views and materialized views) that contain more than one DISTINCT invocation. Make sure that the query is correctly written, including that it does not have unwanted formation of a Cartesian product.

Find views and materialized views that use rank and dense_rank window functions.

Find derived tables where the rows are ordered, i.e., there is ORDER BY clause at the end of the view. Different users may want to see the rows in different order. Thus, the DBMS may have to do extra and unnecessary work by firstly sorting based on one set of rows and after that based on other set of rows. Use sorting if you know that all the users want the rows in the same order and cannot/will not change the order in the application.

Find views and materialized views that use string_agg aggregate function.

Find the different default values that implement something other than a surrogate key.

Find the different search paths used in case of SECURITY DEFINER functions and the number of their occurrences. Make sure that these have been specified correctly and consistently and that they do not refer to any non-existent schemas.

Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task.

Find different tasks that are solved by using triggers, i.e., different triggers on the same table or different tables that do the same thing are considered to solve one task.

Find column descriptions that are candidates for describing a domain.

Find for each domain the number of usages in base tables. The less you have managed to reuse domains in case of different columns, the more you have wasted your time by creating the domains.

Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table.