Optimising Column Order for Better Performance & Maintainability
summary
Shows that reordering columns to group fixed-width types before varlena removes padding and shrinks row size 56→46 bytes, which on a 1M-row test drops table size 575MB→498MB — 77MB saved. Explains alignment rules (1/2/4/8-byte) and how varlena/TOAST and short varlena headers change in-memory vs on-disk size, demonstrates TOAST compression reducing large repeated text, and ties smaller rows to faster sequential scans, quicker VACUUM/CLUSTER runs and more efficient index use.
AI-written summary · about
chapters (8)
- 0:00Introduction to column order
- 6:28Access latency fundamentals
- 10:48Value types in PostgreSQL
- 17:00TOAST and variable-length data
- 24:38TLE construction and alignment
- 29:19Why alignment matters
- 33:37Optimization benefits and trade-offs
- 37:58Modern hardware considerations
related talks
54mReducing Costs and Improving Performance With Data Modeling in Postgres
25mPostgreSQL performance tips you have never seen before
38mShaving off bytes at any scale: Space savings in various subsystems of PostgreSQL
19mLet me show you why data types still matter
39mColumnar Table Performance Enhancements Of Greenplum Database
01h 00m7 Tips to Performance Tuning, Optimization, and Everything
25mThe Secret Life of SQL: How to Optimize Database Performance
59mBoosting MySQL Performance