7 ms·

Since we have some PG devs here: Can we please have a way to reorder columns? Coming from MySQL, this is one of the first missing features one hits. It's like

by pbz 4y ago

Since we have some PG devs here: Can we please have a way to reorder columns? Coming from MySQL, this is one of the first missing features one hits. It's like moving to a new house where you're told you can never clean or move the furniture without moving to a new house. It leaves the unfair impression that this is a "toy" db. "What other basic features is it missing if it doesn't have this?" Please don't think of it as trivial; first impression are very important.

urthor 4y ago
There's like 5 different features wants I could add like this. A columnar database engine comes to mind (slightly more work involved) and poaching a few Sqllite QOL features. Ultimately Postgres is a community driven product, and people want to work on what they want the work on. I don't get angry that some kind soul hasn't volunteered their Saturday for free for me.
- pbz 4y ago
  Oh, absolutely; I'm just trying to raise the awareness/importance of this missing feature. Most devs that I know IRL that looked at PG had a very similar reaction to mine.
  - deleted 4y ago
    [deleted]
  - mixmastamyk 4y ago
    I'm receptive to the feature. But wouldn't it require locking the full table as it works? Seems like it would hurt availability of the biggest databases that would benefit most. Perhaps this is why it is thought somewhat impractical and therefore low-priority.
    - pbz 4y ago
      Depends on how the PG team decides to implement this feature. If they're OK with a virtual order (for "UI" purposes) while keeping the true order hidden then this would not require table locking (or it would be a very quick lock). If the order reflects the real / physical positioning then yes, it would probably require locking for a full table rebuild.
- DangitBobby 4y ago
  That's not exactly true. You can write your own patch for a feature and there's a good chance they'll decide they don't want it. It's their prerogative, but also slightly upsetting when you know they're blocking features you want. Just observe the bike shedding over a trailing commas patch [1]. 1. https://postgrespro.com/list/thread-id/1853280 https://postgrespro.com/list/thread-id/1853280
  - TheP1000 4y ago
    There are lots of good reasons in that thread why the patch was declined. The patch made things more lenient which makes things less compatible (this whole thread is about ANSI SQL standards and they do matter) and changes what was an error behavior to now silently succeed. +1 to postgres devs for curating patches.
    - DangitBobby 4y ago
      I read the thread and have to disagree with "lots of good reasons". I'm pretty sure there are plenty of things in the postgres dialect that are not cross compatible with any other dialect. I agree with curating patches in general, but it's a double edged sword and inaccurate to say you can just spend your weekend to get something you care about.
SnowHill9902 4y ago
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.
jl6 4y ago
Better to channel your displeasure into advocating for a future SQL standard to include this feature. Other DBs that support this do so via proprietary syntax extensions.
- masklinn 4y ago
  Technically it doesn’t require syntactic extensions, postgres stores the column index as the `attnum` attribute column of the `pg_catalog.pg_attributes` system table. So this could be made to work by hooking into the storage system and rewriting the table and all pointers to the table when `attnum` is updated. Without this rewriting this only works if the table was just created and has no data, and no external metadata referencing the columns themselves e.g. views, fks, indexes, defaults, rules, … An alternative would be to add automatic packing (à la rustc) to postgres, decorrelating the “table position” and the “physical position” of the rows, this would also allow free “table position” reordering. And while it’s by far the most complex option, one of the nice bits with it would be that the system columns could be packed as well. Currently there’s quite a bit of waste because there are 6 system columns (by default), the first 5 are 4 bytes, but the 6th (ctid) is 6 bytes, meaning 2 bytes of padding if your first column is a SERIAL, and 6 if the first column is a BIGSERIAL (or an other double-aligned column).
  - RedShift1 4y ago
    Couldn't an ordering column be added to the pg_attributes that determines in which way the columns are sorted when they are displayed? Standard SQL code can then be used to manipulate the display order of the columns.
    - deleted 4y ago
      [deleted]
  - anarazel 4y ago
    > And while it’s by far the most complex option, one of the nice bits with it would be that the system columns could be packed as well. Currently there’s quite a bit of waste because there are 6 system columns (by default), the first 5 are 4 bytes, but the 6th (ctid) is 6 bytes, meaning 2 bytes of padding if your first column is a SERIAL, and 6 if the first column is a BIGSERIAL (or an other double-aligned column). FWIW, system columns aren't stored as normal columns in tuples. Some of them are implied (e.g. tableoid doesn't need to be stored in each tuple, ctid is inferred from position), others are not stored in the way normal columns are stored (e.g. xmin, xmax).
hans_castorp 4y ago
> It leaves the unfair impression that this is a "toy" db. So you consider Oracle, SQL Server and DB2 also to be "toy" databases?
- pbz 4y ago
  With SQL Server the management tool does give you a way to do this. Yes, it does a table rebuild behind the scenes. The point is that it's easy. Don't have experience with the other two, but MySQL is the most popular so it kinda sets the tone whether we like it or not.
  - hans_castorp 4y ago
    Well, writing a procedure that rebuilds the complete table in Postgres or Oracle is easy as well. I never needed this, but I am sure, there are some sample implementations out there. Rebuilding the entire table doesn't seem feasible for large tables to begin with. Especially with a lot of incoming and outgoing foreign keys. I disagree that MySQL is the most "popular". It might be the "most used" one because of so many web hosting services included it for ages by default.
    - jl6 4y ago
      Tangential, but there are almost certainly more SQLite databases in existence than every other RDBMS put together, probably by 3 or 4 orders of magnitude. It doesn’t support column reordering either.
Tostino 4y ago
Ideally, postgres would play column Tetris behind the scenes and store the columns on disk in the most appropriate way, while allowing the representation to be changed at will.
- pbz 4y ago
  Yeah with maybe an option to manually optimize (that would rebuild the table if needed).
dboreham 4y ago
> Please don't think of it as trivial I've worked with relational databases for 20+ years. This is the very first time I heard of this.
- pbz 4y ago
  I've worked with DBs for 20+ years as well. This is a quality of life type of improvement. If you've worked mainly with DBs that don't make this easy it's hard to know what you're missing. Do a search for column reordering for PG and you'll get a ton of hits.
anarazel 4y ago
The problem is that we hear a lot of different features touted as "the crucial missing one"... Anyway, there's been work on this in the past, which recently has been picked up again. It's not all that trivial to do well.
- pbz 4y ago
  The problem is that we hear a lot of different features touted as "the crucial missing one" -- I would definitely put this into the polish category, but items in that category are also important; especially for those with a MySQL background. which recently has been picked up again -- that's awesome to hear

by pbz 4y ago

urthor 4y ago

There's like 5 different features wants I could add like this. A columnar database engine comes to mind (slightly more work involved) and poaching a few Sqllite QOL features. Ultimately Postgres is a community driven product, and people want to work on what they want the work on. I don't get angry that some kind soul hasn't volunteered their Saturday for free for me.

pbz 4y ago
Oh, absolutely; I'm just trying to raise the awareness/importance of this missing feature. Most devs that I know IRL that looked at PG had a very similar reaction to mine.
- deleted 4y ago
  [deleted]
- mixmastamyk 4y ago
  I'm receptive to the feature. But wouldn't it require locking the full table as it works? Seems like it would hurt availability of the biggest databases that would benefit most. Perhaps this is why it is thought somewhat impractical and therefore low-priority.
  - pbz 4y ago
    Depends on how the PG team decides to implement this feature. If they're OK with a virtual order (for "UI" purposes) while keeping the true order hidden then this would not require table locking (or it would be a very quick lock). If the order reflects the real / physical positioning then yes, it would probably require locking for a full table rebuild.
DangitBobby 4y ago
That's not exactly true. You can write your own patch for a feature and there's a good chance they'll decide they don't want it. It's their prerogative, but also slightly upsetting when you know they're blocking features you want. Just observe the bike shedding over a trailing commas patch [1]. 1. https://postgrespro.com/list/thread-id/1853280 https://postgrespro.com/list/thread-id/1853280
- TheP1000 4y ago
  There are lots of good reasons in that thread why the patch was declined. The patch made things more lenient which makes things less compatible (this whole thread is about ANSI SQL standards and they do matter) and changes what was an error behavior to now silently succeed. +1 to postgres devs for curating patches.
  - DangitBobby 4y ago
    I read the thread and have to disagree with "lots of good reasons". I'm pretty sure there are plenty of things in the postgres dialect that are not cross compatible with any other dialect. I agree with curating patches in general, but it's a double edged sword and inaccurate to say you can just spend your weekend to get something you care about.

SnowHill9902 4y ago

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.

jl6 4y ago

Better to channel your displeasure into advocating for a future SQL standard to include this feature. Other DBs that support this do so via proprietary syntax extensions.

masklinn 4y ago
Technically it doesn’t require syntactic extensions, postgres stores the column index as the `attnum` attribute column of the `pg_catalog.pg_attributes` system table. So this could be made to work by hooking into the storage system and rewriting the table and all pointers to the table when `attnum` is updated. Without this rewriting this only works if the table was just created and has no data, and no external metadata referencing the columns themselves e.g. views, fks, indexes, defaults, rules, … An alternative would be to add automatic packing (à la rustc) to postgres, decorrelating the “table position” and the “physical position” of the rows, this would also allow free “table position” reordering. And while it’s by far the most complex option, one of the nice bits with it would be that the system columns could be packed as well. Currently there’s quite a bit of waste because there are 6 system columns (by default), the first 5 are 4 bytes, but the 6th (ctid) is 6 bytes, meaning 2 bytes of padding if your first column is a SERIAL, and 6 if the first column is a BIGSERIAL (or an other double-aligned column).
- RedShift1 4y ago
  Couldn't an ordering column be added to the pg_attributes that determines in which way the columns are sorted when they are displayed? Standard SQL code can then be used to manipulate the display order of the columns.
  - deleted 4y ago
    [deleted]
- anarazel 4y ago
  > And while it’s by far the most complex option, one of the nice bits with it would be that the system columns could be packed as well. Currently there’s quite a bit of waste because there are 6 system columns (by default), the first 5 are 4 bytes, but the 6th (ctid) is 6 bytes, meaning 2 bytes of padding if your first column is a SERIAL, and 6 if the first column is a BIGSERIAL (or an other double-aligned column). FWIW, system columns aren't stored as normal columns in tuples. Some of them are implied (e.g. tableoid doesn't need to be stored in each tuple, ctid is inferred from position), others are not stored in the way normal columns are stored (e.g. xmin, xmax).

hans_castorp 4y ago

> It leaves the unfair impression that this is a "toy" db. So you consider Oracle, SQL Server and DB2 also to be "toy" databases?

pbz 4y ago
With SQL Server the management tool does give you a way to do this. Yes, it does a table rebuild behind the scenes. The point is that it's easy. Don't have experience with the other two, but MySQL is the most popular so it kinda sets the tone whether we like it or not.
- hans_castorp 4y ago
  Well, writing a procedure that rebuilds the complete table in Postgres or Oracle is easy as well. I never needed this, but I am sure, there are some sample implementations out there. Rebuilding the entire table doesn't seem feasible for large tables to begin with. Especially with a lot of incoming and outgoing foreign keys. I disagree that MySQL is the most "popular". It might be the "most used" one because of so many web hosting services included it for ages by default.
  - jl6 4y ago
    Tangential, but there are almost certainly more SQLite databases in existence than every other RDBMS put together, probably by 3 or 4 orders of magnitude. It doesn’t support column reordering either.

Tostino 4y ago

Ideally, postgres would play column Tetris behind the scenes and store the columns on disk in the most appropriate way, while allowing the representation to be changed at will.

pbz 4y ago
Yeah with maybe an option to manually optimize (that would rebuild the table if needed).

dboreham 4y ago

> Please don't think of it as trivial I've worked with relational databases for 20+ years. This is the very first time I heard of this.

pbz 4y ago
I've worked with DBs for 20+ years as well. This is a quality of life type of improvement. If you've worked mainly with DBs that don't make this easy it's hard to know what you're missing. Do a search for column reordering for PG and you'll get a ton of hits.

anarazel 4y ago

The problem is that we hear a lot of different features touted as "the crucial missing one"... Anyway, there's been work on this in the past, which recently has been picked up again. It's not all that trivial to do well.

pbz 4y ago
The problem is that we hear a lot of different features touted as "the crucial missing one" -- I would definitely put this into the polish category, but items in that category are also important; especially for those with a MySQL background. which recently has been picked up again -- that's awesome to hear