# Visualizing a column's space overhead using pg\_hexedit

DevFeed: [Visualizing a column's space overhead using pg\_hexedit](<https://devfeed.tech/articles/visualizing-a-column-s-space-overhead-using-pg-hexedit-33662.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2018/05/visualizing-columns-space-overhead.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2018-05-18T23:11:00Z

Content type: tutorial

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Optimization](<https://devfeed.tech/topics/optimization.md>), [Command-line interface](<https://devfeed.tech/topics/cli.md>), [structure](<https://devfeed.tech/topics/structure.md>)

Tags: [command-line](<https://devfeed.tech/tags/command-line.md>), [index](<https://devfeed.tech/tags/index.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [internals](<https://devfeed.tech/tags/internals.md>), [nbtree](<https://devfeed.tech/tags/nbtree.md>), [optimization](<https://devfeed.tech/tags/optimization.md>), [overhead](<https://devfeed.tech/tags/overhead.md>), [pageinspect](<https://devfeed.tech/tags/pageinspect.md>), [pg-hexedit](<https://devfeed.tech/tags/pg-hexedit.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [schema](<https://devfeed.tech/tags/schema.md>), [storage](<https://devfeed.tech/tags/storage.md>)

## AI overview

The article describes a new pg_hexedit capability for annotating the space used by individual columns within PostgreSQL tuples in tables and B-Tree indexes. It explains how tuple metadata enables the feature and discusses how column ordering and types can reduce on-disk storage, particularly for large fact tables and machine-generated event data.

## Source excerpt

pg_hexedit recently gained the ability to annotate the space taken up by each individual column/attribute within each individual tuple. This works with tables, and with B-Tree indexes. I had to come up with a way of passing the pg_hexedit frontend utility the relevant pg_attribute metadata to make this work. This metadata describes the "shape" of individual tuples in a relation (backend code uses a closely related structure called a "tuple descriptor"). My approach works seamlessly in simple cases, but can still be used when manually running the pg_hexedit command line tool. pg_attribute system catalog table with column annotations/tags This new capability could be applied to optimizing the data layout of a table that is expected to eventually have a massive number of rows. Carefully choosing the order and type of each column can reduce the total on-disk footprint of a table by an appreciable amount, especially when the final table ends up with several 1 byte columns that get packed together. I am aware of several PostgreSQL users that found it worthwhile to have a highly optimized tuple layout, going so far as to use their own custom dataypes. Alignment-aware micro-optimization of a Postgres client application's schema won't help much in most cases, but it can help noticeably with things like fact tables, or tables that contain machine-generated event data. Developing a sense of proportion around storage overhead should now be easier, and more intuitive.