Skip to main content
  • Pricing
Tutorials

PostgreSQL Column Order: Measure Row Padding

By Oakgen TeamPublished 1 min read

Share
PostgreSQL Column Order: Measure Row Padding

PostgreSQL stores fixed-width fields on alignment boundaries. If a small field sits before a field with stricter alignment, a row may need padding between them. Comparing realistic rows is more useful than assuming one column order will always save a specific number of bytes.

Build an apples-to-apples check

In a scratch database, create two small tables with the same columns. Put a Boolean between two bigint columns in the first table. Group the two bigint columns together in the second. Insert an identical set of values in both tables. Use pg_column_size on corresponding rows and repeat with the null patterns your data actually uses.

Read the results together with the storage layout. A table row includes a header, optional null information and the field data. This means two rows can differ for reasons beyond the number of declared columns. A text field may also use different storage depending on its value.

Decide whether the saving is worth a rewrite

Estimate how many rows you store, then repeat the measurement on representative data. A small per-row difference may matter on a very large table. It can be irrelevant when the table is small or work already depends on a stable schema. Check application writes, indexes and migration cost before altering an existing table. For a new table you have more room to choose an order and measure before the schema becomes part of an interface.

Continue with Oakgen Agent

Open in Oakgen Agent

The prompt is filled in for you. Review it before sending.

Open in Oakgen Agent

Sources

  1. PostgreSQL 18 Documentation, Database Page Layout
Share