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	...
941	The number of tables by schema, by type, and in total	Find the number of tables (base, foreign, and derived) in different schemas.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2021-03-07 10:57	MIT License	View
942	The number of user-defined non-trigger routines by schema, by language, and by routine type	Find the number of user-defined non-trigger routines based on their schema, language, and routine type. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 20:53	MIT License	View
943	The number of user-defined non-trigger routines by schema, by security type, and by being deterministic	Find the number of user-defined non-trigger routines based on their schema, security type, and being deterministic. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 20:52	MIT License	View
944	The number of user-defined routines based on their database usage	Find the number of non-extension routines in a database that modify data. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. The query distinguishes trigger and non-trigger routines, thus it also gives information about triggers that modify data.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-15 00:38	MIT License	View
945	The number of user-defined rules	Find the number of rules in a database, excluding the rules that are associated with system catalog tables and rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives some indications about the state of enforcing constraints at the database level.	Sofware measure	system catalog base tables only	2022-10-21 11:19	MIT License	View
946	The number of user-defined rules by schema and in total	Find the number of rules in a database in different schemas, excluding the rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives an indication about the state of enforcing constraints at the database level.	Sofware measure	system catalog base tables only	2022-10-21 11:29	MIT License	View
947	The number of user-defined triggers	Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
948	The number of user defined triggers by different characteristics	Find the number of user defined triggers by action orientation (ROW, STATEMENT), action timing (BEFORE, AFTER, INSTEAD OF), and event type (INSERT, UPDATE, DELETE) and their combinations.	Sofware measure	INFORMATION_SCHEMA only	2024-01-14 17:41	MIT License	View
949	The number of user-defined triggers by schema, by type, and in total	Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
950	The proportion of mandatory and optional textual base table columns	Find the number of textual base table columns, the number of optional textual base table columns (permit NULLs), and the number of mandatory textual base table columns (do not permit NULLs).	Sofware measure	INFORMATION_SCHEMA only	2021-01-15 17:39	MIT License	View
951	The proportion of using different integer types as types of base table columns	Find the number of base table columns that use different integer types (SMALLINT, INTEGER, BIGINT) and their proportion from the overall set of columns that use an integer type.	Sofware measure	INFORMATION_SCHEMA only	2021-03-03 12:41	MIT License	View
952	The storage size of each index (the system catalog excluded)	Get overview of disk usage.	Sofware measure	system catalog base tables only	2020-11-06 14:51	MIT License	View
953	The storage size of each schema data object (the system catalog excluded)	Get overview of disk usage by different schema objects that contain user data.	Sofware measure	system catalog base tables only	2020-11-06 14:51	MIT License	View
954	The storage size of each table (including indexes) (the system catalog excluded)	Get overview of disk usage.	Sofware measure	system catalog base tables only	2020-11-06 14:51	MIT License	View
955	The total number of physical lines of code in the bodies of user-defined SQL and PL/pgSQL functions/procedures	Find a numerical measure that describes the extent of the database public interface (virtual data layer) through which users access the database.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 21:02	MIT License	View
956	The total size of all indexes (system catalog excluded)		Sofware measure	system catalog base tables only	2023-05-14 11:04	MIT License	View
957	The total size of all tables (system catalog excluded)		Sofware measure	system catalog base tables only	2023-05-14 11:04	MIT License	View
958	The total storage size of all indexes (the system catalog excluded)	Get overview of disk usage.	Sofware measure	system catalog base tables only	2020-11-06 14:51	MIT License	View
959	The total storage size of the database (the system catalog included)	Get overview of disk usage.	Sofware measure	system catalog base tables only	2020-11-06 14:51	MIT License	View
960	The usage of double vs singular underscores or spaces in names as separator of name components	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.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-03-17 11:11	MIT License	View