Postgres FM

Here are links to a few things we mentioned: 

• pg_stat_statements
• pgFouine
• pgBadger
• pg_query
• pg_stat_activity
• auto_explain
• Can auto_explain (with timing) have low overhead? (blog post by Michael)
• track_io_timing
• pgbench
• PgHero
• pgCenter
• pgwatch2 (Postgres AI edition)
• pg_stat_kcache
• PASH Viewer

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas Google doc.

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: 

• LSN (log sequence number)
• On the impact of full-page writes (blog post by Tomas Vondra)
• Deep dive on Aurora Postgres (talk by Grant McAlister) 
• Netdata monitoring 
• PostgresqlCO.NF 
• pgPedia 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

Here are links to a few things we mentioned: 

• Using EXPLAIN (PostgreSQL documentation)
• explain.depesz.com
• explain.dalibo.com
• pgMustard 
• EverSQL
• pganalyze
• pg_stat_monitor (Extension by Percona)
• Recent thread on hackers mailing list about plan_id in pg_stat_activity
• auto_explain
• EXPLAIN observer effect (Ongres blog post by Álvaro Hernández)
• auto_explain overhead (blog post by Michael)  
• pg_test_timing
• Database Lab Engine (for thin clones)
• Our previous episode on BUFFERS
• EXPLAIN Explained (talk by Josh Berkus)
• A beginner's guide to EXPLAIN (talk by Michael)
• A deeper dive into EXPLAIN (talk by Michael)
• EXPLAIN glossary (pgMustard docs)
• Topic suggestions document

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

And few things we mentioned: 

• Topic request on Reddit — thanks HerbyHoover!
• Haki Benita's blog 
• PostgreSQL documentation (table of contents) 
• Planet PostgreSQL (blog aggregator) 
• MVCC Unmasked (by Bruce Momjian) 
• The Internals of PostgreSQL (by Hironobu SUZUKI)
• PostgreSQL 14 Internals — parts I and II (by Egor Rogov)
• Cybertec blog
• modern-sql.com (by Markus Winand)
• use-the-index-luke.com (by Markus Winand)
• The Art of PostgreSQL (by Dimitri Fontaine) 
• explain.depesz.com
• explain.dalibo.com 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

Monitoring checklist (dashboard 1):

1. TPS and (optional but also desired) QPS
2. Latency (query duration) — at least average. Better: histogram, percentiles
3. Connections (sessions) — stacked graph of session counts by state (first of all: active and idle-in-transaction; also interesting: idle, others) and how far the sum is from max_connection (+pool size for PgBouncer).
4. Longest transactions (max transaction age or top-n transactions by age), excluding autovacuum activity
5. Commits vs rollbacks — how many transactions are rolled back
6. Transactions left till transaction ID wraparound
7. Replication lags / bytes in replication slot / unused replication slots
8. Count of WALs waiting to be archived (archiving lag)
9. WAL generation rates
10. Locks and deadlocks
11. Basic query analysis graph (top-n by total_time or by mean_time?)
12. Basic wait event analysis (a.k.a. “active session analysis” or “performance insights”)

And links to a few things we mentioned: 

• Postgres monitoring review checklist (community document) 
• pgstats.dev
• Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) 
• Transaction ID Wraparound in Postgres (blog post by David Cramer) 
• Subtransactions Considered Harmful (blog post by Nikolay)
• datadoghq.com  
• pgwatch2 (Postgres.ai Edition) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

Here are links to a few things we mentioned: 

• Do you vacuum everyday? (talk by Hannu Krosing)
• Autovacuum tuning (EDB guide) 
• When autovacuum does not vacuum (2ndQuadrant blog post by Tomas Vondra) 
• Autovacuum tuning basics (old 2ndQuadrant blog post)
• Discussion with Anastasia Lubennikova (on RuPostgres, in Russian)  
• B-tree indexes (talk by Anastasia Lubennikova, in English) 
• Discussion with Peter Geoghegan (on Postgres TV)
• pg_repack 
• pg_squeeze 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

Here are links to a few things we mentioned: 

• Three-valued_logic (Wikipedia)
• Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert) 
• Practical SQL for Data Analysis — Interpolation (blog post by Haki Benita) 
• What is the deal with NULLs? (blog post by Jeff Davis) 
• NULL in SQL: Indicating the Absence of Data (Markus Winand on Modern SQL) 
• The Art of PostgreSQL (book by Dimitri Fontaine) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

Here are links to a few things we mentioned: 

• EXPLAIN parameters (PostgreSQL documentation)
• EXPLAIN (ANALYZE) needs BUFFERS (blog post by Nikolay)
• Using BUFFERS for query optimization (blog post by Michael)  
• H3 indexes on PostGIS data (blog post by Ryan Lambert)
• Turning BUFFERS on by default (latest patch)
• pgMustard 
• explain.depesz.com
• explain.dalibo.com
• Database Lab Engine

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

Here are links to the two main resources we mentioned: 

• Paul Ramsey's recent blog post on BRIN indexes
• Tomas Vondra's slides on BRIN index improvements

A few other things we mentioned:

• B-tree Wikipedia page 
• pg_repack 
• pg_squeeze 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork

A well as discussing pros and cons, we mentioned a LOT of different providers and tools, and a few good articles/videos too. 😅

Here are links to most of them, roughly in the order they came up: 

• How Auto Trader migrated its on-prem databases to Cloud SQL
• PostgreSQL Community Panel: Upgradability
• Postgres TV Open Talks
• PostgreSQL Conference Europe
• Hannu Krosing — excellent vacuum talk
• pg_docs_bot — browser extension for getting to the current docs
• Amazon RDS for PostgreSQL
• Google Cloud SQL for PostgreSQL
• Heroku Postgres
• Crunchy Bridge
• Spilo: HA PostgreSQL Clusters with Docker
• Aiven for PostgreSQL
• AlloyDB for PostgreSQL
• Neon
• Yugabyte
• ScaleGrid PostgreSQL Hosting
• StackGres
• Timescale
• OrioleDB
• Citus
• Supabase
• PlanetScale
• pg_stat_kcache
• pg_wait_sampling
• EDB BigAnimal
• Azure Database for PostgreSQL

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the elephant artwork