Changing fillfactor of existing table

Question

Is it possible to change fillfactor of an existing table in PostgreSQL 8.4?

Or do I have to create copy of a table with new fillfactor - which is not the best approach because of foreign key problems?

Answer 1

Yes, that's possible. But you have to VACUUM FULL or CLUSTER this table afterwards to rewrite the table.

ALTER TABLE foo SET ( fillfactor = 50);
VACUUM FULL foo;

Answer 2

ALTER TABLE foo SET ( fillfactor = 20);
VACUUM FULL foo;

View table options incl. fill factors

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

Then

run pg_repack