HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Are TOAST rows written for UPDATEs not changing the TOASTable column?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rowsthewrittencolumnaretoastablefortoastchangingnot

Problem

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.

Solution

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).

But there is some fine print to "unchanged". See:

  • Does UPDATE write a new row version for unchanged, TOASTed values?

Context

StackExchange Database Administrators Q#301662, answer score: 8

Revisions (0)

No revisions yet.