4

We have a table with relatively large rows (10-40kb) and we have to update a single integer column in this table quite frequently.

As far as I know, an UPDATE in PostgreSQL is a transaction of DELETE+INSERT (due MVCC).

That's mean PostgreSQL will delete and re-insert the entire row, even if I want to update just a single integer (not requires additional space).

I would like to know if it's possible to perform an UPDATE operation "in place" without deleting and re-inserting rows?

user1613797
  • 1,197
  • 3
  • 18
  • 33
  • 2
    You need a HOT-update: http://pgsql.tapoueh.org/site/html/misc/hot.html – Frank Heikens Aug 04 '15 at 14:45
  • 1
    possible duplicate of [Does Postgres rewrite entire row on update?](http://stackoverflow.com/questions/22702686/does-postgres-rewrite-entire-row-on-update) – Renzo Aug 04 '15 at 17:32
  • Maybe factor out heavily updated columns to a new table? Or first confirm that the current state is a problem - the db system is continously being optimized. – Str. Aug 04 '15 at 19:33
  • @FrankHeikens HOT is only of limited utility for wide rows because you can only do a HOT update where there's space on the same page. – Craig Ringer Aug 05 '15 at 04:22

1 Answers1

1

Only fields stored in-line need to be copied. For fields stored out-of-line in TOAST tables, only the reference to the TOAST entry is copied.

Whether a field is stored out-of-line depends on the size of the value in the field and on the field's data type.

If the tuples are large but only have a few fields - like

some_id integer,
frequently_updated integer,
charblob text

then there's not much point changing anything because updates of frequently_updated won't generally rewrite the data in charblob, at least if it's big enough that it's worth caring.

OTOH, if you have a table with lots of fields you'll be rewriting a lot more with each update.

HOT will only help you to a limited extent because a HOT update can only happen when no updated column(s) are part of an index and there's enough free space on the same database page. For wide rows you won't fit many copies on a page even with TOAST, so HOT will be of limited benefit.

It can be worth separating such fields out into separate tables if they're really frequently updated but the rest of the table has wide rows that don't change much.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778