Postgres FM

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 amazing 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.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 @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 amazing 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 amazing 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 amazing artwork 

The podcast discusses the importance of BRIN indexes in Postgres and compares their performance to B-tree indexes. The concept of correlation in indexes is explored, along with the use of hidden column CTID to determine correlation. The podcast also emphasizes the need for indexes to reduce I/O operations and highlights the potential of the improved BRIN index for UID versions data. Additionally, the importance of row numbers and upgrade recommendations are discussed.

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 SQLPostgreSQL Community Panel: UpgradabilityPostgres TV Open TalksPostgreSQL Conference EuropeHannu Krosing — excellent vacuum talkpg_docs_bot — browser extension for getting to the current docsAmazon RDS for PostgreSQLGoogle Cloud SQL for PostgreSQLHeroku PostgresCrunchy BridgeSpilo: HA PostgreSQL Clusters with DockerAiven for PostgreSQLAlloyDB for PostgreSQLNeonYugabyteScaleGrid PostgreSQL HostingStackGresTimescaleOrioleDBCitusSupabasePlanetScalepg_stat_kcachepg_wait_samplingEDB BigAnimalAzure 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 @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 amazing artwork 

The hosts discuss the importance of performance in Postgres, including slow queries and slow transactions. They emphasize the need for monitoring long transactions and transaction ID wraparound, as well as setting timeouts to prevent issues and degradation. They also highlight the benefits of setting statement timeouts in PostgreSQL for operations like big data migration or schema changes.