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 961 queries.

Seq nr	Name	Goal	Type▲	Data source	Last update	License	...
901	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
902	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
903	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
904	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
905	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-01-14 12:54	MIT License	View
906	Percentage of optional columns in each base table	What is the percentage of optional columns (that permit NULLs) in case of each base table? It is better to prohibit the use of NULLs in as many columns as possible. Otherwise the results of queries may be misleading.	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 20:55	MIT License	View
907	Percentage of the total index storage size from the total database storage size (system catalog included)	Get overview of disk usage.	Sofware measure	system catalog base tables only	2020-11-06 14:51	MIT License	View
908	Referential degree of a schema	Referential degree of a schema is defined as the number of foreign keys in the database schema.	Sofware measure	INFORMATION_SCHEMA only	2020-11-13 11:30	MIT License	View
909	Referential degree of tables	This metric represents the number of foreign keys in a base table.	Sofware measure	INFORMATION_SCHEMA only	2021-03-12 11:07	MIT License	View
910	Referential degree of tables (ver 2)	Find how many base tables are referenced from a base table by using foreign keys.	Sofware measure	system catalog base tables only	2021-03-12 11:06	MIT License	View
911	The generic names (columns) (aggregate view)(2)	Find the number of too generic base table and view column names. Do not differentiate between base table and view columns to determine what name is generic.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-01-18 16:42	MIT License	View
912	The longest 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 TOP 3 longest (identifiers) names of user-defined objects by their type. These could be the first candidates of renaming in order to give to database objects better names.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-03-17 01:09	MIT License	View
913	The longest referential paths	Find the longest referential paths between the tables regardless of the schema that contain the tables.	Sofware measure	system catalog base tables only	2020-11-14 15:42	MIT License	View
914	The number and percentage of base tables where all the non-primary key columns are optional	Find the number and percentage of base tables where all the non-primary key columns are optional	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 19:57	MIT License	View
915	The number and percentage of base tables without CHECK constraints	Find the extent in which data integrity is checked at the database level. Find the number and percentage of base tables that do not have any associated CHECK constraints.	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 19:15	MIT License	View
916	The number and percentage of base tables without keys	Find the extent in which repeating rows are permitted in the database. Find the number and percentage (from the total number of base tables) of base tables that do not have the PRIMARY KEY constraint and also do not have any UNIQUE constraints.	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 19:16	MIT License	View
917	The number and percentage of different names of database objects	Names should be expressive. Find the number of different names used in a database as well as the number of named database objects, and the percentage of different names from all the names. 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 and compare it with the total number of database objects. The values could be used to compare different databases.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-03-18 17:12	MIT License	View
918	The number and percentage of different 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. 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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-03-16 15:38	MIT License	View
919	The number and percentage of optinal columns in base tables	Find the number and percentage of optinal columns in base tables	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 21:17	MIT License	View
920	The number of base table columns based on data type	Find the number of base table columns based on data type.	Sofware measure	INFORMATION_SCHEMA only	2020-11-19 15:30	MIT License	View