Column order in PostgreSQL does matter
cybertec-postgresql.com
cybertec-postgresql.com
Very interesting insight.
So for small DWH, I am just using PostgreSQL
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.
Most use hybrid columnar and store chunks of rows in column-major order (Snowflake, Spanner, Parquet, Arrow).
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.
"Command-line tools can be faster than your Hadoop cluster"
http://aadrake.com/command-line-tools-can-be-235x-faster-tha...
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.
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?
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.
[0] https://docs.gitlab.com/ee/development/ordering_table_column...
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)
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)
Thus, I don't see what's wrong with the document – probably I just didn't understand you.
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.
E.g. attributes of types (in order) byte, smallint, smallint, int, bigint would have alignment padding of 0 (offset=0), 1 (o = 1 + 1 padding=2), 0 (o=4), 2 (o=6 +2p=8), and 4(o=12+4p=16), respectively, when all those attributes are filled with data.
[0] 'must' is the case most of the time, because variable length attributes have special handling rules when working with alignment of attributes -- but that's not important right now.
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.
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...
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.
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.
If my client code can’t handle that, then I should specify the list in the order I want.
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.
[1]: https://www.postgresql.org/message-id/flat/20220628083230.c4...
I'm about to move off from PostgreSQL back to MySQL primarily for this reason.
After a period of development, the ordering becomes a mess by keep adding columns to the end with no logical grouping and it's a pain to view the data that way.
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.
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.
If your query is such that an index-only scan will fulfil it then yeah, it should.
grow with your app like a gardener :} dont learn how to make skyscrapers when you are building houses...
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.
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.
https://www.postgresql.org/docs/current/storage-page-layout....
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.
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.
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.
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.
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?
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.
> 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.
> 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.
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...
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.
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.
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.)
Feel free to email me. I'll be offline for part of the next two weeks though.
> 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.
Well, it's easy to say that something doesn't have a high intrinsic complexity from the outside, if you haven't spent years in the guts of a system.
> > 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?
Adding a column to a table. Right now that doesn't require a rewrite, if it were always optimally packed it would (or alternatively increase the per-row overhead).
> > 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.
Shrug. I've spent a lot of time working on postgres, including heap storage. Within the constraints of where we are, I am quite confident that it's not realistic to add that. There's larger possible redesigns (and I've spent working on attempts) that could make it more realistic, but it'd be a lot of work - and I'm doubtful it's the most productive way to spend the effort.
I think time would be much more wisely spent removing the need for most of the alignment padding - for by-value types that's solely needed because of an ancient design choice, namely that system catalogs need a C struct compatible layout. That can be addressed in less harmful ways (two things discussed so far, 1) only pad by-value columns in catalog tables 2) auto-generate mapping code to/from the catalog tuple format to struct format). With that a lot of the padding overhead vanishes - many by-reference types have lower alignment requirements.
I might also be a bit overly sensitive around this - I hear "why don't you simply do this [immensely complicated and fragile sketch of a proposal]" a LOT.
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.
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.
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.