PostgreSQL Query Catalog

This query identifies state, type or category code columns that use unnecessarily large numeric data types, specifically integer or bigint. It locates these columns by matching specific naming patterns in English and Estonian. Since reference tables typically contain a limited number of rows, these code columns should ideally be defined as smallint to optimize storage space and improve performance.

This query identifies performance and concurrency bottlenecks caused by excessive locking. It flags INSERT, UPDATE, or DELETE statements that utilize subqueries containing the FOR UPDATE clause. Using FOR UPDATE acquires an exclusive lock, which is semantically inappropriate if the rows in the subquery are merely being read for reference or validation rather than being modified. This practice degrades system concurrency by unnecessarily blocking other transactions.

Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application.

Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint.

This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking.

This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking.

Find extensions that routines are in the schema public.

Know the privileges that users have in your system. Probably all the database users do not need these privileges.

Find names that contain extremely short terms, due to an excessive word contraction, abbreviation, or acronym usage.

Find base table columns that name refers to the possibility that these are used to register names. Find base table columns that name refers to the possibility that these are used to register comments/descriptions/etc. Find the cases where a base table contains columns from the both sets and the field size in case of the latter is not at least twice as big as in case of the former. For example, if the name of a service can be 100 character long, then it is quite illogical that the description of the service is only at most 200 characters long.

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 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 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 detects data attributes that function as implicit classifiers but lack a corresponding reference entity. It targets textual columns that are not currently constrained (no PK, UK, or FK) but exhibit characteristics of coded data: they either have a very short length (≤ 3 characters) or possess identifiers typical of classifiers (e.g., 'status', 'type'). To reduce false positives, it excludes obvious free-text fields (names, comments) and verifies that no table with a similar name currently exists. This suggests the need to extract these attributes into a new dedicated reference table to enforce domain integrity.

This query audits the database schema for inconsistency regarding the use of consecutive separators (double underscores or spaces) within identifiers. It groups objects by type (e.g., CHECK constraints, indexes) and identifies categories where a mixed naming convention exists—specifically, where some identifiers utilize consecutive separators (e.g., idx__name) while others of the same type do not (e.g., idx_name). This variation suggests a lack of enforced coding standards, leading to unpredictability in the schema. The query facilitates a review to establish and enforce a single, uniform naming convention.

This query audits regular expressions within the database to detect inconsistent syntax for matching alphanumeric characters. It checks for the concurrent use of both the Perl-style shorthand \w and the POSIX character class [[:alnum:]]. While these are often functionally similar, their exact behavior can differ based on locale settings (e.g., \w may include underscores while [[:alnum:]] does not). Using both styles within the same codebase indicates a lack of a clear standard, which can lead to maintainability issues and subtle, locale-dependent bugs. Standardizing on a single, well-understood syntax is recommended for clarity and predictability.

This query identifies potential null-propagation defects in user-defined routines and views. It targets subqueries utilizing the standard concatenation operator (||) where operands are not protected by a coalesce() function. In PostgreSQL, the operation string || NULL yields NULL, causing the entire result to vanish if any component is missing. This behavior is often unintentional. The query flags these risky patterns, suggesting remediation via explicit null handling or the adoption of null-safe alternatives like concat(), concat_ws(), or format().

This query identifies regular expressions that are tautological, specifically those that effectively match any non-NULL string, such as ^.*$. It is superfluous because it evaluates to TRUE for any non-NULL value, including an empty string. It provides no actual data validation and acts as a no-operation (no-op) check. Such patterns are often artifacts of placeholder code, incomplete logic, or a fundamental misunderstanding of regular expression syntax. Removing them improves clarity and eliminates a useless computational step.

This query identifies superfluous function calls within routines and views, specifically targeting invocations of coalesce(), concat(), or concat_ws() that are supplied with only a single argument. These functions are variadic and designed to operate on multiple values (e.g., returning the first non-null value or joining strings). When called with a single argument, they function as an identity operation, returning the input unchanged. This pattern indicates either a coding error (missing arguments) or redundant logic that should be removed to simplify the expression.

A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle.