{"id":1622,"date":"2017-08-09T13:49:53","date_gmt":"2017-08-09T11:49:53","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1622"},"modified":"2017-08-09T16:30:23","modified_gmt":"2017-08-09T14:30:23","slug":"pgsql-lo-space-usage-part-2","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/pgsql-lo-space-usage-part-2\/","title":{"rendered":"PostgreSQL Large Objects and space usage (part 2)"},"content":{"rendered":"

In my previous post <\/a>I showed how large objects use space inside the table pg_largeobject when inserted.<\/p>\n

Let’s see something more:<\/p>\n

The table had 2 large objects (for a total of 1024 records):<\/p>\n

lob_test=# select pg_relation_size('pg_largeobject');\r\npg_relation_size\r\n------------------\r\n          1441792\r\n(1 row)<\/pre>\n
Let’s try to add another random-padded file:<\/div>\n
\n
lob_test=# \\lo_import '\/tmp\/randoms';\r\nlo_import 16493\r\nlob_test=# select pg_relation_size('pg_largeobject');\r\n pg_relation_size\r\n------------------\r\n          2842624\r\n(1 row)\r\n\r\nlob_test=# select oid, * from  pg_largeobject_metadata;\r\n  oid  | lomowner | lomacl\r\n-------+----------+--------\r\n 16491 |       10 |\r\n 16492 |       10 |\r\n 16493 |       10 |\r\n(3 rows)<\/pre>\n<\/div>\n
As expected, because a random sequence of characters cannot be compressed, the size increased again by 171 blocks (see my previous post<\/a> for the explanation)<\/p>\n
If you read this nice series of blog posts<\/a> by Frits Hoogland, you should know about the pageinspect extension and the t_infomask 16-bit mask.<\/p>\n
Let’s install it and check the content of the pg_largeobjects pages:<\/div>\n
<\/div>\n
\n
lob_test=# select * from page_header(get_raw_page('pg_largeobject',0));\r\n     lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid\r\n-------------+----------+-------+-------+-------+---------+----------+---------+-----------\r\n 18\/38004C10 |        0 |     0 |   452 |   488 |    8192 |     8192 |       4 |         0\r\n(1 row)\r\n\r\n-- same result (lower 452, upper 488) for blocks 1...3\r\n\r\nlob_test=# select * from page_header(get_raw_page('pg_largeobject',4));\r\n     lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid\r\n-------------+----------+-------+-------+-------+---------+----------+---------+-----------\r\n 18\/380179F8 |        0 |     0 |   360 |  2144 |    8192 |     8192 |       4 |         0\r\n(1 row)\r\n\r\n\r\nlob_test=# select * from page_header(get_raw_page('pg_largeobject',5));\r\n     lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid\r\n-------------+----------+-------+-------+-------+---------+----------+---------+-----------\r\n 18\/381386E0 |        0 |     0 |    36 |  1928 |    8192 |     8192 |       4 |         0\r\n(1 row)-- same result for the remaining blocks<\/pre>\n
We already know the mathematics, but we love having all the pieces come together \ud83d\ude42<\/p>\n
We know that: The page header is 24 bytes, and that the line pointers use 4 bytes for each tuple.<\/p>\n
The first 4 pages have the lower offset to 452 bytes means that we have (452-24)\/4 = 107 tuples.<\/p>\n
The 5th page (page number 4) has the lower to 360: (360-24)\/4=84 tuples.<\/p>\n
The remaining pages have the lower to 36: (36-24)\/4 = 3 tuples.<\/p>\n
Let’s check if we are right:<\/p>\n<\/div>\n
lob_test=# select generate_series as page,\r\n (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series)))  as tuples\r\n from generate_series(0,5);\r\n page | tuples\r\n------+--------\r\n    0 |    107\r\n    1 |    107\r\n    2 |    107\r\n    3 |    107\r\n    4 |     84\r\n    5 |      3\r\n(6 rows)<\/pre>\n
\ud83d\ude42<\/div>\n
<\/div>\n
Now, let’s delete the 1Mb file and check the space again:<\/div>\n
\n
lob_test=# \\lo_unlink 16492\r\nlo_unlink 16492\r\n\r\n\r\nlob_test=# select pg_relation_size('pg_largeobject');\r\n pg_relation_size\r\n------------------\r\n          2842624\r\n(1 row)\r\n\r\nlob_test=# select oid, * from  pg_largeobject_metadata;\r\n  oid  | lomowner | lomacl\r\n-------+----------+--------\r\n 16491 |       10 |\r\n 16493 |       10 |\r\n(2 rows)\r\n\r\nlob_test=# select generate_series as pageno, (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))  ) from generate_series(0,12);                  pageno | count\r\n--------+-------\r\n      0 |   107\r\n      1 |   107\r\n      2 |   107\r\n      3 |   107\r\n      4 |    84\r\n      5 |     3\r\n      6 |     3\r\n      7 |     3\r\n      8 |     3\r\n      9 |     3\r\n     10 |     3\r\n     11 |     3\r\n     12 |     3<\/pre>\n
The space is still used and the tuples are still there.<\/p>\n
However, we can check that the tuples are no longer used by checking the validity of their t_xmax. In fact, according to the documentation, if the XMAX is invalid the row is at the latest version:<\/p>\n<\/div>\n
\n
[…] a tuple is the latest version of its row iff XMAX is invalid or t_ctid points to itself (in which case, if XMAX is valid, the tuple is<\/span> either locked or deleted). […]
\n<\/span><\/div>\n<\/blockquote>\n
\u00a0(from htup_details.h<\/span><\/a> lines 87-89).<\/div>\n
<\/div>\n
We have to check the infomask against the 12th bit (2048, or 0x0800)<\/div>\n
#define HEAP_XMAX_INVALID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0800\u00a0 \/* t_xmax invalid\/aborted *\/<\/div>\n
<\/div>\n
\n
lob_test=# select generate_series as pageno, \r\n  (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))\r\n  where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,12);\r\n pageno | count\r\n--------+-------\r\n      0 |   107\r\n      1 |   107\r\n      2 |   107\r\n      3 |   107\r\n      4 |    84\r\n      5 |     0\r\n      6 |     0\r\n      7 |     0\r\n      8 |     0\r\n      9 |     0\r\n     10 |     0\r\n     11 |     0\r\n     12 |     0<\/pre>\n
Here we go. The large objects are split in compressed chunks that internally behave the same way as regular rows!<\/p>\n
If we import another lob we will see that the space is not reused:<\/p>\n
lob_test=# \\lo_import '\/tmp\/randoms';\r\nlo_import 16520\r\nlob_test=# select pg_relation_size('pg_largeobject');\r\n pg_relation_size\r\n------------------\r\n          4235264\r\n(1 row)<\/pre>\n
Flagging the tuples as reusable is the vacuum’s job:<\/p>\n
lob_test=# vacuum pg_largeobject;\r\nVACUUM\r\n\r\nlob_test=# select pg_relation_size('pg_largeobject');\r\n pg_relation_size\r\n------------------\r\n          4235264\r\n(1 row)<\/pre>\n
The normal vacuum does not release the empty space, but it can be reused now:<\/p>\n
lob_test=# select generate_series as pageno,\r\n (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))\r\n where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,12);\r\n pageno | count\r\n--------+-------\r\n      0 |   107\r\n      1 |   107\r\n      2 |   107\r\n      3 |   107\r\n      4 |    84\r\n      5 |     0\r\n      6 |     0\r\n      7 |     0\r\n      8 |     0\r\n      9 |     0\r\n     10 |     0\r\n     11 |     0\r\n     12 |     0\r\n\r\nlob_test=# \\lo_import '\/tmp\/randoms';\r\nlo_import 16521\r\nlob_test=#\r\n\r\nlob_test=#  select pg_relation_size('pg_largeobject');\r\n pg_relation_size\r\n------------------\r\n          4235264\r\n(1 row)\r\n\r\n-- same size as before!\r\n\r\nlob_test=#  select generate_series as pageno, \r\n(select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series)) \r\n where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,12);\r\n pageno | count\r\n--------+-------\r\n      0 |   107\r\n      1 |   107\r\n      2 |   107\r\n      3 |   107\r\n      4 |    84\r\n      5 |     3\r\n      6 |     3\r\n      7 |     3\r\n      8 |     3\r\n      9 |     3\r\n     10 |     3\r\n     11 |     3\r\n     12 |     3<\/pre>\n
If we unlink the lob again and we do a vacuum full, the empty space is released:<\/p>\n
lob_test=# \\lo_unlink 16521\r\nlo_unlink 16521\r\nlob_test=#  select pg_relation_size('pg_largeobject');\r\n pg_relation_size\r\n------------------\r\n          4235264\r\n(1 row)\r\n\r\nlob_test=# vacuum full pg_largeobject;\r\nVACUUM\r\nlob_test=#  select pg_relation_size('pg_largeobject');\r\n pg_relation_size\r\n------------------\r\n          2842624\r\n(1 row)<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
In my previous post I showed how large objects use space inside the table pg_largeobject when inserted. Let’s see something more: The table had 2 large objects (for a total of 1024 records): lob_test=# select pg_relation_size(‘pg_largeobject’); pg_relation_size —————— 1441792 (1 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[302,132],"tags":[307,306,305,303],"class_list":["post-1622","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-triblog","tag-blob","tag-large-object","tag-lob","tag-postgresql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1622","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1622"}],"version-history":[{"count":4,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1622\/revisions"}],"predecessor-version":[{"id":1627,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1622\/revisions\/1627"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1622"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1622"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1622"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}