# nbtree Published articles for nbtree. 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. ## 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 ## amcheck "table-matches-index" enhancement now available, detects "freeze-the-dead" corruption DevFeed: [amcheck "table-matches-index" enhancement now available, detects "freeze-the-dead" corruption]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2017-10-26T15:27:00Z Content type: release Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [releases](), [Database]() Tags: [amcheck](), [apt](), [bugs](), [index](), [nbtree](), [postgresql](), [release](), [releases]() ### AI overview The article announces the general availability of amcheck v1.2, which adds an optional check that PostgreSQL table tuples have matching index entries. It explains that the enhancement can detect logical corruption, including corruption associated with the "freeze-the-dead" bug, and notes the planned PostgreSQL point-release fixes. ### Source excerpt I'm pleased to announce that v1.2 of amcheck, a tool for detecting that PostgreSQL relations are logically consistent (that they do not appear to be corrupt) is now generally available. This version adds a big enhancement - the optional ability to check if every tuple that should have an entry in the index does in fact have such an entry. Specifically, we check for a table entry with matching data, as well as a matching heap TID. This happens at the end of the existing tests, as an optional extra step. This enhancement is significant because it seems much more likely to catch corruption in the wild. In general, inconsistencies between a table and its indexes are more likely to occur than inconsistencies between blocks within an index for many reasons. There is simply a much larger window for an inconsistency to arise when something is amiss with database storage that breaks the assumptions PostgreSQL makes during crash recovery, for example. The enhancement is also significant because it played a role in identifying a PostgreSQL data corruption bug that will be fixed in the next point release, scheduled for November 9th, 2017. This bug affects all supported PostgreSQL versions. It was informally dubbed the "freeze-the-dead" bug. November 6 2017 update: The fix was reverted due to additional concerns that came to light. The community is working on a new, more comprehensive fix for the next point release. December 15 2017 update: A new fix has been committed, and will appear in 9.3.21, 9.4.16, 9.5.11, 9.6.7, and 10.2 point releases, scheduled for February 8th, 2018. Packages for v1.2 are available from the community Debian/Ubuntu apt repository, as well as packages from the community Redhat/CentOS/SLES yum repository. Full details on installing these packages are available from the README: https://github.com/petergeoghegan/amcheck/ "Freeze-the-dead" corruption detection I should emphasize that the bug is something that I believe to be very unlikely to hit in the real ## amcheck for Postgres 9.4+ now available from PGDG apt and yum repositories DevFeed: [amcheck for Postgres 9.4+ now available from PGDG apt and yum repositories]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2017-10-16T01:44:00Z Content type: release Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Tool]() Tags: [amcheck](), [apt](), [centos](), [debian](), [index](), [internals](), [nbtree](), [postgres](), [postgresql](), [redhat](), [release](), [repositories](), [ubuntu]() ### AI overview The amcheck tool for detecting PostgreSQL index corruption is now packaged for installation from community Debian/Ubuntu apt repositories and Red Hat/CentOS/SLES yum repositories. These packages support PostgreSQL versions before 10 and install as amcheck_next on PostgreSQL 10. A future enhancement is planned for heap-matches-index verification. ### Source excerpt amcheck, a tool for index corruption detection, now has packages available from the community Debian/Ubuntu apt repository, as well as packages from the community Redhat/CentOS/SLES yum repository. This means that installations built on those community resources can easily install amcheck, even on PostgreSQL versions before PostgreSQL 10, the release that contrib/amcheck actually first appears in. Full details on installing these packages are available from the README: https://github.com/petergeoghegan/amcheck/ It's also possible to install the packages on PostgreSQL 10, because the extension these packages install is actually named "amcheck_next" (not "amcheck"). Currently, it isn't really useful to install "amcheck_next" on PostgreSQL 10, because its functionality is identical to contrib/amcheck. That's expected to change soon, though. I will add a new enhancement to amcheck_next in the coming weeks, allowing verification functions to perform "heap matches index" verification on top of what is already possible. Many thanks to Christoph Berg and Devrim Gündüz for their help with the packaging. ## 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 |