Postgres FM

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

The podcast discusses successful Postgres-related startups and their funding, as well as the growth of the Postgres ecosystem. They highlight new content sources like Postgres TV, Tobias Petry's tips, Hussein NASA's YouTube channel, and the Postgres weekly newsletter. The hosts delve into middleware options and the benefits of application side sharding. They explore the potential threat to Postgres and the concept of branching in databases, specifically in PostgreSQL. Lastly, they discuss the latest features in Postgres, serverless use cases, and the OOTP project for native database branching.

The podcast discusses the importance of row estimates in Postgres query planner. Topics include join strategies, auto vacuum, optimizing row estimates and statistics target, the problem of row estimates, and materialized views, indexes, and hints.

The hosts discuss the various options for replication in PostgreSQL, including physical and logical replication. They explore offloading reads, implementing sticky reads, and using replicas for read-only parts of applications. They mention risks and downsides of using Postgres for replication, limitations and future improvements of logical replication, and differences between physical and logical replication in Postgres.