Goal 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.
Notes The query's logic correctly accounts for how PostgreSQL stores relation options (reloptions). A simple check like reloptions IS NOT NULL is insufficient because this array can contain other settings, or it might explicitly store FILLFACTOR=100 if the value was changed and then reverted. Therefore, to avoid false positives, the query does not just check for the presence of options; it specifically parses the reloptions array to isolate entries where FILLFACTOR is defined as a value other than the default of 100.
Type General (Overview of some aspect of the database.)
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH base_tables_reloptions AS (SELECT 
  pg_class.relname AS table_name, 
  pg_namespace.nspname AS table_schema,
unnest(reloptions) AS reloptions
FROM 
  pg_catalog.pg_class, 
  pg_catalog.pg_namespace
WHERE 
  pg_class.relnamespace = pg_namespace.oid AND relkind='r'),
base_tables_fillfactor AS (SELECT table_schema, table_name, regexp_replace(reloptions,'[^0-9]','','g')::int AS fillfactor
FROM base_tables_reloptions
WHERE reloptions ILIKE 'fillfactor%')
SELECT table_schema, table_name, fillfactor 
FROM base_tables_fillfactor
WHERE fillfactor<>100
AND table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY fillfactor, table_schema, table_name;

Collections

This query belongs to the following collections:

Find problems by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories

This query is classified under the following categories:

Data at the database physical level

Queries of this category provide information about the disk usage.

Performance

Queries of this category provide information about indexes in a database.

NameDescription
Data at the database physical levelQueries of this category provide information about the disk usage.
PerformanceQueries of this category provide information about indexes in a database.