PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal Type ▲ Data source Last update License
121 Do not always depend on one's parent (INFORMATION_SCHEMA) Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
122 Exclude constraint to prevent overlapping time periods Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
123 Explicit locking This query identifies user-defined routines that employ explicit locking mechanisms to supplement PostgreSQL's default Multi-Version Concurrency Control (MVCC). It detects the presence of table-level locking (LOCK TABLE) or explicit row-level locking clauses (e.g., SELECT ... FOR UPDATE, FOR SHARE). While MVCC generally provides sufficient isolation for concurrent transactions, explicit locking is necessary in specific race-condition scenarios. This inventory assists in auditing concurrency control strategies and detecting potential sources of deadlocks or serialization bottlenecks. General INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:13 MIT License View
124 Extension routines Find all routines that belong to an extension. General system catalog base tables only 2025-11-07 10:11 MIT License View
125 Extensions that are available but are not installed Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. General system catalog base tables only 2025-11-07 10:11 MIT License View
126 Find all non-foreign key columns of base tables Find all non-foreign key columns of base tables. Make sure that no foreign key constraint is missing. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
127 Find all publications Find publications of tables that have been created in order to enable logical replication. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
128 Find views that can accept data modification statements This query identifies all views against which data modification statements (INSERT, UPDATE, DELETE) can be executed without raising an error. It evaluates the is_insertable_into and is_updatable attributes to detect both "naturally" updatable views (where PostgreSQL automatically maps changes to base tables) and views made updatable via the rewrite rule system (specifically DO INSTEAD rules). Notably, this definition encompasses views defined with DO INSTEAD NOTHING rules; while such views do not physically modify data, they are technically considered updatable because they accept DML statements without failure. General INFORMATION_SCHEMA only 2025-12-13 12:34 MIT License View
129 Foreign key columns that do not have an integer or varchar type Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
130 Foreign keys with ON DELETE CASCADE 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. General system catalog base tables only 2025-11-08 10:51 MIT License View
131 Foreign keys with ON UPDATE CASCADE 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. General system catalog base tables only 2025-11-08 10:40 MIT License View
132 Generated stored base table columns Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
133 Granted roles Find membership relations between roles. General system catalog base tables only 2025-11-07 10:11 MIT License View
134 Grantees Database must be used by users who have minimal set of privileges for performing tasks. The query helps to find out as to whether some user/role other than PUBLIC and a superuser have rights to use tables and routines of the database. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
135 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
136 Installed extensions Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. General system catalog base tables only 2025-11-07 10:11 MIT License View
137 Mandatory non-primary key columns Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
138 Minimum tuple length required before trying to move long column values into TOAST tables has been changed Find base tables in case of which toast_tuple_target storage parameter value is not the default value (2040). "Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. " Make sure that the parameter has an optimal value. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
139 More than one index on a column Find base table columns that belong to more than one index (including automatically created indexes that support constraints). General system catalog base tables only 2025-11-07 10:11 MIT License View
140 Multiple inheritance Find instances of multiple inheriance of base tables. Make sure that multiple inheritance is indeed needed. General system catalog base tables only 2025-11-07 10:11 MIT License View