PostgreSQL Query Catalog

"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, what are the most popular components of the names (identifiers) of user-defined database objects, assuming, that the separator of the components is "_".

This query provides a statistical analysis of identifier length across the schema. It calculates a frequency distribution by grouping base tables, views, and materialized views based on the character length of their names. The result is a count of how many objects exist for each distinct name length, which can be used to audit naming conventions or identify outliers.

"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 names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average. Also make sure that there is no duplication in play.

"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 names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average in case of the particular type of database objects. Also make sure that there is no duplication in play.

The schema size is the sum of the sizes of all tables in the schema.

The table size is the sum of the total size of the simple columns and the total size of the complex columns in the table. In case of SQL databases large base tables in terms of number of columns could be a side effect of the problems with cloned columns or multiple columns for the same attribute. A base table with a low normalization level, which is meant to hold data that corresponds to multiple entity types has typically also relatively large number of columns compared with other base tables. Thus, the normalization level of base tables with a large number of columns should be checked as well.

"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.

"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 length and average (arithmetic mean) length of the names (identifiers) of user-defined database objects. 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 length and average (arithmetic mean) length of the names (identifiers) of user-defined database objects by the object type. The values could be used to compare different databases.

Find aggregate information about the names of database objects that do not contain any vowels.

This query provides a statistical overview of a potential naming convention issue by counting the number of user-defined database object identifiers that terminate in one or two numerical digits. This naming pattern is a design smell, often indicating bad database structure or semantic ambiguity. Instead of listing each individual name, the query returns an aggregate count, which is useful for quickly assessing the overall prevalence of this issue within the schema and tracking remediation progress over time.

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

Find the number of derived tables that aggregate data.

Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.

Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.

Find the number of base tables, the number of base tables that are referred from at least one derived table (view or materialized view), the number of base tables that are referred from at least one view, and the number of base tables that are referred from at least one materialized view. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table.

Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables.

Find statistics about how many derived tables have how many different underlying tables.

Find statistics about how many base tables have how many derived tables that use these tables.

Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting.