It is very interesting that Oracle and PostgreSQL are going in different directions when it comes to the fillfactor. It probably has to do with the different implementation of MVCC. I am not arguing that the one or the other is better, it is just very interesting to know that they are different. So what are the defaults?
- The fillfactor for PostgreSQL is
- 100% for tables
- 90% for indexes
- The fillfactor for Oracle is
- 90% for tables
- 100% for indexes
But why is this relevant to me? Most of the PostgreSQL Users out there will not notice this detail whatsoever. However, if you have tables with a huge update count, you might notice a big difference. Below I will show you the impact of the fillfactor in PostgreSQL. In case you want to know more about the different implementations, check out the docs on their websites.
Oracle PCTFREE
The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.
PostgreSQL Fillfactor
https://www.postgresql.org/docs/current/sql-createtable.html
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.
Here is a little test case that shows that in PostgreSQL the fillfactor is really 100% for tables and 90% for indexes.
Lets create a little test database first
(postgres@[local]:55001)[postgres]> create database fill; CREATE DATABASE (postgres@[local]:55001)[postgres]> \c fill You are now connected to database "fill" as user "postgres". (postgres@[local]:55001)[fill]> (postgres@[local]:55001)[fill]>
Now lets check out the table fillfactor, by creating two tables. One named t_fill_75 and the other named t_fill_100, and insert some test data into it.
(postgres@[local]:55001)[fill]> create table t_fill_75 ( a int, b text ) with ( fillfactor = 75 );
CREATE TABLE
(postgres@[local]:55001)[fill]> create table t_fill_100 ( a int, b text );
CREATE TABLE
(postgres@[local]:55001)[fill]> \d+ t_fill_75
Table "public.t_fill_75"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | text | | | | extended | |
Access method: heap
Options: fillfactor=75
(postgres@[local]:55001)[fill]> \d+ t_fill_100
Table "public.t_fill_100"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | text | | | | extended | |
Access method: heap
(postgres@[local]:55001)[fill]> insert into t_fill_75 select x, md5(x::text) from generate_series(1,1000) x;
INSERT 0 1000
(postgres@[local]:55001)[fill]> insert into t_fill_100 select x, md5(x::text) from generate_series(1,1000) x;
INSERT 0 1000
As expected, the table t_fill_75 is slightly bigger, because the table is filled only up to 75%.
(postgres@[local]:55001)[fill]> select pg_relation_size('t_fill_75');
pg_relation_size
------------------
98304
(1 row)
(postgres@[local]:55001)[fill]> select pg_relation_size('t_fill_100');
pg_relation_size
------------------
73728
(1 row)
(postgres@[local]:55001)[fill]> select relname, relpages, reltuples, (reltuples/relpages)::int avgtuplperpage
> from pg_class where relname in ('t_fill_75','t_fill_100');
relname | relpages | reltuples | avgtuplperpage
------------+----------+-----------+----------------
t_fill_75 | 12 | 1000 | 83
t_fill_100 | 9 | 1000 | 111
(2 rows)
(postgres@[local]:55001)[fill]> select ctid, * from t_fill_75 where a < 6;
ctid | a | b
-------+---+----------------------------------
(0,1) | 1 | c4ca4238a0b923820dcc509a6f75849b
(0,2) | 2 | c81e728d9d4c2f636f067f89cc14862c
(0,3) | 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
(0,4) | 4 | a87ff679a2f3e71d9181a67b7542122c
(0,5) | 5 | e4da3b7fbbce2345d7772b0674a318d5
(5 rows)
(postgres@[local]:55001)[fill]> select ctid, * from t_fill_100 where a < 6;
ctid | a | b
-------+---+----------------------------------
(0,1) | 1 | c4ca4238a0b923820dcc509a6f75849b
(0,2) | 2 | c81e728d9d4c2f636f067f89cc14862c
(0,3) | 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
(0,4) | 4 | a87ff679a2f3e71d9181a67b7542122c
(0,5) | 5 | e4da3b7fbbce2345d7772b0674a318d5
(5 rows)
When we start updating the tables it becomes interesting. You can see clearly that in table t_fill_100 one row is in a different block.
(postgres@[local]:55001)[fill]> update t_fill_75 set b = 'dummy' where a = 2; UPDATE 1 (postgres@[local]:55001)[fill]> update t_fill_100 set b = 'dummy' where a = 2; UPDATE 1 (postgres@[local]:55001)[fill]> select ctid, * from t_fill_75 where a < 6; ctid | a | b --------+---+---------------------------------- (0,1) | 1 | c4ca4238a0b923820dcc509a6f75849b (0,3) | 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 (0,4) | 4 | a87ff679a2f3e71d9181a67b7542122c (0,5) | 5 | e4da3b7fbbce2345d7772b0674a318d5 (0,91) | 2 | dummy (5 rows) (postgres@[local]:55001)[fill]> select ctid, * from t_fill_100 where a < 6; ctid | a | b --------+---+---------------------------------- (0,1) | 1 | c4ca4238a0b923820dcc509a6f75849b (0,3) | 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 (0,4) | 4 | a87ff679a2f3e71d9181a67b7542122c (0,5) | 5 | e4da3b7fbbce2345d7772b0674a318d5 (8,41) | 2 | dummy <-- Number 8 indicates that you are on block 8 (5 rows)
With the following query you check out the number of hot updates that happened on a particular table.
(postgres@[local]:55001)[fill]> select relname,n_tup_hot_upd from pg_stat_user_tables where relname in ('t_fill_75','t_fill_100');
relname | n_tup_hot_upd
------------+---------------
t_fill_75 | 1
t_fill_100 | 0
(2 rows)
Now we do a little test case to see if the PostgreSQL Btree Indexes has a default fill factor of 90%.
(postgres@[local]:55001)[fill]> create table t_fill (id bigint); -- default is 100% CREATE TABLE (postgres@[local]:55001)[fill]> create index idx_100 on t_fill (id) with (fillfactor = 100); -- set fillfactor to 100% CREATE INDEX (postgres@[local]:55001)[fill]> create index idx_90_default on t_fill (id); -- default is 90% CREATE INDEX (postgres@[local]:55001)[fill]> insert into t_fill SELECT ceil(random() * 10000000) from > generate_series(1, 10000000) AS temp (id) ; INSERT 0 10000000
As expected, the index with the fillfactor of 100% is smaller.
(postgres@[local]:55001)[fill]> \di+ idx_100
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+---------+-------+----------+--------+-------------+--------+-------------
public | idx_100 | index | postgres | t_fill | permanent | 249 MB |
(1 row)
(postgres@[local]:55001)[fill]> \di+ idx_90_default
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+----------------+-------+----------+--------+-------------+--------+-------------
public | idx_90_default | index | postgres | t_fill | permanent | 253 MB |
(1 row)
What happens after the update?
(postgres@[local]:55001)[fill]> update t_fill set id = id+1 where id%100=0;
UPDATE 100004
(postgres@[local]:55001)[fill]> \di+ idx_100
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+---------+-------+----------+--------+-------------+--------+-------------
public | idx_100 | index | postgres | t_fill | permanent | 250 MB |
(1 row)
(postgres@[local]:55001)[fill]> \di+ idx_90_default
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+----------------+-------+----------+--------+-------------+--------+-------------
public | idx_90_default | index | postgres | t_fill | permanent | 255 MB |
(1 row)
(postgres@[local]:55001)[fill]> VACUUM FULL t_fill;
VACUUM
(postgres@[local]:55001)[fill]> \di+ idx_100
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+---------+-------+----------+--------+-------------+--------+-------------
public | idx_100 | index | postgres | t_fill | permanent | 169 MB |
(1 row)
(postgres@[local]:55001)[fill]> \di+ idx_90_default
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+----------------+-------+----------+--------+-------------+--------+-------------
public | idx_90_default | index | postgres | t_fill | permanent | 187 MB |
(1 row)
(postgres@[local]:55001)[fill]> update t_fill set id = id+2 where id%50=0;
UPDATE 99309
(postgres@[local]:55001)[fill]> \di+ idx_100
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+---------+-------+----------+--------+-------------+--------+-------------
public | idx_100 | index | postgres | t_fill | permanent | 310 MB |
(1 row)
(postgres@[local]:55001)[fill]> \di+ idx_90_default
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+----------------+-------+----------+--------+-------------+--------+-------------
public | idx_90_default | index | postgres | t_fill | permanent | 187 MB |
(1 row)
To learn more about the HOT (Heap Only Tuples) feature, take a look at the readme which you can find in the PostgreSQL source code.
[postgres@ocm199 13.1]$ find . -name README.HOT ./postgresql-13.1/src/backend/access/heap/README.HOT [postgres@ocm199 13.1]$ less ./postgresql-13.1/src/backend/access/heap/README.HOT src/backend/access/heap/README.HOT Heap Only Tuples (HOT) ====================== The Heap Only Tuple (HOT) feature eliminates redundant index entries and allows the re-use of space taken by DELETEd or obsoleted UPDATEd tuples without performing a table-wide vacuum. It does this by allowing single-page vacuuming, also called "defragmentation". Note: there is a Glossary at the end of this document that may be helpful for first-time readers. ... ... ...
Conclusion
In case you move from Oracle to PostgreSQL it might be worth to check out little details like the fillfactor. Especially if you have tables with a huge number of updates running on it.