3 ms·

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 o

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.
    - ghusbands 4y ago
      > Perhaps try to give others a bit more benefit of the doubt. I wonder whether you'd be willing to go into a meta-discussion on that, perhaps not public? I seem to hit this kind of stalemate too often and need someone's help to close the gap. I can see your email address in your profile. I don't see how consistent and repeated "can't work" answers can lead to a collaboration on an actual solution. I'm not even the proposer of the optimization, but I can see it has merit and doesn't have high intrinsic complexity. > always packing columns would turn an operation that doesn't currently rewrite into a rewriting operation I don't think this is necessarily true. What is the operation that would rewrite that doesn't already, assuming the same shape of solutions already used? > The per-row overhead would be too large - we already have problems with that. I read this as "the proposal can't work because [...]", on another thing that feels like a technicality. There are likely all sorts of bit-packing and other options that would make the overhead low, and the Postgres per-row overhead is already so large that it seems doubtful that it'd be that big a deal. (For example, if you've very few columns, you don't need much space for the counts, and if you've more columns, the extra space for a few sizes isn't going to have much impact. Also, any active transaction range isn't going to have a large number of table shapes in it, so rows could likely use a table-definition-version number in place of any column counts. Or you could probably look it up in some bookkeeping structure via xmin and drop the per-row details. The fast path would remain when there are no recent shape changes.)

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.
  - ghusbands 4y ago
    > Perhaps try to give others a bit more benefit of the doubt. I wonder whether you'd be willing to go into a meta-discussion on that, perhaps not public? I seem to hit this kind of stalemate too often and need someone's help to close the gap. I can see your email address in your profile. I don't see how consistent and repeated "can't work" answers can lead to a collaboration on an actual solution. I'm not even the proposer of the optimization, but I can see it has merit and doesn't have high intrinsic complexity. > always packing columns would turn an operation that doesn't currently rewrite into a rewriting operation I don't think this is necessarily true. What is the operation that would rewrite that doesn't already, assuming the same shape of solutions already used? > The per-row overhead would be too large - we already have problems with that. I read this as "the proposal can't work because [...]", on another thing that feels like a technicality. There are likely all sorts of bit-packing and other options that would make the overhead low, and the Postgres per-row overhead is already so large that it seems doubtful that it'd be that big a deal. (For example, if you've very few columns, you don't need much space for the counts, and if you've more columns, the extra space for a few sizes isn't going to have much impact. Also, any active transaction range isn't going to have a large number of table shapes in it, so rows could likely use a table-definition-version number in place of any column counts. Or you could probably look it up in some bookkeeping structure via xmin and drop the per-row details. The fast path would remain when there are no recent shape changes.)