PostgreSQL Query Catalog

Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list.

Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods.

This query identifies user-defined routines that employ explicit locking mechanisms to supplement PostgreSQL's default Multi-Version Concurrency Control (MVCC). It detects the presence of table-level locking (LOCK TABLE) or explicit row-level locking clauses (e.g., SELECT ... FOR UPDATE, FOR SHARE). While MVCC generally provides sufficient isolation for concurrent transactions, explicit locking is necessary in specific race-condition scenarios. This inventory assists in auditing concurrency control strategies and detecting potential sources of deadlocks or serialization bottlenecks.

Find all routines that belong to an extension.

Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.

Find all non-foreign key columns of base tables. Make sure that no foreign key constraint is missing.

Find publications of tables that have been created in order to enable logical replication.

This query identifies all views against which data modification statements (INSERT, UPDATE, DELETE) can be executed without raising an error. It evaluates the is_insertable_into and is_updatable attributes to detect both "naturally" updatable views (where PostgreSQL automatically maps changes to base tables) and views made updatable via the rewrite rule system (specifically DO INSTEAD rules). Notably, this definition encompasses views defined with DO INSTEAD NOTHING rules; while such views do not physically modify data, they are technically considered updatable because they accept DML statements without failure.

Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column.

This query identifies all foreign key constraints that use ON DELETE CASCADE for the purpose of a design audit. The results must be manually reviewed to verify that each use case correctly implements a specific conceptual relationship. According to design principles, ON DELETE CASCADE is only appropriate for implementing generalization hierarchies (is-a relationships), compositions (strong ownership), or the existential dependency of a non-main entity on a main entity. Any usage outside of these patterns is considered a potential design flaw.

This query generates a list of all foreign key constraints that are defined with the ON UPDATE CASCADE action. This list must be manually audited to enforce the design principle that this action should be applied exclusively to relationships involving mutable, natural keys. Any instance found referencing an immutable surrogate key should be considered a design flaw and remediated.

Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries.

Find membership relations between roles.

Database must be used by users who have minimal set of privileges for performing tasks. The query helps to find out as to whether some user/role other than PUBLIC and a superuser have rights to use tables and routines of the database.

This query generates a list of all indexes with an explicitly configured, non-default FILLFACTOR for the purpose of a performance audit. The query is aware of the different default FILLFACTOR values associated with various index access methods (e.g., 90 for B-tree, 100 for others like GiST/GIN). This allows administrators to quickly identify and review all instances of customized index storage parameters to assess if these non-standard configurations are justified and still effective.

Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.

Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint.

This query determines the maximum field size of text columns whose names suggest they store some type of code.

Find base tables in case of which toast_tuple_target storage parameter value is not the default value (2040). "Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. " Make sure that the parameter has an optimal value.

Find base table columns that belong to more than one index (including automatically created indexes that support constraints).