Deep dive into PostgreSQL internal statistics
summary
Shows how to read PostgreSQL internal counters to pinpoint real causes not vague symptoms, using concrete SQL against pg_stat_activity and pg_stat_statements and extensions like pg_buffercache pg_fincore and pg_prewarm. Explains meaningful thresholds (cache hit above 90 percent, recovered vs timed checkpoints ratio), how to compute replication lag in bytes and seconds, when autovacuum kicks in from dead tuple counts and fillfactor tuning to increase HOT updates, how to find unused indexes by scan counts and which metrics indicate background writer or disk I O pressure.
AI-written summary · about
chapters (21)
- 0:00Introduction
- 1:28What is PostgreSQL stats
- 2:31Goals of the talk
- 5:25Stats challenges
- 7:33Stats sources in PostgreSQL
- 10:45Stats views overview
- 11:50Cache hit ratio
- 12:52Database anomalies
- 14:56Checkpoint behavior
- 17:50Background writers
- 19:13Replication stats
- 21:00Table statistics
- 22:47Write activity
- 25:40Autovacuum queue
- 28:37Index statistics
- 29:42Database activity
- 31:26Bad transactions
- 33:57Locks and deadlocks
- 34:42pg_stat_statements
- 36:26Extra tools
- 37:56Summary
related talks
41mOptimizing your app by understanding your PostgreSQL database
50mPostgreSQL Query Performance Insights
26mTroubleshooting High CPU Utilization in PostgreSQL Databases
51mPostgreSQL Query Performance Insights
47mIdentifying Slow Queries and Fixing Them!
21mA Guided Tour Through Postgres Internals with Elizabeth Garrett Christensen
47mWhat If We Opened PostgreSQL's Hood?
53mVisualizing Postgres I O Performance for Development