FillFactor for UPDATE

What is FillFactor in PostgreSQL?

As per offical Doc —

fillfactor (integer)

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.

Does this ring any bell related to Oracle?

Yes, its PCTFREE.

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.

In Postgres world, during an UPDATE the old tuple is marked as deleted by the current transaction and new tuple is inserted. Now depending on space availability within the same page, the new tuple can be inserted into it or in another page. Along with this, index pointer modification to the new tuple will be done.

anand@postgres # create table test (data varchar(2));
CREATE TABLE
Time: 7.049 ms

anand@postgres # insert into test values ('a');
INSERT 0 1
Time: 1.408 ms
anand@postgres # select * from heap_page_items(get_raw_page('test',0));
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
| lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
|  1 |   8160 |        1 |     26 |   1012 |      0 |        0 | (0,1)  |           1 |       2050 |     24 | NULL   |  NULL | \x0561 |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
(1 row)

Time: 0.296 ms

anand@postgres # SELECT txid_current_snapshot();
+-----------------------+
| txid_current_snapshot |
+-----------------------+
| 1013:1013:            |
+-----------------------+
(1 row)

Time: 0.213 ms
anand@postgres # insert into test values ('b');
INSERT 0 1
Time: 1.237 ms
anand@postgres # select * from heap_page_items(get_raw_page('test',0));
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
| lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
|  1 |   8160 |        1 |     26 |   1012 |      0 |        0 | (0,1)  |           1 |       2050 |     24 | NULL   |  NULL | \x0561 |
|  2 |   8128 |        1 |     26 |   1013 |      0 |        0 | (0,2)  |           1 |       2050 |     24 | NULL   |  NULL | \x0562 |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
(2 rows)

Time: 0.448 ms

anand@postgres # update test set data='c' where data='a';
UPDATE 1
Time: 1.806 ms
anand@postgres # select * from heap_page_items(get_raw_page('test',0));
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
| lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
|  1 |   8160 |        1 |     26 |   1012 |   1014 |        0 | (0,3)  |       16385 |        258 |     24 | NULL   |  NULL | \x0561 |
|  2 |   8128 |        1 |     26 |   1013 |      0 |        0 | (0,2)  |           1 |       2306 |     24 | NULL   |  NULL | \x0562 |
|  3 |   8096 |        1 |     26 |   1014 |      0 |        0 | (0,3)  |       32769 |      10242 |     24 | NULL   |  NULL | \x0563 |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
(3 rows)

Time: 0.352 ms

anand@postgres # delete from test where data='c';
DELETE 1
Time: 1.542 ms
anand@postgres # select * from heap_page_items(get_raw_page('test',0));
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
| lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
|  1 |   8160 |        1 |     26 |   1012 |   1014 |        0 | (0,3)  |       16385 |       1282 |     24 | NULL   |  NULL | \x0561 |
|  2 |   8128 |        1 |     26 |   1013 |      0 |        0 | (0,2)  |           1 |       2306 |     24 | NULL   |  NULL | \x0562 |
|  3 |   8096 |        1 |     26 |   1014 |   1015 |        0 | (0,3)  |       40961 |       8450 |     24 | NULL   |  NULL | \x0563 |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
(3 rows)

Time: 0.342 ms
anand@postgres # vacuum test;
VACUUM
Time: 12.944 ms
anand@postgres # select * from heap_page_items(get_raw_page('test',0));
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
| lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
|  1 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL   |        NULL |       NULL |   NULL | NULL   |  NULL | NULL   |
|  2 |   8160 |        1 |     26 |   1013 |      0 |        0 | (0,2)  |           1 |       2306 |     24 | NULL   |  NULL | \x0562 |
|  3 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL   |        NULL |       NULL |   NULL | NULL   |  NULL | NULL   |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
(3 rows)

Time: 0.536 ms
anand@postgres # vacuum full test;
VACUUM
Time: 15.169 ms
anand@postgres # select * from heap_page_items(get_raw_page('test',0));
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
| lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
|  1 |   8160 |        1 |     26 |   1013 |      0 |        0 | (0,1)  |           1 |       2818 |     24 | NULL   |  NULL | \x0562 |
+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------+
(1 row)

Time: 0.561 ms
anand@postgres #

Recently in Prod db, I noticed a table with primary key index having ~3500 updates/hr and each update, updated 1 row with mean_time of ~2-3seconds. The updates were eligibile for HOT.

+---------+------------------------+-----------+-------+------------------+
| snap_id |      sample_time       |  queryid  | calls |    mean_time     |
+---------+------------------------+-----------+-------+------------------+
|      99 | 2018-04-03 14:00:01+00 | 983545275 |  3551 | 8624.70651816391 |
|     100 | 2018-04-03 15:00:00+00 | 983545275 |  3831 | 485.123798225008 |
|     103 | 2018-04-03 18:00:01+00 | 983545275 |  3567 | 8752.98942528735 |
|     104 | 2018-04-03 19:00:01+00 | 983545275 |  3445 |  1173.0251509434 |
|     105 | 2018-04-03 20:00:00+00 | 983545275 |  3897 | 906.002405953299 |


Modifying the fill-factor for the table to 50 (keeping 50% space free for updates),  improved the performance by 1000x.

anand@postgres # alter table user.cache set (fillfactor = 50);
ALTER TABLE
Time: 126.374 ms
anand@postgres # vacuum full user.cahce;
VACUUM
Time: 21284.498 ms (00:21.284)
anand@postgres #

Reducing the fill factor increases the table size as during insert some space in the page will be left unused to future updates.

Post Change SQL Peformance —

+---------+------------------------+-----------+-------+-----------+-----------------+
| snap_id |      sample_time       |  queryid  | calls | mean_time | shared_blks_hit |
+---------+------------------------+-----------+-------+-----------+-----------------+
|     529 | 2018-04-22 13:00:01+00 | 983545275 |  3788 |      0.89 |         3219353 |
|     530 | 2018-04-22 14:00:00+00 | 983545275 |  4009 |      0.88 |         3413391 |
|     531 | 2018-04-22 15:00:00+00 | 983545275 |  4021 |      0.91 |         3564540 |
|     532 | 2018-04-22 16:00:01+00 | 983545275 |  4005 |      1.12 |         3721869 |
|     533 | 2018-04-22 17:00:01+00 | 983545275 |  4093 |      1.07 |         3901729 |
|     534 | 2018-04-22 18:00:01+00 | 983545275 |  4033 |      0.98 |         4071757 |
|     535 | 2018-04-22 19:00:01+00 | 983545275 |  3773 |      1.38 |         4232251 |
|     536 | 2018-04-22 20:00:01+00 | 983545275 |  3856 |      1.23 |         4372074 |
|     537 | 2018-04-22 21:00:00+00 | 983545275 |  3978 |      1.07 |         4535671 |
+---------+------------------------+-----------+-------+-----------+-----------------+

+------------------+---------+------------+----------+-------------+-----------+-----------+-----------+-----------+----------+------------+
|       relname    | relsize |   tblsp    | seq_scan |  idx_scan   | n_tup_ins | n_tup_upd | n_tup_del |   total   | hot_rate | fillfactor |
+------------------+---------+------------+----------+-------------+-----------+-----------+-----------+-----------+----------+------------+
|       cache      | 101 MB  | pg_default |       12 |     5926041 |     54404 |   1403579 |     59859 |   1923481 |    71.10 | 50         |

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s