Обсуждение: Does frequent update of a row affects performance
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.
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.
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.
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.
On 04/26/2016 05:55 AM, 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. Might want to take a look at: http://www.postgresql.org/docs/9.5/interactive/indexam.html > > -- Adrian Klaver adrian.klaver@aklaver.com