Showing posts from May, 2017

Deep dive into postgres stats: pg_stat_user_indexes

Everything you always wanted to know about Postgres statsOne of the major strengths of PostgreSQL are indexes - they are flexible and cover almost all use cases - different access methods allow indexing wide range of data types. However, with these great benefits, there are also some disadvantages:
Obviously, every index consumes disk space and for large tables indexes might be large too, especially indexes based on whole columns, however even in this case PostgreSQL offers special BRIN indexes, but unfortunately they aren't as general purpose as btree indexes and don't fit for majority of use cases.Another, less obvious disadvantage is that indexes existence implies additional write operations when data are updated within tables - all index entries that point to the affected table's rows must be updated too. For some indexes, GIN for example, update operations are very expensive.