14

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?

Jeff Widman
  • 22,014
  • 12
  • 72
  • 88
Daimon
  • 3,703
  • 2
  • 28
  • 30

2 Answers2

27

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;
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 1
    Note that you probably want to do CLUSTER instead of VACUUM FULL. See http://wiki.postgresql.org/wiki/VACUUM_FULL – Joe Van Dyk Feb 16 '12 at 00:53
  • 8
    @JoeVanDyk: Note that since pg 9.0 `VACUUM FULL` is fine - as documented in the Postgres Wiki by now. – Erwin Brandstetter Jul 05 '13 at 03:10
  • Does the new fillfactor work for new tuples/records with VACUUM'ing? (I can live with the old records being unaffected) – Christian P. Oct 28 '14 at 12:54
  • @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. – Frank Heikens Oct 28 '14 at 18:22
  • 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? – Christian P. Oct 29 '14 at 09:43
  • 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. – Frank Heikens Oct 29 '14 at 10:14
  • 4
    No `VACUUM FULL` please. `pg_repack` is doing same job without exclusive locks on table. aka online vacuum full https://github.com/reorg/pg_repack – CodeFarmer Jul 26 '18 at 01:10
0
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
CodeFarmer
  • 2,644
  • 1
  • 23
  • 32
  • 1
    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. – marksiemers Jun 17 '20 at 23:34