PostgreSQL Query Catalog

For different types of constraints find the number of columns covered with constraints of such type.

For the sake of making code better understandable follow naming conventions.

Full uppercase means screaming and it makes comprehending the names more difficult. Find the names (identifiers) of user-defined database objects that are fully uppercase. Because PostgreSQL stores regular identifiers lowercase in the system catalog it also means that these are delimited identifiers, i.e., these are case sensitive.

Get overview of disk usage.

Get overview of disk usage.

Get overview of disk usage.

Get overview of disk usage.

Get overview of disk usage.

Get overview of disk usage by different schema objects that contain user data.

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.

Identify disabled rules. These should be enabled or dropped, otherwise these are dead code.

Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code.

Identify possibly missing CHECK constraints.

Identify the number of domains in different schemas.

Identify the number of sequence generators in different schemas. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys at the database level. An alternative is to implement generation of unique values at the application level or at the database level by using triggers. However, such implementation would most probably lead to the performance penalty because adding new rows to the table must be serialized, i.e., it can be done in one session at a time.

If a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object.

If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Find columns that contain Universally Unique Identifiers but are not a part of any simple PRIMARY KEY/UNIQUE constraint and are also not part of a foreign key.

If the name of an object has the prefix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find prefixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use chk_ as the prefix of an index name or pk_ as the prefix of a check constraint name.

If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name.

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.