Thread

1. Postgres default FILLFACTOR value

   Virender Singla <virender.cse@gmail.com> — 2020-05-08T08:20:30Z

   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.
   

2. Re: Postgres default FILLFACTOR value

   Virender Singla <virender.cse@gmail.com> — 2020-05-17T05:48:45Z

   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:
   
   > 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.
   >
   >
   >
   >