TOASTing strategies in PostgreSQL

In Oracle a single row can span more than one block, which is called row chaining. As PostgreSQL does not allow a row to span multiple blocks, there needs to be a way to work around that. The default block size in PostgreSQL is 8kB (same as in Oracle), so something needs to happen when a row comes in which is larger than that. Actually the limit is less than 8kB, but this will be the topic of a follow up post.

What PostgreSQL will do transparently in the background if a tuple/row is too large, is called TOASTing. In short, PostgreSQL will compress, and later on maybe move the row out of the main table. Before we take a look at how that happens, we need to look at the TOASTing strategies. The strategy for TOSTing is defined on the data type, it is not a property of the row/tuple. You can ask pg_attribute for the strategies currently defined for the columns currently available in the system (this is from a fresh PostgreSQL 16devel build):

postgres=# select distinct attstorage from pg_attribute ;
 attstorage 
------------
 m
 p
 x
(3 rows)

The meaning of the characters is:

• m = Main: This means no compression, no out of line storage. This is for data types which are not TOASTable at all.
• p = Plain: Compression, but no out of line storage.
• x = Extended: Compression and out of line storage.

We can easily verify this by creating a small table and ask PostgreSQL afterwards for the TOASTing strategies of the columns we’ve defined:

postgres=# create table t ( a int, b text, c numeric );
CREATE TABLE
postgres=# select attname, atttypid::regtype,
                  case attstorage when 'p' then 'plain'
                                  when 'e' then 'external'
                                  when 'm' then 'main'
                                  when 'x' then 'extended'
                  end AS strategy
             from pg_attribute
            where attrelid = 't'::regclass and attnum > 0;
 attname | atttypid | strategy 
---------+----------+----------
 a       | integer  | plain
 b       | text     | extended
 c       | numeric  | main
(3 rows)

This means an integer column can be compressed, a text column can be both compressed and moved out of line, and a numeric column can neither be compressed nor moved out of line. If you want to know which strategy is defined for which (data) type, you can directly ask pg_type for the strategy of the type:

postgres=# select typname, typstorage 
             from pg_type where typname in ('int4','numeric','text');
 typname | typstorage 
---------+------------
 int4    | p
 text    | x
 numeric | m
(3 rows)

What happens when you create your table is, that the strategy for the data types for your columns is copied from the underlying types, which are defined in pg_type.

For now we’ve seen three TOASTing strategies, but there is one more, which is called “external”. This one is useful for data, for which you already know, that compression does not make any sense. Examples are PDFs or PNGs or MP3s. Trying to compress those kinds of data would only waste CPU cycles, and you can actually avoid that by setting the strategy for those columns to extended:

postgres=# create table tt ( a bytea );
CREATE TABLE
postgres=# select attname, atttypid::regtype,
                  case attstorage when 'p' then 'plain'
                                  when 'e' then 'external'
                                  when 'm' then 'main'
                                  when 'x' then 'extended'
                  end AS strategy
             from pg_attribute
            where attrelid = 'tt'::regclass and attnum > 0;
 attname | atttypid | strategy 
---------+----------+----------
 a       | bytea    | extended
(1 row)
postgres=# alter table tt alter column a set storage extended;
ALTER TABLE
postgres=# select attname, atttypid::regtype,
                  case attstorage when 'p' then 'plain'
                                  when 'e' then 'external'
                                  when 'm' then 'main'
                                  when 'x' then 'extended'
                  end AS strategy
             from pg_attribute
            where attrelid = 'tt'::regclass and attnum > 0;
 attname | atttypid | strategy 
---------+----------+----------
 a       | bytea    | extended
(1 row)

The complete list of strategies if therefore:

• m = Main: This means no compression, no out of line storage. This is for data types which are not TOASTable at all.
• p = Plain: Compression, but no out of line storage.
• x = Extended: Compression and out of line storage.
• e = External: No compression, but out of line storage.

Now, that we know the strategies, we can have a look at TOASTing in more detail in the next post.