Performance drops over time for UPDATE to same row

[jeffreims]

I noticed performance drops when updating a single row many times in a table.

The performance drop seems to increase the more the row is updated and the performance drops for both updating and selecting this row.

This is the performance using SELECT on a row that has been updated many times (>20k times)

postgres=# \timing on
Timing is on.
postgres=# select * from u latest;
                                id                                |               version                | updated_at | scheduled_at | engaged_at | engaged_by | status 
------------------------------------------------------------------+--------------------------------------+------------+--------------+------------+------------+--------
 632a13aa10c12ba91f8806febb1ba998cb154d9d02454e56bf221b2aff27c556 | 37160dd8-6f37-4eb9-a8ff-f613abfcbfeb |          0 |            0 |            |            | 
(1 row)

Time: 245.634 ms

postgres=# select version from latest WHERE id = '632a13aa10c12ba91f8806febb1ba998cb154d9d02454e56bf221b2aff27c556';
               version                
--------------------------------------
 37160dd8-6f37-4eb9-a8ff-f613abfcbfeb
(1 row)

Time: 352.805 ms
postgres=# select version from latest WHERE id = '632a13aa10c12ba91f8806febb1ba998cb154d9d02454e56bf221b2aff27c556';
               version                
--------------------------------------
 37160dd8-6f37-4eb9-a8ff-f613abfcbfeb
(1 row)

Time: 171.439 ms

Now inserting a new row in the same table:

INSERT 0 1
Time: 14.729 ms
postgres=# select * from latest where id = '632a13aa10c12ba91f8806febb1ba998cb154d9d02454e56bf221b2aff27c558';
                                id                                |               version                | updated_at | scheduled_at | engaged_at | engaged_by | status 
------------------------------------------------------------------+--------------------------------------+------------+--------------+------------+------------+--------
 632a13aa10c12ba91f8806febb1ba998cb154d9d02454e56bf221b2aff27c558 | 96643506-f838-43fb-8d01-2306f07db396 |          0 |            0 |            |            | 
(1 row)

Time: 2.291 ms

[kmuthukk]

thx @jeffreims for reporting this issue, and providing the test case as well.

We were able to reproduce the issue, and problem seems specific to a row being updated several times, where we have intent records (from previous mutations to the row) that aren't being garbage collected aggressively. We are working on an optimization to speed up this case, and will keep you posted.

Meanwhile, one question: will it be a common workload pattern in your use case that the same row will undergo thousands of updates within a short span of time? Or was it more symptomatic of the way the test program was written. If in practice, the updates will be spread over many rows and updates to same row will spread out over time, then you should not really see this issue because the garbage collection of older intent records will keep happening periodically.

[jeffreims]

Meanwhile, one question: will it be a common workload pattern in your use case that the same row will undergo thousands of updates within a short span of time? Or was it more symptomatic of the way the test program was written. If in practice, the updates will be spread over many rows and updates to same row will spread out over time, then you should not really see this issue because the garbage collection of older intent records will keep happening periodically.

We normally will not be making that many updates within a short time on a single row.

[kmuthukk]

thx for the additional data point @jeffreims. Glad to hear this won't be a common pattern for you in the near term. Nevertheless, we'll work on optimizing this case.

[ddorian]

This can be fixed by changing how the cache works.
You can keep a per-row cache (instead of block) and "append update in disk" + "update in-memory".
Though the change code-wise is very big, it should provide better performance. Or per-kv in rocksdb.

[kmuthukk]

hi @ddorian -- There is fix for this in the works, and under code review. At a high level it is has to do with more aggressively garbage collecting provisional/intent records for distributed transactions.

Will keep you posted. We plan to move our code review (phabricator tool) also in the open soon. So in future, we would be able to reference/point to the "work-in-progress changes" easily.