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	...
981	Tables that have associated user triggers	Find information about tables that are associated with triggers.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
982	The number of constraints by schema, by type, and in total	Find the number of constraints in different schemas. The number of constraints 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
983	The number of domains by schema and in total	Identify the number of domains in different schemas.	Sofware measure	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
984	The number of schema objects by schema, by type, and in total	Find the number of different types of schema objects in different schemas as well as the total number of schema objects. Show the number of objects that belong to an extension as well as the number of (user-defined) objects that do not belong to an extension.	Sofware measure	system catalog base tables only	2020-11-06 14:51	MIT License	View
985	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
986	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
987	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
988	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
989	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
990	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
991	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
992	Triggers with SELECT (i.e., probably check data based on another table)	If a trigger is used for enforcing a constraint, then it should take into account that due to the implementation of multiversion concurrency control (MVCC) in PostgreSQL, reading data does not block data modification and vice versa. Thus, there may be a need to lock the entire table or some row explicitly.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
993	Triggers with the same name in different schemas	Find trigger names that are used in a database in more than one schema. Different things should have different names. But here different triggers have the same name. Also make sure that this is not a duplication.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
994	Unlogged tables	Find unlogged tables. These may improve the performance of INSERT operations, but with the price of possibly loosing data - an unlogged table is automatically truncated after a crash or unclean shutdown.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
995	User-defined composite types	Find composite types that are created by a user, i.e., the type is not created automatically by the database management system based on a relation.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
996	User-defined routines with the same parameters (same name and type) regardless of the order of parameters	Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View