Checking fillfactor setting for tables and indexes

fillfactor, postgresql

Solution

You need to query the `pg_class` system table:

select t.relname as table_name, 
       t.reloptions
from pg_class t
  join pg_namespace n on n.oid = t.relnamespace
where t.relname in ('tab11_pkey', 'tab1')
  and n.nspname = 'public'

`reloptions` is an array, with each element containing one `option=value` definition. But it will be null for relations that have the default options.

Problem

There is maybe some function to check the fillfactor for indexes and tables? I've tried already \d+ but have basic definition only, without fillfactor value: ``` Index "public.tab1_pkey" Column | Type | Definition | Storage --------+--------+------------+--------- id | bigint | id | plain primary key, btree, for table "public.tab1" ``` For tables haven't found anything. If table was created with fillfactor other than default: ``` CREATE TABLE distributors ( did integer, name varchar(40), UNIQUE(name) WITH (fillfactor=70) ) WITH (fillfactor=70); ``` Then `\d+ distributors`shows non-standard fillfactor. ``` Table "public.distributors" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------+-----------+----------+--------------+------------- did | integer | | plain | | name | character varying(40) | | extended | | Indexes: "distributors_name_key" UNIQUE CONSTRAINT, btree (name) WITH (fillfactor=70) Has OIDs: no Options: fillfactor=70 ``` But maybe there is a way to get this value without parsing output?

Original source