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....@gmail.com>
wrote:
> 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.
>
>
>
>
