Postgres FM Nikolay Samokhvalov and Michael Christofides

Nikolay and Michael discuss Postgres minor releases — how the schedule works, options for upgrading to them, and the importance of reading the release notes. Here are some links to things they mentioned:
PostgreSQL 16.3, 15.7, 14.12, 13.15, and 12.19 released (announcement) https://www.postgresql.org/about/news/postgresql-163-157-1412-1315-and-1219-released-2858/ PostgreSQL versioning policy https://www.postgresql.org/support/versioning/ PostgreSQL 14.4 release notes (most recent minor release not on the usual schedule) https://www.postgresql.org/docs/release/14.4/ Minor release roadmap https://www.postgresql.org/developer/roadmap/ Our last episode on upgrades (major and minor) https://postgres.fm/episodes/upgrades  All versions of Postgres https://bucardo.org/postgres_all_versions.html Why upgrade? (Useful tool by depesz) https://why-upgrade.depesz.com/ Stop and start Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-faster WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning  Postgres CVE-2024-4317 and how to fix the system views (5 mins of Postgres by Lukas Fittl) https://youtube.com/watch?v=fLwVvJ3fKdA Our episode on NULL https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown What should we do for episode 100? https://www.reddit.com/r/PostgreSQL/comments/1cn8ajh/what_should_we_do_for_episode_100_of_postgres_fm/ 
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
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 

Nikolay and Michael discuss custom and generic planning in prepared statements — how it works, how issues can present themselves, some ways to view the generic plan, and some benefits of avoiding planning (not just time).  Here are some links to things they mentioned:
PREPARE https://www.postgresql.org/docs/current/sql-prepare.html track_activity_query_size https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE plan_cache_mode https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE EXPLAIN (GENERIC_PLAN) https://www.postgresql.org/docs/current/sql-explain.html#id-1.9.3.148.8 EXPLAIN (GENERIC_PLAN) in PostgreSQL 16 (blog post by Laurenz from Cybertec)  https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/ Running EXPLAIN on any query, even with $1 parameters (blog post and video by Lukas Fittl of pganalyze) https://pganalyze.com/blog/5mins-postgres-explain-generic-plan EXPLAIN from pg_stat_statements, how to get the generic plan (blog post by Franck Pashto of Yugabyte) https://dev.to/yugabyte/explain-from-pgstatstatements-normalized-queries-how-to-always-get-the-generic-plan-in--5cfi Rework query relation permission checking (commit by Amit Langote) https://git.postgresql.org/gitweb/postgres.git?p=postgresql.git;a=commit;h=a61b1f74823c9c4f79c95226a461f1e7a367764b Partition pruning, prepared statements and generic vs custom query plans (a follow up blog post and video by Lukas) https://pganalyze.com/blog/5mins-postgres-partition-pruning-prepared-statements-generic-vs-custom-query-plans Our episode on over-indexing (inc LWLock discussion) https://postgres.fm/episodes/over-indexing “The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/  
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
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 

Nikolay and Michael discuss LIMIT in Postgres — what it does, how it can help with performance, and an interesting example where adding it can actually hurt performance(!) Here are some links to things they mentioned:
LIMIT considered harmful in PostgreSQL (Twitter thread by Christophe Pettus) https://twitter.com/Xof/status/1413542818673577987 LIMIT and OFFSET (docs) https://www.postgresql.org/docs/current/queries-limit.html No OFFSET (by Markus Winand) https://use-the-index-luke.com/no-offset LIMIT clause (docs) https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT 
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
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 

Nikolay and Michael return to the topic of using the buffers explain parameter — with a new analogy, some (conspiracy) theories of why it's still not on by default, and some related chat about the serialize parameter coming in 17. Here are some links to things they mentioned:
BUFFERS by default (episode 4) https://postgres.fm/episodes/buffers-by-default Lightning talk by Michael at pgDay Paris (5 mins) https://www.youtube.com/watch?v=WfY-mSpUzaQ&t=1470s Waiting for SERIALIZE https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/ 
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
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 

Nikolay and Michael discuss doing massive DELETE operations in Postgres — what can go wrong, how to prevent major issues, and some ideas to minimise their impact. Here are some links to things they mentioned:
Article based on Nikolay’s talk, including batching implementation (translated to English) https://habr-com.translate.goog/en/articles/523536/?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hist=true Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningEgor Rogov’s book on Postgres Internals (chapter 10 on WAL) https://edu.postgrespro.com/postgresql_internals-14_en.pdf full_page_writes https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES TRUNCATE https://www.postgresql.org/docs/current/sql-truncate.html Our episode on partitioning https://postgres.fm/episodes/partitioning Our episode on bloat https://postgres.fm/episodes/bloat Our episode on index maintenance https://postgres.fm/episodes/index-maintenance 
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
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 

Nikolay and Michael are joined by Sai Srirampur, CEO and Co-founder of PeerDB, to discuss how to overcome several logical replication issues. They discuss the protocol versions, minimising slot growth, minimising lag, and some tips and tricks for scaling things well. Here are some links to things they mentioned:
PeerDB https://www.peerdb.io/Our episode on logical replication https://postgres.fm/episodes/logical-replicationExploring versions of the Postgres logical replication protocol (blog post by Sai) https://blog.peerdb.io/exploring-versions-of-the-postgres-logical-replication-protocol Logical Streaming Replication Protocol https://www.postgresql.org/docs/current/protocol-logical-replication.htmlREPLICA IDENTITY FULL https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITYOn the performance impact of REPLICA IDENTITY FULL in Postgres (blog post by Xata) https://xata.io/blog/replica-identity-full-performance max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE Active Active in Postgres 16 (blog post by Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16 pgEdge https://www.pgedge.com/DistSQL (term used by Mark Callaghan) https://smalldatum.blogspot.com/2023/07/keeping-up-with-sql-dbms-market.html Five tips on Postgres logical decoding (blog post by Sai) https://blog.peerdb.io/five-tips-on-postgres-logical-decodingPG Slot Notify: Monitor Postgres Slot Growth in Slack (blog post by PeerDB) https://blog.peerdb.io/pg-slot-notify-monitor-postgres-slot-growth-in-slack
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
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