In this lesson, you will learn how to reduce the memory footprint of custom types to ensure your sequential scans remain fast and cache-efficient.
When you create custom types in PostgreSQL, the storage engine often reserves space based on the maximum possible size of the data structure. If your type is defined with alignment = 'double' or a large internallength, PostgreSQL might insert "padding bytes" between columns to ensure memory alignment. During a sequential scan, the CPU must pull entire blocks from disk into memory; if those blocks are filled with "bloat" (unused padding or oversized type headers), your cache hit ratio drops, and throughput suffers.
The Impact of Alignment
Consider a custom sensor_reading type. If you define it as a fixed-length type, PostgreSQL adds padding to ensure the data is aligned to a 4 or 8-byte boundary. If you have millions of rows, those few extra bytes of padding per row add up, forcing more I/O during a sequential scan.
To minimize bloat, look for opportunities to use pass-by-value types where possible, or align your internal struct members from largest to smallest to minimize the internal "holes" created by the compiler.
-- Bad: Unaligned custom type with internal padding
CREATE TYPE raw_sensor_data AS (
id int, -- 4 bytes
val float8, -- 8 bytes (requires padding before it)
status char(1) -- 1 byte
);
-- Better: Reordered for density and minimal padding
CREATE TYPE optimized_sensor_data AS (
val float8, -- 8 bytes
id int, -- 4 bytes
status char(1) -- 1 byte
-- Padding is now only at the very end of the struct
);
By ordering your internal type fields from largest to smallest, you shrink the overall size of the type instance. When you execute a SELECT that performs a sequential scan, PostgreSQL can fit more rows into a single 8KB page. This means the engine touches fewer pages to complete your query, directly reducing disk I/O and increasing the performance of your scan.
Key Takeaway: Reordering internal type members from largest to smallest minimizes structural padding, allowing more data to fit in each memory page and significantly accelerating sequential scans.