TIL that #PostgreSQL does not support column ordering, i.e. in MySQL you can choose where to place a new column (after X, at the beginning and so on). That's really sad.
Conversation
1/ Because alignment of columns in the on-disk tuple. Sometimes the "logical" ordering leaves gaps so the table uses more space than needed. Not an issue in most cases, but sometimes it can be (table mixing columns in unfortunate ways).
4
2
I think this is bad in real-life tables more often than people realize. Adding a boolean as column 11, with no other booleans next to it, means taking a full 4 bytes of space for it.
But ... I don't want to have manual column ordering. I want it to be automatic.
1
2
Ideal column ordering for any table is mathematically provable. So the physical columns should just be automatically ordered to maximize efficiency. And VACUUM FULL should have a "reorder" option.
3
4
You’re unable to view this Tweet because this account owner limits who can view their Tweets. Learn more
Also that nothing in Postgres is ready for the concept that logical and physical ordering of columns might be different. So you're talking about a massive patch, and lots of follow-on bugs.
1
This patch existed at some point in the past. I think maybe was working on it. It definitely was a scary patch for bugs since it meant every site needed to be very careful to use the "right" column number and if it used the wrong one it would mostly work.
3
I think the direction to head was to actually use type safety or some equivalent tool to enforce that the two indexes could only be used in the right places as much as possible.
1
Don't forget that dropped columns are already involved in a surprisingly enormous number of bugs, all over the place
Yeah, it turned out that enabling metadata-only dropping of columns back in 6.? still has some unanticipated issues ...
1