PostgreSQL Design Check Queries

Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

	Choose collection:
AND	Choose query type:
AND	Choose query reliability:
AND	Choose category:
AND	Choose data source:	From where does the query gets its information?
AND	Enter string:
AND	Has fixing queries?

All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
Statistics about the catalog content and project home in GitHub that has additional information.

There are 996 queries.

Seq nr	Name	Goal	Type▲	Data source	Last update	License	...
921	Frequency of name components of database objects	"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 "_".	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:50	MIT License	View
922	Frequency of table name lengths based on the table type	Find in case of base tables, materialized views, and views the number of tables based on the length of the table name.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2022-11-23 01:09	MIT License	View
923	Frequent names of database objects	"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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:49	MIT License	View
924	Frequent names of database objects by object type	"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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:24	MIT License	View
925	Logical size of a schema	The schema size is the sum of the sizes of all tables in the schema.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2022-10-21 16:18	MIT License	View
926	Logical size of a table	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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2022-10-21 16:17	MIT License	View
927	Median and average number of subcomponents in the names of database objects	"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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:47	MIT License	View
928	Median and average of the length of names of database objects	"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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:25	MIT License	View
929	Median and average of the length of names of database objects by the object type	"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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:37	MIT License	View
930	Name does not contain any vowels (aggregate view)	Find aggregate information about the names of database objects that do not contain any vowels.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:19	MIT License	View
931	Names of database objects that perhaps end with a sequence number (aggregate view)	Find the number of names (identifiers) of user-defined database objects that end with one or more digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:33	MIT License	View
932	Number of columns covered with constraints	For different types of constraints find the number of columns covered with constraints of such type.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2021-10-16 11:01	MIT License	View
933	Number of derived tables that aggregate data	Find the number of derived tables that aggregate data.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 12:33	MIT License	View
934	Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column)	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.	Sofware measure	system catalog base tables only	2023-01-10 14:53	MIT License	View
935	Number of system-generated and user-defined constraint names by constraint type (constraints that involve one column)	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.	Sofware measure	system catalog base tables only	2023-01-10 14:52	MIT License	View
936	Number of tables covered by derived tables	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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-11-11 09:33	MIT License	View
937	Number of underlying tables of derived tables	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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-11-11 09:30	MIT License	View
938	Number of used tables	Find statistics about how many derived tables have how many different underlying tables.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 13:43	MIT License	View
939	Number of using views	Find statistics about how many base tables have how many derived tables that use these tables.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 13:42	MIT License	View
940	Number of views with and without security barrier	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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-13 19:41	MIT License	View