4 ms·

What do you gain from that other than soothing your OCD when executing \d t (?)

fabian2k 4y ago
There is some small benefit to playing column tetris if you have columns with different sizes that waste space due to padding. I'm not convinced this would be worth the complexity of this feature, but in some cases reordering columns might have measurable benefits in reducing the size of the data.
- pbz 4y ago
  I'm not talking about the physical layout of the data, just a thin UI layer that the DB tool could use. Maybe we could have two modes: physical vs UI ordering?
  - andruby 4y ago
    As mentioned by another comment: you can use views for that.
    - pbz 4y ago
      Views are not a good solution for this. The point is to be able see a specific order in 3rd party apps and when writing quick add-hoc queries (select *). Writing views for every table would just pollute the db.
iruoy 4y ago
I like to have my `*_id` columns at the front, and `created_at` etc. at the back. And in the middle all the fields by descending importance. Just a personal habit so I can quickly lookup data in a table.
- pbz 4y ago
  Exactly... You gain so much speed by having everything in the right order.
  - sophacles 4y ago
    This seems pretty voodoo to me. Do you have any examples that show it?
    - pbz 4y ago
      I'm not sure how I could show if something is easier/harder to work with. Speed in this context refers to human processing speed, not database speed.
- ggregoire 4y ago
  I'm totally with you on this, and would love to be able to directly reorder the columns in the tables. 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.
pbz 4y ago
Soothing my OCD is important, but it also helps when working in a team and having a shared ordering style. For example, we have certain types of columns that are always at the bottom. One would look there first. With PG you're forced to scan all the columns every time.
- 9dev 4y ago
  This is why I order anything alphabetically, always. Properties, methods, columns… anything. Saves you the trouble of custom conventions.
  - pbz 4y ago
    The trouble is when you need to add a new column you can only add it at the end, so you'd lose the alphabetical ordering.
MichaelApproved 4y ago
Based on my understanding of DB storage (which is decades old and as I write the comment my explanation seems stupid but it’s what I was taught. Please someone correct me). 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.
- jhgb 4y ago
  > That data is stored in a similar order on disk (is this still true?). 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.
  - masklinn 4y ago
    1. That’s not exactly true, AFAIK all RDBMS return columns in table order when order is unspecified (`*`), and while they could reorder on retrieval 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)

There is some small benefit to playing column tetris if you have columns with different sizes that waste space due to padding. I'm not convinced this would be worth the complexity of this feature, but in some cases reordering columns might have measurable benefits in reducing the size of the data.

pbz 4y ago
I'm not talking about the physical layout of the data, just a thin UI layer that the DB tool could use. Maybe we could have two modes: physical vs UI ordering?
- andruby 4y ago
  As mentioned by another comment: you can use views for that.
  - pbz 4y ago
    Views are not a good solution for this. The point is to be able see a specific order in 3rd party apps and when writing quick add-hoc queries (select *). Writing views for every table would just pollute the db.

iruoy 4y ago

I like to have my `*_id` columns at the front, and `created_at` etc. at the back. And in the middle all the fields by descending importance. Just a personal habit so I can quickly lookup data in a table.

pbz 4y ago
Exactly... You gain so much speed by having everything in the right order.
- sophacles 4y ago
  This seems pretty voodoo to me. Do you have any examples that show it?
  - pbz 4y ago
    I'm not sure how I could show if something is easier/harder to work with. Speed in this context refers to human processing speed, not database speed.
ggregoire 4y ago
I'm totally with you on this, and would love to be able to directly reorder the columns in the tables. 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.

pbz 4y ago

Soothing my OCD is important, but it also helps when working in a team and having a shared ordering style. For example, we have certain types of columns that are always at the bottom. One would look there first. With PG you're forced to scan all the columns every time.

9dev 4y ago
This is why I order anything alphabetically, always. Properties, methods, columns… anything. Saves you the trouble of custom conventions.
- pbz 4y ago
  The trouble is when you need to add a new column you can only add it at the end, so you'd lose the alphabetical ordering.

MichaelApproved 4y ago

Based on my understanding of DB storage (which is decades old and as I write the comment my explanation seems stupid but it’s what I was taught. Please someone correct me). 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.

jhgb 4y ago
> That data is stored in a similar order on disk (is this still true?). 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.
- masklinn 4y ago
  1. That’s not exactly true, AFAIK all RDBMS return columns in table order when order is unspecified (`*`), and while they could reorder on retrieval 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)