# Peter Geoghegan's blog Musings on PostgreSQL, database technology in general, and software development 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 ## 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 | ## amcheck: Verify the logical consistency of PostgreSQL B-Tree indexes DevFeed: [amcheck: Verify the logical consistency of PostgreSQL B-Tree indexes]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2016-05-10T18:50:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Database](), [bug](), [systems]() Tags: [amcheck](), [bug](), [consistency](), [database](), [indexes](), [internals](), [locking](), [postgresql](), [vacuum](), [verify]() ### AI overview The article introduces amcheck, a PostgreSQL extension for verifying the logical consistency of B-Tree indexes. It is intended to detect production index corruption with low overhead and generally non-blocking locks, while acknowledging that verification is not fully comprehensive. ### Source excerpt I've created a project page on Github for amcheck, a tool for verifying the logical consistency of PostgreSQL B-Tree indexes: https://github.com/petergeoghegan/amcheck The tool is primarily useful for detecting index corruption in production database systems. It can do this with low overhead; most verification requires only a non-disruptive lock on the index as it is verified. The strength of the lock taken on an index as it is verified matches that of simple SELECT statements (unless the highest level of verification is requested). The locking involved will generally not block concurrent reads or writes, and will not prevent VACUUM from running concurrently. amcheck is proposed as a contrib extension for PostgreSQL 9.7. This externally maintained version of the extension exists to support earlier versions of PostgreSQL (PostgreSQL 9.4+), and to make the tool available to those that need it sooner. While the level of verification is not totally comprehensive (in particular, there is no verification of indexes against underlying tables), the tool is still likely to detect many subtle problems in practice. amcheck verifies that certain invariants that must hold in the structure of B-Tree indexes actually do, in fact, hold. It's fairly exhaustive. One example of a problem that the tool can detect is inconsistency arising from the recent PostgreSQL 9.5 abbreviated keys glibc issue, where the new-to-9.5 abbreviated keys performance optimization could lead to structurally inconsistent indexes due to a bug in some glibc versions. This issue created a need to get amcheck into the hands of users sooner rather than later. It's not ideal that the tool is maintained externally, since there are complex locking protocols involved; the implementation must make sure that there cannot be false positives to be of much practical use, and so the tool ought to be considered whenever there is a question about these locking protocols. Unfortunately, we ran out of time to get amcheck into ## Suggesting a corrected column name/spelling in the event of a column misspelling DevFeed: [Suggesting a corrected column name/spelling in the event of a column misspelling]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-11-14T20:25:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [SQL](), [client]() Tags: [client](), [join](), [postgresql](), [query](), [sql]() ### AI overview The article describes a PostgreSQL 9.5 feature that suggests likely column names when a user misspells a column in an SQL query. Suggestions are scored heavily using Levenshtein distance and are shown in HINT messages to psql and other client tools, including cases with multiple equally scored matches and table aliases. ### Source excerpt One small PostgreSQL 9.5 feature I worked on is the new hinting mechanism feature, which sometimes hints, based on a score, what you might have meant to type following misspelling a column name in an SQL query. The score heavily weighs Levenshtein distance. A HINT message is sent to the client, which psql and other client tools will display by default. It's common to not quite recall offhand if a column name is pluralized, or where underscores are used to break up words that make up the name of a column. This feature is targeted at that problem, providing guidance that allows the user to quickly adjust their query without mental context switching. For example: postgres=# select * from orders where order_id = 5; ERROR: 42703: column "order_id" does not exist LINE 1: select * from orders where order_id = 5; ^ HINT: Perhaps you meant to reference the column "orders"."orderid". You may also see a hint in the case of two possible matches, provided both matches have the same score, and the score crosses a certain threshold of assumed usefulness: postgres=# select * from orders o join orderlines ol on o.orderid = ol.orderid where order_id = 5; ERROR: 42703: column "order_id" does not exist LINE 1: ...oin orderlines ol on o.orderid = ol.orderid where order_id =... ^ HINT: Perhaps you meant to reference the column "o"."orderid" or the column "ol"."orderid". If an alias was used here (which this query must have anyway), the hint becomes more specific: postgres=# select * from orders o join orderlines ol on o.orderid = ol.orderid where o.order_id = 5; ERROR: 42703: column o.order_id does not exist LINE 1: ...oin orderlines ol on o.orderid = ol.orderid where o.order_id... ^ HINT: Perhaps you meant to reference the column "o"."orderid". This feature should make writing queries interactively in psql a bit more pleasant. Mental context switching to figure these incidental details out has a tendency to slow things down. ## Avoid naming a constraint directly when using ON CONFLICT DO UPDATE DevFeed: [Avoid naming a constraint directly when using ON CONFLICT DO UPDATE]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-10-02T18:36:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [implementation](), [syntax](), [ordering]() Tags: [concurrently](), [implementation](), [indexes](), [postgresql](), [syntax]() ### AI overview The article explains PostgreSQL 9.5's UPSERT syntax and recommends using unique index inference with ON CONFLICT DO UPDATE instead of naming a constraint directly. It describes how inference selects arbiter indexes and handles variations such as column ordering, partial-index predicates, and multiple equivalent unique indexes. ### Source excerpt PostgreSQL 9.5 will have support for a feature that is popularly known as "UPSERT" - the ability to either insert or update a row according to whether an existing row with the same key exists. If such a row already exists, the implementation should update it. If not, a new row should be inserted. This is supported by way of a new high level syntax (a clause that extends the INSERT statement) that more or less relieves the application developer from having to give any thought to race conditions. This common operation for client applications is set to become far simpler and far less error-prone than legacy ad-hoc approaches to UPSERT involving subtransactions. When we worked on UPSERT, many edge-cases were carefully considered. A technique called "unique index inference" allows DML statement authors to be very explicit about what condition they want to take the alternative (UPDATE or NOTHING) path on. That alternative path can only be taken in the event of a would-be duplicate violation in an "arbiter" unique index (for the DO NOTHING variant, a would-be exclusion violation is also a possible reason to take the alternative NOTHING path). The ability to write UPSERT statements explicitly and safely while also having lots of flexibility is an important differentiator for PostgreSQL's UPSERT in my view. As the 9.5 INSERT documentation explains, the inference syntax contains one or more column_name_index (columns) and/or expression_index expressions (expressions), and perhaps an optional index_predicate (for partial unique indexes, which are technically not constraints at all). This is internally used to figure out which of any available unique indexes ought to be considered as an arbiter of taking the alternative path. If none can be found, the optimizer raises an error. The inference syntax is very flexible, and very tolerant of variations in column ordering, whether or not a partial unique index predicate is satisfied, and several other things. It can infer multiple un ## Abbreviated keys for numeric to accelerate numeric sorts DevFeed: [Abbreviated keys for numeric to accelerate numeric sorts]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-04-04T16:19:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Sorting](), [Development]() Tags: [abbreviation](), [benchmarks](), [c](), [cardinality](), [commit](), [count](), [fast](), [improvements](), [indexes](), [internals](), [maintenance](), [postgresql](), [sorting](), [table]() ### AI overview The article discusses PostgreSQL numeric abbreviated keys, a committed patch intended to accelerate numeric sorting. It reports 6x-7x improvements for representative in-memory queries and a 12x improvement in a PostgreSQL 9.5 text-column CREATE INDEX benchmark compared with PostgreSQL 9.4. ### Source excerpt Andrew Gierth's numeric abbreviated keys patch was committed recently. This commit added abbreviation/sortsupport for the numeric type (the PostgreSQL type which allows practically arbitrary precision, typically recommended for representing monetary values). The encoding scheme that Andrew came up with is rather clever - it has an excellent tendency to concentrate entropy from the original values into the generated abbreviated keys in real world cases. As far as accelerating sorts goes, numeric abbreviation is at least as effective as the original text abbreviation scheme. I easily saw improvements of 6x-7x with representative queries that did not spill to disk (i.e. that used quicksort). In essence, the patch makes sorting numeric values almost as cheap as sorting simple integers, since that is often all that is actually required during sorting proper (the abbreviated keys compare as integers, except that the comparison is inverted to comport with how abbreviation builds abbreviated values from numerics as tuples are copied into local memory ahead of sorting - see the patch for exact details). Separately, over lunch at pgConf.US in New York, Corey Huinker complained about a slow, routine data warehousing CREATE INDEX operation that took far too long. The indexes in question were built on a single text column. I suggested that Corey check out how PostgreSQL 9.5 performs, where this operation is accelerated by text abbreviation, often very effectively. Corey chose an organic set of data that could be taken as a reasonable proxy for how PostgreSQL behaves when he performs these routine index builds. In all cases maintenance_work_mem was set to 64MB, meaning that an external tapesort is always required - those details were consistent. This was a table with 18 million rows. Apparently, on PostgreSQL 9.4, without abbreviation, the CREATE INDEX took 10 minutes and 19 seconds in total. On PostgreSQL 9.5, with identical settings, it took only 51.3 seconds - a 12x improvemen ## Abbreviated keys: exploiting locality to improve PostgreSQL's text sort performance DevFeed: [Abbreviated keys: exploiting locality to improve PostgreSQL's text sort performance]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-01-24T00:43:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Sorting](), [Databases](), [SQL]() Tags: [abbreviation](), [c](), [index](), [internals](), [performance](), [postgresql](), [sorting](), [sql]() ### AI overview The article explains PostgreSQL's abbreviated keys patch, which improves text sorting performance by exploiting locality. It reports that, in realistic cases, text-based CREATE INDEX operations in PostgreSQL 9.5 are over three times faster than in PostgreSQL 9.4, while noting that the improvement varies by workload. ### Source excerpt On Monday, Robert Haas committed a patch of mine that considerably speeds up the sorting of text in PostgreSQL. This was the last and the largest in a series of such patches, the patch that adds "abbreviated keys". PostgreSQL 9.5 will have big improvements in sort performance. In realistic cases, CREATE INDEX operations on text are over 3 times faster than in PostgreSQL 9.4. Not every such utility operation, or data warehousing query involving a big sort is sped up by that much, but many will be. This was a piece of work that I spent a considerable amount of time on over the past few months. It's easy to justify that effort, though: sorting text is a very fundamental capability of any database system. Sorting is likely the dominant cost when creating B-Tree indexes, performing CLUSTER operations, and, most obviously, for sort nodes that are required by many plans that are executed in the service of queries with ORDER BY or DISTINCT clauses, or aggregates using the GroupAggregate strategy. Most of the utility statements that need to perform sorts must perform them with a very disruptive lock on the target relation (CREATE INDEX CONCURRENTLY is a notable exception), so quite apart from the expense of the sort, the duration of sorts often strongly influences how long a production system is seriously disrupted. My interest in sorting is not new: I first worked on it in 2011. Early research on it back then prompted Robert Haas and Tom Lane to write the SortSupport infrastructure, which I've now extended here. Originally, the SortSupport infrastructure was all about providing alternative versions of comparators for use in sort routines, versions that avoided certain overhead otherwise inherent to calling functions that are generally accessible from SQL. As a highly extensible system, PostgreSQL requires that sort behavior be defined in terms of a default B-Tree operator class, which is itself defined in terms of SQL operators with underlying SQL-callable functions. These ## PostgreSQL jsonb: Internal Representation, JSON Compatibility, and Differences from BSON DevFeed: [PostgreSQL jsonb: Internal Representation, JSON Compatibility, and Differences from BSON]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2014-03-24T01:39:00Z Content type: opinion Language: en Sources: [Peter Geoghegan's blog]() Topics: [JSON](), [Databases](), [SQL](), [MongoDB]() Tags: [c](), [internals](), [json](), [jsonb](), [mongodb](), [postgresql](), [sql]() ### AI overview The article examines PostgreSQL's jsonb data type, including its binary and internally typed representation, indexing of nested predicates, numeric comparison behavior, duplicate-key handling, and compatibility with textual JSON. It argues that jsonb is distinct from MongoDB's BSON and discusses limitations anticipated by JSON RFC 7159. ### Source excerpt Unsurprisingly, there has been a lot of interest in the jsonb type, which made it into the upcoming 9.4 release of Postgres. I was initially a reviewer of jsonb, although since I spent weeks polishing the code, I was ultimately credited as a co-author. Jsonb is a new datatype for Postgres. It is distinct from the older json datatype in that its internal representation is binary, and in that it is internally typed. It also makes sophisticated nested predicates within queries on jsonb indexable. I've occasionally described the internally-typed scalar values as having "shadow types" unknown to the core SQL parser. This has several implications. For example, if you sort two Jsonb values containing only scalar numbers, the implementation invokes the numeric comparator (which the jsonb default B-Tree opclass comparator is defined in terms of). The on-disk representation of jsonb includes the same representation as is used for, say, numerics (as the internal binary representation of JSON primitive numbers, for example). Plus, JSON objects are de-duplicated by key on input, and optimized for cheap binary searches within a single jsonb. Still, like the earlier json type, jsonb in every sense "speaks JSON". There are some limitations on what can be represented as a jsonb number, but those are exactly the same limitations that apply to the core numeric type (plus some limitations imposed by the JSON RFC, such as not accepting NaN values). I hope it suffices to say that these limitations are virtually irrelevant, and that many implementations have similar or worse limitations. All of these minor implementation-defined restrictions are explicitly anticipated and allowed for by the recent JSON RFC-7159. Jsonb is emphatically not like the BSON format used by MongoDB. That format accepts input in such a way as to be backwards compatible with JSON, but I believe that BSON isn't really a practical interchange format, because the software development community at large is presumably d ## Finding plans in pg\_stat\_plans easily with pg\_find\_plans DevFeed: [Finding plans in pg\_stat\_plans easily with pg\_find\_plans]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-12-04T10:40:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [SQL](), [JSON](), [Python](), [Monitoring]() Tags: [join](), [json](), [pg-stat-plans](), [pg-stat-statements](), [planner](), [postgresql](), [python](), [sql]() ### AI overview The article introduces pg_find_plans, an experimental pg_stat_plans submodule for PostgreSQL. It uses machine-readable EXPLAIN output to search stored execution plans by criteria such as sequential scans, execution costs, and join counts directly from SQL. The article also discusses its PL/Python and PL/pgSQL implementation, input sanitization, limitations, and the usefulness of JSON for representing plan structures. ### Source excerpt As I recently blogged about, pg_stat_plans is a PostgreSQL satellite project I've been working on that aims to support earlier versions of Postgres that cannot use the new pg_stat_statements, and to track execution costs at the plan rather than the query granularity. It allows the user to easily explain each stored query text to see the plan for the entry, and has features that facilitate monitoring planner regressions. Since PostgreSQL 9.0, support for machine-readable EXPLAIN output has existed. I'm not aware that anyone else got around to actually doing something interesting with this capability, though. I knew that in order to get the most benefit from pg_stat_plans, it ought to be possible to leverage this capability to search for plans based on arbitrary criteria, directly from SQL. I've written an experimental submodule of pg_stat_plans, called pg_find_plans, that is designed to do just that - to quickly find plans and their execution costs, for those plans that, say, perform a sequential scan on a known large table. Here's the description of pg_find_plans from its documentation: pg_find_plans is written in PL/Python and PL/PgSQL. It is intended to provide users with a better way to ask questions like "what are the execution costs of all plans tracked since last statistics reset that involve a sequential scan against mytable, and have more than 2 joins?". That might be written as: mydb=# select join_count(json_plan), p.* from pg_stat_plans p join stored_plans sp on (p.userid=sp.userid and p.dbid=sp.dbid and p.planid=sp.planid) where from_our_database and join_count(json_plan) > 2 and contains_node(json_plan, 'Seq Scan', 'mytable'); order by 1 desc nulls last; Users should have a high degree of confidence that their queries on plan's structure are free of detectable errors, and pg_find_plans ensures this by carefully sanitising user input. For example, if the node of interest was specified as 'seq scan' above, the query would raise an error - to do any less mi ## Notes on index-only scans DevFeed: [Notes on index-only scans]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-11-16T12:31:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL]() Tags: [article](), [performance](), [postgres](), [postgresql](), [wiki]() ### AI overview The article discusses index-only scans in Postgres 9.2, which allow certain queries to avoid retrieving table data and potentially reduce I/O. It also notes that the feature has important implementation limitations. ### Source excerpt One of the most important performance features in Postgres 9.2 is index-only scans: the ability for certain types of queries to be performed without retrieving data from tables, potentially greatly reducing the amount of I/O needed. I recently completely overhauled the Index-only scans PostgreSQL wiki page, so that the page is now targeted at experienced PostgreSQL users that hope to get the most out of the feature. My apologies to the authors of the feature, Robert Haas, Ibrar Ahmed, Heikki Linnakangas and Tom Lane, if my handling of the topic seems to focus on the negatives. Any reasonable article about any given index-only scan implementation would have to extensively discuss that implementation's limitations. Any discussion of Postgres index-only scans that focussed on the positives would be much shorter, and would essentially just say: "Index-only scans can make some of your queries go much faster!". ## First release of pg\_stat\_plans DevFeed: [First release of pg\_stat\_plans]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-10-20T14:53:00Z Content type: release Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [SQL](), [Open Source](), [Logging]() Tags: [database](), [open-source](), [performance](), [pg-stat-plans](), [pg-stat-statements](), [planner](), [postgresql](), [query](), [release](), [sql]() ### AI overview The article announces the first release of pg_stat_plans, an open-source PostgreSQL module based on pg_stat_statements. It analyzes plan execution costs and planner regressions, extends normalized-statement analysis to PostgreSQL 9.0 and 9.1, and tracks plan costs over time. ### Source excerpt Anyone who attended my recent talk at Postgres Open, which was co-presented with my 2ndQuadrant colleague Greg Smith, "Beyond Query Logging", will be aware that pg_stat_statements, the standard contrib module that assigns execution costs to queries and makes them available from a view in the database, has been improved considerably in the recent 9.2 Postgres release. It has been improved in a way that we believe will alter the preferred approach to workload analysis on PostgreSQL databases away from log analysis tools, which just don't offer the performance, flexibility or granularity of this new approach. We also announced a new open source tool that addresses a related but slightly different problem (the analysis of plan execution costs, and planner regressions), as well as making most of the benefits of pg_stat_statements on 9.2 available to users stuck on earlier versions of Postgres. This new tool is called pg_stat_plans, and is itself based on pg_stat_statements. The 9.2 pg_stat_statements feature of particular importance, the ability to "normalise" non-prepared statements that the large majority of applications use exclusively is now brought to earlier versions (versions 9.0 and 9.1, though pg_stat_plans works fine on 9.2 too). Since pg_stat_plans fingerprints plans rather than query trees, the way this works is slightly different to pg_stat_statements, and perhaps doesn't quite match people's intuitive expectations about how normalisation ought to behave in some cases. These differences have been extensively documented. pg_stat_plans also has the ability to EXPLAIN a stored, representative SQL text, in order to facilitate deeper analysis of plan execution costs. Plan total_cost and startup_cost is tracked over time for each plan, for example, so that the "crossover point" at which the planner begins to prefer an alternative plan can sometimes be observed, and the planner's "reasoning" can perhaps be better understood. pg_stat_plans is distributed under the P ## Sorting improvements in PostgreSQL 9.2: the case for micro-optimisation DevFeed: [Sorting improvements in PostgreSQL 9.2: the case for micro-optimisation]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-08-02T02:04:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Sorting](), [Compiler](), [Programming](), [inlining]() Tags: [c](), [compiler](), [cpu](), [inlining](), [performance](), [postgresql](), [sorting]() ### AI overview This article explains the development of PostgreSQL 9.2 sorting improvements. The approach specialized quicksort code, used compiler inlining and generated specializations, and reduced indirection in comparator calls. The article states that simple in-memory integer and floating-point sorting became about 23% faster. ### Source excerpt There has been much discussion of performance improvements in the upcoming 9.2 release of PostgreSQL. Recently, I noticed that Regina Obe and Leo Hsu's new book, "PostgreSQL: Up and running" prominently listed "Sorting improvements that improve in-memory sorting operations by as much as 20%" as a performance feature of that release. While they do get things about right there, I'm not sure that this improvement warrants such prominent placement, at least in sheer terms of its likely impact on the performance of production PostgreSQL systems - we packed a lot of great performance improvements into 9.2. The likely reason that it was picked up on in the book, and the real reason for this blogpost, is the story behind the development of the optimisation, which I for one find kind of interesting, and worth sharing. It's more interesting from the perspective of someone with a general interest in systems programming or PostgreSQL's design philosophy than a casual user, though. If you're a casual user, the short version is that simple queries that perform in-memory sorting of integers and floats will be about 23% faster. I wrote a rough prototype of the patch, that had a number of ideas, and proved the viability of the approach. Principal among those ideas was specialisation of the quicksort code: Formatting the code such that the compiler had compile-time knowledge of functions, with inlining used as an enabling optimisation, and a few variations produced. So rather than using complex indirection involving function pointers, a macro infrastructure was used to generate multiple specialisations, allowing the compiler to optimise the code more effectively as a result of being able to integrate everything. A secondary problem was that comparators (i.e. the comparison functions that all sorting within Postgres currently needs) were accessed in a round-about away. Roughly speaking, tuplesort (the part of the code that deals with sorting tuples, perhaps as part of a query's execut ## Towards 14,000 write transactions per second on my laptop DevFeed: [Towards 14,000 write transactions per second on my laptop]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-06-04T18:00:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [Transactions](), [Scalability](), [backends](), [implementation](), [Latency]() Tags: [backends](), [batching](), [benchmark](), [c](), [performance](), [postgresql](), [queue](), [scalability](), [time](), [transactions]() ### AI overview The article explains a Postgres 9.2 performance improvement for small write transactions. By reducing contention around WALWriteLock and batching commit-related WAL flushes, the change increases throughput without adding arbitrary commit latency or requiring additional configuration. ### Source excerpt Postgres 9.2 will have many improvements to both read and write scalability. Simon Riggs and I collaborated on a performance feature that greatly increased the throughput of small write transactions. Essentially, it accomplishes this by reducing the lock contention surrounding an internal lock called WALWriteLock. When an individual backend/connection holds this lock, it is empowered to write WAL from wal_buffers, an area of shared memory that temporarily holds WAL until it is written, and ultimately flushed to persistent storage. Original update.sql "new group commit" benchmark, January 2012. This made it into Postgres 9.2. Here, we compare the performance of my original patch (red line) and Postgres master in January (green line). 9.1 performance on this benchmark would probably be very similar to that of the baseline seen here. With this patch, we don't have the backends queue up for the WALWriteLock to write their WAL as before. Rather, they either immediately obtain the WALWriteLock, or else queue up for it. However, when the lock becomes available, no waiting backend actually immediately acquires the lock. Rather, each backend once again checks if WAL has been flushed up to the LSN that the transaction being committed needs to be flushed up to. Oftentimes, they will find that this has happened, and will be able to simply fastpath out of the function that ensures that WAL is flushed (a call to that function is required to honour transactional semantics). In fact, it is expected that only a small minority of backends (one at a time, dubbed "the leader") will actually ever go through with flushing WAL. In this manner, we batch commits, resulting in a really large increase in throughput, as you can tell from the diagram above. In Postgres 9.2, this improvement automatically becomes available without any further configuration. This was one of the subjects of my recent talk, co-presented with Greg Smith at PgCon 2012, "A Batch of Commit Batching". There is some conf ## Much improved statement statistics coming to Postgres 9.2 DevFeed: [Much improved statement statistics coming to Postgres 9.2]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-03-29T13:02:00Z Content type: opinion Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [Query (disambiguation)](), [Parsing]() Tags: [c](), [database](), [optimisation](), [performance](), [pg-stat-statements](), [postgres](), [postgresql](), [query]() ### AI overview This article discusses an improvement to PostgreSQL's pg_stat_statements module for Postgres 9.2. The change normalizes similar queries by fingerprinting analyzed parse-tree fields, allowing statistics from queries that differ only in constants to be grouped together for production performance analysis. ### Source excerpt There is a tendency for people with an interest in improving databases performance to imagine that it mostly boils down to factors outside of their application - the hardware, operating system configuration, and database settings. While these are obviously crucially important, experience suggests that in most cases, by far the largest gains are to be had by optimising the application's interaction with the database. Doing so invariably involves analysing what queries are being executed in production, their costs, and what the significance of the query is to the application or business process that the database supports. PostgreSQL has had a module available in contrib since version 8.4 - pg_stat_statements, originally developed by Takahiro Itagaki. The module blames execution costs on queries, so that bottlenecks in production can be isolated to points in the application. It does so by providing a view that is continually updated, giving real-time statistical information. Here is an example from the Postgres 9.2 docs: bench=# SELECT pg_stat_statements_reset(); $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; -[ RECORD 1 ]--------------------------------------------------------------------- query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; calls | 3000 total_time | 9.60900100000002 rows | 2836 hit_percent | 99.9778970000200936 -[ RECORD 2 ]--------------------------------------------------------------------- query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; calls | 3000 total_time | 8.015156 rows | 2990 hit_percent | 99.9731126579631345 -[ RECORD 3 ]--------------------------------------------------------------------- query | copy pgbench_accounts from stdin calls | 1 total_time | 0.310624 rows | 100000 hit_percent | 0.303 ## Power consumption in Postgres 9.2 DevFeed: [Power consumption in Postgres 9.2]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-01-29T00:00:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Database](), [cpu](), [paas and iaas](), [cloud-computing](), [Embedded Software Dev]() Tags: [cloud-computing](), [cpu](), [database](), [embedded-systems](), [postgresql](), [processors](), [services]() ### AI overview The article discusses efforts to reduce idle CPU wake-ups in PostgreSQL as part of the 4CaaSt research project. It explains why wake-ups affect power consumption in virtualized server environments and reports a measured idle rate of 11.5 wake-ups per second for an unconfigured PostgreSQL 9.1 server. ### Source excerpt One of the issues of major concern to CPU vendors is optimising the power consumption of their devices. In a world where increasingly, computing resources are purchased in terms of fairly abstract units of work, and where, when selecting the location of a major data-centre, the local price of a kilowatt hour is likely to be weighed just as heavily as the wholesale price of bandwidth, this is quite understandable. Globally, data centres consumed between 1.1 and 1.5 percent of electricity in 2010 (Source: Koomey). The economic and ecological importance of minimizing that number is fairly obvious. The broad trend towards increasing amounts of computing being performed within large data centres, with consolidated infrastructure, sold as a service rather than a product is undeniable. Of course, the term "cloud computing" is often applied to this phenomenon. That's a term that I try to avoid, as it's fairly ambiguous. There has been considerable effort to reduce wake-ups when idle in software in general, including everything from web browsers to word processors, which is related to the increasing importance of mobile and embedded platforms. However, this effort is most pronounced among developers of software that is expected to be deployed in virtualised environment on many servers, as wake-ups prevent CPUs from entering various idle states that allow them to save electricity, and when these wakeups are multiplied by thousands of VM instances, they add up very quickly. As part of 4CaaSt, a research project funded by the European Commission's Seventh Framework programme, that brings together members of industry and academia with the collective goal of producing an innovative platform-as-as-service offering, I spent time reducing the idle wake-ups per second in PostgreSQL. Postgres services firm 2ndQuadrant, where I work as a database architect, has had the development of several PostgreSQL features sponsored by 4CaaSt in furtherance of that goal, of which this is only one. ## Clang now builds Postgres without additional warnings DevFeed: [Clang now builds Postgres without additional warnings]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2011-08-06T20:14:00Z Content type: opinion Language: en Sources: [Peter Geoghegan's blog]() Topics: [clang](), [PostgreSQL](), [bug](), [gcc]() Tags: [bug](), [c](), [c-plus-plus](), [clang](), [enum](), [gcc](), [postgres](), [postgresql]() ### AI overview The article reports that Clang can build PostgreSQL without additional warnings beyond one warning also produced by GCC. It describes how Clang's diagnostic context helped reveal a potentially dangerous enum-type misuse in PostgreSQL code and notes fixes for other spurious warnings. ### Source excerpt I'm happy to report that as of this evening, Clang builds PostgreSQL without any warnings, apart from a single remaining warning that also occurs when building with GCC, which is actually a bug in GNU Flex that the Flex developers don't seem to want to fix. On GCC 4.6, the warning looks like this: In file included from gram.y:12962:0: scan.c: In function 'yy_try_NUL_trans': scan.c:16246:23: warning: unused variable 'yyg' [-Wunused-variable] With Clang, however, it looks like this: scan.c:16246:23: warning: unused variable 'yyg' [-Wunused-variable] struct yyguts_t * yyg = (struct yyguts_t*)yyscanner; /* This var may be unused depending upon options. */ ^ Note that the "^" is directly underneath the offending variable "yyg" on the terminal emulator that generated this warning. Note also that Clang usefully gives the context of the warning, and as a result a comment is displayed that suggests that the warning is spurious. The Clang developers finally committed a fix to remove spurious warnings that occured when building Postgres as a result of it being statically detected that there are assignments past what appears to be the end of a single element array at the end of a struct. That doesn't happen now, although only under circumstances exactly consistent with the use of a popular idiom that is seen quite a bit in the Postgres code. In working towards removing all Clang warnings, we detected a bug; we were assigning an enum constant from one enum to a variable that was actually another type of enum, which represented a potentially dangerous misuse of an abstraction that the Postgres code uses to represent nodes. This all occurred within a nested macro. Without Clang, it probably would have taken a long time for the problem to be noticed. ## Could Clang displace GCC generally? Part II: Performance of PostgreSQL binaries DevFeed: [Could Clang displace GCC generally? Part II: Performance of PostgreSQL binaries]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2011-07-28T16:12:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [clang](), [gcc](), [PostgreSQL](), [Benchmark](), [cpu](), [Compiler]() Tags: [benchmark](), [c](), [c-plus-plus](), [clang](), [compiler](), [cpu](), [gcc](), [performance](), [postgresql]() ### AI overview This second article in a two-part series compares the performance of PostgreSQL binaries built with Clang and GCC. It uses repeated pgbench runs with identical compiler flags, four connections, and a CPU-focused workload to examine the difference between the two compilers. ### Source excerpt This is the second in a two-part series on Clang. If you haven't already, you'll want to read my original post on the topic, Could Clang displace GCC among PostgreSQL developers? Part I: Intro and compile times. So, what about the performance of PostgreSQL binaries themselves when built with each compiler? I had heard contradictory reports of the performance of binaries built with Clang. In Belgium, Chris Lattner said that Clang built binaries could perform better, but a number of independent benchmarks suggested that Clang was generally behind, with some notable exceptions. I asked 2ndQuadrant colleague and PostgreSQL performance expert Greg Smith to suggest a useful benchmark to serve as a good starting point for comparing Postgres performance when built with Clang to performance when built with GCC. He suggested that I apply Jeff Janes' recent patch for pgbench that he'd reviewed. It stresses the executor, and therefore the CPU quite effectively, rather than table locks or IPC mechanisms. The results of this benchmark were very interesting. Greg provided me with shell access to a beefy server, the same server that he used in his review of Jeff's patch, which added the -P option: http://archives.postgresql.org/message-id/4DFE788F.5020704@2ndQuadrant.com . I hacked together a shell script to run pgbench for this purpose. Binaries were built using GCC and Clang, each with exactly the same flags - Clang accepts the same flags as GCC. To smooth the results out, and to get a conclusive outcome, I decided on 16 10 minute -P runs with 4 connections, that alternated between using each set of binaries, lasting a total of 3 hours. Here's a summary of the results: 1) GCC test: tps = 34.242839 (including connections establishing) 2) Clang test: tps = 34.370732 (including connections establishing) 3) GCC test: tps = 34.186687 (including connections establishing) 4) Clang test: tps = 34.922954 (including connections establishing) 5) GCC test: tps = 32.393383 (including connecti