How many people have ordered the database column types explicitly for better disk packing?
Conversation
Replying to
You mean nulls last? I did on oracle with (ugly) tables with lot of nulls.
Does it make a big difference in PostgreSQL?
dbfiddle.uk/?rdbms=postgre
1
Replying to
Not even that, was strictly reordering common datatypes. Oversimplified, but basically order things as:
ints/dates
shorts
booleans
numeric/text/other
varchar
1
1
I think that it's fairly rare. The example that comes to mind is from Christopher Travers. Apparently Adjust applied this and other low-level optimizations. They were dealing with tens of terabytes of data.
1
4
I've definitely done that in the past when helping out postgres users, to pretty good effect. Both reducing table space and reducing query times (having fixed-width NOT NULL columns first can make tuple deforming a lot faster).
We really ought to make it easier to benefit here.
2
7
Wonder if a first start would be a SRF showing the tuple layout for a table, including the padding space?
2
8
Yeah, it's way too hard to figure out what each layout looks like. Seems like it would be easy to provide better tooling, which is a start.
Looks like it exists in a third party extension already: github.com/braintree/pg_c (from the braintree blog post)
4