8

Imagine I have a table in Postgres 13 like this:

CREATE TABLE public.people (
    id integer PRIMARY KEY,
    full_name character varying(255),
    bio text
);

I then insert a row with enough characters for the bio to be written to a TOAST table (4000 random bytes that should compress to > 2Kb):

# insert into people values (1, 'joe toast', (SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer) FROM generate_series(1,4000)), '')));
INSERT 0 1

Then insert a row with enough characters for the bio fit inline (3000 repeated bytes that should compress to < 2Kb):

# insert into people values (2, 'joe compressed', (SELECT array_to_string(ARRAY(SELECT chr(65) FROM generate_series(1,3000)), '')));
INSERT 0 1

Finally insert a row with only a few characters in the bio so it'll store inline (10 repeated bytes):

# insert into people values (3, 'joe inline', 'aaaaaaaaaa');
INSERT 0 1

Is there any way for me to detect the storage strategy for the bio in each tuple? Can I report on the percentage of rows that are inline or in TOAST ("22% of tuples store the bio inline, 78% in TOAST")?

A related question: is there anyway for me to know the number of bytes on disk for the tuple broken down by inline, inline compressed, and TOAST storage?

context: I'm working with a partitioned table that has more than a billion rows in aggregate, and I'm interested in knowing how often a particular column is stored inline vs in TOAST.

Research

I can get the size on disk for each bio, in one case it's clearly the inline-compressed size:

# select id, full_name, pg_column_size(bio) from people order by id;
 id |   full_name    | pg_column_size 
----+----------------+----------------
  1 | joe toast      |           4000
  2 | joe compressed |             44
  3 | joe inline     |             11
(3 rows)

Comparing that size to the size of the uncompressed data tells us something about the compression, but can it tell us anything about the TOAST state?

# select id, full_name, pg_column_size(bio), length(bio) from people order by id;
 id |   full_name    | pg_column_size | length 
----+----------------+----------------+--------
  1 | joe toast      |           4000 |   4000
  2 | joe compressed |             44 |   3000
  3 | joe inline     |             11 |     10

I can manually check there's some rows in the TOAST table:

# select relname from pg_class where oid = (select reltoastrelid from pg_class where relname='people');
    relname     
----------------
 pg_toast_20138

# select chunk_id, sum(length(chunk_data)) from pg_toast.pg_toast_20138 group by chunk_id;
 chunk_id | sum  
----------+------
    20149 | 4000

Is the following true in the general case?

# select id, full_name, pg_column_size(bio), length(bio),
case
  when pg_column_size(bio) < length(bio) then 'inline-compressed'
  when pg_column_size(bio) = length(bio) then 'toast'
  else 
    'inline'
end as storage_strategy
from people order by id;

 id |   full_name    | pg_column_size | length | storage_strategy  
----+----------------+----------------+--------+-------------------
  1 | joe toast      |           4000 |   4000 | toast
  2 | joe compressed |             44 |   3000 | inline-compressed
  3 | joe inline     |             11 |     10 | inline

2 Answers 2

6
+100

About the approach

  • It works with Little Endian byte order. Will have to make it work for Big Endian at some point (tell me if your system is Big Endian)
  • out_of_line means data is stored in TOAST
  • bytes_on_disk and uncompressed_bytes may include some metadata length (either 1 or 4 bytes), need to polish it out some day.
  • It uses inner join people, if you want to see rows that aren't visible (e.g. deleted but not yet vacuumed), use left join people
+--+--------------+------------------+----------+-----------+-------------+
|id|full_name     |uncompressed_bytes|compressed|out_of_line|bytes_on_disk|
+--+--------------+------------------+----------+-----------+-------------+
|1 |joe toast     |4004              |false     |true       |4000         |
|2 |joe compressed|3000              |true      |false      |44           |
|3 |joe inline    |10                |false     |false      |11           |

Implementation

First turn on pageinspect inspection and create functions to get the information from column metadata:

create extension pageinspect;

create or replace function is_toasted(datum_header bytea) returns bool as $$ begin
  return get_byte(datum_header, 0) = 1;
end; $$ language plpgsql;

create or replace function is_1b_meta(datum_header bytea) returns bool as $$ begin
  return not is_toasted(datum_header) and get_byte(datum_header, 0) & 1 > 0;
end; $$ LANGUAGE plpgsql;

create or replace function is_compressed(datum_header bytea) returns bool as $$ begin
  if(is_1b_meta(datum_header)) then
    return false;
  elsif(not is_toasted(datum_header)) then
    return get_byte(datum_header, 0) & 2 > 0;
  else
    return bytes_on_disk(datum_header)+4 != toasted_original_len(datum_header);
  end if;
end; $$ LANGUAGE plpgsql;

create or replace function meta_len(datum_header bytea) returns int as $$begin
  if is_1b_meta(datum_header) then return 1;
  else                        return 4;
  end if;
end;$$ language plpgsql;

create or replace function bytes_on_disk(datum_header bytea) returns int language plpgsql as $$begin
  if(is_1b_meta(datum_header)) then
    return get_byte(datum_header, 0) >> 1;
  elsif(not is_toasted(datum_header)) then
    return (get_byte(datum_header, 0) >> 2)
         | (get_byte(datum_header, 1) << 6)
         | (get_byte(datum_header, 2) << 14)
         | (get_byte(datum_header, 3) << 22);
  else
    return get_byte(datum_header, 6)
         | (get_byte(datum_header, 7) << 8)
         | (get_byte(datum_header, 8) << 16)
         | (get_byte(datum_header, 9) << 24);
  end if;
end;$$;

create or replace function toasted_original_len(datum_header bytea) returns integer language plpgsql as $$ begin
  if(not is_toasted(datum_header)) then
    return get_byte(datum_header, 0) >> 1;--not needed anymore
  else
    return get_byte(datum_header, 2)
         | (get_byte(datum_header, 3) << 8)
         | (get_byte(datum_header, 4) << 16)
         | (get_byte(datum_header, 5) << 24);
  end if;
end;$$;

create or replace function meta_bits(datum_header bytea) returns bit as $$
declare
  len int;
  i int;
  res bit varying(32);
begin
  i = 0;
  res = '';
  len = meta_len(datum_header);
  while i < len loop
    res = res || get_byte(datum_header, i)::bit(8);
    i = i+1;
  end loop;
  return res;
end; $$ language plpgsql;

Now you can select some column ([3] means column #3), take binary data and parse the header:

with bits as(
  select t_ctid as ctid,
         (tuple_data_split('people'::regclass, t_data, t_infomask, t_infomask2, t_bits))[3] as bits
  from generate_series(0, (select max((ctid::text::point)[0]::int) from people)) as page,
  lateral heap_page_items(get_raw_page('people', page))
)
select p.id, p.full_name,
       case when is_toasted(bits) then toasted_original_len(bits)
            else                       length(p.bio)
       end as uncompressed_bytes,
       --meta_bits(bits),
       is_compressed(bits) compressed, is_toasted(bits) out_of_line,
       bytes_on_disk(bits)
from bits
inner join people p on p.ctid=bits.ctid;

Inside Postgres

This information is determined from what Postgres stores internally. There are 3 options for varlena (fields of variable length) metadata (code, docs, presentation):

  • 1 byte. The data itself is inlined, 126 bytes max. Never compressed.
  • 4 bytes. The data is inlined, could be either compressed or not.
  • 18 bytes (1st byte has only 1 bit set) with the data stored in TOAST. Could be either compressed or not.
4
  • It's wild how much code is required, but this answers my questions. Cheers! I'm surprised the TOAST data isn't stored compressed, does it only compress above a certain size? Jan 4, 2022 at 22:50
  • The byte columns in your table almost match those in my "is the following true in the general case" question. Is determining the storage strategy solely by comparing pg_column_size(bio) and length(bio) a viable alternative (although I acknowledge examining the tuple headers is certainly the Most Correct approach). Jan 4, 2022 at 23:00
  • 1
    To get compression in TOAST you need to have compressible data and after compression there should be >2KB of it. In your examples you had either not compressible data (random) or too compressible so that it ended up tiny (same letter used many times). Something less extreme (randomization of small set of letters) should work. Jan 5, 2022 at 8:29
  • 1
    As for pg_column_size() - it does work, but not for TOAST. So I had to figure out how to parse out the info from the header. And since I already figured it out - I left it in bytes_on_disk() even for not TOASTed data. But you can simplify it and use header only for TOAST and fall back to pg_column_size() for the rest. Jan 5, 2022 at 8:33
1

In the comments of the accepted answer I asked if TOAST data can also be compressed. Following guidance from Stanislav, I added another row that is large enough to require TOAST, but also can be compressed:

insert into people values (4, 'joe toast compressed', (SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 5)) :: integer) FROM generate_series(1,50000)), '')));

Now the query in the accepted answer shows that TOAST data is also stored compressed when it'll save space:

# with bits as(                                                                                                                                                        
  select t_ctid as ctid,
         (tuple_data_split('people'::regclass, t_data, t_infomask, t_infomask2, t_bits))[3] as bits
  from generate_series(0, (select max((ctid::text::point)[0]::int) from people)) as page,
  lateral heap_page_items(get_raw_page('people', page))
)
select p.id, p.full_name,
        case when is_toasted(bits) then toasted_original_len(bits)
            else                       length(p.bio)
       end as uncompressed_bytes,
       --meta_bits(bits), 
       is_compressed(bits) compressed, is_toasted(bits) out_of_line,
       bytes_on_disk(bits)
from bits
inner join people p on p.ctid=bits.ctid;
 id |      full_name       | uncompressed_bytes | compressed | out_of_line | bytes_on_disk 
----+----------------------+--------------------+------------+-------------+---------------
  1 | joe toast            |               4004 | f          | t           |          4000
  2 | joe compressed       |               3000 | t          | f           |            44
  3 | joe inline           |                 10 | f          | f           |            11
  4 | joe toast compressed |              50004 | t          | t           |         23647
(4 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.