PODCAST

Postgres FM

Nikolay Samokhvalov and Michael Christofides

A weekly podcast about all things PostgreSQL

technology tech news

All Episodes

30:28

superuser

Postgres FM ·

2024/03/15

Nikolay and Michael discuss the superuser role in PostgreSQL — what it is, how and when it shouldn’t be used, and whether most cloud providers are right to not give us it (no prizes for guessing). Here are some links to things they mentioned:superuser (docs) https://www.postgresql.org/docs/current/role-attributes.html#id-1.6.9.6.2.1.2.1.1Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG)

26:48

transaction_timeout

Postgres FM ·

2024/03/08

Nikolay and Michael discuss transaction_timeout (a recently committed addition for Postgres 17) — what it's for, how to get around not having it already, and whether it will replace the need to set statement_timeout globally in future. Here are some links to things they mentioned:transaction_timeout (devel docs) https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-TRANSACTION-TIMEOUTCommit:

45:23

Rails + Postgres

Postgres FM ·

2024/03/01

Michael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together — where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better. Here are some links to things they mentioned:Planet Argon survey https://rails-hosting.com/2022/#databasesActive Record https://guides.rubyonrails.org/active_record_basics.htmlPostgreSQL specific

35:25

Why isn't Postgres using my index?

Postgres FM ·

2024/02/23

Nikolay and Michael discuss a common question — why Postgres isn't using an index, and what you can do about it! Here are some links to things they mentioned:Why isn’t Postgres using my index? (blog post by Michael) https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index Why isn’t Postgres using my functional index? (Stack Exchange question from Brent Ozar) https://dba.stackexchange.com/questions/336019/why-isnt-postgres-using-my-functional-index enable_seqscan (and similar

34:07

Overhead of pg_stat_statements and pg_stat_kcache

Postgres FM ·

2024/02/16

Nikolay and Michael discuss the overhead of pg_stat_statements and pg_stat_kcache — mostly focusing on some interesting things Nikolay found while stress-testing some large spot instances up to and beyond 2m TPS(!) Here are some links to things they mentioned:pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlpg_stat_kcache https://github.com/powa-team/pg_stat_kcachepg_stat_statements overhead estimate in pganalyze FAQs https://pganalyze.com/faqWhat is the

32:09

Modern SQL

Postgres FM ·

2024/02/02

Michael is joined by Markus Winand, creator of use-the-index-luke.com and modern-sql.com, and author of SQL Performance Explained, to discuss Modern SQL — what Markus means by it, why it's important, some benefits, some examples, and at least one phrase that should be on a t-shirt. Here are some links to things they mentioned:Modern SQL (site) https://modern-sql.comUse the index, Luke! (site) https://use-the-index-luke.comSQL Performance Explained (book)

36:17

Bloat

Postgres FM ·

2024/01/26

Michael is joined by Chelsea Dole, Staff Software Engineer and Tech Lead of the Data Storage Team at Brex, to discuss bloat. They cover what it is, why it's a problem, strategies for minimising it, and the options when it's really bad. Here are some links to things they mentioned:Managing your tuple graveyard (talk by Chelsea) https://www.youtube.com/watch?v=gAgbzvGT6ckpgstattuple https://www.postgresql.org/docs/current/pgstattuple.html pg_class

49:06

pgvector

Postgres FM ·

2024/01/19

Michael is joined by Jonathan Katz, PostgreSQL Core Team member, Principle Product Manager at AWS, and also pgvector contributor, to discuss pgvector. They cover what it is, why it's so popular all of a sudden, some tuning and tradeoff guidance, and a look to the future. Here are some links to things they mentioned:pgvector https://github.com/pgvector/pgvectorOur episode on companion databases https://postgres.fm/episodes/companion-databasesVectors are the new JSON (talk by Jonathan)

31:31

pgBadger

Postgres FM ·

2024/01/12

Michael is joined by Alicja Kucharczyk, Program Manager for Azure Database for PostgreSQL at Microsoft and organiser of the Warsaw PostgreSQL User Group, to discuss Alicja's favorite tool: pgBadger. They cover what it is, how and when it's useful, and some guidance for getting the most out of it (without causing an outage). Here are some links to things they mentioned:pgBadger website https://pgbadger.darold.net pgBadger repo https://github.com/darold/pgbadgerAn example pgBadger report

19:33

EXPLAIN

Postgres FM ·

2024/01/05

With Nikolay taking a well-earned break, Michael covers the basics of EXPLAIN — what it is, the parameters available, and a process he recommends for using it. Here are some links to the things mentioned:EXPLAIN (docs) https://www.postgresql.org/docs/current/sql-explain.htmlUsing EXPLAIN (docs) https://www.postgresql.org/docs/current/using-explain.htmlEXPLAIN Explained (talk by Josh Berkus https://www.youtube.com/watch?app=desktop&v=mCwwFAl1pBUBeyond joins and indexes (slides from talk

19:07

work_mem

Postgres FM ·

2023/12/31

Happy holidays! Today's topic is work_mem — how it's used and how to tune it. Related post: https://x.com/samokhvalov/status/1740813478150189172?s=46&t=HSxrW86NZqzPQP9vXVJEWg.

38:53

Partitioning by ULID

Postgres FM ·

2023/12/22

Nikolay and Michael discuss partitioning by ULID — revisiting some of the old UUID vs bigint key debate in light of some new UUID specs, and how these can be used for partitioning (by time). Here are some links to things they mentioned:Nikolay’s episode on UUID (for primary keys) https://postgres.fm/episodes/uuid Postgres TV hacking session with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8UUIDv7 patch https://commitfest.postgresql.org/43/4388/ Use ULID Idempotency Keys (tip

30:32

Hash indexes

Postgres FM ·

2023/12/15

Nikolay and Michael discuss hash indexes in Postgres — what they are, some brief history, their pros and cons vs btrees, and whether or when they recommend using them.Update: the idea Nikolay mentioned at the end of this episode turns out to be a little fraught (and as such, inadvisable). Here are some links to things they mentioned:Index types (docs) https://www.postgresql.org/docs/current/indexes-types.html Re-introducing hash indexes in PostgreSQL (blog post by Haki Benita and Michael)

39:04

Constraints

Postgres FM ·

2023/12/08

Nikolay and Michael discuss PostgreSQL constraints — the six different types we have, how to add and modify them, including on large tables with high TPS. Here are some links to things they mentioned:Constraints (docs) https://www.postgresql.org/docs/current/ddl-constraints.htmlUnique index vs unique constraint issue (2017) https://www.postgresql.org/message-id/flat/CANNMO%2B%2B7%2BuVMj%2BZPGKB_zuRpKE0sNthy2Z-_oKSS%3DoE3_rN7QA%40mail.gmail.com Our episode on NULLs

52:09

Events

Postgres FM ·

2023/12/01

Nikolay and Michael discuss PostgreSQL events — whether in-person or online, large conferences or small meet-ups, as well as some strong opinions based on their experiences attending, speaking, and organising them. Here are some links to things they mentioned:PGSQL Phriday #014: PostgreSQL Events https://www.cybertec-postgresql.com/en/pgsql-phriday-014-postgresql-events/ PGCon https://www.pgcon.org/ Highload https://highload.rs/ The San Francisco Bay Area PostgreSQL Meetup Group

24:12

Subtransactions

Postgres FM ·

2023/11/24

Nikolay and Michael discuss PostgreSQL subtransactions — what they are, and four issues they can cause at scale. Here are some links to things they mentioned:Subtransactions (docs) https://www.postgresql.org/docs/current/subxacts.html SAVEPOINT (docs) https://www.postgresql.org/docs/current/sql-savepoint.html PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful Subtransactions and performance

45:58

Companion databases

Postgres FM ·

2023/11/17

Nikolay and Michael discuss companion databases — when and why you might want to add another database management system to your stack (or not), and some specifics for analytics, timeseries, search, and vectors. Here are some links to things they mentioned:Heap were using Postgres + Citus for analytics as of 2022 https://www.heap.io/blog/juggling-state-machines-incident-response-and-data-soup-a-glimpse-into-heaps-engineering-culture Heap recently moved their core analytics to SingleStore

43:45

Blue-green deployments

Postgres FM ·

2023/11/10

Nikolay and Michael discuss blue-green deployments — specifically an RDS blog post, how similar this is (or not) to what they understand to be blue-green deployments, and how applicable the methodology might be in the database world more generally. Here are some links to things they mentioned:Fully managed Blue/Green Deployment in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

38:04

Data model trade-offs

Postgres FM ·

2023/11/03

Nikolay and Michael discuss data model trade-offs — some cases where you might want to (gasp) de-normalize, and which PostgreSQL internals are at play. Here are some links to things they mentioned:Our episode on JSON https://postgres.fm/episodes/json PostgreSQL limits https://www.postgresql.org/docs/current/limits.htmlBoyce–Codd normal form https://en.wikipedia.org/wiki/Boyce–Codd_normal_form Our episode on over-indexing https://postgres.fm/episodes/over-indexing Heap-Only Tuples (HOT)

34:36

Under-indexing

Postgres FM ·

2023/10/27

Nikolay and Michael discuss under-indexing — how to tell if this applies to you, some general and specific cases, as well as some tricky ones. Here are some links to things they mentioned:Indexes (docs) https://www.postgresql.org/docs/current/indexes.html random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/Sequential scans https://www.pgmustard.com/docs/explain/sequential-scan pg_stat_user_tables https://pgpedia.info/p/pg_stat_user_tables.html pg_stat_statements

168 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