what's the calculation with fillfactor in postgresql

All we need is an easy explanation of the problem, so here it is.

create table big(id integer primary key generated always as identity, s char(1000)) with(fillfactor = 10);

 insert into big(s) values('foo');
 insert into big(s) values('foo');
 select ctid, id from big;

return:

 ctid  | id
-------+----
 (0,1) |  1
 (1,1) |  2
(2 rows)

I don’t know how to do calculation with fillfactor, so that the above table will be structured as one record/row one page?

-- return 2064,2
SELECT sum(pg_column_size(t.*)) as filesize, count(*) as filerow FROM big as t; 
--return 1004
select pg_column_size(s) from big; --1004

Only info i get from manual.

fillfactor (integer)
The fillfactor for a table is a percentage
between 10 and 100. 100 (complete packing) is the default. When a
smaller fillfactor is specified, INSERT operations pack table pages
only to the indicated percentage; the remaining space on each page is
reserved for updating rows on that page. This gives UPDATE a chance to
place the updated copy of a row on the same page as the original,
which is more efficient than placing it on a different page. For a
table whose entries are never updated, complete packing is the best
choice, but in heavily updated tables smaller fillfactors are
appropriate. This parameter cannot be set for TOAST tables.

Further reference: https://drive.google.com/file/d/1Bobgebpb07ctCM-Jk9XBxmY8k9CZkorn/view?usp=sharing Page: 190.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Looks like your rows are larger than 10% of one 8kB block, so they end up in different blocks. char(1000) is mean, because it is padded with blanks, and the size is to small to trigger TOAST compression.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply