PostgreSQL Query Catalog

Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.

Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.

Find base tables and materialized views that do not have any index.

Find base tables columns with temporal types (date and timestamp) that do not belong to a foreign key and that do not have any associated simple CHECK constraints, i.e., constraint that involves only one column. For instance, in the column registration_time that does not have any associated CHECK constraints could be values '1200-01-01 00:00' or '5900-12-31 00:00'. Rows with these values most probably represent wrong propositions and the system should restrict registration of such data.

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 tables that do not have any column with a timestamp type. In such tables certainly registration time is not recorded. Make sure as to whether recording registration time is necessary.

Find base tables that (due to the types of their columns) do not have an associated TOAST table for storing out-of-line data.

Find base tables that have 1000 rows or more.

Find base tables that have a column for last update time but the table does not have associated before update row level trigger for changing the last update time.

Find base tables that have at least two columns that have Boolean type and have at least one Boolean column that is not covered by a CHECK constraint involving more than one Boolean column. The Boolean columns possibly mean that we want to record data about states. Often the states depend on each other. For instance, if an order is archived it must be inactive.

Find base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting.

Find base tables that have been created based on a composite type and thnk through as to whether it was really needed.

Find base tables that have more than five indexes. Indexes can be used to increase the speed of queries (SELECT statements). However, the amount of indexes shouldn't be too large. Otherwise it may reduce the speed of operations that are used to modify data.

Find base tables that have more than one column with a temporal type (date or timestamp).

Find base tables that have more than one column with Boolean type.

Find base tables that have more than one stored generated column with the same expression. The support of generated columns was added to PostgreSQL 12. 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 base tables that have multiple triggers to update tsvector values.

Find base tables that have optional columns for recording both given name and surname and do not have a CHECK constraint that requires that at least one of the name components must be registered in case of each person.

Find base tables that have the primary key that is not a surrogate key and an alternate key that is a surrogate key. Perhaps the surrogate key column is not needed.

Find base tables that implement a highly generic database design (EAV design - Entiry-Attribute-Value design), according to which attribute values are recorded in a generic table that contains attribute-value pairs.