PostgreSQL Query Catalog

Find table names that are the same as some routine name. Use different names to avoid confusion.

Find table privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.

Find table, routine, and usage privileges 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 tables that contain both a state and a status column.

Find tables that contain multiple columns for free-form textual descriptions. Make sure that the names of columns are understandable and sufficiently different. Make sure that there are no duplicate columns.

Find tables that have one column or zero columns.

Find tables where is both a column for registering name and description. Find the permitted maximum field size in these columns. Take into account that the maximum length may be controlled by using a CHECK constraint. Make sure that the permitted maximum field sizes are sufficiently different.

Find tables where user-defined (non-system) triggers are used to implement referential integrity. In addition to table name show the triggers and the number of triggers.

Find tables with the same name in different schemas. Make sure that this is not a duplication.

Find textual columns that potentially contain usernames (including columns that potentially contain e-mail addresses) that do not have a unique constraint or a unique index that involves only this column.

Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables.

Find the base tables where all the unique columns are optional. In such tables there can be rows without values that identify these rows. In this case there can be rows in the table where the values that should identify the row are missing.

Find the cases when the names of columns in different foreign keys that reference to the same candidate key are different. If different names reflect different roles, then it is legitimate. However, there could also be accidental differences that makes it more difficult to use the database.

Find the columns that name is the same as the name of the type of the column or the domain of the column.

Find the constraint types in case of which there exists system-defined names.

Find the constraint types in case of which there exists system-defined names.

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 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 the extent in which data integrity is checked at the database level. Find the number and percentage of base tables that do not have any associated CHECK constraints.