PODCAST

Postgres FM

Nikolay Samokhvalov and Michael Christofides

A weekly podcast about all things PostgreSQL

technology tech news

All Episodes

40:40

High availability

Postgres FM ·

2023/07/28

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

40:18

Beginner tips

Postgres FM ·

2023/07/21

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 rowsRelated episodes: https://postgres.fm/episodes/how-to-become-a-dba and https://postgres.fm/episodes/hot-updates Tip 2: always use EXPLAIN (ANALYZE,

31:29

Connection poolers

Postgres FM ·

2023/07/14

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)

31:24

Anniversary mailbag

Postgres FM ·

2023/07/07

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

25:38

pg_upgrade: the tricky and dangerous parts

Postgres FM ·

2023/06/30

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:

22:11

UUID

Postgres FM ·

2023/06/23

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=storyUnexpected 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

44:15

Memory

Postgres FM ·

2023/06/16

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.htmlAndres 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)

30:23

Extensions

Postgres FM ·

2023/06/09

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/plrustZomboDB https://github.com/zombodb/zombodb Why is Postgres

28:08

Zero-downtime migrations

Postgres FM ·

2023/06/02

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 and retries (blog post by Nikolay)lock_timeoutFast Column Creation with Defaults (blog post by Brandur)Database Schema

44:24

Parallelism

Postgres FM ·

2023/05/26

Nikolay and Michael discuss parallelism — both parallel query execution and other parallel operations. Here are links to a few things we mentioned: Parallel query (docs)Parallelism in PostgreSQL 11 (talk by Thomas Munro)Parallelism in PostgreSQL 15 (talk by Thomas Munro)Towards Millions TPS (blog post by Alexander Korotkov)Memory resource consumption (docs)Our episode about index maintenanceOur episode about partitioning Patch to make postgres_fdw parallel-safe (by Swarm64) PostgreSQL

42:23

Corruption

Postgres FM ·

2023/05/19

Nikolay and Michael discuss database corruption — various types, how they can come about, and what to do (and not do) if you come across it. Here are links to a few things we mentioned: The dangers of streaming across versions of glibc (TripAdvisor mailing list thread)The floor is Java memeData Corruption talk by Sebastian Webber (on Postgres TV) Data corruption monitoring & troubleshooting talk by Andrey Borodin (on Postgres TV)Postgres data corruption (document from Nikolay) Data

35:01

ChatGPT x  PostgreSQL

Postgres FM ·

2023/05/12

Nikolay and Michael discuss using ChatGPT for Postgres tasks — should you, if so what for, and some things to be mindful of! Here are links to a few things we mentioned: ChatGPTNikolay’s polls on Twitter and on LinkedIn The Art of PostgreSQL (book by Dimitri Fontaine)SQL Performance Explained (book by Markus Winand)Nikolay’s YouTube correction about deletes and index amplificationDon’t use ChatGPT to solve problems (blog post by Christophe Pettus)Query optimization session with ChatGPT,

45:51

pg_stat_statements

Postgres FM ·

2023/05/05

Nikolay and Michael discuss pg_stat_statements — why everyone should use it, but also some downsides! Here are links to a few things we mentioned: pg_stat_statements (docs)pg_stat_statements (PgPedia)PGSQL PhridayObserver effect in pg_stat_statements and pg_stat_kcache (Postgres Hacking session on Postgres TV) track_io_timing (docs)Overhead comment (by Tom Kate, via Jeremy Schneider) pg_stat_monitorPGConOur episode on query analysisMarginalia ~~~What did you like or not like? What should we

37:18

auto_explain

Postgres FM ·

2023/04/28

Nikolay and Michael discuss auto_explain — what it is, how it can help, and how to check it's overhead. Here are links to a few things we mentioned: auto_explain (docs)ScaleGrid guide to auto_explain Can auto_explain, with timing, have low overhead? (Blog post by Michael)pgBadger pg_stat_monitorEXPLAIN ANALYZE may be lying to you (blog post by Álvaro from Ongres)pg_test_timingOur episode on benchmarkingDatabase Lab Engine~~~What did you like or not like? What should we discuss next time

39:48

Queues in Postgres

Postgres FM ·

2023/04/21

Nikolay and Michael discuss queues in Postgres — the pros and cons vs dedicated queuing tools, and some tips for scaling. A couple of apologies-in-advance: Near the end, we incorrectly say "idempotent" when we meant "stateless", and also 50 TPS instead of 500 TPSWe also had a couple of audio issues, sorry!Here are links to a few things we mentioned: Recent discussion on Hacker NewsPgQWhat is SKIP LOCKED (blog post by Craig Ringer) autovacuumPostgres queues (blog post by Brandur)pg_repackOur

39:50

Read-only considerations

Postgres FM ·

2023/04/14

Nikolay and Michael discuss a listener request — special considerations for databases that are used in a read-only mode all day, and get an update at night with additional data.Here are links to a few things we mentioned: Index-only scansVacuumUK Covid-19 dashboardpg_repackPartitioningOur episode on BRIN indexesAlways load sorted data (blog post by Haki Benita)GIN indexes: the good and the bad (blog post by Lukas Fittl)Our episode on materialised viewspg_buffercacheTowards Millions TPS

34:04

Partitioning

Postgres FM ·

2023/04/07

Nikolay and Michael discuss table partitioning — what it is, why and when it's helpful, and some considerations for your partition key. Here are links to a few things we mentioned: Partitioning docspg_partmanIndex maintenance episode Timescale partitioningpg_cronXtreme PostgreSQL (talk by Christophe Pettus)Database Antipatterns (also by Christophe, slides 46-49)Understanding an outage (blog post by Duffel)~~~What did you like or not like? What should we discuss next time? Let us know on

43:15

Peter Zaitsev

Postgres FM ·

2023/03/31

This week we're sharing an edited version of Nikolay's recent interview with Peter Zaitsev from Percona — they discuss MySQL vs Postgres, Percona’s success, open source licenses, FerretDB, and databases on Kubernetes… phew! And here are some links to a few things mentioned: PerconapgCloudHacker browser extension PMMPercona Distribution for PostgreSQLFerretDBPeter's Twitter profilePeter's LinkedIn profile------------------------What did you like or not like? What should we discuss next time

31:12

psql vs GUIs

Postgres FM ·

2023/03/24

Nikolay and Michael discuss command line and graphical user interfaces for Postgres — what they are, some tips and tricks for learning, and what we each use and prefer. Here are links to a few things we mentioned: psql (docs)psql is awesome! (talk by Lætitia Avrot)psql tips (site by Lætitia Avrot)pgAdminPostico DBeaverDataGripPgManage (new Command Prompt fork of OmniDB) PopSQLpostgres_dbapspgMaterialized views episodepgcli------------------------What did you like or not like? What should we

40:18

Upgrades

Postgres FM ·

2023/03/17

Nikolay and Michael discuss major and minor version Postgres upgrades — what they are, how often they come out, and how regularly we should be upgrading. Here are links to a few things we mentioned: Postgres versioning policy why-upgrade (by depesz)postgresqlco.nf (by Ongres)postgresql.conf comparison (by Rustproof Labs) pg_upgradeLogical replication CHECKPOINTamcheckLocale data changes (e.g. glibc upgrades)ANALYZEUpgrades are hard (summary of panel discussion by Andreas 'ads'

176 results

Postgres FM

All Episodes

Similar Podcasts

Lead With AI

Tamara Nall

Linux in the Ham Shack

Black Sparrow Media

Data Engineering Podcast

Tobias Macey

Eye On A.I.

Craig S. Smith

Reflections

Livewire Labs