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?

Cashier

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;

Sophistic

Note that you probably want to do CLUSTER instead of VACUUM FULL. See wiki.postgresql.org/wiki/VACUUM_FULL – Dolliedolloff

@JoeVanDyk: Note that since pg 9.0 VACUUM FULL is fine - as documented in the Postgres Wiki by now. – Springhalt

Does the new fillfactor work for new tuples/records with VACUUM'ing? (I can live with the old records being unaffected) – Sacaton

@ChristianP.: Only if there is enough free space within a block the database can use HOT updates. Extra space can be created when rewriting the blocks, using VACUUM FULL or CLUSTER. – Sophistic

Sorry, I can see I made a typo. I meant if the fillfactor change works for new tuples WITHOUT vacuum'ing, so only newly created tuples get the fillfactor added while older tuples do not? – Sacaton

It works for all data blocks, but when a block is for 100% full, HOT won't work and a normal update will be done. This also means that for the next update of a tuple in this block, a HOT update might work! It's dynamic and it depends on my things, including auto vacuum, the fill factor and the amount of updates you do. – Sophistic

No VACUUM FULL please. pg_repack is doing same job without exclusive locks on table. aka online vacuum full github.com/reorg/pg_repack – Yet

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

Yet

pg_repack is a great tool. It is only available with the 'contrib' modules installed. If not, a VACUUM FULL or CLUSTER is required, as mentioned in the other answer. – Reverential