4 ms·

People generally expect mature databases to have optimized any low-hanging fruit, and reordering different-alignment data to improve packing is trivial. There's

People generally expect mature databases to have optimized any low-hanging fruit, and reordering different-alignment data to improve packing is trivial. There's also no reason to assume that the order of columns in a table definition has any relationship to the order of columns in a row in physical storage. Essentially, the current behaviour is wasteful and never needed to be.

valenterry 4y ago
Well yeah okay, but that's what I wouldn't call an insufficient abstraction. Since even if the query order would logically matter (in some cases it actually does), it doesn't really tell much about how much it was or wasn't optimized under the hood no? > Essentially, the current behaviour is wasteful and never needed to be It's not so easy. You can't just simply physically re-order columns based on new columns that were added. I mean, doing that causes IO and locks/latency issues, so maybe the user wants to run this on demand only or wants to configure it to run in the background under certain conditions, similar to vacuum. It's not trivial.
- ghusbands 4y ago
  It's all just implementation detail, not show-stoppers. > I wouldn't call an insufficient abstraction Terminology can vary, but the root comment is pointing out that Postgres didn't abstract away column order and has multiple performance penalties from it. It seems reasonable to call that "insufficient abstraction". > You can't just simply physically re-order columns based on new columns that were added. You can. You could use a table lock (as Postgres already does in many column-adding situations), or there are plenty of other ways it could work (definition-versions at the row level, or such). > It's not trivial. Then nothing is. In a hypothetical world in which Postgres had had this optimisation from the start, nobody would be saying "Well, we can't have an add columns feature because we'd need to [X] or [Y]"; there's nothing particularly complicated about it. But, yes, going from the current state of Postgres to this hypothetical world could well be painful and is almost certainly not worth it, but I believe the original point is approximately that this aspect of the Postgres design has imposed performance issues that weren't necessary and are unintuitive. Also, note that Postgres already has features that are in the same area - null bitmaps put the null-ness of columns at the start of the row, and adding a ninth column moves all of the data in a row. It's all just implementation detail, not show-stoppers.
  - anarazel 4y ago
    > Also, note that Postgres already has features that are in the same area - null bitmaps put the null-ness of columns at the start of the row, and adding a ninth column moves all of the data in a row. It's all just implementation detail, not show-stoppers. It doesn't move all the data for existing rows, because every row has an indicator about the number of columns in that specific row. Compare that to adding a column and efficient packing requiring that the entire table be rewritten to fit the new column at its optimal place...
    - ghusbands 4y ago
      In the same vein, you could just have an indicator of the number of columns of each alignment, since the simplest solution is to group columns by alignment and add new ones to the end of each. Perhaps try to fully evaluate the idea rather than just shooting it down at the first perceived hurdle. Repeatedly introducing strawmen like "entire table be rewritten" doesn't really help the discussion; the performance impediments and solutions are largely the same shape as what Postgres already does elsewhere. It's all just feeling a bit like the Windows Terminal team telling Casey that the obvious optimisations won't work or require a PhD to fully evaluate or such. It's not that hard and any systems developer could do it. It probably wouldn't be worth the effort, compared to other things, though.
      - anarazel 4y ago
        > Perhaps try to fully evaluate the idea rather than just shooting it down at the first perceived hurdle. I'm not trying to shoot improvements in this area down? I've spent time reviewing patches in the past, and I plan to do so in the future. Perhaps try to give others a bit more benefit of the doubt. > Repeatedly introducing strawmen like "entire table be rewritten" doesn't really help the discussion; the performance impediments and solutions are largely the same shape as what Postgres already does elsewhere. I don't think it's a strawman. For one there's plenty complaints about those rewrites where we have them, so we try to avoid adding more of them. For another, always packing columns would turn an operation that doesn't currently rewrite into a rewriting operation - which'd wreck havoc for people with existing upgrade scripts etc. > In the same vein, you could just have an indicator of the number of columns of each alignment, since the simplest solution is to group columns by alignment and add new ones to the end of each. The per-row overhead would be too large - we already have problems with that.
anarazel 4y ago
> People generally expect mature databases to have optimized any low-hanging fruit IME that's not true. I've been hacking on postgres, with occasional forays into other databases, for quite a while, and it's typically trivial to find low-hanging fruits across a varied set of workloads. It's a bit harder to find things in the most commonly benchmarked workloads, particularly when those benchmarks are easy-ish to run. But even there there's often lots.

valenterry 4y ago

Well yeah okay, but that's what I wouldn't call an insufficient abstraction. Since even if the query order would logically matter (in some cases it actually does), it doesn't really tell much about how much it was or wasn't optimized under the hood no? > Essentially, the current behaviour is wasteful and never needed to be It's not so easy. You can't just simply physically re-order columns based on new columns that were added. I mean, doing that causes IO and locks/latency issues, so maybe the user wants to run this on demand only or wants to configure it to run in the background under certain conditions, similar to vacuum. It's not trivial.

ghusbands 4y ago
It's all just implementation detail, not show-stoppers. > I wouldn't call an insufficient abstraction Terminology can vary, but the root comment is pointing out that Postgres didn't abstract away column order and has multiple performance penalties from it. It seems reasonable to call that "insufficient abstraction". > You can't just simply physically re-order columns based on new columns that were added. You can. You could use a table lock (as Postgres already does in many column-adding situations), or there are plenty of other ways it could work (definition-versions at the row level, or such). > It's not trivial. Then nothing is. In a hypothetical world in which Postgres had had this optimisation from the start, nobody would be saying "Well, we can't have an add columns feature because we'd need to [X] or [Y]"; there's nothing particularly complicated about it. But, yes, going from the current state of Postgres to this hypothetical world could well be painful and is almost certainly not worth it, but I believe the original point is approximately that this aspect of the Postgres design has imposed performance issues that weren't necessary and are unintuitive. Also, note that Postgres already has features that are in the same area - null bitmaps put the null-ness of columns at the start of the row, and adding a ninth column moves all of the data in a row. It's all just implementation detail, not show-stoppers.
- anarazel 4y ago
  > Also, note that Postgres already has features that are in the same area - null bitmaps put the null-ness of columns at the start of the row, and adding a ninth column moves all of the data in a row. It's all just implementation detail, not show-stoppers. It doesn't move all the data for existing rows, because every row has an indicator about the number of columns in that specific row. Compare that to adding a column and efficient packing requiring that the entire table be rewritten to fit the new column at its optimal place...
  - ghusbands 4y ago
    In the same vein, you could just have an indicator of the number of columns of each alignment, since the simplest solution is to group columns by alignment and add new ones to the end of each. Perhaps try to fully evaluate the idea rather than just shooting it down at the first perceived hurdle. Repeatedly introducing strawmen like "entire table be rewritten" doesn't really help the discussion; the performance impediments and solutions are largely the same shape as what Postgres already does elsewhere. It's all just feeling a bit like the Windows Terminal team telling Casey that the obvious optimisations won't work or require a PhD to fully evaluate or such. It's not that hard and any systems developer could do it. It probably wouldn't be worth the effort, compared to other things, though.
    - anarazel 4y ago
      > Perhaps try to fully evaluate the idea rather than just shooting it down at the first perceived hurdle. I'm not trying to shoot improvements in this area down? I've spent time reviewing patches in the past, and I plan to do so in the future. Perhaps try to give others a bit more benefit of the doubt. > Repeatedly introducing strawmen like "entire table be rewritten" doesn't really help the discussion; the performance impediments and solutions are largely the same shape as what Postgres already does elsewhere. I don't think it's a strawman. For one there's plenty complaints about those rewrites where we have them, so we try to avoid adding more of them. For another, always packing columns would turn an operation that doesn't currently rewrite into a rewriting operation - which'd wreck havoc for people with existing upgrade scripts etc. > In the same vein, you could just have an indicator of the number of columns of each alignment, since the simplest solution is to group columns by alignment and add new ones to the end of each. The per-row overhead would be too large - we already have problems with that.

anarazel 4y ago

> People generally expect mature databases to have optimized any low-hanging fruit IME that's not true. I've been hacking on postgres, with occasional forays into other databases, for quite a while, and it's typically trivial to find low-hanging fruits across a varied set of workloads. It's a bit harder to find things in the most commonly benchmarked workloads, particularly when those benchmarks are easy-ish to run. But even there there's often lots.