7 ms·
Column order in PostgreSQL does matter
- londons_explore 4y agoThis 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.
- 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.
- i_like_waiting 4y agoIt shows once again how knowing fundamentals is important at more advanced level. There is this overall motion "very wide tables are the future of DWH", but in reality depends on context and how your setup is built. Very interesting insight.
- jdub 4y agoBut a row-based database is not a data warehouse, and a columnar database doesn't care about column order because they're not stored together.
- i_like_waiting 4y agoDoesn't it depend on database size? at least I heard somewhere online that column based is not really worth implementing if your regular tables have less than 10M rows. So for small DWH, I am just using PostgreSQL
- kjeetgill 4y agoI mean, if it's small I wouldn't really call it DWH.
- greggyb 4y agoThe term "data warehouse" is commonly used to define two different things: 1. The database where all business data for a wide swath of a company's operational groups relevant to reporting and analytics lands. 2. A specific type of database appliance/platform that is optimized for holding the type of data described above, which product is typically multi-node and often based around columnar storage of data. More recently these also emphasize ingesting or providing transparent access to to unstructured data (typically with functionality to push down queries to big data stores or other external data sources). The first is an observation about use cases and is agnostic to technology. The second is a specific type of product that fills the use case of large instances of the first.
- Someone 4y agoFTA: Now imagine what that means if we need to loop over 1000 columns? It does create some non-trivial overhead. I would guess cache misses have a larger effect than the bit twiddling needed to walk to the nth item in each row. A way to sort-of test that would be by using a table that fits in cache and repeating the query. The noise on time measurement might make that difficult, though.
- anarazel 4y agoBoth are significant. But the bit twiddling definitely shows up. There's some caching to avoid it in some situations, and breaking that causes measurable slowdowns. In the end it's not too surprising - a good number of columns fit in a cache line, and adjacent lines can easily be prefetched. The cache miss matters a lot for the first column / row header, but not that much after.
- thom 4y agoThe article doesn't go into depth on alignment but this is another thing to pay attention to with Postgres column ordering. Postgres uses 8 byte alignment and so if you interleave types of different sizes (say a 2-byte smallint between some 8-byte bigints) you may end up with extra padding. This can bloat your tables and potentially reduce performance. It's annoying to have to think about this when in your mind the columns probably have a more meaningful order.
- masklinn 4y ago> Postgres uses 8 byte alignment It doesn’t? Postgres columns have alignment, and that alignment goes from 1 to 8 bytes, but “postgres uses 8 byte alignment” doesn’t really make any sense.
- thom 4y agoApologies for the sloppiness, just trying to be helpful.
- magicalhippo 4y ago> but “postgres uses 8 byte alignment” doesn’t really make any sense Makes perfect sense to me, but it seems it is wrong? At least this page[1] suggests different data types have different alignment. [1]: https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql https://www.enterprisedb.com/postgres-tutorials/data-alignme...
- kevincox 4y agoI've always thought that it would be super valuable if you could mark groups of columns to be stored together. This would move Postgres from pure row-oriented to somewhere between row and column oriented. So for example if you have some rarely accessed columns you could mark those to be stored out of line. IDK if TOAST could be reused here or if it should be more like a different table with an implicit join.
- latch 4y agoCockroachDB has column families which we use in a very few cases. We have some fairly wide tables with a few stats columns that are updated normally a few times per second, but can go up to hundreds of times per second.
- jve 4y agoYou mean not having purely column index for ColumnA, but looking up row within ColumnbB and then having all the column values from ColumnA where ColumnC = D ?
- chrisjc 4y agoVery interesting idea... Perhaps a way to describe the sets of columns (clusters) and maybe even whether the cluster is row or column-oriented. But if you want to split the table between frequently and rarely accessed sets of columns you could just use a view to bring different types of tables together? Eg: a view that brings a row-oriented table together with a column-oriented one? Or row-oriented table together with an external table (storage). Obviously, DMLs would then become a little more complicated.
- kevincox 4y agoYeah, you can do this manually. In many cases probably almost as efficiently. But having this done with only a small configuration would be very powerful.
- jng 4y agoI'm a bit surprised that Posgres doesn't optimize the COUNT(column) expression to not actually read the value of the column, which is not necessary. Am I missing something?
- nattaylor 4y agoIt does have to read it because NULLs aren't counted
- teddyh 4y agoIf the column is NOT NULL, does it still read it?
- avianlyric 4y agoShouldn’t do. Postgres is usually pretty good at making these types of optimisations.
- zeroimpl 4y agoSame - the row format itself starts with a bitmask listing all null columns. You don’t need to read the column data itself to determine if a column is null. https://www.postgresql.org/docs/current/storage-page-layout.html https://www.postgresql.org/docs/current/storage-page-layout....
- nicwolff 4y agoPg has to read the xmin and xmax anyway to determine if the row is visible to your transaction (for rows without hint bits set, in pages without hint bits set).
- masklinn 4y ago> We just used varchar here to prove the point. The same issues will happen with other data types – the problem is simply more apparent with varchar as it is more complicated internally than, say, integer. That’s not entirely honest. The problem is caused by the varchar (or any other variable-size column). With fixed-size columns, postgres doesn’t need to look at the row to determine the offset of a value, it can just loop through the pg_attributes (the column definitions) and look at the nulls bitmap in the row header to skip null values. It still needs some computation (to sum the sizes of the previous columns iff they’re non-null) but that’s a lot more performance-friendly than also needing to go hit and the data tuple and decode the varlen metadata of every varlen field.
- deleted 4y ago[deleted]
- jabart 4y agoAlso pretty sure almost every database with variable width columns has this issue. An old MS SQL Server optimization was to use chars and not have nulls since that drops a few bits too. That way your table was easy to find row 500 on disk. This was back when SCSI and IDE drives were common though. Doesn't indexes fix this too? You can index on column 100 which puts in on disk as Column100|PrimaryKey and now it's a really narrow table. Like yeah you shouldn't have a table with 1,500 columns but sometimes you really do need it. Edit Flipped the index layout.
- yawgmoth 4y agoI really like Gitlab's explanation on the matter! https://docs.gitlab.com/ee/development/ordering_table_columns.html https://docs.gitlab.com/ee/development/ordering_table_column...
- MichaelApproved 4y agoWhat about UPDATE performance? Your article and the parent one are explaining performance improvements related to INSERT and SELECT but what about the improved performance for UPDATE? I wrote a long comment[0] on yesterday’s PostgreSQL post related to column order optimization with regards to UPDATE. My information was 20+ years old and I figured it was horribly outdated but these articles make me think it could still be true. The TLDR is you want to put variable length columns at the end because it makes UPDATE more efficient. My theory was it’ll be less likely the DB would need to move data around when updating the variable column contents. Less data being moved = improved performance. Seeing these articles means I was right with regards to improved performance of INSERT/SELECT but I wonder if I’m right about improved UPDATE performance. Anyone know? [0] https://news.ycombinator.com/item?id=32055596 https://news.ycombinator.com/item?id=32055596
- singron 4y agoAn update in postgres will always copy the tuple for MVCC, so it can't take advantage of an optimization like this to modify it in place.
- jhgb 4y agoMVCC could use delta records instead. Doesn't Firebird do that?
- CodeWriter23 4y ago> MVCC could use delta records instead. Every design choice is trading on thing for another. In your proposed case, you get storage savings in exchange for complex data structure design. And if the performance is a wash or not depends on how many columns and the types of the columns. Probably better to understand the limitations of the tools you're using. If you can live in those boundaries, that's awesome. If you can't but can find a tool with different constraints, also awesome. If neither, your choice becomes understanding the limitations of your tools or infinite recursive optimization.
- xwdv 4y agoI know it matters but I really wish it didn’t. When I’m developing I simply don’t have time to think about the nuances of how I should order columns in a table, this really needs to be abstracted out and optimized behind the scenes.
- minusf 4y agoas with many things "it depends" -- it matters "after a certain point". will your app make it to that point? let's hope it does, and then you'll be happy to learn about some more advanced optimisations like this. it's a good problem to have. grow with your app like a gardener :} dont learn how to make skyscrapers when you are building houses...
- dspillett 4y agoThey were showing a pathological case where there are a very large number of columns and table scanning is the only option the optimiser has, so in most cases it won't matter significantly (removing the need to scan at all will fix that, by adding indexes or altering the query to make filtering and joining clauses sargable). This is more an interesting experiment than something you generally need to worry about, and if you do need to worry about it there are other things you may need to fix first (do you really need 1,500 columns on that table, is there not a design that is orders of magnitude more efficient generally thus making this issue measurable but insignificant?). PG could be refactored to make these cases much more efficient, but the developer (and QA) time required would be much better spent elsewhere and the changes needed may impact the performance of other operations that occur far more regularly in both good and bad designs.
- AdrianB1 4y agoI saw in such cases a 2 step approach, the first part the developers write their code and later on the database administrators perf tune it. It worked well for my teams in the past 5-6 years, YMMV.
- SnowHill9902 4y agoExtreme case but proves the point. I stand corrected.
- pwagland 4y agoThis looks like a reply to something, but has no parent…
- onnnon 4y agoUnfortunately, the only way to change column order is to either recreate the table, or add new columns and move data. https://wiki.postgresql.org/wiki/Alter_column_position https://wiki.postgresql.org/wiki/Alter_column_position
- pvillano 4y ago> The current problem with implementing this lies in that currently postgres uses the same identifiers for both the logical and physical position within a table. The current hot plan for solving this would be to change the system to reference three identifiers... a permanent identifier for the column, as well as a separate logical and physical identifier. This would allow places that need to deal specifically with column order at the logical level (ie. select *) to reference the logical number, while places that interact with disk system can access the physical number, and all other places just use the column's permanent id.
- pvillano 4y agoThe hard part aside... Some cool optimizations are possible if the size of each row doesn't change. * Rows could be done in parallel. * The simplest method would be to copy the row to a scratch buffer, and then copy over data to their final position. * Furthermore, with some fancy permutation, you only need a register of scratch space. Although, with more random memory access, that could result in worse performance.
- uhoh-itsmaciek 4y agoFor what it's worth, there is currently some active work in this area [1]. It's too late for 15, but it might land in 16 (next fall). [1]: https://www.postgresql.org/message-id/flat/20220628083230.c4qhp4fdp3dp22ig%40jrouhaud https://www.postgresql.org/message-id/flat/20220628083230.c4...
- terandle 4y agoNot sure they really need to complicate the system with different physical/logical identifiers. Just an easy way to move physical columns around if you're willing to do a full table lock would be nice.
- craigkerstiens 4y agoA really handy tool for helping to guide you in laying out column ordering is pg_column_byte_packer (https://github.com/braintree/pg_column_byte_packer https://github.com/braintree/pg_column_byte_packer).
- zepearl 4y agoUnrelated question, about updating rows: when I insert rows into a table that has e.g. 30 varchar columns which are initially empty but which will be updated later (not all at once but e.g. 10 in a first round, then another 10 in another cycle, then the last 10), is it ok to leave those columns empty or should I e.g. use space-chars to fill those columns with the expected future length of the string? Asking because I'm not sure if having the columns contents increase their size will cause kind of "row movement" (row cannot be updated in-place as there is no space to accomodate the new long string) which might(?) entangle a bit the contents of the table (e.g. maybe later the more rows are updated the more fragmented the table/tablespace becomes etc...). I guess that Vacuum would fix such situations (if this actually happens), but maybe I can decrease the work that it has to do... . I'm just starting with Postgres, thx for reading. EDIT: forget this, I just read here the post by "singron" stating "An update in postgres will always copy the tuple for MVCC, so it can't take advantage of an optimization like this to modify it in place", so the row gets moved anyway.
- upupandup 4y agowish there was a view where you could vertically arrange rows, instead of columns that run across, it would run downwards, this is helpful for tables with hundreds of columns. its realy tough to scroll horizontally over and over, espeically if the column orders are not alphabetical
- edmundsauto 4y agopsql has this feature! If you're in the shell, you can use the control sequence \x. From the CLI, use the -x flag. https://stackoverflow.com/a/36044642 https://stackoverflow.com/a/36044642
- nja 4y agoI've found myself sharing this article on pg column order quite frequently: https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/ https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
- jagged-chisel 4y agoWhy can’t the engine determine a good packing for the field types? I’ve always been under the impression that rdbms engines do this, that my CREATE TABLE command doesn’t dictate column order - why would it? It what case does the column order matter to the user? During SELECT, I expect the returned fields to come in the order the query specifies, but that’s usually different from how the table was created.
- klysm 4y agoThere is an argument for you to be able to decide the packing yourself. Let’s say you have a few variable length fields but you know one of them will be accessed much more frequently. You can put that one first. The database won’t know that information.
- gleenn 4y agoSeems like it couldn't hurt to do the initial schema load in an optimal way, and if you add and drop columns to oblivion then that sucks but most people are add-only for databases of any sufficient size in general.
- jagged-chisel 4y agoI can see it being optional.
- dietr1ch 4y agoI just want to specify an order that makes reading easier, not one that would work nicely, I'm not an optimizing compiler, don't make me do that work. There's sometimes need for control in performance-critical things, but that needs an escape hatch instead of providing everyone with an implicit tool they don't even know they have. The same applies to struct packing in I guess most compiled languages and using the order to define ABIs.
- fdr 4y agoit's been occasionally suggested. There are limits: for example, maybe during initial creation, sure, it can pack for you, but as you start adding and dropping fields it gets more complex, and databases of consequence tend to have long lives where the returns on complication to get a better result in the initial schema load look a bit more marginal. If you think a bit about how much more complex the cataloging of pg_attributes and the storage code needs to be to support multiple arrangements of attributes in one table -- feasibly more complicated, but definitely a lot more complicated -- you can get an idea why it isn't done.