Cost of updating one of several toasted columns

Question

I have a table in Postgresql 12 which has --- among other columns --- two columns a and b of type bytea which are most of the time TOASTed. STORAGE is set to EXTENDED for both of them.

Assuming all other columns could be stored in the non-toasted table, would an update to column b require writing column a again? Would Postgres store the values of column a and b of one row in one row of the TOAST table or in two or does it depend? And then on what?

You can assume the values in a and b occupy usually multiple kilobytes, up to several hundred kilobytes.

Answer 1

An experiment says more than a thousand theoretical considerations:

/* helper function to create large byteas */
CREATE OR REPLACE FUNCTION noise(size integer) RETURNS bytea
   LANGUAGE plpgsql AS
$$DECLARE
   b bytea := '';
   i integer;
BEGIN
   FOR i IN 0..size/16 LOOP
      b := b || ('\x' || md5(random()::text))::bytea;
   END LOOP;
   RETURN b;
END;$$;

CREATE TABLE toastme (
   id integer PRIMARY KEY,
   a bytea,
   b bytea
);

INSERT INTO toastme VALUES (
   1,
   noise(2048),
   noise(2048)
);

Let's check the physical location of the new row in the main table:

SELECT ctid
FROM toastme
WHERE id = 1;

 ctid  
-------
 (0,1)
(1 row)

Now let's have a look at the toast table, along with the physical location of the rows:

SELECT ctid
FROM toastme
WHERE id = 1;

 ctid  
-------
 (0,2)

SELECT reltoastrelid::regclass AS toast_table
FROM pg_class
WHERE relname = 'toastme' \gset

SELECT ctid,
       chunk_id,
       chunk_seq,
       substring(chunk_data FOR 10)
FROM :toast_table;

 ctid  | chunk_id | chunk_seq |       substring        
-------+----------+-----------+------------------------
 (0,1) |    57174 |         0 | \x0f9eae178bcda3dbf745
 (0,2) |    57174 |         1 | \xecbe99e83b8416d4f31c
 (0,3) |    57175 |         0 | \x4b3b9425742c299c7d20
 (0,4) |    57175 |         1 | \x43d1b1782ad3e0c1bf4c
(4 rows)

There are two chunk_ids for the two table columns, each of which consists of two “slices” of TOAST.

Let's update one of the toasted columns:

UPDATE toastme
SET a = noise(2048)
WHERE id = 1;

Now let's check again:

SELECT ctid,
       chunk_id,
       chunk_seq,
       substring(chunk_data FOR 10)
FROM :toast_table;

 ctid  | chunk_id | chunk_seq |       substring        
-------+----------+-----------+------------------------
 (0,3) |    57175 |         0 | \x4b3b9425742c299c7d20
 (0,4) |    57175 |         1 | \x43d1b1782ad3e0c1bf4c
 (0,5) |    57176 |         0 | \xc780f3b8e2a5e4976f4a
 (0,6) |    57176 |         1 | \x5702616863dfbc54829b
(4 rows)

What do we see:

• When there is an update of the table row — even if only a toasted value is modified — a new row version is written.

• Only the toasted values that are modified are changed, the unmodified toasted values stay the same.

Answer 2

The contents of "a" will not be written again. The TOAST pointer for "a" will get copied from the old row into the new row of the main table, but the contents of the toast table for "a" do not get changed or copied.

Answer 3

Assuming all other columns could be stored in the non-toasted table, would an update to column b require writing column a again?

No. According to the documentation (in Out-of-Line, On-Disk TOAST Storage):

During an UPDATE operation, values of unchanged fields are normally preserved as-is

Would Postgres store the values of column a and b of one row in one row of the TOAST table or in two or does it depend? And then on what?

The structure of TOAST tables is:

   Column   |  Type   
------------+---------
 chunk_id   | oid
 chunk_seq  | integer
 chunk_data | bytea

Each row in the TOAST table holds one chunk of one field only. In this row-based segmentation, it would be pointless to rewrite the rows related to a field whose value hasn't changed. This is different from the heap storage where consecutive non-TOASTed columns are stored consecutively on disk, so when one column changes, the new version of the row must be written as a whole.