| # | Name | Goal | Type | Data source | Last update ▼ | License | |
|---|---|---|---|---|---|---|---|
| 141 | Routines with BOOLEAN return type that do not have a good name | This query audits the naming conventions of routines that return a BOOLEAN data type. It identifies routines that do not adhere to the recommended semantic prefix convention (i.e., starting with is_, has_, can_, or on_). A function name should represent a state or a question (e.g., has_rights) rather than an action (e.g., check_rights). Enforcing this standard makes the routine's purpose and return value immediately obvious from its name. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:53 | MIT License | View |
| 142 | Inconsistent naming of columns with BOOLEAN type | This query audits the naming conventions of BOOLEAN columns to detect inconsistency. It checks whether the database contains a mix of styles: some boolean columns adhering to a semantic prefix convention (e.g., is_active, has_permission) and others using unprefixed nouns or adjectives (e.g., active, permission). The presence of both styles indicates a lack of a consistent standard, which can reduce schema clarity and predictability for developers. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:50 | MIT License | View |
| 143 | Double checking of the maximum character length | This query identifies superfluous CHECK constraints where a programmatic length check duplicates a declarative, data type-based length limit. For instance, a CHECK constraint like char_length(column) <= 100 on a column already defined as VARCHAR(100) is redundant. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:14 | MIT License | View |
| 144 | Length and char_length functions are used within the same expression | This query identifies expressions that use both the length() and char_length() functions, which is a strong indicator of a logical flaw. Since these functions are functional synonyms in PostgreSQL (both returning the character count), an expression that compares their results (e.g., length(col) > char_length(col)) may be either tautological or will always evaluate to false. Such code is typically written under the mistaken assumption that length() returns bytes, a behavior seen in other database management systems. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:12 | MIT License | View |
| 145 | Checking the text maximum length with CHECK | This query identifies all base table columns that enforce a maximum value length through the use of a CHECK constraint. It specifically searches for constraint definitions that contain length-calculating functions (such as length(), char_length(), or similar patterns) to provide a comprehensive list of all columns where data length is explicitly managed by a business rule at the database level. | General | system catalog base tables only | 2025-11-13 13:05 | MIT License | View |
| 146 | Base table column of personal names does not restrict the maximum character length | This query identifies base table columns that, based on their name, are presumed to store personal names but lack an explicit maximum length constraint. It operates on a heuristic, flagging columns with names like first_name, surname, etc., that are defined with unbounded textual types (e.g., text, varchar) and have no corresponding CHECK constraint to limit their length (e.g., char_length(col) <= n). The absence of such a limit is a design flaw that can introduce usability issues in front-end applications and create potential security vulnerabilities related to excessive data submission. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:04 | MIT License | View |
| 147 | Frequency of table name lengths based on the table type | This query provides a statistical analysis of identifier length across the schema. It calculates a frequency distribution by grouping base tables, views, and materialized views based on the character length of their names. The result is a count of how many objects exist for each distinct name length, which can be used to audit naming conventions or identify outliers. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 12:56 | MIT License | View |
| 148 | Inconsistent use of length and char_length functions | This query identifies inconsistent usage of string length functions within the database. Although length() and char_length() are functional synonyms in PostgreSQL (both returning the character count), mixing them violates clean coding principles. The query checks if both variants are present in the codebase, flagging a lack of standardization. Enforcing a single choice (typically the SQL-standard char_length or character_length) improves code maintainability and readability. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 12:56 | MIT License | View |
| 149 | Avoid using length function | This query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 12:53 | MIT License | View |
| 150 | Optional base table columns that have a default value | This query identifies columns that are both NULLable and have a DEFAULT value. This configuration represents a semantic contradiction: the DEFAULT clause implies that a value should always exist for the column, while the absence of a NOT NULL constraint explicitly permits the absence of a value. The presence of a DEFAULT strongly suggests the column's business logic requires a value, and therefore it should be defined with a NOT NULL constraint to enforce this consistently and make the schema's intent unambiguous. | Problem detection | INFORMATION_SCHEMA only | 2025-11-12 19:04 | MIT License | View |
| 151 | Perhaps an unnecessary default value (the empty string or a string that consists of only whitespace) of a base table column/domain | This query identifies table columns and domains that are configured with a semantically void DEFAULT value. It specifically flags defaults that are an empty string ('') or a string consisting solely of whitespace characters (e.g., spaces, newlines). This practice is a design flaw because it automatically populates the database with non-substantive data, which can lead to application-level bugs when code does not explicitly check for such "blank" values in addition to NULL. | Problem detection | INFORMATION_SCHEMA only | 2025-11-12 15:02 | MIT License | View |
| 152 | Incorrect prevention of the empty string or strings that consist of only spaces in a field | This query identifies ineffectual CHECK constraints on base and foreign table columns that incorrectly attempt to prohibit empty or whitespace-only strings using the predicate trim(column_name) IS NOT NULL. Due to PostgreSQL's strict distinction between an empty string ('') and NULL, this check is a tautology; trim('') evaluates to '', and the condition '' IS NOT NULL is always true. The query finds these logically flawed constraints, which fail to provide any data validation and permit the insertion of the exact values they were intended to prevent. | Problem detection | INFORMATION_SCHEMA only | 2025-11-12 14:56 | MIT License | View |
| 153 | Too generic names (many-to-many relationship types that do not have additional attributes) | This query identifies junction tables with semantically redundant names, specifically targeting those whose names contain the word "join". The structure of a many-to-many table (typically two foreign keys forming a composite primary key) is inherently declarative of its role as a join mechanism. Including the word "join" in the name is superfluous and violates the principle of naming based on the entity or relationship being modeled, not the implementation detail. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:41 | MIT License | View |
| 154 | Base table FILLFACTOR is not 100 | This query generates a list of all base tables where the FILLFACTOR has been explicitly set to a value other than the default of 100. This non-default setting is a deliberate performance tuning decision, intended to reserve free space within table pages to improve the efficiency of UPDATE operations by facilitating HOT updates. The query provides a comprehensive list for administrators to audit these customizations and verify that they are still necessary and appropriate for the current table workload. | General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:20 | MIT License | View |
| 155 | Index FILLFACTOR is not default | 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. | General | system catalog base tables only | 2025-11-10 09:17 | MIT License | View |
| 156 | B-tree index fillfactor has been explicitly set to 90 | This query identifies B-tree indexes where the FILLFACTOR has been explicitly set to 90. Since 90 is the default FILLFACTOR for B-tree indexes in PostgreSQL, this explicit declaration is superfluous. Removing such redundant settings simplifies the schema definition, improves maintainability, and makes intentionally non-default configurations more apparent. | Problem detection | system catalog base tables only | 2025-11-10 09:15 | MIT License | View |
| 157 | FILLFACTOR is probably too small (2) | This query identifies base tables with a suboptimal FILLFACTOR setting, specifically targeting tables that are heuristically identified as junction tables in a many-to-many relationship. A table is considered a probable junction table if it primarily consists of foreign and primary key columns, with no additional data columns that would suggest UPDATE operations. For these tables, the workload is almost exclusively INSERT and DELETE. Therefore, a FILLFACTOR below 100 serves no performance benefit and results only in wasted storage space and reduced data density. The job of these linking tables is simple: you add a line to connect two things, or you remove the line to disconnect them. You almost never change a line that's already there. The fillfactor setting is used to leave empty space for changes. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:13 | MIT License | View |
| 158 | FILLFACTOR is probably too small | This query identifies base tables with a FILLFACTOR setting below 90, flagging them for potentially inefficient storage utilization. While a low FILLFACTOR is intended to accommodate UPDATEs, an excessively low value can lead to wasted disk space and reduced data density, negatively impacting the performance of operations like full table scans. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:10 | MIT License | View |
| 159 | FILLFACTOR is probably too big | This query identifies base tables with a potentially suboptimal FILLFACTOR setting, targeting those that likely undergo UPDATE operations. A high FILLFACTOR on such tables can lead to poor performance by reducing the likelihood of Heap-Only Tuple (HOT) updates. When a new row version cannot be stored on the same page as the old version, all indexes on the table must be updated with the new tuple's location. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:08 | MIT License | View |
| 160 | Base table columns that lack any simple CHECK, i.e, permit empty strings and strings that consist of only whitespace characters | This query identifies non-foreign key columns of base tables with a textual data type that lack any simple (single-column) CHECK constraint. The absence of such constraints indicates a complete lack of column-level validation, creating a risk of low-quality data ingress, including the implicit allowance of empty or whitespace-only strings. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-09 10:23 | MIT License | View |