Schema Expert Recommendations v8
| Rule | Check for missing primary keys |
| Recommendation | Ensure tables have a primary key |
| Trigger | Postgres Expert detected a table with no defined primary key. |
| Severity | Low |
Description: Primary keys are used to define the set of columns that make up the unique key to each row in the table. Whilst they are similar to unique indexes, primary keys cannot contain NULL values, thus are always able to identify a single row. Tools such as Postgres Enterprise Manager and other pieces of software such as ORM will automatically detect primary keys on tables and use their definition to identify individual rows.
| Rule | Check for missing foreign key indexes |
| Recommendation | Ensure columns of child tables in foreign key relationships are indexed. |
| Trigger | Postgres Expert detected a child table with no index on referencing column(s). |
| Severity | Medium |
Description: Foreign keys are used to define and enforce relationships between child and parent tables. The foreign key specifies that values in one or more columns of the child table must exist (in the same combination, if more than one column) in the referenced column(s) of the parent table. A unique index is required to be present on the referenced columns in the parent table, however an index is not required, but is generally advisable, on the referencing columns of the child table to allow cascading updates to the parent to be executed efficiently.
| Rule | Check Database Encoding |
| Recommendation | Avoid encoding as SQL_ASCII for databases |
| Trigger | encoding = SQL_ASCII |
| Severity | Medium |
Description: The database is created to store data using the SQL_ASCII encoding. This encoding is defined for 7 bit characters only; the meaning of characters with the 8th bit set (non-ASCII characters 127-255) is not defined. Consequently, it is not possible for the server to convert the data to other encodings. If you're storing non-ASCII data in the database, you're strongly encouraged to use a proper database encoding representing your locale character set to take benefit from the automatic conversion to different client encodings when needed. If you store non-ASCII data in an SQL_ASCII database, strange characters may be written to or read from the database, caused by code conversion problems. This may cause problems when accessing the database using different client programs and drivers. For most installations, Unicode (UTF8) encoding will provide the most versatility.
| Rule | Check for too many indexes |
| Recommendation | Don't overload a table with too many indexes. |
| Trigger | Postgres Expert has detected that a table has more than 10 indexes. |
| Severity | Low, Medium or High (based on number of indexes) |
Description: Whilst indexes can speed up SELECT queries by allowing Postgres to quickly locate records, it is important to choose which indexes are required carefully to ensure they are used. Maintaining an index has a cost, and the more indexes there are to update, the slower INSERT, UPDATE or DELETE queries can become. There are no hard and fast rules to tell you how many indexes are required on a particular table -the DBA must balance the need for indexes for different types of SELECT queries and constraints against the cost of maintaining them.
| Configuration Item | Check data and transaction log on same drive |
| Recommendation | Avoid using the same storage device for the data directory and transaction logs. |
| Trigger | Postgres Expert has detected that a data directory and transaction log directory share a device. |
| Severity | High |
Description: Postgres' performance can be adversely affected on medium to heavily loaded systems if both the data and the transaction logs (WAL) are stored on the same device. It is considered good practice to store them on separate physical devices if performance is an issue. On busy servers, significant performance gains may be seen when separating the data directory and transaction log directory onto different physical storage devices.
| Rule | Check tablespace and transaction log on same drive |
| Recommendation | Avoid using the same storage device for the transaction logs and a tablespace. |
| Trigger | Postgres Expert has detected that transaction log directory and a tablespace other than pg_default share a device. |
| Severity | Medium |
Description: Before updating database files to reflect data modifications, the server writes the change to the transaction log. The database files may be separated onto different devices using tablespaces (defined storage areas used by the database server). On busy servers, significant performance gains may be seen when separating tablespace directories and the transaction log directory onto different physical storage devices.
| Rule | Check multiple tablespace on same drive |
| Recommendation | Avoid using the same storage device for multiple tablespaces. |
| Trigger | Postgres Expert has detected that multiple tablespaces share a device. |
| Severity | Low |
Description: Multiple tablespaces may be defined in the database to allow tables and indexes to be distributed into different storage areas, usually for performance reasons for example, tables with high performance requirements may be stored on expensive , high speed disks, while archive data may be stored on much larger, but slower devices. There is usually little to be gained from having more than one tablespace on a single device (because the cost and access characteristics will be identical), except in very unusual situations where it may be desirable to configure them with different planner cost parameters.