Postgres FM Nikolay Samokhvalov and Michael Christofides

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 

Nikolay and Michael discuss several "Don't do this" lists about Postgres — picking out their favourite items, as well as some contentious ones that could be clearer, or not included.
 Here are some links to things they mentioned:
Don’t do this (PostgreSQL wiki page) https://wiki.postgresql.org/wiki/Don't_Do_ThisHow to get into trouble using some Postgres features (how to by Nikolay) https://github.com/postgres-ai/postgres-howtos/blob/main/0016_how_to_get_into_trouble_using_some_postgres_features.mdDon’t do this (jOOQ list) http://www.jooq.org/doc/3.19/manual/reference/dont-do-thisDon’t use NOT IN (jOOQ) https://www.jooq.org/doc/3.19/manual/reference/dont-do-this/dont-do-this-sql-not-in/ Our episode about NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown Our episode on timestamps https://postgres.fm/episodes/timestamps
~~~
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 have a high-level discussion on all things search — touching on full-text search, semantic search, and faceted search. They discuss what comes in Postgres core, what is possible via extensions, and some thoughts on performance vs implementation complexity vs user experience.
 Here are some links to things they mentioned:
Simon Riggs https://www.linkedin.com/feed/update/urn:li:activity:7178702287740022784/Companion databases episode https://postgres.fm/episodes/companion-databasespgvector episode https://postgres.fm/episodes/pgvectorFull Text Search https://www.postgresql.org/docs/current/textsearch.htmlSemantic search https://en.wikipedia.org/wiki/Semantic_searchFaceted search https://en.wikipedia.org/wiki/Faceted_searchFaceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/RUM index https://github.com/postgrespro/rum Hybrid search (Supabase guide) https://supabase.com/docs/guides/ai/hybrid-search Elastic https://www.elastic.co/ GiST indexes https://www.postgresql.org/docs/current/gist.html GIN indexes https://www.postgresql.org/docs/current/gin.html btree_gist https://www.postgresql.org/docs/current/btree-gist.html btree_gin https://www.postgresql.org/docs/current/btree-gin.html pg_trgrm https://www.postgresql.org/docs/current/pgtrgm.html Text Search Types (tsvector and tsquery) https://www.postgresql.org/docs/current/datatype-textsearch.html Postgres full text search with the “websearch” syntax (blog post by Adam Johnson) https://adamj.eu/tech/2024/01/03/postgresql-full-text-search-websearch/Understanding Postgres GIN Indexes: The Good and the Bad (blog post by Lukas Fittl) https://pganalyze.com/blog/gin-index ParadeDB https://www.paradedb.com/ ZomboDB https://www.zombodb.com/ Introduction to Information Retrieval (book by Manning, Raghavan, and Schütze) https://www.amazon.co.uk/Introduction-Information-Retrieval-Christopher-Manning/dp/0521865719 How to build a search engine with Ruby on Rails (blog post by Justin Searls) https://blog.testdouble.com/posts/2021-09-09-how-to-build-a-search-engine-with-ruby-on-rails/
~~~
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 Postgres health checks — what they are, things to include, how often makes sense, and whether improvements to Postgres would increase or decrease the need for them. Here are some links to things they mentioned:
MOT (car test in the UK) https://en.wikipedia.org/wiki/MOT_test Let's make PostgreSQL multi-threaded (discussion started by Heikki) https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4%40iki.fi postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup Why upgrade https://why-upgrade.depesz.com/  Related episodes: Default configuration https://postgres.fm/episodes/default-configurationIndex maintenance https://postgres.fm/episodes/index-maintenanceBloat https://postgres.fm/episodes/bloatMonitoring checklist https://postgres.fm/episodes/monitoring-checklistpg_stat_statements https://postgres.fm/episodes/pg_stat_statementsBackups https://postgres.fm/episodes/backups
~~~
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 the superuser role in PostgreSQL — what it is, how and when it shouldn’t be used, and whether most cloud providers are right to not give us it (no prizes for guessing). Here are some links to things they mentioned:
superuser (docs) https://www.postgresql.org/docs/current/role-attributes.html#id-1.6.9.6.2.1.2.1.1Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/crunchy-data-postgresql-security-technical-implementation-guide-now-availableSupabase docs (unsupported operations) https://supabase.com/docs/guides/database/postgres/roles-superuserCrunchy Data docs https://docs.crunchybridge.com/concepts/usersRDS docs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.htmlCloud SQL docs https://cloud.google.com/sql/docs/postgres/usersAzure docs https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-serversRoles, Privileges, and Security (talk by Ryan Booz) https://www.youtube.com/watch?v=mtPM3iZFE04
~~~
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 transaction_timeout (a recently committed addition for Postgres 17) — what it's for, how to get around not having it already, and whether it will replace the need to set statement_timeout globally in future. Here are some links to things they mentioned:
transaction_timeout (devel docs) https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-TRANSACTION-TIMEOUTCommit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5 Mailing list thread: https://www.postgresql.org/message-id/flat/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com  Hacking Postgres session on Postgres TV where work started https://www.youtube.com/live/WLoMpg8A4WU?t=50 Our first ever episode, on slow queries and slow transactions https://postgres.fm/episodes/slow-queries-and-slow-transactions statement_timeout (v7.3) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT idle_in_transaction_session_timeout (v9.6) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT idle_session_timeout (v14) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT 
~~~
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