4 ms·

> a lot of the pain the Uber is feeling has to do with the way Postgres has implemented MVCC. Because they write new records all the time, they need to update a

by neilc 10y ago

> a lot of the pain the Uber is feeling has to do with the way Postgres has implemented MVCC. Because they write new records all the time, they need to update all indexes, all the time This is not true in general: there are circumstances where Postgres can skip updating indexes when a tuple is updated, assuming the update only changes values in non-indexed columns. See: http://pgsql.tapoueh.org/site/html/misc/hot.html http://pgsql.tapoueh.org/site/html/misc/hot.html https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;h=f12cad44e56c363e62fa617497bbedfe1ba8c1fe;hb=HEAD https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...

nl 10y ago
The Uber article referenced makes it extremely clear they are working on indexed columns - the examples are all about updating the secondary indexed.
- Uberphallus 10y ago
  I'm curious on the particular usecase that requires slamming indexed columns that often.
  - matthewrudy 10y ago
    Imagine the Uber `Rides` table, indexed on `status` Every time a new order is created the status flows from * unassigned * assigned * passenger on board * completed That should really be an event stream, but if you want to find all `unassigned` orders, an index would be good. We had a similar problem with this kind of table, at much lower volume than uber. But only when there was a two column index `status, pickup time` Or `status, created at`
    - Uberphallus 10y ago
      > That should really be an event stream, but if you want to find all `unassigned` orders, an index would be good. No, an index is not good for that, because it only has 4 values (at most 4 times faster than a full table scan), and it hammers the index on every update, using PostgreSQL or not. Same for the two column index: single column indexes on pickup time and creation time are highly selective, and their values are updated basically never, whereas status has low selectivity and updates are common: combining them you don't significantly improve selects on the two columns, and limit the performance edge on time select where time. Status index only performs well when you select one of the values which is is significantly underrepresented in the column (e.g. all except completed). The most performing approach without doing black magic is to split in columns with separate indexes: Assigned Time, Pickup Time, Completed Time. If you want unassigned ones, you select where Assigned Time IS NULL, same for the other columns. Performance on update for most RDBMS blasts, as index updates are now distributed among several, allowing higher concurrency and overall throughput.
- saurik 10y ago
  No: the example updated an indexed column (which frankly is likely rarer in practice but makes for a simpler example), but the serious write amplification comes because of the other indexes that had to be updated; in their example they change the birth year (so obviously the index has to change!) but then the name index also gets updated due to the new row location. HOT mitigates this issue.

by neilc 10y ago

nl 10y ago

The Uber article referenced makes it extremely clear they are working on indexed columns - the examples are all about updating the secondary indexed.

Uberphallus 10y ago
I'm curious on the particular usecase that requires slamming indexed columns that often.
- matthewrudy 10y ago
  Imagine the Uber `Rides` table, indexed on `status` Every time a new order is created the status flows from * unassigned * assigned * passenger on board * completed That should really be an event stream, but if you want to find all `unassigned` orders, an index would be good. We had a similar problem with this kind of table, at much lower volume than uber. But only when there was a two column index `status, pickup time` Or `status, created at`
  - Uberphallus 10y ago
    > That should really be an event stream, but if you want to find all `unassigned` orders, an index would be good. No, an index is not good for that, because it only has 4 values (at most 4 times faster than a full table scan), and it hammers the index on every update, using PostgreSQL or not. Same for the two column index: single column indexes on pickup time and creation time are highly selective, and their values are updated basically never, whereas status has low selectivity and updates are common: combining them you don't significantly improve selects on the two columns, and limit the performance edge on time select where time. Status index only performs well when you select one of the values which is is significantly underrepresented in the column (e.g. all except completed). The most performing approach without doing black magic is to split in columns with separate indexes: Assigned Time, Pickup Time, Completed Time. If you want unassigned ones, you select where Assigned Time IS NULL, same for the other columns. Performance on update for most RDBMS blasts, as index updates are now distributed among several, allowing higher concurrency and overall throughput.
saurik 10y ago
No: the example updated an indexed column (which frankly is likely rarer in practice but makes for a simpler example), but the serious write amplification comes because of the other indexes that had to be updated; in their example they change the birth year (so obviously the index has to change!) but then the name index also gets updated due to the new row location. HOT mitigates this issue.