Postgres FM

Nikolay and Michael discuss connections — the options, security and performance tradeoffs, and a few other things to be aware of.
 
Here are some links to some things they mentioned:

• Episode on connection poolers https://postgres.fm/episodes/connection-poolers 
• listen_addresses https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-LISTEN-ADDRESSES 
• SSLMODE Explained (blog post by Andrew Kane) https://ankane.org/postgres-sslmode-explained 
• pg_hba.conf https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
• Timing a query (blog post by Bruce Momjian) https://momjian.us/main/blogs/pgblog/2012.html#June_6_2012 
• How to connect (blog post by Lætitia Avrot) https://mydbanotebook.org/post/cant-connect/ 
• Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462 
• idle_session_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT 
• Do you vacuum everyday? (talk by Hannu Krosing) https://www.youtube.com/watch?v=JcRi8Z7rkPg 
• Don’t use now() with pg_stat_activity (tweet by Nikolay) https://twitter.com/samokhvalov/status/1664981076014690304 

~~~

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 a listener question — about products that take Postgres and transform it to something that decouples compute from storage (RDS Aurora, GC AlloyDB, Neon etc.) and whether they see something like this landing upstream in the medium term.

Here are some links to some things they mentioned:

• Amazon Aurora https://aws.amazon.com/rds/aurora/
• Google Cloud AlloyDB for PostgreSQL https://cloud.google.com/alloydb
• Neon https://neon.tech/ 
• Google Cloud Spanner https://cloud.google.com/spanner
• Is Aurora PostgreSQL really faster and cheaper than RDS PostgreSQL? (blog post by Avinash Vallarapu from MigOps) https://www.migops.com/blog/is-aurora-postgresql-really-faster-and-cheaper-than-rds-postgresql-benchmarking/ 
• Deep dive on Amazon Aurora with PostgreSQL compatibility (presentation by Grant McAllister) https://www.youtube.com/watch?v=HQg8wqlxefo 
• Intro to Aurora PostgreSQL Query Plan Management https://aws.amazon.com/blogs/database/introduction-to-aurora-postgresql-query-plan-management/ 
• Michael Stonebraker Turing Award Lecture https://www.youtube.com/watch?v=BbGeKi6T6QI
• Interview with Stas Kelvich from Neon on Postgres TV https://www.youtube.com/watch?v=4PUKNznq_eM 
• Interview with Ben Vandiver from Google Cloud Spanner on Postgres TV https://www.youtube.com/watch?v=BW-Uexhv-bk 
• Timescale Cloud bottomless storage feature (data tiering to Amazon S3) https://www.timescale.com/blog/expanding-the-boundaries-of-postgresql-announcing-a-bottomless-consumption-based-object-storage-layer-built-on-amazon-s3/ 
• Testing Database Changes the Right Way (Heap Analytics article) https://www.heap.io/blog/testing-database-changes-right-way  

~~~

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 self-managing Postgres — both the practicalities of doing so, as well as some managed-service style tooling. 
 
Here are some links to some things they mentioned:

• Our episode on Managed services vs. DIY https://postgres.fm/episodes/managed-services-vs-diy 
• WAL-G https://github.com/wal-g/wal-g 
• pgBackRest https://pgbackrest.org/ 
• Barman https://github.com/EnterpriseDB/barman 
• Dead Man’s Snitch https://deadmanssnitch.com/ 
• Netdata https://www.netdata.cloud/ 
• Upgrades https://postgres.fm/episodes/upgrades  
• High availability https://postgres.fm/episodes/high-availability 
• Configuration https://postgres.fm/episodes/default-configuration 
• Corruption https://postgres.fm/episodes/corruption 
• Connection poolers https://postgres.fm/episodes/connection-poolers 
• Index maintenance https://postgres.fm/episodes/index-maintenance 
• StackGres supported extensions (Michael was wrong, it also has a timescale_tls extension!) https://stackgres.io/extensions/ 
• postgresql_cluster https://github.com/vitabaks/postgresql_cluster 
• Supabase self-hosting https://supabase.com/docs/guides/self-hosting
• Tembo https://github.com/tembo-io/tembo 
• Open source licenses, clouds, Postgres (Postgres TV discussion) https://www.youtube.com/watch?v=1rcbyIjA4gI&t=149s 

~~~

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 sharding Postgres — what it means, why and when it's needed, and the available options right now. 
 
Here are some links to some things they mentioned:

• PGSQL Friday monthly blogging event https://www.pgsqlphriday.com/
• Did “sharding” come from Ultima Online? https://news.ycombinator.com/item?id=23438399 
• Our episode on partitioning: https://postgres.fm/episodes/partitioning
• Vitess https://vitess.io/
• Citus https://www.citusdata.com/ 
• Lessons learned from sharding Postgres (Notion 2021) https://www.notion.so/blog/sharding-postgres-at-notion 
• The Great Re-shard (Notion 2023) https://www.notion.so/blog/the-great-re-shard 
• The growing pains of database architecture (Figma 2023) https://www.figma.com/blog/how-figma-scaled-to-multiple-databases/
• Timescale multi-node https://docs.timescale.com/self-hosted/latest/multinode-timescaledb/about-multinode/ 
• PgCat https://github.com/postgresml/pgcat 
• SPQR https://github.com/pg-sharding/spqr 
• PL/Proxy https://plproxy.github.io/ 
• Sharding GitLab by top-level namespace https://about.gitlab.com/handbook/engineering/development/enablement/data_stores/database/doc/root-namespace-sharding.html 
• Loose foreign keys (GitLab) https://docs.gitlab.com/ee/development/database/loose_foreign_keys.html 

~~~

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 data types in PostgreSQL — including system types, choosing between types, types provided by extensions, and more. 
 
Here are some links to some things they mentioned:

• Data Types (docs) https://www.postgresql.org/docs/current/datatype.html 
• 10 tips for beginners https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners 
• Tid Scan (explain glossary) https://www.pgmustard.com/docs/explain/tid-scan 
• Don’t do this (wiki) https://wiki.postgresql.org/wiki/Don't_Do_This 
• Boundless `text` and back again https://brandur.org/text 
• UUID episode https://postgres.fm/episodes/uuid 
• I use ENUM (30min talk by Boriss Mejías) https://archive.fosdem.org/2021/schedule/event/postgresql_i_use_enum_vindicating_the_underdog_of_data_types/ 
• Peter Geoghegan tweet https://twitter.com/petervgeoghegan/status/1680275871905775616 
• JSON episode https://postgres.fm/episodes/json 
• pg_repack reorder columns discussion https://github.com/reorg/pg_repack/issues/101 
• Use bigint https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default

~~~

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 HA (high availability) — what it means, tools and techniques for maximising it, while going through some of the more common causes of downtime. 
 
Here are some links to some things they mentioned:

• https://en.wikipedia.org/wiki/High_availability
• https://postgres.fm/episodes/upgrades   
• https://github.com/shayonj/pg_easy_replicate/ 
• pg_easy_replicate discussion on Hacker News https://news.ycombinator.com/item?id=36405761  
• https://postgres.fm/episodes/connection-poolers  
• https://www.postgresql.org/docs/current/libpq.html  
• Support load balancing in libpq (new feature in Postgres 16)
  https://commitfest.postgresql.org/42/3679/ 
• target_session_attrs options for high availability and scaling (2021; a post by Laurenz Albe) https://www.cybertec-postgresql.com/en/new-target_session_attrs-settings-for-high-availability-and-scaling-in-postgresql-v14/
• Postgres 10 highlight - read-write and read-only mode of libpq (2016, a post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/ 
• Postgres 10 highlight - Quorum set of synchronous standbys (2017, a post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-10-quorum-sync/
• https://github.com/zalando/patroni  
• https://postgres.fm/episodes/replication  
• https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default 
• Zero-downtime Postgres schema migrations need this: lock_timeout and retries (2021) https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries 
• A fix in Patroni to mitigate a very long shutdown attempt when archive_command has a lot of WALs to archive https://github.com/zalando/patroni/pull/2067 

~~~

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

Nikolay and Michael discuss 10 beginner tips Nikolay recently shared — they go into a bit more detail on each, and even disagree a little on one or two! 
 
Here are some links to some things they mentioned:

• Nikolay’s tweet with all 10 tips: https://twitter.com/samokhvalov/status/1679953049899642880 
• Tip 1: tuples are physical versions of rows
• Related episodes: https://postgres.fm/episodes/how-to-become-a-dba and https://postgres.fm/episodes/hot-updates 
• Tip 2: always use EXPLAIN (ANALYZE, BUFFERS)
• Related episode: https://postgres.fm/episodes/buffers-by-default 
• Tip 3: throw away pgAdmin
• Related episode: https://postgres.fm/episodes/psql-vs-guis  
• Tip 4: enable as much logging as you can afford
• Related episode: https://postgres.fm/episodes/default-configuration 
• Tip 5: install pg_stat_statements
• Related episodes: https://postgres.fm/episodes/pg_stat_statements and https://postgres.fm/episodes/auto_explain, and https://postgres.fm/episodes/macro-query-analysis-intro
• Tip 6: run experiments on realistic data sets (use thin cloning and branching)
• Related episode: https://postgres.fm/episodes/database-branching 
• Tip 7: make sure data checksums are enabled 
• Related episode: https://postgres.fm/episodes/corruption  
• Tip 8: tune autovacuum to run frequently and move faster
• Related episode: https://postgres.fm/episodes/vacuum
• Tip 9: query optimization will eventually be more important than configuration tuning
• Related episodes: https://postgres.fm/episodes/intro-to-query-optimization and https://postgres.fm/episodes/102-query-optimization
• Tip 10: indexes need to be rebuilt, unfortunately, since their health decline over time
• Related episode: https://postgres.fm/episodes/index-maintenance  
• Bonus tip: the official documentation, release notes, and source code comments/readmes are your friends! 

~~~

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 connection poolers — when and why we need them, the king that is PgBouncer, and the many new pretenders to the throne.
 
Here are links to a few things they mentioned: 

• max_connections https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS 
• Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462 
• PgBouncer https://github.com/pgbouncer/pgbouncer
• Odyssey https://github.com/yandex/odyssey
• PgCat https://github.com/postgresml/pgcat 
• Adopting PgCat: A Nextgen Postgres Proxy https://www.instacart.com/company/how-its-made/adopting-pgcat-a-nextgen-postgres-proxy/ 
• Supavisor https://github.com/supabase/supavisor 
• pgagroal https://github.com/agroal/pgagroal
• PgBouncer is useful, important, and fraught with peril (blog post from JP Camara) https://jpcamara.com/2023/04/12/pgbouncer-is-useful.html 

~~~

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 celebrate the podcast's 1 year anniversary by going through several questions and suggestions received over the year.
 
Here are the questions and some links to things we mentioned: 

• Question 1: Effect of wal_log_hints=on after bulk Deletes: Why next select runs slow and generated tons of WAL? https://twitter.com/dmx551/status/1598253188926570496 
• wal_log_hints https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LOG-HINTS 
• Exploring how SELECT Queries can produce disk writes https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6f

• Question 2: How to get started reading PostgreSQL source code. Maybe a PostgreSQL style C reference guide to consult with for non C programmers https://twitter.com/andatki/status/1578088843940593678 
• So, you want to be a developer? https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
• GitHub search https://github.com/search?q=repo%3Apostgres%2Fpostgres+wal_log_hints&type=code 
• The Internals of PostgreSQL (by Hironobu SUZUKI) https://www.interdb.jp/pg/ 
• PostgreSQL 14 Internals (by Egor Rogov) https://postgrespro.com/community/books/internals 

• Question 3: Isolation Levels Primer/Strategies — their uses in different scenarios, battle tested strategies and insights, performance tradeoffs, edge cases to consider at scale (with replication and sharding, etc.) I remember reading some interesting stuff on the jepsen analysis https://jepsen.io/analyses/postgresql-12.3 about Postgres's Serializable Isolation Level behaving more like Snapshot Isolation. Has this type of behavior or another one similar to this affected you or your clients in any significant way?
• Transaction Isolation https://www.postgresql.org/docs/current/transaction-iso.html
• What developers find surprising about Postgres transactions https://blog.lawrencejones.dev/isolation-levels/  

• Question 4: Data encryption in Postgres
• Cybertec Transparent Data Encryption https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/ 
• EDB Transparent Data Encryption https://www.enterprisedb.com/docs/tde/latest/

• Question 5: Migration from other DBMSs
• PostgreSQL transition/migration guide https://github.com/postgresql-transition-guide/guide  

• Question 6: Latest failover best practices
• Patroni https://github.com/zalando/patroni

~~~

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 (alone, again) reveals some issues that might hit those who perform major PostgreSQL upgrades with minimal downtime.

Links:
- "Upgrades" – PostgresFM episode 037: https://postgres.fm/episodes/upgrades
- recovery_target_lsn: https://postgresqlco.nf/doc/en/param/recovery_target_lsn/
- recovery_target_action: https://postgresqlco.nf/doc/en/param/recovery_target_action/
- pg_easy_replicate https://github.com/shayonj/pg_easy_replicate/
- HN discussion of this tool: https://news.ycombinator.com/item?id=36405761
- Waiting for Postgres 16: Logical decoding on standbys: https://pganalyze.com/blog/5mins-postgres-16-logical-decoding
- pg_upgrade and logical replication (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud
- allow upgrading publisher node (proposal to natively support running pg_upgrade on publisher, WIP): https://commitfest.postgresql.org/43/4273/
- ❓🤔 pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption? (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/CAM527d8heqkjG5VrvjU3Xjsqxg41ufUyabD9QZccdAxnpbRH-Q%40mail.gmail.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!

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

With special thanks to:

• Jessie Draws for the amazing artwork