Checking fillfactor setting for tables and indexes

Question

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+ distributorsshows 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?

Whitacre

Answer 1

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 ('an_index_name', 'a_table_name')
  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.

Lauralauraceous