PostgreSQL Query Catalog

Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all.

Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table.

Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values.

Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application.

Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint.

Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods.

Find all routines that belong to an extension.

Find extensions that routines are in the schema public.

Know the privileges that users have in your system. Probably all the database users do not need these privileges.

Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.

Find names that contain extremely short terms, due to an excessive word contraction, abbreviation, or acronym usage.

Find base table columns that name refers to the possibility that these are used to register names. Find base table columns that name refers to the possibility that these are used to register comments/descriptions/etc. Find the cases where a base table contains columns from the both sets and the field size in case of the latter is not at least twice as big as in case of the former. For example, if the name of a service can be 100 character long, then it is quite illogical that the description of the service is only at most 200 characters long.

Find all non-foreign key columns of base tables. Make sure that no foreign key constraint is missing.

Find publications of tables that have been created in order to enable logical replication.

A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle.

Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints.

Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column.

Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table.

Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column.

Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns.