Postgres default FILLFACTOR value
Why Postgres default FILLFACTOR for table is 100 and for Index is 90.
Although Oracle is having completely different MVCC architecture, it uses
default 90 for table and 100 for Index (exact reverse of Postgres)
Postgres blocks needed more spaces for row update compares to Oracle
(because Oracle keeps buffer space only for row expansion, whereas Postgres
need to create new versioned row). As I see Postgres is more suitable for
OLTP workload, keeping TABLE FILLFACTOR value to 90 is more suitable rather
than stressing to save storage space. Less FILLFACTOR value will be useful
to make UPDATEs as HOT applicable as well and that is going to benefit new
Postgres adopting users who are initially not aware of such setting and
only realize this later when VACUUM are really running long and Indexes
gets bloated. .
Other side Index FILLFACTOR makes sense only for existing populated tables
and for any row (new INSERTs or INSERT coming through UPDATEs), it can fill
the block above FILLFACTOR value. I think 100 default make more sense here.
In Postgres, Index FILLFACTOR only works for monotonically increasing
column values and for random values it will do 50:50 block split. However
it's really less likely that monotonically increasing columns gets updated
then why we need to waste that 10% space and also making Index range scan
on such tables less performant.
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); --default to 90.
CREATE INDEX
postgres=> insert into test SELECT ceil(random() * 10000000) from
generate_series(1, 10000000) AS temp (id) ;
INSERT 0 10000000
postgres=> \di+ idx1_test
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------+-------+----------+-------+--------+-------------
public | idx1_test | index | postgres | test | 278 MB |
postgres=> \di+ idx2_test
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------+-------+----------+-------+--------+-------------
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
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------+-------+----------+-------+--------+-------------
public | idx1_test | index | postgres | test | 281 MB |
postgres=> \di+ idx2_test
List of relations
Schema | Name | Type | Owner | Table | Size |
--------+-----------+-------+----------+-------+--------+-----------
public | idx2_test | index | postgres | test | 282 MB |
On Fri, May 8, 2020 at 1:50 PM Virender Singla <virender.cse@gmail.com>
wrote:
Show quoted text
Why Postgres default FILLFACTOR for table is 100 and for Index is 90.
Although Oracle is having completely different MVCC architecture, it uses
default 90 for table and 100 for Index (exact reverse of Postgres)Postgres blocks needed more spaces for row update compares to Oracle
(because Oracle keeps buffer space only for row expansion, whereas Postgres
need to create new versioned row). As I see Postgres is more suitable for
OLTP workload, keeping TABLE FILLFACTOR value to 90 is more suitable rather
than stressing to save storage space. Less FILLFACTOR value will be useful
to make UPDATEs as HOT applicable as well and that is going to benefit new
Postgres adopting users who are initially not aware of such setting and
only realize this later when VACUUM are really running long and Indexes
gets bloated. .Other side Index FILLFACTOR makes sense only for existing populated tables
and for any row (new INSERTs or INSERT coming through UPDATEs), it can fill
the block above FILLFACTOR value. I think 100 default make more sense here.