Postgres FM

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_This
• How 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.md
• Don’t do this (jOOQ list) http://www.jooq.org/doc/3.19/manual/reference/dont-do-this
• Don’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.ai
• Michael Christofides, founder of pgMustard

With 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-databases
• pgvector episode https://postgres.fm/episodes/pgvector
• Full Text Search https://www.postgresql.org/docs/current/textsearch.html
• Semantic search https://en.wikipedia.org/wiki/Semantic_search
• Faceted search https://en.wikipedia.org/wiki/Faceted_search
• Faceting 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.ai
• Michael Christofides, founder of pgMustard

With 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-configuration
  • Index maintenance https://postgres.fm/episodes/index-maintenance
  • Bloat https://postgres.fm/episodes/bloat
  • Monitoring checklist https://postgres.fm/episodes/monitoring-checklist
  • pg_stat_statements https://postgres.fm/episodes/pg_stat_statements
  • Backups 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.ai
• Michael Christofides, founder of pgMustard

With 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.1
• Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/crunchy-data-postgresql-security-technical-implementation-guide-now-available
• Supabase docs (unsupported operations) https://supabase.com/docs/guides/database/postgres/roles-superuser
• Crunchy Data docs https://docs.crunchybridge.com/concepts/users
• RDS docs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html
• Cloud SQL docs https://cloud.google.com/sql/docs/postgres/users
• Azure docs https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-servers
• Roles, 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.ai
• Michael Christofides, founder of pgMustard

With 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-TIMEOUT
• Commit: 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Michael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together — where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better.
 
Here are some links to things they mentioned:

• Planet Argon survey https://rails-hosting.com/2022/#databases
• Active Record https://guides.rubyonrails.org/active_record_basics.html
• PostgreSQL specific usage of Active Record https://guides.rubyonrails.org/active_record_postgresql.html
• Multiple Databases with Active Record https://guides.rubyonrails.org/active_record_multiple_databases.html
• schema.rb vs structure.sql https://blog.appsignal.com/2020/01/15/the-pros-and-cons-of-using-structure-sql-in-your-ruby-on-rails-application.html
• activerecord-clean-db-structure (Ruby gem by Lukas Fittl) https://github.com/lfittl/activerecord-clean-db-structure
• GitLab’s migration_helpers.rb https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/migration_helpers.rb
• SQLite https://www.sqlite.org
• PlanetScale’s foreign key support announcement video https://twitter.com/PlanetScale/status/1732070818958500083
• DoorDash Engineering Blog https://doordash.engineering/blog
• rails-pg-extras https://github.com/pawurb/rails-pg-extras
• Benoit Tigeot testing Peter Geoghegan improvement for large IN lists https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
• High Performance PostgreSQL for Rails (Andy’s book, 35% discount code “postgres.fm”) https://pragprog.com/titles/aapsql/high-performance-postgresql-for-rails
• Andy’s blog and website https://andyatkinson.com

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss a common question — why Postgres isn't using an index, and what you can do about it!
 
Here are some links to things they mentioned:

• Why isn’t Postgres using my index? (blog post by Michael) https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index 
• Why isn’t Postgres using my functional index? (Stack Exchange question from Brent Ozar) https://dba.stackexchange.com/questions/336019/why-isnt-postgres-using-my-functional-index  
• enable_seqscan (and similar parameters) https://www.postgresql.org/docs/current/runtime-config-query.html 
• Crunchy Bridge changed random_page_cost to 1.1 https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1 
• Make indexes invisible (trick from Haki Benita) https://hakibenita.com/sql-tricks-application-dba#make-indexes-invisible 
• ANALYZE https://www.postgresql.org/docs/current/sql-analyze.html
• Statistics used by the planner https://www.postgresql.org/docs/current/planner-stats.html 
• Our episode on query hints https://postgres.fm/episodes/query-hints 
• transaction_timeout (commit for Postgres 17) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5 
• What’s new in the Postgres 16 query planner / optimizer (blog post by David Rowley) https://www.citusdata.com/blog/2024/02/08/whats-new-in-postgres-16-query-planner-optimizer/ 

~~~

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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Nikolay and Michael discuss the overhead of pg_stat_statements and pg_stat_kcache — mostly focusing on some interesting things Nikolay found while stress-testing some large spot instances up to and beyond 2m TPS(!) 
 
Here are some links to things they mentioned:

• pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
• pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
• pg_stat_statements overhead estimate in pganalyze FAQs https://pganalyze.com/faq
• What is the performance impact of pg_stat_statements? (DBA Stack Exchange answer with several links) https://dba.stackexchange.com/questions/303503/what-is-the-performance-impact-of-pg-stat-statements
• Towards Millions TPS (blog post by Alexander Korotkov) https://akorotkov.github.io/blog/2016/05/09/scalability-towards-millions-tps
• Postgres.ai blog post with links to benchmarks https://postgres.ai/blog/20240127-postges-ai-bot
• postgresql_cluster https://github.com/vitabaks/postgresql_cluster
• pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
• pg_stat_kcache issue, discussion, and fast resolution https://github.com/powa-team/pg_stat_kcache/issues/41 
• log_statement_sample_rate https://postgresqlco.nf/doc/en/param/log_statement_sample_rate/ 
• auto_explain.sample_rate https://www.postgresql.org/docs/current/auto-explain.html#id-1.11.7.14.5.3.13.1.3
• pg_stat_statements performance penalty on Intel much higher than on AMD https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/52

~~~

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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Michael is joined by Markus Winand, creator of use-the-index-luke.com and modern-sql.com, and author of SQL Performance Explained, to discuss Modern SQL — what Markus means by it, why it's important, some benefits, some examples, and at least one phrase that should be on a t-shirt.
 
Here are some links to things they mentioned:

• Modern SQL (site) https://modern-sql.com
• Use the index, Luke! (site) https://use-the-index-luke.com
• SQL Performance Explained (book) https://sql-performance-explained.com
• Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
• IS DISTINCT FROM (null-safe not equals comparison) https://modern-sql.com/caniuse/is-distinct-from
• UNIQUE NULLS DISTINCT https://modern-sql.com/caniuse/unique-nulls-distinct
• Modernes SQL ist mehr als SELECT * FROM (a German language podcast Markus was on recently) https://engineeringkiosk.dev/podcast/episode/99-modernes-sql-ist-mehr-als-select-from-mit-markus-winand
• Markus’ website https://winand.at

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Michael is joined by Chelsea Dole, Staff Software Engineer and Tech Lead of the Data Storage Team at Brex, to discuss bloat. They cover what it is, why it's a problem, strategies for minimising it, and the options when it's really bad.
 
Here are some links to things they mentioned:

• Managing your tuple graveyard (talk by Chelsea) https://www.youtube.com/watch?v=gAgbzvGT6ck
• pgstattuple https://www.postgresql.org/docs/current/pgstattuple.html 
• pg_class https://www.postgresql.org/docs/current/catalog-pg-class.html 
• Bloat in PostgreSQL: a taxonomy (talk by Peter Geoghegan) https://youtube.com/watch?v=JDG4bMHxCH8 
• It's Not You, It's Me: Breaking Up with Massive Tables via Partitioning (talk by Chelsea) https://youtube.com/watch?v=TafwSuLNxe8 
• pg_repack https://github.com/reorg/pg_repack
• pg_squeeze https://github.com/cybertec-postgresql/pg_squeeze 
• VACUUM https://www.postgresql.org/docs/current/sql-vacuum.html
• autovacuum https://www.postgresql.org/docs/current/runtime-config-autovacuum.html 
• CLUSTER https://www.postgresql.org/docs/current/sql-cluster.html 
• HOT updates https://www.postgresql.org/docs/current/storage-hot.html 
• Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default 
• Our episode on TOAST https://postgres.fm/episodes/toast 
• Our episode on index maintenance https://postgres.fm/episodes/index-maintenance 
• Chelsea's website: https://chelseadole.com/

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the amazing artwork