# pg\_hexedit Published articles for pg\_hexedit. 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 Postgres page images within GDB DevFeed: [Visualizing Postgres page images within GDB]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2019-03-23T00:07:00Z Content type: tutorial Language: en Sources: [Peter Geoghegan's blog]() Topics: [gdb](), [debug](), [debugging](), [Code]() Tags: [command](), [debugging](), [gdb](), [interactive](), [internals](), [pg-hexedit](), [postgresql]() ### AI overview This article explains how to configure GDB to invoke pg_hexedit on PostgreSQL page images without using the filesystem, including images from local temporary buffers. It also describes using a user-defined GDB command during debugging and with core dumps. ### Source excerpt It's straightforward to set up GDB to quickly invoke pg_hexedit on a page image, without going through the filesystem. The page image can even come from a local temp buffer. A user-defined GDB command can be created that shows an arbitrary page image in pg_hexedit from an interactive GDB session. This is a good way to understand what's really going on when debugging access method code. It also works well with core dumps. I found this valuable during a recent project to improve the Postgres B-Tree code. An example of how to make this work is available from a newly added section of the pg_hexedit README file: https://github.com/petergeoghegan/pg_hexedit/#using-pg_hexedit-while-debugging-postgres-with-gdb ## 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. ## Decoding pg\_filenode.map files with pg\_filenodemapdata DevFeed: [Decoding pg\_filenode.map files with pg\_filenodemapdata]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2018-03-20T04:44:00Z Content type: tutorial Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [postgresql clusters]() Tags: [file](), [filesystem](), [internals](), [pg-filenode-map](), [pg-hexedit](), [postgresql](), [query](), [sql](), [vacuum]() ### AI overview This article explains how to identify the filesystem relation file for a PostgreSQL table or index when normal SQL queries are unavailable because system catalog corruption prevents a database connection. It introduces pg_filenodemapdata, a pg_hexedit-bundled program that reads pg_filenode.map files to determine the relfilenode numbers associated with system catalog entries. ### Source excerpt From time to time, you may need to figure out which file in a PostgreSQL data directory corresponds to a particular table or index in the database. For example, pg_hexedit users sometimes need this information, since pg_hexedit is a frontend utility that works by reading relation files from the filesystem. In practice, a pg_hexedit convenience script can usually be used instead. Users need only give the name of the table or index that is to be examined. The convenience scripts call the built-in function pg_relation_filepath() via an SQL query. This approach won't always work, though. pg_hexedit is a tool for investigating corruption, and sometimes corruption can affect system catalogs in a way that makes it impossible to even establish a connection to the database. You may find that you're greeted with an arcane error any time you attempt to connect to the database. The error may look something like this: ERROR: catalog is missing 3 attribute(s) for relid 827 In this example, the issue that prevents us from connecting must have something to do with the system catalog pg_attribute, and/or an index on pg_attribute. The catalog with relid/pg_class OID 827 (pg_default_acl_role_nsp_obj_index) appears to lack pg_attribute entries, making the built-in/catalog index pg_default_acl_role_nsp_obj_index unusable (note that there is no reason to think that the underlying relfile for pg_default_acl_role_nsp_obj_index is itself corrupt). To confirm this theory, we'll need to directly examine the pg_attribute relation for the database. Of course, there is no way to query pg_attribute, because we cannot connect. Moreover, there is no easy way to know where the files associated with pg_attribute are, so that we can at least examine pg_attribute using pg_hexedit. The "relfilenode" number that corresponds to pg_attribute (or any other table) isn't hard-coded or stable. For example, the relfilenode of a table will change any time VACUUM FULL is used on the table. I've written a tool cal ## 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 now supports GiST, GIN, and hash indexes DevFeed: [pg\_hexedit now supports GiST, GIN, and hash indexes]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2017-12-15T20:29:00Z Content type: release Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [hash](), [Sequences]() Tags: [experimental](), [hash](), [index](), [indexes](), [internals](), [nbtree](), [pg-hexedit](), [postgresql](), [sequences]() ### AI overview An update to the experimental pg_hexedit toolkit adds annotations for GiST, GIN, and hash indexes, as well as sequences, when inspecting raw PostgreSQL relation files. The author plans to add BRIN and SP-GiST support later. ### Source excerpt I've added several enhancements to pg_hexedit, the experimental hex editor toolkit that allows you to open up raw PostgreSQL relation files with useful tags and annotations about the state and purpose of each field. The tool now supports annotations for GiST, GIN, and hash indexes, as well as sequences. GIN "posting tree" leaf page. Compressed TIDs are in orange. It wasn't very time consuming to add these enhancements, because most index access methods share the same basic approach to page layout. I plan to add support for the two remaining index access methods (BRIN and SP-GiST) early in the new year. My hope is that this will spur interest in the internals of PostgreSQL index access methods (PDF link), and how they deal with index tuples and space management (PDF link). Hat tip to Pat Shaughnessy, who just today wrote a great blog post on the internals of GiST. The fact that he has done such a thorough job of explaining how GiST works to a wider audience is encouraging. ## 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