Postgres FILLFACTOR — deep dive

In last post we learnt about the differences in MVCC nature of Oracle vs Postgres and how setting appropriate FILLFACTOR value in Postgres can be beneficial. Let’s do more deep dive on this.

Completely compact table (FILLFACTOR = 100) will lead to bad performance of UPDATES and may lead to Index fragmentation as well. Whereas a low FILLFACTOR value causes Sequential Scan on a table to go slow because it has to read more partially filled blocks.

FILLFACTOR dependency factors:

To get optimal FILLFACTOR value, one should know the complete UPDATEs pattern on the table and how much space a block might need for a new version of tuples.

• UPDATES are doing bulk rows update or single row update? Bulk update means all the rows may get updated in a block.
• new updated data expanding the row size? If the new version of row is larger in size than the older one, then the block will need more space to accommodate that.
• A row gets updated once or multiple times in his lifetime? If we are expecting a row gets updated multiple times, VACUUM should run frequently to clean up the dead tuples and hence makes space for new/updated rows.
• Long running SQLs deferring VACUUM to clean up dead tuples.In case of VACUUM are deferring on a table, dead tuples will not get cleaned up.

For simplicity, we take the most common example of OLTP systems where usually UPDATE query affects a few rows only. Let’s get the TOP updated tables for a database.

select
   schemaname,
   relname,
   pg_size_pretty(pg_total_relation_size (relname::regclass)) as full_size,
   pg_size_pretty(pg_relation_size(relname::regclass)) as table_size,
   pg_size_pretty(pg_total_relation_size (relname::regclass) - pg_relation_size(relname::regclass)) as index_size,
   n_tup_upd,
   n_tup_hot_upd 
from
   pg_stat_user_tables 
order by
   n_tup_upd desc limit 10;
schemaname | relname | full_size | table_size | index_size | n_tup_upd | n_tup_hot_upd
------------+----------+-----------+------------+------------+------
 public     | test     | 297 MB    | 182 MB     | 115 MB     |  46171448 |         13382
 public     | testtd   | 334 MB    | 244 MB     | 90 MB      |    260900 |             0

Table FILACTLFOR:

Table FILLFACTOR default value is 100. We will take two use cases:

1. If UPDATE query is not updating any column which is part of corresponding Indexes on the table.

postgres=> create table test(id bigint) with (fillfactor = 90);
CREATE TABLE
postgres=> ALTER TABLE test SET (autovacuum_enabled = false);
ALTER TABLE
postgres=> insert into test SELECT temp.id from generate_series(1, 10000000) AS temp (id);
INSERT 0 10000000
postgres=> \dt+ test
Schema | Name | Type  |  Owner   |  Size  |--------+------+-------+----------+--------+
public | test | table | postgres | 383 MB |
postgres=> update test set id=id where id % 11 = 0;
UPDATE 909090
postgres=> \dt+ test
Schema | Name | Type  |  Owner   |  Size  |--------+------+-------+----------+--------+
public | test | table | postgres | 383 MB |
postgres=> update test set id=id where id % 11 = 0;
UPDATE 909090
postgres=> \dt+ test
Schema | Name | Type  |  Owner   |  Size  | Description
--------+------+-------+----------+--------+-------------
public | test | table | postgres | 383 MB |
postgres=> update test set id=id where id % 11 = 0;
UPDATE 909090
postgres=> \dt+ test
Schema | Name | Type  |  Owner   |  Size  | Description
--------+------+-------+----------+--------+-------------
public | test | table | postgres | 383 MB |

In this case if UPDATE query is updating a few records then we can Keep the FILLFACTOR value to be on the higher side say 90–95. This way UPDATE can be applicable for HOT and even ctid change will not update the Index entries. Later with the space pressure on that block, a mini block level VACUUM will automatically take care of dead tuples and thus make space for new UPDATED rows in the block.

2. If UPDATE query is UPDATING any Indexed column, Index entries will of course need to get change. Hence low FILLFACTOR here is only useful for making UPADTE query slightly faster because it does not need to look for another free block and migrate the new row version there.

Trade off with setting FILLFACTOR on such table is:

Faster UPDATE vs Slower Sequential Scan and wasted space (partially filled blocks)

FILLFACTOR for such tables can be started with a value with percentage calculations of rows in the tables which we are suspecting to get UPDATED (just a probable number).

For Ex — An application is designed where every row needs to updated shortly after its creation. In this case FILLFACTOR value can be set to 50.

Another aspect here is we need to make sure, VACUUM runs frequently on these tables so it removes dead tuples and makes room for new UPDATED rows within the block.

Note: FILLFACTOR value can be changed (increase or decrease) after table creation. In case we are increasing the FILLFACTOR value will also be added to the free space map (FSM) and hence eligible to get the new rows. While reducing FILLFACTOR value later, will not impact the blocks which are already filled more than new FILLFACTOR value.

B-tree Index FILLFACTOR:

Postgres Index FILLFACTOR default value is 90. Index FILLFACTOR functions differently than the Table FILLFACTOR. The difference here is how a new row comes — direct INSERTs or INSERTs caused by UPDATES (delete and then insert).

In case of Table, new row INSERTs obey the FILLFACTOR value and Postgres does not allow new row to get into a block above FILLFATOR value whereas in case of INSERTs caused by UPDATEs, it allows those new versioned rows to get into the block (within the same block only if possible) above FILLFACTOR value as well.

On other side with Index FILLFACTOR, both types of INSERTs are treated in same manner and even with setting of FILLFACTOR value say 90, new rows will be fit in that block above the FILLFACTOR value (except monotonically increasing columns).

Let’s take an example of a new data coming into an Index leaf block –

1. Monotonically increasing Inserts:

If new column value is the largest compared to existing keys, then it will not fill the block above FILLFACTOR value. Block split happens and new entry will go to the new block.

In the below example we see the size difference between two Indexes as Index with FILLFACTOR value 90 will keep 10% space in the block for further updates and hence when update is happening on this index, its size remains same.

postgres=> create table test(id bigint);
CREATE TABLE
postgres=> CREATE INDEX idx1_test ON test (id) with (fillfactor = 100);
CREATE INDEX
postgres=> CREATE INDEX idx2_test ON test (id); --fillfactor = 90 
CREATE INDEX
postgres=> insert into test SELECT temp.id from generate_series(1, 10000000) AS temp (id) ;
INSERT 0 10000000
postgres=> \di+ idx1_test
Schema |   Name    | Type  |  Owner   | Table |  Size  |--------+-----------+-------+----------+-------+--------+
public | idx1_test | index | postgres | test  | 193 MB |
postgres=> \di+ idx2_test
Schema |   Name    | Type  |  Owner   | Table |  Size  |
--------+-----------+-------+----------+-------+--------+
public | idx2_test | index | postgres | test  | 214 MB |
postgres=> update test set id = id+1 where id%100=0;
UPDATE 100000
postgres=> \di+ idx1_test
Schema |   Name    | Type  |  Owner   | Table |  Size  |
--------+-----------+-------+----------+-------+--------+
public | idx1_test | index | postgres | test  | 386 MB |
postgres=> \di+ idx2_test
Schema |   Name    | Type  |  Owner   | Table |  Size  |
--------+-----------+-------+----------+-------+--------+
public | idx2_test | index | postgres | test  | 214 MB |

2. Random Inserts:

If a new column value is coming in random order, then it will fill the block up to 100% and then a 50–50 split occurs.

In the below example we see, both the indexes with FILLFACTOR value 90 and 100 are almost same in size.

Here Index sizes are more compared to 100% packed index in the previous example because of 50–50 split and hence many blocks are partially filled.

postgres=> insert into test SELECT ceil(random() * 10000000) from generate_series(1, 10000000) AS temp (id) ;
INSERT 0 10000000
postgres=> \di+ idx1_test
Schema |   Name    | Type  |  Owner   | Table |  Size --------+-----------+-------+----------+-------+--------+
public | idx1_test | index | postgres | test  | 278 MB |
postgres=> \di+ idx2_test
Schema |   Name    | Type  |  Owner   | Table |  Size  |
--------+-----------+-------+----------+-------+--------+
public | idx2_test | index | postgres | test  | 280 MB |
postgres=> update test set id = id+1 where id%100=0;
UPDATE 99671
postgres=> \di+ idx1_test
Schema |   Name    | Type  |  Owner   | Table |  Size  |--------+-----------+-------+----------+-------+--------+
public | idx1_test | index | postgres | test  | 281 MB |
postgres=> \di+ idx2_test
Schema |   Name    | Type  |  Owner   | Table |  Size  |
--------+-----------+-------+----------+-------+--------+
public | idx2_test | index | postgres | test  | 282 MB |

Now if we re-index these indexes, they will obey the FILLFACTOR value. This is like creating a new Index on a table with monotonically increasing data.

postgres=> Reindex index idx2_test;
REINDEX
postgres=> \di+ idx2_test     --fillfactor 90
Schema |   Name    | Type  |  Owner   | Table |  Size  |--------+-----------+-------+----------+-------+--------+
public | idx2_test | index | postgres | test  | 214 MB |
postgres=> Reindex index idx1_test;
REINDEX
postgres=> \di+ idx1_test    --fillfactor 100
Schema |   Name    | Type  |  Owner   | Table |  Size  |
--------+-----------+-------+----------+-------+--------+
public | idx1_test | index | postgres | test  | 193 MB |

With above explanation, we see that Index FILLFACTOR is useful in cases where:

• A pre populated table exists and we need to create a new Index and then expecting a lot of updates to come on table Index columns (even if updated columns are part of other Index as ctid change will propagate to all the Indexes).
• Table where index extends at the right side (largest key values) and we are expecting many updates to come on table Index columns.

Closing Notes: I see Oracle default value for PCTFREE is 90 and 100 respectively for Tables and Indexes, whereas Postgres chooses FILLFACTOR value as 100 and 90 respectively for Tables and Indexes.

For OLTP use cases where DML is expected on many tables, do you think a default value of 90 and 100 on Tables and Indexes makes more sense? Open for suggestions, feedback and experiences in the comment section.