When modeling a Postgres database, you probably don’t give much thought to the order of columns in your tables. After all, it seems like the kin

Renato's Runtime Reflections

submited by

Style Pass

2024-10-06 16:00:05

When modeling a Postgres database, you probably don’t give much thought to the order of columns in your tables. After all, it seems like the kind of thing that wouldn’t affect storage or performance. But what if I told you that simply reordering your columns could reduce the size of your tables and indexes by 20%? This isn’t some obscure database trick — it’s a direct result of how Postgres aligns data on disk.

In this post, I’ll explore how column alignment works in Postgres, why it matters, and how you can optimize your tables for better efficiency. Through a few real-world examples, you’ll see how even small changes in column order can lead to measurable improvements.

So far so good. That’s exactly what you’d expect: the more data you have in your row, the more space in disk it will use. Disk usage is directly proportional to the data types.

In other words, if we have one integer column, we expect the row size to be 24 + 4 = 28 bytes. If we have one integer column and one smallint column, we expect the row size to be 24 + 4 + 2 = 30 bytes.