PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 25 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
1 All system-defined TOAST-able types Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). General system catalog base tables only 2020-11-06 14:51 MIT License View
2 All user-defined TOAST-able types Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). General system catalog base tables only 2020-11-06 14:51 MIT License View
3 Base table columns where TOASTing is possible Find all base table columns in case of which the system can use TOAST technique. General system catalog base tables only 2020-11-06 14:51 MIT License View
4 Base table columns where TOAST-ing strategy has been changed Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal. General system catalog base tables only 2020-11-06 14:51 MIT License View
5 Base table columns where TOAST-ing strategy has been changed to plain Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
6 Base table FILLFACTOR is not 100 Find all base tables where FILLFACTOR is not 100, i.e., the default value. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
7 Base tables that do not have a TOAST table Find base tables that (due to the types of their columns) do not have an associated TOAST table for storing out-of-line data. General system catalog base tables only 2020-11-06 14:51 MIT License View
8 B-tree index fillfactor has been explicitly set to 90 Find B-tree indexes that fillfactor has been explicitly set to 90. In case of B-tree indexes the default is 90. Problem detection system catalog base tables only 2024-12-11 14:37 MIT License View
9 FILLFACTOR is probably too big Find base tables in case of which the FILLFACTOR property has perhaps a too big value. Try to find base tables that probably encounter UPDATE operations. In the tables that have frequent updates you want to have free space in table pages (blocks) to accommodate new row versions, which the system automatically creates as a result of fulfilling UPDATE statements. If a new row version will be put to another page by the system, then it means that table indexes have to be updated as well. Thus, the more there are indexes, the more the table would benefit from keeping a new row version in the same page as the old version. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-21 09:19 MIT License View
10 FILLFACTOR is probably too small Too small fillfactor wastes storage space. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-02 10:43 MIT License View
11 FILLFACTOR is probably too small (2) Find base tables that probably implement many to many relationship type, have no clear sign that there are columns that should be updated, but still have fillfactor less than 100. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-11 14:38 MIT License View
12 Index FILLFACTOR is not default Find all indexes where FILLFACTOR is not default, i.e., it has been changed. The default is different in case of different index types is different. In case of B-tree indexes the default is 90. General system catalog base tables only 2024-11-21 09:28 MIT License View
13 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 2020-11-06 14:51 MIT License View
14 Percentage of the total index storage size from the total database storage size (system catalog included) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
15 The size of base tables and their indexes Find the size of base tables without indexes, size of the indexes of the table, total size of the table (including its indexes) and percentage of the index size from the total size. If the size of indexes of a table is relatively high, then check as to whether all the indexes are needed. General system catalog base tables only 2023-10-27 20:38 MIT License View
16 The storage size of each index (the system catalog excluded) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
17 The storage size of each schema data object (the system catalog excluded) Get overview of disk usage by different schema objects that contain user data. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
18 The storage size of each table (including indexes) (the system catalog excluded) Get overview of disk usage. Sofware measure system catalog base tables only 2020-11-06 14:51 MIT License View
19 The total size of all indexes (system catalog excluded) Sofware measure system catalog base tables only 2023-05-14 11:04 MIT License View
20 The total size of all tables (system catalog excluded) Sofware measure system catalog base tables only 2023-05-14 11:04 MIT License View