Postgres FM

Latest Episodes:

Logical replication common issues Apr 12, 2024

Show notes

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-replication
Exploring 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.html
REPLICA IDENTITY FULL https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY
On 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-decoding
PG 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.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the elephant artwork

Don't do this Apr 05, 2024

Show notes

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

Search Mar 29, 2024

Show notes

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

Health check Mar 22, 2024

Show notes

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

superuser Mar 15, 2024

Show notes

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

transaction_timeout Mar 08, 2024

Show notes

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

Rails + Postgres Mar 01, 2024

Show notes

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

Why isn't Postgres using my index? Feb 23, 2024

Show notes

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

Overhead of pg_stat_statements and pg_stat_kcache Feb 16, 2024

Show notes

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

Modern SQL Feb 02, 2024

Show notes

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

Our TOPPODCAST Picks

Follow Us

Stay Connected

Related Podcasts

Links

Stay Connected