Schedule - PGConf.EU 2015
Tuple internals: exposing, exploring and explaining
Date: 2015-10-28
Time: 12:10–13:00
Room: Palais I-III
Level: Intermediate
Tuple is an important element of PostgreSQL core. However all available PostgreSQL documentation just give a superficial description of tuple's headers, without actually looking into tuple data area. Physical tuple layout was considered to be useful mostly for PostgreSQL developers. However, for DBA and application developers it is not only fun to know how attribute data is packed in a tuple, but it also helps to create more efficient and less disk consuming databases. To fill this gap we would need a tool that allow easily look into tuple data and see what is there. That is why we modified pageinspect extension so it can show not only tuple headers, but also raw data of the tuple and how it is split into attributes.
However this talk is not about pageinspect improvements itself, but about usage of improved pageinspect to reveal actual content of the tuple. Using snapshots taken by pageinspect as an examples, we will discuss in details the following topics:
- how does PostgreSQL pack fixed and variable length attributes;
- speak about attribute compression and TOAST storage;
- see how does aligning work;
- find out how NULL-values are actually stored.
And finally, we'll take a look at the algorithm of attribute extraction, see what optimizations are made there, and thus come to understanding what changes can be made in the table structure in order to get better extraction performance.
