Re: Postgres default FILLFACTOR value
| От | Virender Singla |
|---|---|
| Тема | Re: Postgres default FILLFACTOR value |
| Дата | |
| Msg-id | CAM6Zo8x7nTrSQ094oUqPew9sUAgu9LSg8-hKex7Nw7XZS2sK-Q@mail.gmail.com обсуждение исходный текст |
| Ответ на | Postgres default FILLFACTOR value (Virender Singla <virender.cse@gmail.com>) |
| Список | pgsql-hackers |
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
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 |
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.
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Andres FreundДата:
Сообщение: Re: [HACKERS] Restricting maximum keep segments by repslots