5

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:

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

Finally, I update the row without changing the TOAST column:

# update people set full_name='jane user' where id=1;
UPDATE 1

Does the UPDATE change any rows (or require any writes at all) in the associated TOAST table?

Context: I'm working with some database tables that have thousands of transactions per second, and the observed write load on the server is quite high. I'm wondering if UPDATEs to tuples with large values in TOAST but the TOAST value itself mostly not changing is contributing to the write load and is worth optimising.

7

There is a clear answer in the manual:

During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.

That's one of the main benefits of an UPDATE over DELETE + INSERT (while either writes a new row version).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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