how to understand the whole table tenk1 row size byte by byte

Question

first query from Measure the size of a PostgreSQL table row
table tenk1 from https://github.com/postgres/postgres/blob/6ff2e8cdd410f70057cfa6259ad395c1119aeb32/src/test/regress/sql/test_setup.sql#L144

CREATE VIEW tenk1_storage_info AS
SELECT
    l.metric,
    l.nr AS bytes,
    CASE WHEN is_size THEN
        pg_size_pretty(nr)
    END AS bytes_pretty,
    CASE WHEN is_size THEN
        nr / NULLIF (x.ct, 0)
    END AS bytes_per_row
FROM (
    SELECT
        min(tableoid) AS tbl,
        count(*) AS ct,
        sum(length(t::text)) AS txt_len
    FROM
        public.tenk1 t) x
    CROSS JOIN LATERAL (
        VALUES (TRUE, 'core_relation_size', pg_relation_size(tbl)),
            (TRUE, 'visibility_map', pg_relation_size(tbl, 'vm')),
            (TRUE, 'free_space_map', pg_relation_size(tbl, 'fsm')),
            (TRUE, 'table_size_incl_toast', pg_table_size(tbl)),
            (TRUE, 'index_size', pg_indexes_size(tbl)),
            (TRUE, 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl)),
            (TRUE, 'live_rows_in_text_representation', txt_len),
            (FALSE, '----------------------------------', NULL),
            (FALSE, 'row_count', ct),
            (FALSE, 'live_tuples', pg_stat_get_live_tuples(tbl)),
            (FALSE, 'dead_tuples', pg_stat_get_dead_tuples(tbl))) l (is_size, metric, nr);

select * from tenk1_storage_info;
return

               metric               |  bytes  | bytes_pretty | bytes_per_row
------------------------------------+---------+--------------+---------------
 core_relation_size                 | 2826240 | 2760 kB      |           282
 visibility_map                     |    8192 | 8192 bytes   |             0
 free_space_map                     |   24576 | 24 kB        |             2
 table_size_incl_toast              | 2859008 | 2792 kB      |           285
 index_size                         |  827392 | 808 kB       |            82
 total_size_incl_toast_and_indexes  | 3686400 | 3600 kB      |           368
 live_rows_in_text_representation   |  680800 | 665 kB       |            68
 ---------------------------------- |         |              |
 row_count                          |   10000 |              |
 live_tuples                        |   10000 |              |
 dead_tuples                        |       0 |              |
(11 rows)

CREATE TABLE tenk1 (
    unique1 int4,
    unique2 int4,
    two int4,
    four int4,
    ten int4,
    twenty int4,
    hundred int4,
    thousand int4,
    twothousand int4,
    fivethous int4,
    tenthous int4,
    odd int4,
    even int4,
    stringu1 name,
    stringu2 name,
    string4 name
);
\set filename '/home/jian/Desktop/pg_sources/main/postgres/src/test/regress/data/tenk.data'
COPY tenk1
FROM
    :'filename';
VACUUM ANALYZE tenk1;

select ctid, * from tenk1
where (ctid::text::point)[0] = 0 and (ctid::text::point)[1] = 3 \gx

return:

ctid        | (0,3)
unique1     | 3420
unique2     | 2
two         | 0
four        | 0
ten         | 0
twenty      | 0
hundred     | 20
thousand    | 420
twothousand | 1420
fivethous   | 3420
tenthous    | 3420
odd         | 40
even        | 41
stringu1    | OBAAAA
stringu2    | CAAAAA
string4     | OOOOxx

SELECT
    lp,
    lp_off,
    lag(lp_off) OVER () - lp_off AS gap_lapoff,
    lp_flags,
    lp_len,
    t_hoff,
    t_ctid,
    t_infomask::bit(16),
    t_infomask2
FROM
    heap_page_items (get_raw_page ('tenk1', 0)) LIMIT 4;

return:

 lp | lp_off | gap_lapoff | lp_flags | lp_len | t_hoff | t_ctid |    t_infomask    | t_infomask2
----+--------+------------+----------+--------+--------+--------+------------------+-------------
  1 |   7920 |            |        1 |    268 |     24 | (0,1)  | 0000100100000000 |          16
  2 |   7648 |        272 |        1 |    268 |     24 | (0,2)  | 0000100100000000 |          16
  3 |   7376 |        272 |        1 |    268 |     24 | (0,3)  | 0000100100000000 |          16
  4 |   7104 |        272 |        1 |    268 |     24 | (0,4)  | 0000100100000000 |          16

hexdump -C -n 268 -s 7376 58105

return:

00001cd0  58 22 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |X"..............|
00001ce0  03 00 10 00 00 09 18 00  5c 0d 00 00 02 00 00 00  |........\.......|
00001cf0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001d00  14 00 00 00 a4 01 00 00  8c 05 00 00 5c 0d 00 00  |............\...|
00001d10  5c 0d 00 00 28 00 00 00  29 00 00 00 4f 42 41 41  |\...(...)...OBAA|
00001d20  41 41 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |AA..............|
00001d30  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001d50  00 00 00 00 00 00 00 00  00 00 00 00 43 41 41 41  |............CAAA|
00001d60  41 41 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |AA..............|
00001d70  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001d90  00 00 00 00 00 00 00 00  00 00 00 00 4f 4f 4f 4f  |............OOOO|
00001da0  78 78 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |xx..............|
00001db0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001dd0  00 00 00 00 00 00 00 00  00 00 00 00              |............|
00001ddc

questions:

How can one row take 268 bytes? If integer column(left to right) will be padding together as 8 bytes, name data type 64 bytes. then whole will be 6 * 8 + 8 + 64 * 3 = 248.
in hexdump, what does * mean? or How can I interpret the hexdump generally. to_hex(3420) return d5c. if i read 0d 5c in hexdump that means read from right to left?
last query: gap_lapoff != lp_len but in here[https://youtu.be/L-dw1yRFYVg?t=2236] it's the same. wondering why.

Did you consider the 23 byte row header? * means that the last line ir repeated one or more times. — Laurenz Albe, Apr 10 '23 at 20:22
@LaurenzAlbe yech, I should consider it. Now I am confused by the difference lap_len 272 with gap_lapoff 268. — jian, Apr 11 '23 at 01:14

score 1 · Accepted Answer · answered Apr 11 '23 at 07:01

To measure the size of the row, let's follow the documentation:

there are 23 bytes of row header
if all values are not NULL, there will be no NULL bitmask
there will be one byte of padding, since the actual row data must start at an address that is divisible by eight
if all values are not NULL, the table row consists of 13 times four bytes plus three times 64 bytes, which adds up to 244 bytes; there will be no padding in between, since name has an alignment of 1 byte

So a table row without NULLs will be 23 + 1 + 244 = 268 bytes in size. There will be an additional 4 bytes padding between such rows, because each row will also be at an address that is a multiple of 8. That accounts for the offset of 272 bytes between individual rows.

can you explain how to read hexdump? the hexdump is the second last of query output. I don't know how to read it as I mentioned in the second question.... — jian, Apr 11 '23 at 08:37
The table row is all there, from the xmin in the first 4 bytes (58 22 00 00, which is 8792 in a little-endian machine) to the nulls to fill up the 64 bytes of the names. You have to be specific, but perhaps in a different question. — Laurenz Albe, Apr 11 '23 at 08:53

how to understand the whole table tenk1 row size byte by byte

1 Answers1