When thinking about database size, most people focus on indexes, unused tables, or excessive text data. However, there’s a subtle space waster that often flies under the radar: padding bytes caused by column misalignment in PostgreSQL.
This blog will explain how PostgreSQL stores rows in memory, how certain column arrangements lead to hidden space waste, and how you can spot and fix it.
How Padding Happens
Every data type in PostgreSQL must be stored at an address that meets its alignment requirement:
- BIGINT, DOUBLE PRECISION, TIMESTAMP: 8-byte alignment
- INTEGER, DATE: 4-byte alignment
- SMALLINT: 2-byte alignment
- BOOLEAN, CHAR: 1-byte alignment
- Variable-length types (TEXT, VARCHAR, JSONB): start at 4-byte boundaries for their length headers
When writing a row, PostgreSQL starts placing columns one after the other in memory. If the next column requires stricter alignment than the current position provides, PostgreSQL inserts empty padding bytes until the position meets the required boundary.
Example:
- BOOLEAN (1 byte) is stored
- Next column is DOUBLE PRECISION (8-byte)
- PostgreSQL sees that the current offset is 1, but it needs to start at a multiple of 8
- It inserts 7 padding bytes
This padding is repeated for each row, and the wasted space grows with the table size. When PostgreSQL stores a row, it ensures each column starts at an address that matches its alignment requirement. If the previous column doesn’t end on the right boundary, PostgreSQL adds padding bytes—empty space—to fix the alignment.
Why This Matters
If you mix column types without considering alignment, PostgreSQL may insert several padding bytes between columns. Multiply this waste by millions of rows, and the impact becomes measurable.
Example: Wasted Space in Action
Consider these two table definitions:
-- Poor alignment
CREATE TABLE orders_bad (
flag BOOLEAN,
order_total DOUBLE PRECISION,
quantity INTEGER
);
-- Better alignment
CREATE TABLE orders_good (
order_total DOUBLE PRECISION,
quantity INTEGER,
flag BOOLEAN
);
In the orders_bad table, the BOOLEAN column (1 byte) comes before an 8-byte column (DOUBLE PRECISION). PostgreSQL adds 7 bytes of padding after the boolean to align the double, wasting space.
In the orders_good table, the largest alignment columns come first, minimizing padding.
Measuring the Difference
Let’s insert 1 million rows into each table:
-- For orders_bad (BOOLEAN, DOUBLE PRECISION, INTEGER)
INSERT INTO orders_bad
SELECT (random() > 0.5), random() * 1000, (random() * 100)::INT
FROM generate_series(1, 1000000);
-- For orders_good (DOUBLE PRECISION, INTEGER, BOOLEAN)
INSERT INTO orders_good
SELECT random() * 1000, (random() * 100)::INT, (random() > 0.5)
FROM generate_series(1, 1000000);
Then check their sizes:
SELECT pg_size_pretty(pg_total_relation_size('orders_bad'));
SELECT pg_size_pretty(pg_total_relation_size('orders_good'));You’ll notice that orders_good uses significantly less space, purely because of better alignment.
Key Takeaway
PostgreSQL doesn’t reorder columns for you. If you care about space efficiency—especially in large tables—put higher-alignment data types first, followed by smaller ones.
For applications like Odoo, which already define columns in a consistent order, the effect is often minimized. But for custom tables or migrations, checking column alignment can reveal surprising savings.
Padding bytes are silent space thieves in PostgreSQL. By understanding alignment rules and ordering columns thoughtfully, you can reduce table size, improve cache efficiency, and speed up queries—without changing a single row of data.