postgres Statistiken: pg_stat_all_tables

Alexey Lesovsky hat bereits eine ganze Reihe interessanter Artikel im Rahmen einer Serie "Deep dive into postgres stats" veröffentlicht. Bisher hatte ich mich davor gedrückt, diese Ausführungen zusammenzufassen, aber heute ist ein ruhiger Tag und die pg_stat_all_tables scheint mir eine besonders interessante Quelle zu sein...

Die View pg_stat_all_tables enthält eine ganze Reihe interessanter Informationen zur Nutzung von Tabellen. Unter anderem weist sie hin auf:

• Zugriffsinformationen: die View enthalt Informationen zur Anzahl sequentieller Scans (also Full Table Scans, wie man sie in anderen RDBMS nennt) und zur Anzahl von Index-Zugriffen sowie zur Anzahl der dabei zurückgelieferten Datensätze. Eine hohe Zahl sequentieller Scans auf großen Tabellen deutet häufig auf fehlende Indizes hin. Weiterhin könnten Index-Zugriffe mit hohen durchschnittlichen Satzanzahlen auf wenig selektive Index-Zugriffe hindeuten.
• Schreiboperationen: zeigt die Menge der DML-Operationen auf den Tabellen. Dabei wird zwischen Standard- und HOT-(=Heap-only tuples) Updates unterschieden, wobei letztere wünschenswert sind, da sie Indizes nicht aktualisieren, wenn ein Update keine inidzierten Werte verändert. Allerdings funktionieren HOT-Updates nur, wenn die zugehörige page ausreichend Platz übrig hat. Für Tabellen, bei denen sich HOT-Updates kaum ergeben, könnte eine Anpassung des Fillfactors sinnvoll sein (der nur für neue Datensätze berücksichtigt wird, den Platzverbrauch des Objekts vergrößert und in diesem Zusammenhang nur dann relevant ist, wenn es tatsächlich updates ohne Bezug auf indizierte Spalten gibt).
• die autovacuum queue: seit 9.6 kann man bestimmte Informationen zum autovacuum aus der View pg_stat_progress_vacuum bekommen. Was fehlt ist allerdings weiterhin eine Liste der Tabellen, für die aktuell ein vacuum-Lauf erforderlich wäre. Dazu hat der Autor eine komplexe Query (die unter anderem auf pg_stat_all_tables zugreift) bereitgestellt, mit deren Hilfe sich die Länge der autovacuum queue bestimmen lässt. Basierend auf den Aussagen der Query kann man dann über Maßnahmen zur Optimierung des autovacuum nachdenken (Erhöhung der autovacuum_max_workers oder Anpassung anderer autovacuum-Parameter).

Im Vergleich zu dem, was andere RDBMS an internen Statistiken liefern, sind die Informationen bei postgres überschaubar, erlauben aber durchaus interessante Analysen.