{"id":72875,"date":"2020-03-05T08:42:09","date_gmt":"2020-03-05T05:42:09","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika"},"modified":"2020-03-05T08:42:09","modified_gmt":"2020-03-05T05:42:09","slug":"zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"The health of indexes in PostgreSQL from the perspective of a Java developer","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hello.<\/p>\n

<\/p>\n

My name is Vanya, and I am a Java developer. It so happens that I work a lot with PostgreSQL \u2013 I handle database configuration, structure optimization, performance, and occasionally dabble in DBA on weekends.<\/p>\n

<\/p>\n

Recently, I tidied up several databases in our microservices and wrote a Java library pg-index-health<\/a><\/noindex>, which simplifies this work, saves me time, and helps avoid some common mistakes made by developers. This library is what we will discuss today.<\/p>\n

<\/p>\n

\"The<\/p>\n

<\/p>\n

Disclaimer<\/h3>\n

<\/p>\n

The main version of PostgreSQL I am working with is 10. All the SQL queries I use have also been tested on version 11. The minimum supported version is 9.6.<\/p>\n

<\/p>\n

Background<\/h3>\n

<\/p>\n

It all started almost a year ago with a strange situation for me: concurrent index creation in a straightforward environment ended with an error. The index, as usual, was left in an invalid state in the database. Analyzing the logs showed a lack of temp_file_limit<\/a><\/noindex>. And it all began\u2026 Digging deeper, I discovered a whole slew of problems in the database configuration, and rolling up my sleeves, with gleam in my eyes, I set about fixing them.<\/p>\n

<\/a><\/noindex><\/p>\n

The first problem \u2013 default configuration<\/h3>\n

<\/p>\n

Perhaps the metaphor about Postgres being able to run on a coffee maker is getting quite old, but... the default configuration really raises a number of questions. At the very least, one should pay attention to maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> and lock_timeout<\/strong>.<\/p>\n

<\/p>\n

In our case maintenance_work_mem<\/strong> it was set by default to 64 MB, while temp_file_limit<\/strong> around 2 GB \u2013 we simply lacked memory to create the index on a large table.<\/p>\n

<\/p>\n

Therefore, pg-index-health<\/strong> I compiled a set of key<\/a><\/noindex>, in my opinion, parameters that should be adjusted for each database.<\/p>\n

<\/p>\n

The second problem \u2013 duplicate indexes<\/h3>\n

<\/p>\n

Our databases live on SSD drives, and we use HA<\/em>-configuration with multiple data centers, a master host, and n<\/em>-an array of replicas. Disk space is a very valuable resource for us; it is no less important than performance and CPU consumption. Therefore, on one hand, we need indexes for fast reads, but on the other hand, we don't want to see unnecessary indexes in the database, as they consume space and slow down data updates.<\/p>\n

<\/p>\n

So, having restored all invalid indexes<\/a><\/noindex> and having watched reports from Oleg Bartunov<\/a><\/noindex>, I decided to carry out a \"great\" cleanup. It turned out that developers do not like to read the database documentation. They really do not. Because of this, two typical mistakes arise - a manually created index on the primary key and a similar \"manual\" index on the unique column. The thing is, they are unnecessary \u2013 Postgres will handle it all by itself. These indexes can safely be removed, and for this, a diagnostic was introduced. duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

The third problem \u2013 overlapping indexes<\/h3>\n

<\/p>\n

Most novice developers create indexes on a single column. Gradually, as they gain more experience, they start optimizing their queries and adding more complex indexes that include multiple columns. This is how indexes on columns like A<\/em>, A+B<\/em>, A+B+C<\/em> and so on appear. The first two of these indexes can safely be discarded, as they are prefixes of the third. This also significantly saves disk space, and there is a diagnostic for this. intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

The fourth problem \u2013 foreign keys without indexes<\/h3>\n

<\/p>\n

Postgres allows the creation of foreign key constraints without specifying a supporting index. In many situations, this is not a problem and may even go unnoticed\u2026 until a certain point\u2026<\/p>\n

<\/p>\n

That was the case with us: at some point in time, a scheduled job that cleans the database of test orders started to 'stack' master hosts. CPU and IO spiked, queries were sluggish and timing out, the service was throwing 500 errors. A quick analysis pg_stat_activity<\/a><\/noindex> showed that queries of the type:<\/p>\n

<\/p>\n

delete from <table> where id in (…)<\/code><\/pre>\n
<\/p>\n
At the same time, the index on id in the target table was, of course, present, and entries were deleted based on a condition quite minimally. It seemed that everything should work, but alas, it did not.<\/p>\n
<\/p>\n
Help came from the marvelous explain analyze<\/strong> which showed that besides deleting entries in the target table, there was also a check of referential integrity happening, and on one of the related tables, this check fell into a sequential scan<\/em> due to the absence of a suitable index. Thus, the diagnostic was born. foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
The fifth problem \u2013 null values in indexes<\/h3>\n
<\/p>\n
By default, Postgres includes null values in btree indexes, but they are generally unnecessary. Therefore, I diligently try to eliminate these nulls (diagnostic indexes_with_null_values<\/a><\/noindex>), creating partial indexes on nullable columns like where  is not null<\/code>In this way, I managed to reduce the size of one of our indexes from 1877 MB to 16 KB. In one of the services, the total database size decreased by 16% (by 4.3 GB in absolute terms) due to the elimination of null values from the indexes. A colossal saving of disk space with rather simple modifications. \ud83d\ude42 <\/p>\n
<\/p>\n
The sixth problem \u2013 absence of primary keys<\/h3>\n
<\/p>\n
Due to the specifics of the mechanism MVCC in Postgres<\/a><\/noindex> such a situation may arise where bloat<\/a><\/noindex>the size of your table quickly grows due to a large number of dead tuples. I naively thought this would not affect us, as we are considered decent developers... How foolish and naive I was...<\/p>\n
<\/p>\n
One fine day, a wonderful migration updated all records in a large and actively used table. We ended up with an additional 100 GB in table size out of nowhere. It was incredibly frustrating, but our troubles didn\u2019t end there. After 15 hours, the autovacuum on this table completed, and it became clear that the physical space would not return. We couldn't stop the service and perform VACUUM FULL, so we decided to use pg_repack<\/a><\/noindex>. And then it turned out that pg_repack<\/em> it cannot process tables without a primary key or another uniqueness constraint, and our table did not have a primary key. Thus, the diagnosis tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
In the version of the library 0.1.5<\/strong> the ability to collect data on table and index bloat and respond to it in a timely manner was added.<\/p>\n
<\/p>\n
Problems seven and eight \u2013 lack of indexes and unused indexes<\/h3>\n
<\/p>\n
The next two diagnostics \u2014 tables_with_missing_indexes<\/a><\/noindex> and unused_indexes<\/a><\/noindex> \u2013 appeared in their final form relatively recently. The thing is, they couldn\u2019t just be added straightforwardly.<\/p>\n
<\/p>\n
As I mentioned, we are using a configuration with multiple replicas, and the read load on different hosts is fundamentally different. As a result, it happens that some tables and indexes on certain hosts are hardly used, and to analyze this, statistics need to be collected from all hosts in the cluster. Resetting statistics<\/a><\/noindex> also needs to be done on each host in the cluster; it cannot be performed only on the master.<\/p>\n
<\/p>\n
This approach allowed us to save several tens of gigabytes by removing indexes that were never used, as well as adding the missing indexes on rarely used tables.<\/p>\n
<\/p>\n
In conclusion<\/h3>\n
<\/p>\n
Of course, almost all diagnostics can be configured an exception list<\/a><\/noindex>. This way, you can quickly implement checks in your application, preventing new errors from occurring, and then gradually fix the old ones.<\/p>\n
<\/p>\n
Some diagnostics can already be performed in functional tests right after applying the database migrations. And this is perhaps one of the most powerful features of my library. You can see an example of usage in demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
Checks for unused or missing indexes, as well as for bloat, should only be performed on a real database. The collected values can be recorded in ClickHouse<\/a><\/noindex> or sent to the monitoring system.<\/p>\n
<\/p>\n
I really hope that pg-index-health<\/strong> will be useful and in demand. You can also contribute to the development of the library by reporting any issues you discover and suggesting new diagnostics.<\/p>\n
Source: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u041f\u0440\u0438\u0432\u0435\u0442. \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u0430\u043d\u044f, \u0438 \u044f Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a. \u0422\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u043e\u0441\u044c, \u0447\u0442\u043e \u044f \u043c\u043d\u043e\u0433\u043e \u0440\u0430\u0431\u043e\u0442\u0430\u044e \u0441 PostgreSQL \u2013 \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0441\u044c \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u043e\u0439 \u0411\u0414, \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0435\u0439 \u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u044b, \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u0438 \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u0438\u0433\u0440\u0430\u044e \u0432 DBA \u043f\u043e \u0432\u044b\u0445\u043e\u0434\u043d\u044b\u043c. \u0417\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u0435 \u0432\u0440\u0435\u043c\u044f \u044f \u043f\u0440\u0438\u0432\u0451\u043b \u0432 \u043f\u043e\u0440\u044f\u0434\u043e\u043a \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u043d\u0430\u0448\u0438\u0445 \u043c\u0438\u043a\u0440\u043e\u0441\u0435\u0440\u0432\u0438\u0441\u0430\u0445 \u0438 \u043d\u0430\u043f\u0438\u0441\u0430\u043b java-\u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a\u0443 pg-index-health, \u043a\u043e\u0442\u043e\u0440\u0430\u044f \u043e\u0431\u043b\u0435\u0433\u0447\u0430\u0435\u0442 \u044d\u0442\u0443 \u0440\u0430\u0431\u043e\u0442\u0443, \u044d\u043a\u043e\u043d\u043e\u043c\u0438\u0442 \u043c\u043e\u0451 \u0432\u0440\u0435\u043c\u044f \u0438 \u043f\u043e\u043c\u043e\u0433\u0430\u0435\u0442 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":72876,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-72875","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Index Health in PostgreSQL from a Java Developer's Perspective | ProHoster","description":"Hi. My name is Vanya, and I am a Java developer.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0417\u0434\u043e\u0440\u043e\u0432\u044c\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0432 PostgreSQL \u0433\u043b\u0430\u0437\u0430\u043c\u0438 Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0430 | ProHoster","og:description":"\u041f\u0440\u0438\u0432\u0435\u0442. \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u0430\u043d\u044f, \u0438 \u044f Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-05T05:42:09+00:00","article:modified_time":"2020-03-05T05:42:09+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"72875","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:43:29","updated":"2022-09-27 23:10:08","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}