PostgreSQL Query Catalog

Find base tables that name starts with a prefix. Do not use prefixes in case of base table names. Derive the names from the names of entity types. Do not use "_", "t_", "tab_", "t11_" etc as prefixes of a table.

Find columns of base tables and foreign tables where one uses a check constraint to prevent values that consist of only spaces. Make sure that this is the correct constraint and there is no need to prevent values that consist of only whitespace characters.

In SQL tables each column has the ordinal position. Find all the base tables where the primary key columns are not the first in the table, i.e., there is at least one non-primary key column that comes before a primary key column. It is easier to grasp the primary key if its columns are the first in the table. It could be that a table inherits from an abstract table where no keys have been defined.

Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have).

Find privileges to execute routines that have been given to non-superusers. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to modify data in the database (in case of table functions read data), then they must execute a routine.

If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns

Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables.

Find privileges to use views. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to read data from a database, then they should use views.

You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.

Find publications that do not contain any table.

Find as to whether PUBLIC (all current and future users) has TEMPORARY privilege in the database. PUBLIC gets the privilege by default.

Find schemas where PUBLIC has the usage privilege.

Find privileges to use system catalog base tables or views that have been granted to public.

Find incorrectly implemented adjacency lists.

Do not cause potentially infinite loops. Recursive rules would fire itself over and over again. Although the system is able to detect these after executing a data modification statement it is better to avoid creating these altogether.

Find indexes that may be redundant. In addition to identical indexes it also considers indexes that cover the same columns and have the same properties except uniqueness. The query considers all types of indexes, including indexes that have been automatically created to support a constraint and function-based indexes.

Find base table columns that have the DECIMAL/NUMERIC type, but do not have precision and scale specified. "Specifying: NUMERIC without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale."

Referential degree of a schema is defined as the number of foreign keys in the database schema.

This metric represents the number of foreign keys in a base table.

Find how many base tables are referenced from a base table by using foreign keys.