What do you gain from that other than soothing your OCD when executing \d t (?)
A possible workaround is to have a view for every table (which I often read is a good practice anyway) and order the columns as you want in the views.
Image a table with the following:
id (int), title (varchar), created_date (date)
That data is stored in a similar order on disk (is this still true?).
So, if the initial insert has a title of 10 characters, then the date will get placed after those 10 characters on disk:
Int, 10 characters, date
Later, the row is updated with a longer title of 500 characters.
Because it’s longer, you now have to get more space for the title on another part of the disk.
Since there are columns after the title, it’ll either leave dead space where the old 10 character title was or it’ll need to move those other columns too.
I can’t remember which I was taught. I just remember that columns after variable columns get impacted when the data in a preceding variable column changes.
If the title were at the end of the row, the db could expand the data without needing to move other columns over (if there happened to be available space right there).
If you were updating with a shorter value, it could shorten the row by moving the terminating character to the new shorter location, freeing up the space the longer title was using.
Bottom line is you want to keep variable columns which get updated most frequently towards the end of your table structure.
Knowing which column that would be isn’t always clear when creating the table.
Again, this understanding was taught to me over 20 years ago. I’m probably remembering parts of it wrong and DB storage has likely (hopefully) advanced since then.
There should be no requirement for this. Columns in relations are not conceptually ordered, so it shouldn't matter for the things you're doing with the data anyway, and the database should be able to reorder the data in whatever way it likes, since desire to isolate the user from physical data structures was one of the main reasons for the rise of RDBMS.
2. postgres definitely does not, and column tetris is absolutely a thing in the same way struct packing is (with the additional complexity of variable-size columns)
And what PostgreSQL does is of course an implementation detail of PostgreSQL.
My point was that you’re trying to prevent the DB from reordering the data because there’s a performance cost when that happens.
Sure. “Reduce the need” would be a better word than “prevent”.
If I can do a good job organizing the table columns (as described above) it’ll lower the need for the DB to reorder data.
Reduced need to reorder, improves performance.
Most databases use Slotted Pages to organize storage. Pages are fixed size and numbered by their offset within the database file. The page header contains the number of rows, followed by an array of offsets for individual rows within the page. Rows themselves generally are stored at the end of the page filling downwards. The storage engine can move around rows in arbitrary ways to consolidate free space.
Fundamentally there's no connection between SQL schema order and how table storage is organized on disk. For example in a column store there's often no contiguous row stored anywhere, instead there's just separate indexes per column.