Postgres FM

Latest Episodes:

Our favourite v16 feature Sep 08, 2023

Show notes

Nikolay and Michael discuss their favourite feature each from the upcoming PostgreSQL 16 release.
Here are some links to some things they mentioned:

v16 draft release notes https://www.postgresql.org/docs/16/release-16.html
PGSQL Phriday #012 invitation from Ryan Booz https://www.pgsqlphriday.com/2023/08/pgsql-phriday-012/
Subscribe options for the podcast https://postgres.fm/subscribe
A recent closed source ClickHouse feature https://github.com/ClickHouse/ClickHouse/issues/44767#issuecomment-1683293218
Postgres TV hacking session with Andrey Borodin on \watch with limited number of loops (v16) https://www.youtube.com/watch?v=vTV8XhWf3mo
Allow \watch queries to stop on minimum rows returned (v17) https://github.com/postgres/postgres/commit/f347ec76e2a227e5c5b5065cce7adad16d58d209
pg_stat_io commit mentioning the op_bytes column (v16) https://github.com/postgres/postgres/commit/a9c70b46dbe152e094f137f7e6ba9cd3a638ee25
pg_size_pretty function https://www.postgresql.org/docs/current/functions-admin.html#id-1.5.8.33.9.3.2.2.7.1.1.1
Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI
Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default
EXPLAIN (GENERIC_PLAN) blog post by Laurenz Albe https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/
Running EXPLAIN on any query (video by Lukas Fittl) https://www.youtube.com/watch?v=CMftYJnqou0
PostgreSQL 16 Beta 1 New Features with Examples.(English Version) by Noriyoshi Shinoda https://twitter.com/nori_shinoda/status/1664481483355226114
Have auto_explain's log_verbose mode honor the value of compute_query_id (commitfest entry) https://commitfest.postgresql.org/42/4136/
Make auto_explain print the query identifier in verbose mode (commit) https://github.com/postgres/postgres/commit/9d2d9728b8d546434aade4f9667a59666588edd6

~~~

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

Connections Sep 01, 2023

Show notes

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

Decoupled storage and compute Aug 25, 2023

Show notes

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

Self-managing Aug 18, 2023

Show notes

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

Sharding Aug 11, 2023

Show notes

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

Data types Aug 04, 2023

Show notes

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

High availability Jul 28, 2023

Show notes

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

Beginner tips Jul 21, 2023

Show notes

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

Connection poolers Jul 14, 2023

Show notes

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

Anniversary mailbag Jul 07, 2023

Show notes

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

Our TOPPODCAST Picks

Follow Us

Stay Connected

Related Podcasts

Links

Stay Connected