3 ms·

What about UPDATE performance? Your article and the parent one are explaining performance improvements related to INSERT and SELECT but what about the improved

What about UPDATE performance? Your article and the parent one are explaining performance improvements related to INSERT and SELECT but what about the improved performance for UPDATE? I wrote a long comment[0] on yesterday’s PostgreSQL post related to column order optimization with regards to UPDATE. My information was 20+ years old and I figured it was horribly outdated but these articles make me think it could still be true. The TLDR is you want to put variable length columns at the end because it makes UPDATE more efficient. My theory was it’ll be less likely the DB would need to move data around when updating the variable column contents. Less data being moved = improved performance. Seeing these articles means I was right with regards to improved performance of INSERT/SELECT but I wonder if I’m right about improved UPDATE performance. Anyone know? [0] https://news.ycombinator.com/item?id=32055596 https://news.ycombinator.com/item?id=32055596

singron 4y ago
An update in postgres will always copy the tuple for MVCC, so it can't take advantage of an optimization like this to modify it in place.
- jhgb 4y ago
  MVCC could use delta records instead. Doesn't Firebird do that?
  - CodeWriter23 4y ago
    > MVCC could use delta records instead. Every design choice is trading on thing for another. In your proposed case, you get storage savings in exchange for complex data structure design. And if the performance is a wash or not depends on how many columns and the types of the columns. Probably better to understand the limitations of the tools you're using. If you can live in those boundaries, that's awesome. If you can't but can find a tool with different constraints, also awesome. If neither, your choice becomes understanding the limitations of your tools or infinite recursive optimization.
  - citrin_ru 4y ago
    Surprised to see Firebird being mentioned on HN. Worked with Firebird in 2000s (for a short time) and it left a good impression. How come almost no-one uses this DB anymore? At that time it was comparable to Postgres but looks like FB struggled to attract developers and users and without them it started to lag behind.

singron 4y ago

An update in postgres will always copy the tuple for MVCC, so it can't take advantage of an optimization like this to modify it in place.

jhgb 4y ago
MVCC could use delta records instead. Doesn't Firebird do that?
- CodeWriter23 4y ago
  > MVCC could use delta records instead. Every design choice is trading on thing for another. In your proposed case, you get storage savings in exchange for complex data structure design. And if the performance is a wash or not depends on how many columns and the types of the columns. Probably better to understand the limitations of the tools you're using. If you can live in those boundaries, that's awesome. If you can't but can find a tool with different constraints, also awesome. If neither, your choice becomes understanding the limitations of your tools or infinite recursive optimization.
- citrin_ru 4y ago
  Surprised to see Firebird being mentioned on HN. Worked with Firebird in 2000s (for a short time) and it left a good impression. How come almost no-one uses this DB anymore? At that time it was comparable to Postgres but looks like FB struggled to attract developers and users and without them it started to lag behind.