postgres.fm: Recent Episodes
Nikolay Samokhvalov and Michael Christofides
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
- 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 in PostgreSQL — what it is, 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)
- 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
textand 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 amazing 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 , 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
- 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
Lonely Nikolay discusses the performance aspects of using UUID for primary keys.
Here are links to a few things I mentioned:
- "postgresql" posts on HN, most popular last week (Algolia search): https://hn.algolia.com/?dateRange=pastWeek&page=0&prefix=true&query=postgresql&sort=byPopularity&type=story
- Unexpected downsides of UUID keys in PostgreSQL (a post by Ants Aasma, Cybertec): https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/
- HN discussion of that post: https://news.ycombinator.com/item?id=36429986
- Additional math by me (converting to bytes): https://twitter.com/samokhvalov/status/1671962111092850689
- Updated RFC4122 (proposal): https://github.com/ietf-wg-uuidrev/rfc4122bis
- Status of that proposal: https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/
- Patch UUID v7 (commitfest record): https://commitfest.postgresql.org/43/4388/
- Postgres hacking with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8 (where that patch was developed)
~~~
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
Nikolay and Michael discuss memory in PostgreSQL — how it works, important settings, and how to go about tuning them.
Here are links to a few things we mentioned:
- Resource Consumption (PostgreSQL docs) https://www.postgresql.org/docs/current/runtime-config-resource.html
- Andres Freud tweet about shared_buffers https://twitter.com/AndresFreundTec/status/1438912583554113537
- Henrietta (Hettie) Dombrovskaya https://hdombrovskaya.wordpress.com/about-the-author/
- annotated.conf (by ash Berkus) https://github.com/jberkus/annotated.conf
- Our episode about checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
- Our episode about BUFFERS https://postgres.fm/episodes/buffers-by-default
- Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266#memory-usage
- Tuning memory parameters for Aurora PostgreSQL https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.BestPractices.Tuning-memory-parameters.html
- RDS for PostgreSQL memory https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.html#PostgreSQL.Tuning.concepts.memory
- EDB guide by Via Fearing https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization#resourceusage
- pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
- pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.html
- Process and Memory Architecture chapter (from Hironobu SUZUKI) https://www.interdb.jp/pg/pgsql02.html
- PostgreSQL 14 internals PDF book from Egor Rogov (pages 37, 184) https://edu.postgrespro.com/postgresql_internals-14_en.pdf
- src/backend/storage/buffer/README https://github.com/postgres/postgres/blob/master/src/backend/storage/buffer/README
- pg_backend_memory_contexts (PostgreSQL 14+) https://www.postgresql.org/docs/current/view-pg-backend-memory-contexts.html
- pg_stat_io (coming in PostgreSQL 16) https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW
- pg_prewarm https://www.postgresql.org/docs/current/pgprewarm.html
- Configuring work_mem blog post https://www.pgmustard.com/blog/work-mem
~~~
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
Nikolay and Michael discuss Postgres extensions — what they are, how they affect your decisions around Postgres, and some things to keep in mind when using them.
Here are links to a few things we mentioned:
- Extensions (docs) https://www.postgresql.org/docs/current/external-extensions.html
- Extension (pgPedia) https://pgpedia.info/e/extension.html
- pgvector https://github.com/pgvector/pgvector
- PL/Rust https://github.com/tcdi/plrust
- ZomboDB https://github.com/zombodb/zombodb
- Why is Postgres popular episode https://postgres.fm/episodes/why-is-postgres-popular
- Citus https://github.com/citusdata/citus
- TimescaleDB https://github.com/timescale/timescaledb
- OrioleDB https://github.com/orioledb/orioledb
- PostGIS https://trac.osgeo.org/postgis/
- “There’s an extension for that” (tweet from Robert Treat) https://twitter.com/robtreat2/status/1665735485883314178
- RDS supported extensions https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html
- RUM https://github.com/postgrespro/rum
- pg_repack https://github.com/reorg/pg_repack
- PGXN https://pgxn.org/
- pgTrunk by CoreDB https://pgtrunk.io/
- Dbdev by Supabase https://supabase.com/blog/dbdev
- StackGres https://github.com/ongres/stackgres
- pg_tle by AWS https://github.com/aws/pg_tle
- Modern Postgres monitoring (slides from Nikolay’s tutorial) https://twitter.com/samokhvalov/status/1664686535562625034
- Awesome Postgres https://github.com/dhamaniasad/awesome-postgres
~~~
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
Nikolay and Michael discuss zero-downtime schema migrations — why they're a challenge, a variety of different cases, and some things you can do to achieve them.
Here are links to a few things we mentioned:
- Comparison of JOINS: MongoDB vs. PostgreSQL (blog post by Michael Stonebraker and Álvaro Hernández)
- Common DB schema change mistakes (blog post by Nikolay)
- lock_timeout
- Fast Column Creation with Defaults (blog post by Brandur)
- Database Schema Changes Without Downtime (new version of blog post by Braintree)
- Zero-downtime Postgres migrations - the hard parts (blog post from GoCardless)
- GitLab migration_helpers.rb
- GitLab migration style guide
- dblab
~~~
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
Apologies, Michael's audio is not great in this, we'll hopefully be back to normal next week!
Here are links to a few things we mentioned:
- Materialized views (docs)
- Refresh materialized view (docs)
- Timescale blog post
- PlanetScale Boost (content warning: MySQL)
- Incremental Materialized Views with pg_ivm (video by Lukas Fittl)
- Articles on how to do your own incremental updates(?)
- Materialize (company)
- Materialize talk
- Incremental View Maintenance (Postgres wiki)
- Implementing Incremental View Maintenance (mailing list thread)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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
Here are links to a few things we mentioned:
- Heap-only tuple updates (docs)
- README file (source code)
- Heap Only Tuple chapter (Internals of PostgreSQL by Hironobu SUZUKI)
- How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay)
- Why Uber switched from Postgres to MySQL (blog post)
- Fighting write amplification with HOT updates (Adyen blog post)
- HOT Updates vs Bottom-Up Index Deletion (video by Lukas Fittl from pganalyze)
- Reduced Bloat with Bottom-Up Deletion (blog post by Hamid Akthar from Percona)
- What is fillfactor and how does it affect performance? (blog post by Kaarel Moppel from Cybertec)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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
Here are links to a few things we mentioned:
- Database Lab Engine
- Overview of Aurora cloning
- PlanetScale branching
- Common DB schema change mistakes (blog post by Nikolay)
- Supabase vision
- Neon branching
- Shift-left testing
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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
Here are links to a few things we mentioned:
- A brief history of Version Control Systems (blog post by Mehran Hajirajabi)
- Redgate Source Control for Oracle (and SQL Server)
- Flyway
- Sqitch
- Liquibase
- pgAdmin Schema Diff
- Migra
- PostgresCompare
- Schema based versioning and deployment (blog post by Valentine Gogichashvili)
- Change management tools and techniques (PostgreSQL Wiki)
- GitLab migration_helpers.rb
- Database schema changes without downtime (blog post by James Coleman from Braintree/PayPal)
- Zero-downtime Postgres schema migrations need lock_timeout and retries (blog post by Nikolay)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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
Here are links to a few things we mentioned:
- PostgreSQL 14 coin (upside down!)
- Intro to Planner Hacking (talk by Melanie Plageman)
- Demystifying contributing to PostgreSQL (talk by Lætitia Avrot)
- How to become a PostgreSQL contributor (blog post by Aleksander Alekseev)
- Compile and install from source code (PostgreSQL Wiki)
- PostgreSQL mailing lists
- GitHub PostgreSQL mirror
- GitLab PostgreSQL mirror
- Commitfests
- So, you want to be a developer? (PostgreSQL Wiki)
- Resolving the search engine issue (mailing list thread)
- Planet PostgreSQL
- pg_stat_monitor (extension by Percona)
- ZomboDB
- pgx (framework for developing extensions)
- Awesome Postgres
- Depesz blog
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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
Here are links to a few things we mentioned:
- Extending SQL
- PostgREST
- PL/pgSQL
- EdgeDB
- pgTAP
- Sqitch
- Flyway
- Liquibase
- client_min_messages
- log_min_messages
- RAISE DEBUG
- Simplify: move code into database functions (blog post by Derek Sivers)
- PL/sh
- Supabase
- auto_explain.log_nested_statements
- Hasura
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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
Here are links to a few things we mentioned:
- PostgreSQL 15 release notes
- Speeding up sort performance in Postgres 15 (blog post by David Rowley)
- Past, Present, and Future of Logical Replication (talk by Amit Kapila)
- Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert)
- Do you vacuum everyday? (talk by Hannu Krosing)
- Why upgrade PostgreSQL? (by depesz)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas 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
Here are links to a few things we mentioned:
- pg_plan_advsr
- How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay)
- Why Uber Switched from Postgres to MySQL (blog post)
- pganalyze index advisor
- Nancy bot (project is not active)
- pgreplay
- pgreplay go
- Real Application Testing on YugabyteDB with pgreplay (blog post by Franck Pachot)
- pg_query
- Database Lab thin clones
- Migrating to Aurora: easy except the bill (blog post by Kimberley Nicholls)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas 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
This episode was badly affected by internet issues. Hopefully the edit came out ok, but the quality should be back to a better level from next week.
Here are links to a few things we mentioned:
- Monthly blog event, PSQL Phriday (blog post from Ryan Booz)
- Who or what made Postgres cool? (tweet from Kenneth Cassel)
- PostGIS
- Acquisition of Sun by Oracle
- DB-Engines trend
- Hacker News hiring trends
- Supabase on GitHub (nearly 40k stars)
- How I Built This (podcast)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas 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
Important correction from this episode: amcheck promises no false positives, not no false negatives, sorry!
Here are links to a few things we mentioned:
- pgstattuple
- pg_repack
- postgres-checkup
- Workload Analysis for GitLab.com
- Bloat Analysis (also from GitLab)
- Bloat, pg_repack, and deferred constraints (blog post by Miro)
- amcheck
- Peter Geoghegan interview (on Postgres TV)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas 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
Here are links to a few things we mentioned:
- pg_stat_statements
- pgFouine
- pgBadger
- pg_query
- pg_stat_activity
- auto_explain
- Can auto_explain (with timing) have low overhead? (blog post by Michael)
- track_io_timing
- pgbench
- PgHero
- pgCenter
- pgwatch2 (Postgres AI edition)
- pg_stat_kcache
- PASH Viewer
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas 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
Here are links to a few things we mentioned:
- LSN (log sequence number)
- On the impact of full-page writes (blog post by Tomas Vondra)
- Deep dive on Aurora Postgres (talk by Grant McAlister)
- Netdata monitoring
- PostgresqlCO.NF
- pgPedia
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides 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
Here are links to a few things we mentioned:
- Using EXPLAIN (PostgreSQL documentation)
- explain.depesz.com
- explain.dalibo.com
- pgMustard
- EverSQL
- pganalyze
- pg_stat_monitor (Extension by Percona)
- Recent thread on hackers mailing list about plan_id in pg_stat_activity
- auto_explain
- EXPLAIN observer effect (Ongres blog post by Álvaro Hernández)
- auto_explain overhead (blog post by Michael)
- pg_test_timing
- Database Lab Engine (for thin clones)
- Our previous episode on BUFFERS
- EXPLAIN Explained (talk by Josh Berkus)
- A beginner's guide to EXPLAIN (talk by Michael)
- A deeper dive into EXPLAIN (talk by Michael)
- EXPLAIN glossary (pgMustard docs)
- Topic suggestions document
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides 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
And few things we mentioned:
- Topic request on Reddit — thanks HerbyHoover!
- Haki Benita's blog
- PostgreSQL documentation (table of contents)
- Planet PostgreSQL (blog aggregator)
- MVCC Unmasked (by Bruce Momjian)
- The Internals of PostgreSQL (by Hironobu SUZUKI)
- PostgreSQL 14 Internals — parts I and II (by Egor Rogov)
- Cybertec blog
- modern-sql.com (by Markus Winand)
- use-the-index-luke.com (by Markus Winand)
- The Art of PostgreSQL (by Dimitri Fontaine)
- explain.depesz.com
- explain.dalibo.com
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides
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
Monitoring checklist (dashboard 1):
- TPS and (optional but also desired) QPS
- Latency (query duration) — at least average. Better: histogram, percentiles
- Connections (sessions) — stacked graph of session counts by state (first of all: active and idle-in-transaction; also interesting: idle, others) and how far the sum is from max_connection (+pool size for PgBouncer).
- Longest transactions (max transaction age or top-n transactions by age), excluding autovacuum activity
- Commits vs rollbacks — how many transactions are rolled back
- Transactions left till transaction ID wraparound
- Replication lags / bytes in replication slot / unused replication slots
- Count of WALs waiting to be archived (archiving lag)
- WAL generation rates
- Locks and deadlocks
- Basic query analysis graph (top-n by total_time or by mean_time?)
- Basic wait event analysis (a.k.a. “active session analysis” or “performance insights”)
And links to a few things we mentioned:
- Postgres monitoring review checklist (community document)
- pgstats.dev
- Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund)
- Transaction ID Wraparound in Postgres (blog post by David Cramer)
- Subtransactions Considered Harmful (blog post by Nikolay)
- datadoghq.com
- pgwatch2 (Postgres.ai Edition)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides
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
Here are links to a few things we mentioned:
- Do you vacuum everyday? (talk by Hannu Krosing)
- Autovacuum tuning (EDB guide)
- When autovacuum does not vacuum (2ndQuadrant blog post by Tomas Vondra)
- Autovacuum tuning basics (old 2ndQuadrant blog post)
- Discussion with Anastasia Lubennikova (on RuPostgres, in Russian)
- B-tree indexes (talk by Anastasia Lubennikova, in English)
- Discussion with Peter Geoghegan (on Postgres TV)
- pg_repack
- pg_squeeze
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides
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
Here are links to a few things we mentioned:
- Three-valued_logic (Wikipedia)
- Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert)
- Practical SQL for Data Analysis — Interpolation (blog post by Haki Benita)
- What is the deal with NULLs? (blog post by Jeff Davis)
- NULL in SQL: Indicating the Absence of Data (Markus Winand on Modern SQL)
- The Art of PostgreSQL (book by Dimitri Fontaine)
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides
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
Here are links to a few things we mentioned:
- EXPLAIN parameters (PostgreSQL documentation)
- EXPLAIN (ANALYZE) needs BUFFERS (blog post by Nikolay)
- Using BUFFERS for query optimization (blog post by Michael)
- H3 indexes on PostGIS data (blog post by Ryan Lambert)
- Turning BUFFERS on by default (latest patch)
- pgMustard
- explain.depesz.com
- explain.dalibo.com
- Database Lab Engine
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides
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
Here are links to the two main resources we mentioned:
- Paul Ramsey's recent blog post on BRIN indexes
- Tomas Vondra's slides on BRIN index improvements
A few other things we mentioned:
- B-tree Wikipedia page
- pg_repack
- pg_squeeze
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides
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
A well as discussing pros and cons, we mentioned a LOT of different providers and tools, and a few good articles/videos too. 😅
Here are links to most of them, roughly in the order they came up:
- How Auto Trader migrated its on-prem databases to Cloud SQL
- PostgreSQL Community Panel: Upgradability
- Postgres TV Open Talks
- PostgreSQL Conference Europe
- Hannu Krosing — excellent vacuum talk
- pg_docs_bot — browser extension for getting to the current docs
- Amazon RDS for PostgreSQL
- Google Cloud SQL for PostgreSQL
- Heroku Postgres
- Crunchy Bridge
- Spilo: HA PostgreSQL Clusters with Docker
- Aiven for PostgreSQL
- AlloyDB for PostgreSQL
- Neon
- Yugabyte
- ScaleGrid PostgreSQL Hosting
- StackGres
- Timescale
- OrioleDB
- Citus
- Supabase
- PlanetScale
- pg_stat_kcache
- pg_wait_sampling
- EDB BigAnimal
- Azure Database for PostgreSQL
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides
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
- Introducing Postgres FM
- What counts as a slow query? How should we monitor for them?
- What about transactions?
What did you like or not like? What would you like to hear us discuss in future? Let us know by tweeting us on @samokhvalov and @michristofides
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai
- Michael Christofides, founder of pgMustard