Reducing table size with optimal column ordering and array-based storage

*Find the article on our blog here:*

In E100 of "5mins of Postgres" we discuss how to optimize the row size in Postgres to reduce the overall table size. Specifically, we take a look at how column ordering affects storage size, how you can play column tetris to optimize it, and the big benefits that array-based storage can yield for some workloads.

*Learn more about pganalyze:*

*Check out the pganalyze library for eBooks, webinars, and more:*

📑 *What we have discussed in this episode of 5mins of Postgres:*

*On Rocks and Sand*

*Calculating and saving space in PostgreSQL*

*Does the optimized column order for a PostgreSQL table always have variable length types at the end?*

*pg_column_byte_packer - PostgreSQL Column Byte Packer*

*PostgreSQL Deep Dive: How Your Data Model Affects Storage*

*pganalyze*

[smyrnian_]

Congratulations on 100th episode Lukas!

[rosendo]

Erwin the stackoverflow legend. Almost on every post about relational databases you gonna find his answer.

[professortrog]

It often works even better if you group the data on itemid and put a chunk of time (say a day or a week) of that item’s data together. Works especially well for physical measurements, as they tend to change more gradual.
This is basically what TimescaleDB does, reaching over 90% compression if configured properly.

[ayoubarahmat]

thanks fotr sharing, keep up the good work 🔥