[HN Gopher] Column order in PostgreSQL does matter
___________________________________________________________________
Column order in PostgreSQL does matter
Author : todsacerdoti
Score : 173 points
Date : 2022-07-12 09:24 UTC (13 hours ago)
(HTM) web link (www.cybertec-postgresql.com)
(TXT) w3m dump (www.cybertec-postgresql.com)
| masklinn wrote:
| > 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.
| jabart wrote:
| Also 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.
| sokoloff wrote:
| I haven't looked at the pg source, but wouldn't an index on
| Column100 be laid out on disk as Column100|PrimaryKey rather
| than PrimaryKey|Column100?
| jabart wrote:
| Oops yeah, got that flipped.
| masklinn wrote:
| > Doesn't indexes fix this too?
|
| If your query is such that an index-only scan will fulfil it
| then yeah, it should.
| [deleted]
| xwdv wrote:
| I 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.
| AdrianB1 wrote:
| I 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.
| minusf wrote:
| as 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 wrote:
| They 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.
| nja wrote:
| I've found myself sharing this article on pg column order quite
| frequently: https://www.2ndquadrant.com/en/blog/on-rocks-and-
| sand/
| upupandup wrote:
| wish 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 wrote:
| psql 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
| londons_explore wrote:
| 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.
| valenterry wrote:
| And 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 wrote:
| 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.
| simiones wrote:
| How 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 wrote:
| A 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 wrote:
| That'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.
| ghusbands wrote:
| The 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.
| [deleted]
| craigkerstiens wrote:
| A 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).
| thom wrote:
| The 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 wrote:
| > 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.
| magicalhippo wrote:
| > 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-
| alignme...
| thom wrote:
| Apologies for the sloppiness, just trying to be helpful.
| i_like_waiting wrote:
| It 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.
| OrangeMonkey wrote:
| All knowledge is worth having and the pursuit of this knowledge
| will help us become more than we were. I appreciate that.
|
| That said, consider the path of the warehouses over the last 20
| years. Previously, you needed teams of data developers and
| engineers with modeling experts to put forth a datawarehouse
| that may solve a companies problem. Now, you _can_ toss very
| wide tables in a cloud data platform (snowflake, redshift
| serverless, synapse) and it likely will 'just work'. Sure it
| can be faster, but these problems are being slowly removed from
| something we have to care about.
|
| I'm a data specialist, and my knowledge is going to be
| worthwhile for a good long time, but the premium that exists
| for it will go down I think.
| citrin_ru wrote:
| Very wide tables pair well with columnar stores like
| ClickHouse, not with row-based ones.
| jdub wrote:
| But 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 wrote:
| Doesn'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
| edmundsauto wrote:
| Postgres -> Citus is a great path for a v0 -> v1 data
| warehouse as it scales.
| kjeetgill wrote:
| I mean, if it's small I wouldn't really call it DWH.
| greggyb wrote:
| The 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.
| kjeetgill wrote:
| That's a good perspective on it, thanks. I'm absolutely
| guilty of seeing the term it through the lens of the
| technical solution rather than the problem class.
| tomnipotent wrote:
| > because they're not stored together
|
| Most use hybrid columnar and store chunks of rows in column-
| major order (Snowflake, Spanner, Parquet, Arrow).
| SnowHill9902 wrote:
| Extreme case but proves the point. I stand corrected.
| pwagland wrote:
| This looks like a reply to something, but has no parent...
| kevincox wrote:
| I'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.
| jve wrote:
| You 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 wrote:
| Very 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 wrote:
| Yeah, 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.
| latch wrote:
| CockroachDB 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.
| zepearl wrote:
| Unrelated 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.
| jagged-chisel wrote:
| Why 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 wrote:
| There 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.
| fdr wrote:
| it'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.
| jng wrote:
| I'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 wrote:
| It does have to read it because NULLs aren't counted
| teddyh wrote:
| If the column is NOT NULL, does it still read it?
| avianlyric wrote:
| Shouldn't do. Postgres is usually pretty good at making
| these types of optimisations.
| zeroimpl wrote:
| Same - 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....
| nicwolff wrote:
| Pg 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).
| yawgmoth wrote:
| I really like Gitlab's explanation on the matter!
| https://docs.gitlab.com/ee/development/ordering_table_column...
| MichaelApproved wrote:
| What 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
| singron wrote:
| 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.
| jhgb wrote:
| MVCC could use delta records instead. Doesn't Firebird do
| that?
| CodeWriter23 wrote:
| > 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.
| mattashii wrote:
| I just skimmed the document, and it uses wrong data to start
| with: the type sizes may be correct, but the table with
| alignment data [0] is wrong: byte, smallint and int align to 1,
| 2 and 4 bytes, respectively. The 'real' datatype also aligns to
| 4 bytes, as opposed to the 'one word' aka '8 bytes' in that
| doc.
|
| [0]
| https://docs.gitlab.com/ee/development/ordering_table_column...
| samokhvalov wrote:
| I don't think I understand you.
|
| 1 word is 8 bytes, and if you use two columns, the first one
| having size 2 bytes, and the second - 8 bytes, you'll end up
| spending 8 + 8 = 16 bytes, because the first one (2byte in
| length) will be aligned with 6 zeroes to fill the whole
| "8-byte word". For example, with smalling (which has size 2
| bytes but _might_ be aligned, depending on the "column
| tetris" situation we have) + int8: test=#
| select pg_column_size(1::int2); pg_column_size
| ---------------- 4 (1 row)
| test=# create table tttt1 as select 1::int2 as c1, 2::int8 as
| c2; SELECT 1 test=# create extension pageinspect;
| CREATE EXTENSION test=# select lp_len from
| heap_page_items(get_raw_page('tttt1', 0)); lp_len
| -------- 40 (1 row)
|
| -- the total length of the tuple in this example is 40 bytes.
| Why 40? Because the tuple header is 23 bytes aligned to 3
| "words", hence 24 bytes - so total is 24 + 8 + 8 = 40;
| though, effective data size us just 23 + 2 + 8 = 33; and 7
| bytes are "wasted". And our int2 column was aligned by 6
| additional bytes, to whole 8-byte word.
|
| Of course, if the second column is of size 4, then both
| columns fit into a single word - let's try real + int4:
|
| if we'd have 2 int2 columns, followed by a int4 one, we'd use
| just one word (8 bytes to store this data, plus 24 bytes for
| tuple header): test=# create table tttt2 as
| select 1::int2, 2::int2, 3::int4; SELECT 1 test=#
| select lp_len from heap_page_items(get_raw_page('tttt2', 0));
| lp_len -------- 32 (1 row)
|
| -- in this case, the tuple length now is 32 bytes (24 bytes
| tuple header + 8 bytes for the data). Alignment was not
| needed.
|
| Thus, I don't see what's wrong with the document - probably I
| just didn't understand you.
| ghusbands wrote:
| The linked table claims that integers and smallints always
| require an alignment of one word, which is untrue, as you
| also point out. There is no sense in which a two-byte
| smallint 'requires' an alignment of four or eight bytes, as
| listed.
|
| Instead, the "alignment needed" column is a self-fulfilling
| prophecy that tells you the required alignment for the
| succeeding column if the succeeding column is of at least a
| size requiring that alignment, and then it's not a required
| alignment for that type but instead that for a different
| type.
| Someone wrote:
| FTA: 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 wrote:
| Both 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.
| onnnon wrote:
| Unfortunately, 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
| pvillano wrote:
| > 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 wrote:
| The 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 wrote:
| For 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.c4...
___________________________________________________________________
(page generated 2022-07-12 23:01 UTC)