PostgreSQL Query Catalog

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.
So, if you're leaving empty space in a table where things never change, you're just wasting disk space. This query finds those specific linking tables where you're leaving unnecessary empty space, so you can pack them 100% full and be more efficient.

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.

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.

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.

This query identifies foreign key constraints that likely require an ON DELETE CASCADE action. It targets a specific pattern: where the foreign key constraint's columns are identical to a candidate key (Primary Key or Unique constraint) of the same table. This structure implements a one-to-one identifying relationship, where the child entity is existentially dependent on the parent (a weak entity depending on a strong entity). The absence of ON DELETE CASCADE in this scenario is a design flaw, as it prevents the parent row from being deleted and breaks the conceptual model.

This query identifies all foreign key constraints that use ON DELETE CASCADE for the purpose of a design audit. The results must be manually reviewed to verify that each use case correctly implements a specific conceptual relationship. According to design principles, ON DELETE CASCADE is only appropriate for implementing generalization hierarchies (is-a relationships), compositions (strong ownership), or the existential dependency of a non-main entity on a main entity. Any usage outside of these patterns is considered a potential design flaw.

This query identifies referential paths (chains of foreign key relationships) where every constraint in the path is configured with ON DELETE CASCADE. The analysis is intended to uncover potential transitive deletion risks, where a single DELETE operation on a root table could trigger a catastrophic, cascading data loss across multiple, deeply-nested tables. The length of such chains is a key indicator of architectural fragility.

This query detects inconsistencies in the application of ON UPDATE CASCADE for foreign keys referencing the same candidate key. It identifies sets of foreign key constraints where a proper, non-empty subset is configured with ON UPDATE CASCADE. Such a mixed configuration is a functional flaw, as any attempt to update the parent key will be blocked by the non-cascading constraints, rendering the CASCADE action ineffective and preventing the intended data modification. The principle of atomicity requires that for any given key, either all referencing foreign keys have ON UPDATE CASCADE, or none do.

This query generates a list of all foreign key constraints that are defined with the ON UPDATE CASCADE action. This list must be manually audited to enforce the design principle that this action should be applied exclusively to relationships involving mutable, natural keys. Any instance found referencing an immutable surrogate key should be considered a design flaw and remediated.

This query identifies foreign key constraints that likely require an ON UPDATE CASCADE action. It operates on a strong heuristic based on data type: the query flags foreign keys where the column type is neither integer nor uuid and the ON UPDATE CASCADE action is absent. This assumes that non-numeric, non-UUID keys are natural keys (e.g., VARCHAR, CHAR) whose values are meaningful and may need to be updated over time. The absence of ON UPDATE CASCADE on these relationships can impede data maintenance and violate referential integrity upon updates.