Check optimal column order

[subnix]

In PostgreSQL, every data type has its own alignment requirements. When columns are not ordered with these alignments in mind, PostgreSQL automatically inserts padding bytes between them to maintain proper alignment. This can lead to wasted storage space and less efficient processing.

To check the alignment requirements of data types, you can run:

SELECT typalign, typlen, string_agg(typname, ', ') AS types
FROM pg_type
WHERE typtype = 'b' 
  AND typelem = 0 
  AND typnamespace = 'pg_catalog'::regnamespace 
  AND typname NOT LIKE 'reg%'
GROUP BY typalign, typlen;

Because PostgreSQL does not allow changing the column order, it is best to optimize the column order during the initial schema design to improve both storage efficiency and access performance.

Example:

CREATE TABLE unordered (
  a boolean,
  b bigint,
  c boolean,
  d bigint
);

bigint requires 8-byte alignment. PostgreSQL will insert padding bytes as follows:
a boolean → 1 byte
padding → 7 bytes
b bigint → 8 bytes
c boolean → 1 byte
padding → 7 bytes
d bigint → 8 bytes
Total: 32 bytes per row

Optimizing the column order reduces padding:

CREATE TABLE ordered (
  b bigint,
  d bigint,
  a boolean,
  c boolean
);

Here, PostgreSQL stores the row as:
b bigint → 8 bytes
d bigint → 8 bytes
a boolean → 1 byte
c boolean → 1 byte
Total: 18 bytes per row

Designing tables with awareness of type alignment can significantly improve tuple storage and memory efficiency.

References:
https://www.percona.com/blog/postgresql-column-alignment-and-padding-how-to-improve-performance-with-smarter-table-design/
https://www.cybertec-postgresql.com/en/type-alignment-padding-bytes-no-space-waste-in-postgresql/
https://docs.gitlab.com/development/database/ordering_table_columns/
https://www.postgresql.org/docs/current/catalog-pg-type.html

[sbdchd]

This makes a lot of sense, thank you for putting together all the links for this!

I think my ideal approach would avoid talking to postgres directly and instead calculate the sizes ourselves. That way squawk doesn't need a postgres connection.

We can have a lint for this with a quick fix that people can choose to run.

In squawk_ide we have some semantic analysis stuff that we could use for looking up the types of a given column and then calculate the sizing based on their definitions (we don't support builtin types for goto def yet though)

I've also been thinking about adding some related hover info for columns/types/tables a la rust-analyzer's:

struct LexError {
    msg: String,
    token: u32,
}

when hovering over LexError we get:

squawk_parser::lexed_str
struct LexError {
    msg: String,
    token: u32,
}
--
size = 32 (0x20), align = 0x8, needs Drop

when hovering over token we get:

squawk_parser::lexed_str::LexError
token: u32
---
size = 4, align = 0x4, offset = 0x18, no Drop

[subnix]

I think my ideal approach would avoid talking to postgres directly and instead calculate the sizes ourselves. That way squawk doesn't need a postgres connection.

Sounds reasonable, since squawk currently works without connecting to postgres.

we could use for looking up the types of a given column and then calculate the sizing based on their definitions (we don't support builtin types for goto def yet though)

Here are several key considerations to keep in mind:

• Certain types have unknown sizes, such as variable-length types like text.
• Not all user-defined types (for example, enums) can be resolved.
• Searching for the optimal column ordering could lead to an O(n!) complexity.
• A purely optimal column order without padding may be difficult for humans to maintain.

Given these challenges, a practical, suboptimal, and straightforward solution is to use a static ordering based on (typalign, typlen), similar to gitlab’s approach. For example:

1. (*, 32), (*, 24), (*, 16), (*, 8)   // typlen % 8 == 0
2. (d, 12)                             // typlen % 4 == 0 and typalign == d
3. (i, 12), (i, 8), (i, 4)             // typlen % 4 == 0 and typalign == i
4. (i, 6)                              // typlen % 2 == 0 and typalign == i
5. (s, *)
6. (d, -1)
7. (i, -1)
8. (c, *)