PostgreSQL Query Catalog

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 (due to the types of their columns) do not have an associated TOAST table for storing out-of-line data.

A surrogate key is a primary key that consist of one column. The values of this column do not have any meaning for the user and the system usually generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. If a key covers an optional column then it does not prevent duplicate rows where some values are missing and other values are equal. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL,
b INTEGER,
CONSTRAINT ak_uniq UNIQUE (a, b));

INSERT INTO Uniq(a, b) VALUES (1, NULL);
INSERT INTO Uniq(a, b) VALUES (1, NULL);

A common style is to declare in each base table one of the candidate keys as the primary key. All the other candidate keys would be alternate keys that will be enforce with the help of UNIQUE + NOT NULL constraints.

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 without any unique constraints and the primary key. In such tables there are no restrictions for recording duplicate rows. Each row represents a true proposition about the real world. It does not make the proposition truer if one presents it more than once. Moreover, duplicate rows increase data size. Without keys the DBMS lacks vital information about data in the database that it can internally use to choose better execution plans and in this way improve performance of database operations. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables.

Find base tables without any unique constraints and primary key as well as any unique index, whether it is created explicitly by a developer or automatically by the DBMS. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables.

Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values.

Find base tables where all the columns are optional, i.e., permit NULLs. In such tables can be rows with no identity value and thus indistinguishable from other rows.

Find the base tables where all the unique columns are optional. In such tables there can be rows without values that identify these rows. In this case there can be rows in the table where the values that should identify the row are missing.

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 where uniqueness is achieved by using only unique indexes, i.e., there is at least one unique index but no uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE)

Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago.

Find all base tables that have exactly one PRIMARY KEY or UNIQUE constraint. Find and enforce all the keys. Are you sure there are not more keys in the table?

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

Find base tables with more than one comment columns

Find base tables with more than one name columns. Perhaps the normalization level of the table is low.

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

Find base tables that have 1000 rows or more.

Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables.