In-Place Updates with PostgreSQL
We have a table with relatively large rows (10-40kb), and we need to update one integer column in this table quite often.
As far as I know, UPDATE
in PostgreSQL it is a transaction DELETE+INSERT
(due to MVCC).
This means PostgreSQL will delete and re-insert the entire row, even if I only want to update one integer (no extra space needed).
I would like to know if it is possible to perform an UPDATE
in-place operation without deleting and reinserting rows?
source to share
You only need to copy the fields stored in the string. For fields stored off-line in TOAST tables, only the reference to the TOAST record is copied.
Whether the field is stored off-line depends on the size of the value in the field and the data type of the field.
If the tuples are large but only have a few fields - for example
some_id integer,
frequently_updated integer,
charblob text
then nothing changes because updates frequently_updated
usually won't overwrite the data in charblob
, at least if it's big enough to be worth taking care of.
OTOH, if you have a table with a lot of fields, you will be rewriting a lot more with each update.
HOT will help you to a limited extent because a HOT update can only happen when the updated column is not part of the index and there is enough free space on one database page. For wide swaths, you won't be putting many copies per page even with TOAST, so HOT will have limited benefit.
It is possible to separate such fields from individual tables if they are indeed updated frequently, but there are large rows in the rest of the table that do not change much.
source to share