Backup data in update operations

Question

Hibernate generates UPDATE that include all columns, regardless of whether I change the value in these columns, for example:

 tx.begin(); Item i = em.find(Item.class, 12345); i.setA("a-value"); tx.commit(); 

displays this UPDATE :

 update Item set A = $1, B = $2, C = $3, D = $4 where id = $5 

therefore, columns B, C, D are updated, but I did not change them.

Say, items are updated frequently, and all columns are indexed. The question is: does it make sense to optimize the Hibernate part for something like this:

 tx.begin(); em.createQuery("update Item i set ia = :a where i.id = :id") .setParameter("a", "a-value") .setParameter("id", 12345) .executeUpdate(); tx.commit(); 

What confuses me more is that the EXPLAIN plans for the “non-optimized” and “optimized” versions of the request are identical!

Answer 1

Thanks to PostgreSQL MVCC , UPDATE is almost like DELETE plus INSERT - with a notable exception to toasts. Cm:

• Does Postgres write the entire line when updating?

To be precise, the "deleted" line is simply invisible to any transaction, starting from the moment of deletion, and cleared later. Thus, on the database side, including index manipulation, there is no difference between the two operations. (Exceptions apply, keep reading.) This slightly increases network traffic (depending on your data) and requires a bit of parsing.

I studied HOT updates a few more after entering @araqnid and did some tests. Column updates that do not change the value do not matter with respect to HOT updates. My answer holds on. See below for more details.

This also applies to roasted attributes, as they are also not affected if the values ​​are not changed .

However , if you use column triggers (introduced with pg 9.0), this can have unwanted side effects!

I am quoting a trigger guide :

... a command, such as UPDATE ... SET x = x ... , triggers the trigger column x , although the value of the column has not changed .

My bold accent.

Abstraction levels for convenience. They are useful for developers without illiterate SQL or if the application should be portable between different DBMSs. On the other hand, they can increase the performance of the ball and introduce additional points of failure. I avoid them where possible.

Regarding HOT updates (Heap-only tuple)

Heap-only kits were introduced with Postgres 8.3 with important improvements 8.3.4 and 8.4.9 .
Release notes for Postgres 8.3:

UPDATE and DELETE leave dead tuples behind since INSERT s failed. VACUUM used to be able to free up the space occupied by dead tuples. WITH HOT dead tuples can be automatically restored during INSERT or UPDATE if no changes are made to the indexed columns . This allows for more stable operation. In addition, HOT avoids adding duplicate index entries.

The emphasis is mine. And “no change” includes cases where columns are updated with the same value as theirs. I actually tested that now, as I was not sure.

Toasted columns also do not stand in the way of HOT updates. The updated HOT tuple is tied to the same immutable tuple (s) in the fork relation toast. HOT updates even work with fried values ​​in the target list (actually changed or not). If the fried values ​​change, this entails a fork fork entry, obviously. I also tested all this.

You do not need to talk about it. See for yourself, Postgres provides a couple of features for checking statistics . Run UPDATE with and without all columns and check whether it matters.

 -- Number of rows HOT-updated in table: SELECT pg_stat_get_tuples_hot_updated('table_name'::regclass::oid) -- Number of rows HOT-updated in table, in the current transaction: SELECT pg_stat_get_xact_tuples_hot_updated('table_name'::regclass::oid) 

Or use pgAdmin . Select a table and check the "Statistics" tab in the main window.

Remember that HOT-updates are possible only when there is a place for a new version of the tuple on the same page of the main relationship fork. One easy way to get this condition checked is with a small table containing only a few rows. The page size is usually 8k, so there should be free space on the page.

Answer 2

You can use hibernate @Entity annotation:

 @org.hibernate.annotations.Entity(dynamicUpdate = true) public class Item 

This will only update the changed fields.