Hey.
My name is Vanya and I am a Java developer. It so happened that I work a lot with PostgreSQL - I am involved in tuning the database, optimizing the structure, performance and playing a little DBA on weekends.
Recently, I tidied up several databases in our microservices and wrote the pg-index-health java library that facilitates this work, saves my time and helps to avoid some common mistakes made by developers. It is about this library that will be discussed today.
Disclaimer
The main version of PostgreSQL I'm working with is 10. All the SQL queries I use are also checked on the 11th version. The minimum supported version is 9.6.
Background
: . , , . temp_file_limit. … , , , .
–
, Postgres, , , … . , maintenance_work_mem, temp_file_limit, statement_timeout lock_timeout.
maintenance_work_mem 64 , temp_file_limit - 2 – .
pg-index-health , , , .
–
SSD , HA- -, - n- . – ; , CPU. , , , , , .
, , «» . , . . - – «» . , – Postgres . , duplicated_indexes.
–
. , , , . A, A+B, A+B+C .. , . intersected_indexes.
–
Postgres . , … …
: - job’, , «» . CPU IO , , . pg_stat_activity , :
delete from <table> where id in (…)
id , , , . , , , , .
explain analyze , , , sequential scan - . foreign_keys_without_index.
– null value
Postgres null btree-, , , . null’ ( indexes_with_null_values), nullable- where <A> is not null. 1877 16 . 16% ( 4.3 ) null . . :)
–
MVCC Postgres’ , bloat, . , , , , -!!!, … …
. +100 . , . , 15 , , . VACUUM FULL , pg_repack. , pg_repack , . tables_without_primary_key.
0.1.5 bloat’ .
–
— tables_with_missing_indexes unused_indexes – . , .
, , . , - - , . , .
, , .
, . , , .
. , , . .
, bloat, . ClickHouse .
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 problems discovered and offering new diagnostics.