PostgreSQL Query Catalog

If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code.

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

If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11.

If your table contains columns with temporal data, then it will be appropriate to restrict the range of possible values in these columns because some of the values that belong to the type might not be appropriate (for instance, imagine a client who was born in 1100-12-03 or a contract that was registered in 3890-12-12- 12:45). If your table contains multiple columns with temporal data that denote events, then the rule about the order of the events must be enforced, if possible.

Improve the readability of names. Find the names (identifiers) of user-defined database objects that start with an underscore. This is not necessarily a mistake. For instance, parameter names could start with an underscore. On the other hand, it could be that the prefix is missing in the name.

Improve the readability of names. Find the number of names (identifiers) of user-defined database objects that contain two or more consecutive underscores or spaces as the separator of name components vs. the number of names that contain a single underscore or space to separate name components. Try to be consistent in the usage of underscores.

In case of a chain of relationships between tables (where the primary key and the foreign key have the same columns) the use of ON UPDATE compensating action should be consistent. For instance, in the next example there is inconsistency, because if one changes the person_code in table Person, then the modification does not succeed because it does not cascade to the table Product. It is unclear as to whether it should be possible to change the person_code or not. Person (person_code, surname) Primary key (person_code) Worker(person_code)
Primary key (person_code)
Foreign key (person_code) References Person (person_code) ON UPDATE CASCADE

Product(product_code, registrator)
Primary key (product_code)
Foreign key (registrator) References Worker (person_code) ON UPDATE NO ACTION

"In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys." (https://www.postgresql.org/docs/current/datatype-json.html)

INSERT statements shouldn't be sensitive towards the order of columns. If one changes the order of columns in a table then these statements must be rewritten. Otherwise the code will not work or works incorrectly.

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.

Keywords in uppercase improve readability.

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

Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator.

Make sure that in case of using the type DECIMAL/NUMERIC as the type of a base table column the precision (the permitted number of digits in the number) is not too small. For instance, the biggest value in the type NUMERIC(1,1) is 0.9.

Make sure that there is genuine overloading instead of duplication or dead code. "In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations." (Wikipedia) In PostgreSQL one can do it automagically by having multiple routines with the same name but different parameters in the same schema.

Make sure that you do not drop INFORMATION_SCHEMA schema. In this case most of the design checking queries will not work. This schema automatically exists in all databases.

Make sure that your parameters have appropriate types in order to avoid unnecessary type casting.

"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Different objects should have different names. The smaller the percentage the less descriptive are the names in the database. Find the number of different names (identifiers) of user-defined database objects by the object type and compare it with the total number of database objects with this type. The values could be used to compare different databases.

"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find how many names (identifiers) of database objects there are with different lengths. The values could be used to compare different databases.

"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find the median and average (arithmetic mean) number of subcomponents in the names of user-defined database objects. The values could be used to compare different databases.