# pageinspect Published articles for pageinspect. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Visualizing a column's space overhead using pg\_hexedit DevFeed: [Visualizing a column's space overhead using pg\_hexedit]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2018-05-18T23:11:00Z Content type: tutorial Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Optimization](), [Command-line interface](), [structure]() Tags: [command-line](), [index](), [indexes](), [internals](), [nbtree](), [optimization](), [overhead](), [pageinspect](), [pg-hexedit](), [postgresql](), [schema](), [storage]() ### 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. ## Exploring SP-GiST and BRIN indexes visually using pg\_hexedit DevFeed: [Exploring SP-GiST and BRIN indexes visually using pg\_hexedit]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2018-01-08T22:06:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Database](), [Data structures]() Tags: [database](), [experimental](), [exploring](), [index](), [internals](), [pageinspect](), [pg-hexedit](), [postgresql](), [property](), [storage](), [structure]() ### AI overview This article examines the visual representation of PostgreSQL SP-GiST and BRIN indexes using pg_hexedit. It explains SP-GiST's support for unbalanced tree structures and adaptive mapping of tree nodes to disk blocks, then describes BRIN's block-range summaries and in-place index-tuple updates. ### Source excerpt Support for both BRIN and SP-GiST access methods was recently added to pg_hexedit, the experimental hex editor framework for PostgreSQL relation files. These were the final access methods among the standard Postgres index access methods that required support. SP-GiST (Space-Partitioned GiST) Beginning of an SP-GiST leaf page SP-GiST is unique among index access methods whose index structure is tree-like, in that it supports tree structures that are unbalanced. SP-GiST operator classes exist that support k-d trees, quadtrees, and suffix trees. These structures are traditionally only suited to a fully in-memory representation, with dynamically-allocated nodes that contain a small number of simple pointers (byte addresses) pointing to other nodes. SP-GiST presents a generalized interface through which all of these space-partitioned trees can be constructed for a given datatype, in a way that minimizes disk seeks (PDF) and works well with block-orientated storage. Essentially, SP-GiST maps tree nodes onto disk blocks in an adaptive fashion, rather than simply having a block directly correspond to a tree node, as happens with other access methods. There are particularly intricate data structures needed to support all of this. Space utilization can be an issue with SP-GiST indexes, though that's probably very workload dependent. This is something that pg_hexedit can be effective at representing visually. SP-GiST is a good example of the PostgreSQL community implementing a concept that comes directly from state of the art database research (PDF). I suspect that we have yet to fully realize the benefit of SP-GiST for specific application domains, due to a simple lack of awareness among users and potential users that work in those domains. Perhaps this enhancement can contribute in some small way towards a better understanding of what is possible. BRIN (Block Range Index) BRIN "revmap" page The structure of BRIN indexes is not at all tree-like. BRIN works by summarizing the ## pg\_hexedit: Rich hex editor annotations for Postgres relfiles DevFeed: [pg\_hexedit: Rich hex editor annotations for Postgres relfiles]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2017-11-27T01:48:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [GUI](), [Open Source](), [file]() Tags: [editor](), [file](), [gui](), [internals](), [nbtree](), [open-source](), [pageinspect](), [pg-hexedit](), [postgresql]() ### AI overview This article introduces pg_hexedit, an experimental open-source tool that displays PostgreSQL relation files in a hex editor with annotations, tooltips, and bit-field values. It describes uses in corruption detection, corruption simulation, white-box testing, and education, while warning that the tool can corrupt data and should be used only with disposable installations. ### Source excerpt I've written an experimental tool for presenting PostgreSQL relation files in a hex editor with annotations/tags and tooltips that show the structure of the data and its content, including bit field values. This tool is called pg_hexedit, and is available from: https://github.com/petergeoghegan/pg_hexedit pg_hexedit is built on top of the open source, cross-platform GUI hex editor wxHexEditor. Since it's an experimental tool that is primarily made available for educational purposes, you are well advised to not use it on any data directory that isn't entirely disposable. It may cause data corruption. Opening a Postgres relation file in a hex editor while the server is running is a fundamentally unsafe thing to do if you care about your data. Use of the tool should be limited to throwaway installations on users' personal machines. wxHexeditor and pg_hexedit together show information about each individual field in an interactive, easy to use way: pg_type catalog table opened in wxHexeditor, with annotations I originally wrote the tool in order to meet my own needs in this area. I was working on corruption detection, and it became clear that a tool like this would help with corruption simulation/white-box testing, something that I've spent rather a lot of time on. Simulating and testing novel corruption scenarios became significantly easier with pg_hexedit. Tools like contrib/pageinspect are great, but they are still somewhat interpretive, which can actually be a hindrance for this kind of work. In short, pageinspect functions show "what tuples are on the page" logically, as well as the physical contents of individual tuples, but the exact physical state of the entire page is obscured, in order to support an item-pointer-wise SQL interface. The subtle details of how free space is managed within a single page can matter. At least to me. "cities" nbtree page, starting with ItemId array (shown as blue tags) I eventually realized that pg_hexedit is also broadly useful as an ## PostgreSQL Index bloat under a microscope DevFeed: [PostgreSQL Index bloat under a microscope]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2017-07-19T05:43:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Database]() Tags: [index](), [indexes](), [internals](), [nbtree](), [pageinspect](), [postgresql](), [query](), [wiki]() ### AI overview This technical article presents a PostgreSQL query that summarizes the keyspace of a target B-Tree index. Using pageinspect and a pgbench database, it examines index levels, page utilization, indexed value ranges, and block pointers, then interprets the resulting structure and relationships between the index and table. ### Source excerpt I've posted a snippet query to the PostgreSQL Wiki that "summarizes the keyspace" of a target B-Tree index. This means that it displays which range of indexed values belong on each page, starting from the root. It requires pageinspect. The query recursively performs a breadth-first search. Along the way, it also displays information about the space utilization of each page, and the number of distinct key values that actually exist on the page, allowing you to get a sense of how densely filled each page is relative to what might be expected. The query is available from: https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index If I use the query against the largest index that results from initializing a pgbench database at scale factor 10 (pgbench_accounts_pkey), the query takes about 3 seconds to execute on my laptop, and returns the following: level | l_item | blkno | btpo_flags | type | live_items | dead_items | avg_item_size | page_size | free_size | distinct_real_item_keys | highkey | distinct_block_pointers -------+--------+-------+------------+------+------------+------------+---------------+-----------+-----------+-------------------------+---------+------------------------- 2 | 1 | 290 | 2 | r | 10 | 0 | 15 | 8192 | 7956 | 10 | | 10 1 | 1 | 3 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 103945 | 284 1 | 2 | 289 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 207889 | 284 1 | 3 | 575 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 311833 | 284 1 | 4 | 860 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 415777 | 284 1 | 5 | 1145 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 519721 | 284 1 | 6 | 1430 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 623665 | 284 1 | 7 | 1715 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 727609 | 284 1 | 8 | 2000 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 831553 | 284 1 | 9 | 2285 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 935497 | 284 1 | 10 | 2570 | 0 | i | 177 | 0 | 15 | 8192 | 4616 | 177 | | 177 0 | 1 | 1 |