Hello.
My name is Vanya, and I am a Java developer. It so happens that I work a lot with PostgreSQL – I handle database configuration, structure optimization, performance, and occasionally dabble in DBA on weekends.
Recently, I tidied up several databases in our microservices and wrote a Java library , which simplifies this work, saves me time, and helps avoid some common mistakes made by developers. This library is what we will discuss today.
Disclaimer
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.
Background
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 . And it all began… 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.
The first problem – default configuration
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, temp_file_limit, statement_timeout and lock_timeout.
In our case maintenance_work_mem it was set by default to 64 MB, while temp_file_limit around 2 GB – we simply lacked memory to create the index on a large table.
Therefore, pg-index-health I compiled a set of , in my opinion, parameters that should be adjusted for each database.
The second problem – duplicate indexes
Our databases live on SSD drives, and we use HA-configuration with multiple data centers, a master host, and n-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.
So, having restored all and having watched , 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 – Postgres will handle it all by itself. These indexes can safely be removed, and for this, a diagnostic was introduced. .
The third problem – overlapping indexes
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, A+B, A+B+C 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. .
The fourth problem – foreign keys without indexes
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… until a certain point…
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 showed that queries of the type:
delete from <table> where id in (…)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.
Help came from the marvelous explain analyze 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 due to the absence of a suitable index. Thus, the diagnostic was born. .
The fifth problem – null values in indexes
By default, Postgres includes null values in btree indexes, but they are generally unnecessary. Therefore, I diligently try to eliminate these nulls (diagnostic ), creating partial indexes on nullable columns like where is not nullIn 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. 🙂
The sixth problem – absence of primary keys
Due to the specifics of the mechanism such a situation may arise where 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...
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’t 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 . And then it turned out that pg_repack it cannot process tables without a primary key or another uniqueness constraint, and our table did not have a primary key. Thus, the diagnosis .
In the version of the library 0.1.5 the ability to collect data on table and index bloat and respond to it in a timely manner was added.
Problems seven and eight – lack of indexes and unused indexes
The next two diagnostics — and – appeared in their final form relatively recently. The thing is, they couldn’t just be added straightforwardly.
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. also needs to be done on each host in the cluster; it cannot be performed only on the master.
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.
In conclusion
Of course, almost all diagnostics can be configured . This way, you can quickly implement checks in your application, preventing new errors from occurring, and then gradually fix the old ones.
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 .
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 or sent to the monitoring system.
I really hope that pg-index-health 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.
Source: habr.com
