We can't find the internet
Attempting to reconnect
Something went wrong!
Hang in there while we get back on track
Unconference - Stream 3 - 14:30-15:30 - PGCon 2022
Discover how table alignment and column reordering impact Postgres performance, and explore potential solutions to optimize table creation and automatic column reordering.
- Table alignment can lead to unused bytes and wasted space in heap tuples.
- Currently, table columns are not reordered automatically and can cause performance issues.
- The 64-bit transaction ID is not part of the heap tuple format.
- Unused columns are a problem, as they can’t be dropped and lead to wasted space and performance issues.
- The best alignment for a column depends on its data type, and some columns (like booleans) can fit in a smaller space.
- Postgres has attributes and stores them in an aligned manner, but it’s not always the best alignment.
- The current layout of columns is not ideal, and the system doesn’t automatically reorder columns.
- Adding columns in the middle of a row can waste space and alignment.
- Timestamps (Xmin and Xmax) have similar issues to unused columns.
- Table creation could be improved by automatically reordering columns.
- The current infrastructure doesn’t support automatic column reordering, but this could be achieved with a huge patch.
- Unused columns can’t be dropped, but they could be renumbered and re-created.
- Postgres has a lot of unused bits in the heap tuple header, which could be used for other purposes.
- There is a lot of unused space in Postgres, which is wasted due to the fixed order of columns.
- The heap tuple header already has a count of attributes, which could be used as a version identifier for the column order.
- Automatic column reordering could be achieved by reusing unused bits in the heap tuple header.
- The best way to handle different orders of attributes for the same table is unclear.
- It’s possible to have a separate block format for individual blocks within a segment, which could include 64-bit transaction IDs.
- Column reordering could be achieved during a cluster operation, which already rewrities the whole table.
- Unused columns can’t be recovered, and dropped columns remain in the table forever.