3 ms·

Yes, that's a really great point. I should emphasize that more clearly in the blog :).

mattashii 5y ago
A few more items I've encountered: - Citus data, not Citrus data. - In tables, column order matters. Order const-width non-null columns before all other columns (best so that there's no unnecessary alignment padding). Then maybe some low null fraction fixed-width columns, then ordered by query access frequency. A lot of time can be spent extracting tuple values, and you can save a lot of time using the offset caches by correctly tetris-ing your columns. Note that dropped columns are set to null, so a table rewrite (by redefining your table) may be in order for 100% performance.
- galkk 5y ago
  Isn't #2 something that the database engine should handle by itself: const-width, non-null is written in DDL and the engine should be be able to handle it.
  - mattashii 5y ago
    yes, ish. But Postgresql has no reordering of table columns builtin, mostly due to the limitations of the internal representation of its schema (also called features) and in how the DLL is performed. Adding or removing a column in this system does not require postgres to rewrite the whole table, which means that old data can stay in the table effectively forever, as long as there are no other rewrite-required DDLs performed. Additionally, columns can be updated to SET NOT NULL / DROP NOT NULL, further complicating the whole system you're trying to optimize. Eventually postgresql might support some form of table column reordering that permanently optimizes out deleted columns, but I think it's unlikely to happen anytime soon. There are lower hanging fruits on the tree; altering existing table definitions in a backwards-incompatible manner is a lot of effort for likely very little gain. As for column packing: Maybe this can be implemented for CREATE TABLE with an option, but in the current transactional DDL framework this cannot be implemented for ADD COLUMN, because we can't reorder columns.

A few more items I've encountered: - Citus data, not Citrus data. - In tables, column order matters. Order const-width non-null columns before all other columns (best so that there's no unnecessary alignment padding). Then maybe some low null fraction fixed-width columns, then ordered by query access frequency. A lot of time can be spent extracting tuple values, and you can save a lot of time using the offset caches by correctly tetris-ing your columns. Note that dropped columns are set to null, so a table rewrite (by redefining your table) may be in order for 100% performance.

galkk 5y ago
Isn't #2 something that the database engine should handle by itself: const-width, non-null is written in DDL and the engine should be be able to handle it.
- mattashii 5y ago
  yes, ish. But Postgresql has no reordering of table columns builtin, mostly due to the limitations of the internal representation of its schema (also called features) and in how the DLL is performed. Adding or removing a column in this system does not require postgres to rewrite the whole table, which means that old data can stay in the table effectively forever, as long as there are no other rewrite-required DDLs performed. Additionally, columns can be updated to SET NOT NULL / DROP NOT NULL, further complicating the whole system you're trying to optimize. Eventually postgresql might support some form of table column reordering that permanently optimizes out deleted columns, but I think it's unlikely to happen anytime soon. There are lower hanging fruits on the tree; altering existing table definitions in a backwards-incompatible manner is a lot of effort for likely very little gain. As for column packing: Maybe this can be implemented for CREATE TABLE with an option, but in the current transactional DDL framework this cannot be implemented for ADD COLUMN, because we can't reorder columns.