PostgreSQL Query Catalog

Find user-defined views and materialized views. Pay attention to the outer join operations. One should use these if and only if there is a real need for them. Otherwise they just reduce performance. On the other hand, pay attention that outer join is used where it is logically needed.

Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations.

Find user user-defined routines that produce a temporary table

Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables.

Find views and materialized views that use rank and dense_rank window functions.

Find views and materialized views that use string_agg aggregate function.

Find views, materialized views, and user-defined routines that do not have a comment at all or the comment is shorter than twice the length of the object name, or the comment states that it is missing (TODO). You should give information to future developers and maintainers of the system (including the future version of yourself). Do not just repeat the name in the comment (with perhaps some rewording).

Find views that do not have the security barrier option.

Find views that do not have the security barrier option but restrict rows in some way.

Find views that have the security invoker option. Such option is possible starting from PostgreSQL 15. In case of using such views one cannot create a system where data is accessed through views and the users (applications) do not have direct access to the base tables.

Find views that use a function to get data from another table.

Find views with exactly the same subquery. There should not be multiple views with exactly the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker."

Find what are the base table columns that are not foreign key columns and that have no associated CHECK constraints? Perhaps some CHECK constraints are missing.

Find what database object names are used more than once in case the objects of the same type. If the names differ from each other only by digits or underscores, then consider these the same name. For instance, if there are base tables Person and Person2 (in the same schema or different schemas), then the query returns the name Person. Make sure that there is no duplication of implementation elements in the database.

Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table.

Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four).

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 whether the database uses both CHAR(1) and VARCHAR(1) columns to register a single symbol.

Fing regular expression patterns that use [^\S] instead of \s or [^\D] instead of \d or [^\W] instead of \w.