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	...
21	All updatable views	Find all views through which it is possible to modify data in base tables. Is_insertable_into and is_updatable show as to whether the view is naturally updatable in PostgreSQL or has associated rules that make sure that INSERT/UPDATE/DELETE operations against the view will not cause an error. Please note that the rule could be DO INSTEAD NOTHING rule, i.e., data modification through the view does not cause an error but the data is not actually modified. If the updatability is achieved due to rules, then is_updatable=YES if the view has both UPDATE and DELETE rule that make sure that UPDATE and DELETE operations do not cause an error.	General	INFORMATION_SCHEMA only	2024-01-14 17:09	MIT License	View
22	Different tasks of rules	Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task.	General	system catalog base tables only	2024-01-14 16:10	MIT License	View
23	The number of rules by different characteristics	Find the number of rules by event (SELECT, INSERT, UPDATE, and DELETE) and in total.	Sofware measure	system catalog base tables only	2024-01-14 16:10	MIT License	View
24	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
25	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
26	Views with WHERE but without security barrier	Find views that do not have the security barrier option but restrict rows in some way.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 12:58	MIT License	View
27	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
28	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
29	Usage of base tables	Find for each derived table the list of base tables that are used by the derived table.	General	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 00:50	MIT License	View
30	Explicit locking is missing	PostgreSQL uses multiversion concurrency control (MVCC). SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 00:35	MIT License	View
31	Routines with BOOLEAN return type that do not have a good name	The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: check_rights. Better: has_rights.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-13 20:41	MIT License	View
32	Grantable column privileges	Find column privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
33	Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers	Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	system catalog base tables only	2024-01-07 13:43	MIT License	View
34	Grantable routine privileges	Find routine privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
35	Grantable table privileges	Find table privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
36	Grantable usage privileges	Find usage privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
37	Grantable roles	Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	system catalog base tables only	2024-01-07 13:42	MIT License	View
38	Granted roles	Find membership relations between roles.	General	system catalog base tables only	2024-01-07 13:30	MIT License	View
39	Perhaps a too long SQL routine	A large routine may have multiple tasks that should be split between multiple routines that have a more focused task. Find the SQL routines where the number of statements (logical lines of code) is bigger than 5.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-04 16:39	MIT License	View
40	Perhaps a too long PL/pgSQL routine	A large routine may have multiple tasks that should be split between multiple routines, each of which has a more focused task. Find the PL/pgSQL routines where the number of physical lines of code is bigger than 20.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-04 16:38	MIT License	View