PostgreSQL Alignment theory not completely accurate?

Question

I was reading this article on the Internet regarding alignment PostgreSQL.

Based on the example of the article and on some work experience I had with the alignment, I did a small experiment with the same model:

CREATE TABLE tab ( col1 smallint, col2 timestamp, col3 integer, col4 double precision );

CREATE TABLE tab2 ( col2 timestamp, col4 double precision, col3 integer, col1 smallint );

INSERT INTO tab (col1, col2, col3, col4)
SELECT (random()*32767)::smallint, NOW() - (random()*365)::int * INTERVAL '1 day' + (random()*86400)::int * INTERVAL '1 second', (random()*1000)::int, random()*100.0 FROM generate_series(1,10000);

INSERT INTO tab2 (col2, col4, col3, col1)
SELECT NOW() - (random()*365)::int * INTERVAL '1 day' + (random()*86400)::int * INTERVAL '1 second', random()*100.0, (random()*1000)::int, (random()*32767)::smallint FROM generate_series(1,10000);

If we now check the size of these tables:

SELECT pg_relation_size(quote_ident('public') || '.' || quote_ident('tab')) AS size_bytes;

The above returns 606,208 bytes (for Table 'tab'). Whilst the following:

SELECT pg_relation_size(quote_ident('public') || '.' || quote_ident('tab2')) AS size_bytes;

Returns 524,288 bytes (for Table 'tab2').

The real difference between both is 606,208 - 524,288 = 81,920 bytes, however, if we follow the theory, 10 padding bytes * 10,000 rows = 100,000 bytes, so the theory kind of overestimates the alignment effect,

Am I missing out something else?

Answer 1

Looking at the size of the rows, the difference of 10 bytes seems to hold up:

SELECT pg_column_size(tab) FROM tab LIMIT 1;

 pg_column_size 
════════════════
             56
(1 row)

SELECT pg_column_size(tab2) FROM tab2 LIMIT 1;

 pg_column_size 
════════════════
             46
(1 row)

But there are other factors to consider:

• Each row starts at an address that is a multiple of 8. Since the rows of tab2 are 46 bytes in size, there are two extra padding bytes after each row. There are no padding bytes after the tab rows, since 56 is divisible by 8. So the effective difference is actually 8 bytes, or 80000 bytes for 10000 rows.

• Table blocks usually cannot be filled completely with rows. There will typically be a little hole left in each block that is too small to fit another row. The effect shouldn't be huge for shortish rows as these, but you'd have to take it into account if you want to be accurate. I expect that that will account for the 1920 bytes that are still unexplained.

I recommend that you use the pageinspect extension to look at reality:

SELECT lp, lp_off, t_ctid, length(t_data)
FROM heap_page_items(get_raw_page('tab', 0));

 lp  │ lp_off │ t_ctid  │ length 
═════╪════════╪═════════╪════════
   1 │   8136 │ (0,1)   │     32
   2 │   8080 │ (0,2)   │     32
   3 │   8024 │ (0,3)   │     32
   4 │   7968 │ (0,4)   │     32
   5 │   7912 │ (0,5)   │     32
...
 133 │    744 │ (0,133) │     32
 134 │    688 │ (0,134) │     32
 135 │    632 │ (0,135) │     32
 136 │    576 │ (0,136) │     32
(136 rows)

SELECT lp, lp_off, t_ctid, length(t_data)
FROM heap_page_items(get_raw_page('tab2', 0));

 lp  │ lp_off │ t_ctid  │ length 
═════╪════════╪═════════╪════════
   1 │   8144 │ (0,1)   │     22
   2 │   8096 │ (0,2)   │     22
   3 │   8048 │ (0,3)   │     22
   4 │   8000 │ (0,4)   │     22
   5 │   7952 │ (0,5)   │     22
...
 154 │    800 │ (0,154) │     22
 155 │    752 │ (0,155) │     22
 156 │    704 │ (0,156) │     22
 157 │    656 │ (0,157) │     22
(157 rows)

The length is without the header. The lp_off shows the offset where the row is stored, which is 56 for tab and 48 for tab2. You also can see how many rows fit into a single page.