Column Order Matters: How PostgreSQL Padding Affects Table Size
A PostgreSQL user experimenting with table design found a real-world example of how column alignment impacts storage. By creating two tables with identical columns but different orders, they measured a significant size difference despite the same data being inserted.
The test involved two tables with four columns: smallint, timestamp, integer, and double precision. In one table, the columns were ordered as smallint, timestamp, integer, double precision. In the second, the order was timestamp, double precision, integer, smallint. Both tables were populated with 10,000 rows of generated data.
After loading the data, the size of the first table was 606,208 bytes, while the second table was only 524,288 bytes—a difference of 81,920 bytes. This is less than the theoretical estimate of 100,000 bytes (10 padding bytes per row times 10,000 rows), suggesting that alignment padding calculations are not always perfectly predictable in practice.
The result highlights a useful optimization tip for PostgreSQL schemas: arranging columns to minimize padding bytes can reduce table size, but the actual savings may differ from simple theoretical models.
