Postgres FM

Nikolay and Michael discuss wait events — what they are, why we have them, and how to use them to help diagnose performance issues. Here are links to a few things we mentioned: Wait events table (docs)9.6 release notesPostgreSQL Scalability (blog post by Alexander Korotkov)Wait event analysis in pganalyzeauto_explainDatabase Lab Enginetrack_io_timingpg_test_timingpgBadgerCorootOkmeterpgwatch2 Postgres.ai Editionpg_wait_samplingpgsentinelDatadogAWS RDS docs for PostgreSQL wait eventspgMustard newsletterPASH ViewerpgCenterIntro to query optimisation episodeMonitoring checklist episode------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Hussein Nasser talks about rows per page. Peter Zaitsev discusses Postgres TV. Topics include TOAST, data storage and limitations, LZ-Boys compression, toast limitations in PostgreSQL, optimizing column ordering, and exploring the impact of medium-size text in a database.

Nikolay and Michael discuss JSON, options for storing it in Postgres, JSON functions available, JSONB indexing, popularity and advantages of PostgreSQL, configuring toast value threshold in Postgres, and performance penalties for medium-sized data.

The podcast discusses real-time analytics in Postgres, including the challenges of integration and the importance of partitioning and analytics databases. They explore materialized views in real-time analytics and discuss estimates and Autovacuum for performance optimization in Postgres.

Guests Alexander Korotkov, Andres Freund, and Nikolay discuss the importance of benchmarking in databases. They cover topics like conducting experiments, system limits, query optimization, and realistic workloads. They also mention specific tools like PG bench and JMeter, as well as case studies and references to books on system performance.

Nikolay and Michael discuss the default config — some tools and principles you can use to customise it, as well as several parameters you probably always want to change.Here are links to a few things we mentioned: shared_buffersAndres Freund tweets about shared_buffers PGTune Leopard Cybertec Configuratorpg_stat_statementsJIT configurationpostgresqlco.nfannotated.confOtterTunework_memrandom_page_costmax_connectionsWhat to logmax_wal_sizeWAL and checkpoint tuning episode effective_cache_sizeIntro to Performance Tuning and Optimization (EDB guide)max_parallel_workers_per_gather ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: The Cost of Cloud, a Trillion Dollar Paradox (blog post from Andreessen Horowitz)OVHHetznerpostgresql_clusterWhy we're leaving the cloud (blog post by DHH from Basecamp)Managed services vs. DIY episodeec2instances.infoVantagePostgres TV episode with Everett Berry from VantageMigrating to Aurora: easy except the bill (blog post by Kimberly Nicholls from Gridium)Database Lab EnginePostgres.ai consultingNetdatasysbenchfioQuery macro analysis episodeTop queries by buffers (Gist from Michael)------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG)pgAudit (site)pgAudit (repo)noset (extension)SplunkKibanaTimescaleCREATE TRIGGER docsTransition table triggers (blog post by David Fetter)Table Audit (blog post by Lorenzo Alberton)Row change auditing options (blog post by CYBERTEC)Hydra founders interview (on Postgres TV)max_slot_wal_keep_sizeeBPFBuilding a perf-like tool for PostgreSQL (talk by Ronan Dunklau)Party tricks for PostgreSQL: perf, ftrace and bpftrace (talk by Dmitry Dolgov)------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: pg_dump pg_restore  pgcopydb (tool by Dimitri Fontaine)  pg_dump and external snapshots (blog post by Michael Paquier) Systems Performance (book by Brendan Gregg)  Performance troubleshooting methodologies (two part talk by Brendan Gregg)  pg_basebackup  wal-g pgBackRest Database Lab Engine  Database branching episode  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: The Internals of PostgreSQL chapter 5 (book by Hironobu SUZUKI) PostgreSQL 14 internals chapter 7 (book by Egor Rogov)  Transaction ID Wraparound (blog post from Sentry) What We Learned from the Recent Mandrill Outage (blog post from Mailchimp) How to simulate transaction ID wraparound (blog post by Prashant Dixit)Add 64-bit XIDs into PostgreSQL 16 (commitfest entry)  Partitioning (docs)  Consider using VACUUM’s INDEX_CLEANUP option (tweet by Peter Geoghan) Add wraparound failsafe to VACUUM (commit) Do you vacuum everyday? (talk by Hannu Krosing) Multixacts wraparound monitoring (mailing list thread) Subtransactions Considered Harmful (blog post by Nikolay) Buffer management in PostgreSQL (talk by Alexander Korotkov) OrioleDB pageinspectpg_hexedit  pg_visibility  Visibility Map (docs)------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the elephant artwork