patternsqlMinor
Are TOAST rows written for UPDATEs not changing the TOASTable column?
Viewed 0 times
rowsthewrittencolumnaretoastablefortoastchangingnot
Problem
Imagine I have a table in Postgres 13 like this:
I then insert a row with enough characters for the bio to be written to a TOAST table:
Finally, I update the row without changing the TOAST column:
Does the
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
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
preserved as-is; so an
incurs no TOAST costs if none of the out-of-line values change.
That's one of the main benefits of an
But there is some fine print to "unchanged". See:
During an
UPDATE operation, values of unchanged fields are normallypreserved as-is; so an
UPDATE of a row with out-of-line valuesincurs 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.