Re: Does frequent update of a row affects performance

From: Alvaro Aguayo Garcia-Rada <aaguayo(at)opensysperu(dot)com>
To: pgsql-general(at)postgresql(dot)org, Rakesh Kumar <rakeshkumar464a3(at)gmail(dot)com>
Subject: Re: Does frequent update of a row affects performance
Date: 2016-04-26 13:10:30
Message-ID: eird44eb2kr0l0u5m82k0mn6.1461676050093@email.android.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

As far as I know, postgres will update the index with each DML statement, so the scenario you present should not be a problem by itself. However, having a table updated that frequently can be a problem if the table grows too much and is not well indexed.

If the table is small, you can consider an unlogged table, which will pefform faster.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

---- Rakesh Kumar wrote ----

Pardon me if this has been discussed before.

I believe that PG back-end does not version index rows the way it does the data rows. Assume that the app updates a row frequently (several times in a second). For each update, PG will create a new version.  However I believe the primary key index pointing to that row will point to only the first row and the back end has to work its way traversing the list until it finds the appropriate row matching the xmin/xmax.  Does frequent vaccum take care of this by removing the dead rows and hence reducing the commute.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Johann Spies 2016-04-26 13:25:53 9.5: tsvector problem
Previous Message Rakesh Kumar 2016-04-26 12:55:16 Does frequent update of a row affects performance