Sample Header Ad - 728x90

How do you make sure a data page can only have 3 rows

2 votes
1 answer
69 views
For a table with a setting of (fillfactor = 75) I am trying to make sure that each data page can only have 3 rows. select ((8192 * 0.75 - 24) / 3)- 4 - 24; -- returns 2012. First 24 is page header meta byte. Last 24 is t_hoff (row meta bytes for every row) Last 4 is the line pointer for each inserted row. 2012 % 8 = 4. So I guess to make sure one page has only 3 rows I can only use 2008 byte? ~~~pgsql CREATE TABLE hot (hotid int,s char(2000))WITH (fillfactor = 75,autovacuum_enabled = OFF); INSERT INTO hot VALUES (1, 'A'); SELECT     lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2     FROM     heap_page_items (get_raw_page ('hot', 0)); ~~~ returns ~~~pgsql lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2 ----+--------+--------+--------+--------+------------------+------------- 1 | 6160 | 2032 | 24 | (0,1) | 0000100000000010 | 2 (1 row) ~~~ My guess of why **lp_len = 2032**. 2032 = 8 + 2000 + 24. first 4 byte int paddling it to 8 bytes. 2000 is for column s, the last 24 bytes are row meta page bytes. For the following table I expected lp_len to be 2032. But it's not. What did I miss? ~~~pgsql CREATE TABLE hot_test (     hotid int,    hotid1 int,s char(2000) )WITH (fillfactor = 75,autovacuum_enabled = OFF); INSERT INTO hot_test    VALUES (1, 1, 'A'); SELECT     lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2     FROM     heap_page_items (get_raw_page ('hot_test', 0)); ~~~ returns ~~~pgsql lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2 ----+--------+--------+--------+--------+------------------+------------- 1 | 8128 | 59 | 24 | (0,1) | 0000100000000010 | 3 (1 row) ~~~ Update: ~~~pgsql drop table hot_test; CREATE TABLE hot_test ( hotid int, hotid1 int,s char(1999) )WITH (fillfactor = 75,autovacuum_enabled = OFF); ALTER TABLE hot_test ALTER s SET STORAGE PLAIN; INSERT INTO hot_test VALUES (1, 1, 'A'); SELECT lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2 FROM heap_page_items (get_raw_page ('hot_test', 0)); ~~~ returns ~~~pgsql lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2 ----+--------+--------+--------+--------+------------------+------------- 1 | 6152 | 2035 | 24 | (0,1) | 0000100000000010 | 3 (1 row) ~~~ 2035 = 4 + 4 + 1999 + 4 + 24 So I guess varlena text needs 4 bytes of padding? ~~~pgsql drop table paddling; CREATE TABLE paddling ( hotid int, hotid1 int,s char(127) )WITH (fillfactor = 75,autovacuum_enabled = OFF); ALTER TABLE paddling ALTER s SET STORAGE PLAIN; INSERT INTO paddling VALUES (1, 1, 'ASDFGHJKLQWERTYUI'); SELECT lp,lp_off,lp_len,lp_len - 24 - 8,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2 FROM heap_page_items (get_raw_page ('paddling', 0)); ~~~ returns ~~~pgsql lp | lp_off | lp_len | ?column? | t_hoff | t_ctid | t_infomask | t_infomask2 ----+--------+--------+----------+--------+--------+------------------+------------- 1 | 8024 | 163 | 131 | 24 | (0,1) | 0000100000000010 | 3 (1 row) ~~~ ~~~pgsql drop table paddling; CREATE TABLE paddling ( hotid int, hotid1 int,s char(126) )WITH (fillfactor = 75,autovacuum_enabled = OFF); ALTER TABLE paddling ALTER s SET STORAGE PLAIN; INSERT INTO paddling VALUES (1, 1, 'ASDFGHJKLQWERTYUI'); SELECT lp,lp_off,lp_len,lp_len - 24 - 8,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2 FROM heap_page_items (get_raw_page ('paddling', 0)); ~~~ returns ~~~pgsql lp | lp_off | lp_len | ?column? | t_hoff | t_ctid | t_infomask | t_infomask2 ----+--------+--------+----------+--------+--------+------------------+------------- 1 | 8032 | 159 | 127 | 24 | (0,1) | 0000100000000010 | 3 (1 row) ~~~ Seems for char(x), if x 126 then padding is 4 byte. Tested, also applies to text data type.
Asked by jian (487 rep)
Apr 27, 2023, 05:18 AM
Last activity: Apr 28, 2023, 11:59 PM