Postgres FM

This episode was badly affected by internet issues. Hopefully the edit came out ok, but the quality should be back to a better level from next week.Here are links to a few things we mentioned: Monthly blog event, PGSQL Phriday (blog post from Ryan Booz) Who or what made Postgres cool? (tweet from Kenneth Cassel) PostGIS Acquisition of Sun by Oracle DB-Engines trendHacker News hiring trends Supabase on GitHub (nearly 40k stars)How I Built This (podcast) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

The podcast discusses the importance of index maintenance in PostgreSQL databases, exploring bloat analysis, redundant indexes, and the relationship between index usage and bloat. The hosts emphasize the negative impact of not using indexes, caution against certain tools due to potential bugs, and highlight the use of vacuum in parallel and the importance of partitioning for efficient database maintenance.

Here are links to a few things we mentioned: pg_stat_statementspgFouinepgBadgerpg_querypg_stat_activityauto_explainCan auto_explain (with timing) have low overhead? (blog post by Michael)track_io_timingpgbenchPgHeropgCenterpgwatch2 (Postgres AI edition)pg_stat_kcachePASH 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

This podcast dives deep into the topics of WAL and checkpoint tuning in Postgres. It covers the purpose of checkpoints, the importance of triggering manual checkpoints before server restart, the various uses of the write ahead log, tuning and controlling processes in PostgreSQL, the concept of checkpoint timeout, and the importance of checking and understanding Postgres parameters.

This podcast discusses query optimization in Postgres, including the use of 'explain' command, analyzing query performance, common mistakes in query optimization, and the impact of enabling timing on in Postgres. It also emphasizes the importance of query optimization on performance and invites listener engagement for future topics.

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 blogmodern-sql.com (by Markus Winand)use-the-index-luke.com (by Markus Winand)The Art of PostgreSQL (by Dimitri Fontaine) explain.depesz.comexplain.dalibo.com ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the elephant artwork

Monitoring checklist (dashboard 1):TPS and (optional but also desired) QPSLatency (query duration) — at least average. Better: histogram, percentilesConnections (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).Longest transactions (max transaction age or top-n transactions by age), excluding autovacuum activityCommits vs rollbacks — how many transactions are rolled backTransactions left till transaction ID wraparoundReplication lags / bytes in replication slot / unused replication slotsCount of WALs waiting to be archived (archiving lag)WAL generation ratesLocks and deadlocksBasic query analysis graph (top-n by total_time or by mean_time?)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.devImproving 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 @michristofidesIf 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.aiMichael Christofides, founder of pgMustardWith 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 @michristofidesIf 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the elephant artwork

The podcast discusses challenges and new features in handling NULL values in databases, including a new feature in Postgres 15. They also share experiences with coding issues in survey data and the importance of handling null values in SQL queries. The chapter highlights resources for learning, emphasizes the importance of testing, and mentions the benefits of Postgres 11 in updating billion row tables.

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.comexplain.dalibo.comDatabase Lab Engine------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the elephant artwork