4 ms·
This is an example of insufficient abstraction. Postgresql can store the data in any order, and I expect it to do so when it clearly makes sense to do so, such
by londons_explore 4y ago
This is an example of insufficient abstraction.
Postgresql can store the data in any order, and I expect it to do so when it clearly makes sense to do so, such as this.
- simiones 4y agoHow would abstraction magically materialize a data-layout optimizer into being? The interface of Postgres is in fact abstract enough to do such an optimization (perhaps there would be a problem with "SELECT * FROM table" queries? not sure if in that case the order of columns is specified by the standard), but they just haven't. Optimizations don't happen simply because they are allowed by an interface.
- londons_explore 4y agoA data-layout-optimizer could be as simple as 25 lines of C++. It's fairly trivial to just insert any other field where there would otherwise be padding. It hasn't been written because it would be a breaking change for the whole ecosystem of postgres addons, things that consume postgres WAL logs, etc.
- dymk 4y agoThat's fine for new tables, but the abstraction now leaks when adding new columns. Now your abstraction has broken again, as existing tables with new columns added are performing poorly compared to new tables, even though from the abstraction level of the user, they _should_ be the same thing.
- deleted 4y ago[deleted]
- ghusbands 4y agoThe abstraction is already heavily leaking. It's trivial to patch this claimed abstraction leakage and it won't be worse than the way PostgreSQL already often exclusive-locks whole tables when you add a column and can only statically know the position of columns appearing before a variable-sized one.
- valenterry 4y agoEither every abstraction is leaky (so we can just replace "leaky abstraction" with "abstraction") - or only some abstraction is leaky, then please explain where the current abstraction gives any guarantees of performance that are violated.
- ghusbands 4y agoI didn't introduce the claim of leaky abstractions. The context is that the 'new' abstraction is claimed to be leaky by not providing particular performance guarantees. I pointed out that the 'old' one is just as (or more) leaky by that (or most any) definition. If you want me to cite some guarantee for the old one, you should first cite the guarantee for the new one. What people have issue with is the effect column ordering has on the performance of fetching a fixed-size column from a loaded row and on the size of a row. Those are not necessary features and a better design at the start would have avoided it.
- valenterry 4y ago> I didn't introduce the claim of leaky abstractions But you used the term as well - I disagree with both you and the OP on if this case should be called "leaky abstraction". > The context is that the 'new' abstraction is claimed to be leaky by not providing particular performance guarantees That's not a useful definition of "leaky abstraction" by any means. Who decides if "particular performance guarantees" have been provided or not?
- ghusbands 4y agoI agree that it's not a good term. Also, you're confusing your posters - "abstraction now leaks" was introduced by someone opposing the optimization (dymk), not the OP (londons_explore, who used "insufficient abstraction"). And when I used it I intentionally paraquoted as "claimed abstraction leakage", due to the leaky terminology.
- 4y ago
- valenterry 4y agoAnd where does this expectation come from? Usually the abstraction/interface gives you certain guarantees, such as "the data that a query X returns will be the same, no matter of the order of columns". But unless the abstraction makes explicit statements about performance guarantees, you shouldn't assume any.
- ghusbands 4y agoPeople 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 agoWell 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 agoIt'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.