Comment by MichaelApproved | Hacker News Reader

Comment by MichaelApproved | Hacker News Reader

Parent Full thread

MichaelApproved

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

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.

MVCC could use delta records instead. Doesn't Firebird do that?

> 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.

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.

Reply on news.ycombinator.com