Postgres FM

Latest Episodes:

Postgres Emergency Room Aug 30, 2024

Show notes

Nikolay and Michael discuss PostgreSQL emergencies — both the psychological side of incident management, and some technical aspects too.
Here are some links to things they mentioned:

Site Reliability Engineering resources from Google https://sre.google
GitLab Handbook SRE https://handbook.gitlab.com/job-families/engineering/infrastructure/site-reliability-engineer
Keeping Customers Streaming — The Centralized Site Reliability Practice at Netflix https://netflixtechblog.com/keeping-customers-streaming-the-centralized-site-reliability-practice-at-netflix-205cc37aa9fb
Our monitoring checklist episode https://postgres.fm/episodes/monitoring-checklist
Hannu Krosing talk on Postgres TV — Do you vacuum everyday? https://www.youtube.com/watch?v=JcRi8Z7rkPg
Our episode on corruption https://postgres.fm/episodes/corruption
Nikolay’s episode on stopping and starting Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-faster
Our episode on out of disk https://postgres.fm/episodes/out-of-disk
The USE method (Brendan Gregg) https://www.brendangregg.com/usemethod.html
Thundering herd problem https://en.wikipedia.org/wiki/Thundering_herd_problem
pgwatch2 Postgres AI edition https://gitlab.com/postgres-ai/pgwatch2

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Get or Create Aug 23, 2024

Show notes

Michael and Nikolay are joined by Haki Benita, a technical lead and database enthusiast who writes an excellent blog and gives popular talks and training sessions too, to discuss the surprisingly complex topic of trying to implement “get or create” in PostgreSQL — handling issues around idempotency, concurrency, and bloat.
Here are some links to things they mentioned:

Haki Benita https://hakibenita.com
How to Get or Create in PostgreSQL (blog post by Haki) https://hakibenita.com/postgresql-get-or-create
"Find-or-insert" using a single query (how-to guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0036_find-or-insert_using_a_single_query.md?ref_type=heads
Is SELECT or INSERT in a function prone to race conditions? (Answer by Erwin Brandstetter) https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions/15950324#15950324
get_or_create() in Django https://docs.djangoproject.com/en/5.1/ref/models/querysets/#get-or-create
Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
MERGE (Postgres documentation) https://www.postgresql.org/docs/current/sql-merge.html
Hidden dangers of duplicate key violations in PostgreSQL and how to avoid them (blog post by Divya Sharma and Shawn McCoy from the RDS team) https://aws.amazon.com/blogs/database/hidden-dangers-of-duplicate-key-violations-in-postgresql-and-how-to-avoid-them/
One, Two, Skip a Few... (blog post by Pete Hamilton from Incident) https://incident.io/blog/one-two-skip-a-few

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Getting started with benchmarking Aug 16, 2024

Show notes

Michael and Nikolay are joined by Melanie Plageman, database internals engineer at Microsoft and major contributor and committer to PostgreSQL, to discuss getting started with benchmarking — how it differs for users and developers of Postgres, how and when it comes up during development, some tools and lessons, as well as what she's working on at the moment.
Here are some links to things they mentioned:

Melanie Plageman https://postgres.fm/people/melanie-plageman
Meanie’s Introduction to Benchmarking With pgbench talk slides https://postgresql.us/events/pgconfnyc2023/schedule/session/1410-introduction-to-benchmarking-with-pgbench/#slides
Melanie’s Visualizing Postgres I/O Performance for Development talk recording https://www.youtube.com/watch?v=CxyPZHG5beI
Melanie’s Visualizing Postgres I/O Performance for Development talk slides https://speakerdeck.com/melanieplageman/o-performance-for-development
pgbench https://www.postgresql.org/docs/current/pgbench.html
Mark Callaghan’s blog https://smalldatum.blogspot.com
Some of Tomas Vondra’s blog posts https://www.enterprisedb.com/blog/author/tomas-vondra
Some of Andres Freund’s blog posts https://www.citusdata.com/blog/authors/andres-freund/
An example of Alexander Lakhin’s work https://www.postgresql.org/message-id/b32bed1b-0746-9b20-1472-4bdc9ca66d52%40gmail.com
Simplifying the TPC Benchmark C, an OLTP Workload (talk by Mark Wong) https://www.youtube.com/watch?v=qi0I74urLoY
Matplotlib https://matplotlib.org
pandas https://pandas.pydata.org
pg_stat_io https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW
pg_stat_io commit https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a9c70b46dbe152e094f137f7e6ba9cd3a638ee25
pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.html
tmpfs https://docs.kernel.org/filesystems/tmpfs.html
Eager page freeze criteria mailing list thread https://www.postgresql.org/message-id/CAAKRu_b3tpbdRPUPh1Q5h35gXhY%3DspH2ssNsEsJ9sDfw6%3DPEAg%40mail.gmail.com
The path to using AIO in postgres (talk by Andres Freund) https://www.youtube.com/watch?v=qX50xrHwQa4
Improve dead tuple storage for lazy vacuum (Masahiko Sawada) https://www.postgresql.org/message-id/flat/CAD21AoAfOZvmfR0j8VmZorZjL7RhTiQdVttNuC4W-Shdc2a-AA@mail.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!

~~~

Postgres FM is produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Index-Only Scans Aug 09, 2024

Show notes

Nikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice.
Here are some links to things they mentioned:

Index-Only Scans and Covering Indexes (docs) https://www.postgresql.org/docs/current/indexes-index-only-scans.html
Discussion on Twitter about JIT and Parallel Query defaults https://x.com/jer_s/status/1819749688184373742
Postgres Wiki on Index-Only Scans https://wiki.postgresql.org/wiki/Index-only_scans
Heap Fetches https://www.pgmustard.com/docs/explain/heap-fetches
Rows Removed By Filter https://www.pgmustard.com/docs/explain/rows-removed-by-filter
random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/
pg_dump docs change https://x.com/samokhvalov/status/1820539826363588755
Crunchy Bridge changed random_page_cost to 1.1 for new servers https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1
Autovacuum Tuning Basics (updated blog post by Tomas Vondra) https://www.enterprisedb.com/blog/autovacuum-tuning-basics
Our episode on over-indexing https://postgres.fm/episodes/over-indexing
Our episode on HOT updates https://postgres.fm/episodes/hot-updates
Our episode on partitioning https://postgres.fm/episodes/partitioning

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Why Postgres? Aug 02, 2024

Show notes

Nikolay and Michael discuss why they chose Postgres — as users, for their businesses, for their careers, as well as some doubts.
Here are some links to things they mentioned:

Our episode on why Postgres become popular https://postgres.fm/episodes/why-is-postgres-popular
Database Systems: The Complete Book (by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom) http://infolab.stanford.edu/~ullman/dscb.html
Our episode on the Postgres startup ecosystem https://postgres.fm/episodes/postgres-startup-ecosystem
Will Postgres Live Forever? (talk by Bruce Momjian) https://www.youtube.com/watch?v=iYVxWpyaGpA
"Anarcho-Syndicalist Commune" from Monty Python and the Holy Grail https://www.youtube.com/watch?v=R7qT-C-0ajI
The Cathedral and the Bazaar: Musings on Linux and Open Source by an Accidental Revolutionary (book by Eric S. Raymond) https://en.wikipedia.org/wiki/The_Cathedral_and_the_Bazaar

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Compression Jul 26, 2024

Show notes

Nikolay and Michael discuss compression in Postgres — what's available natively, newer algorithms in recent versions, and several extensions with compression features.
Here are some links to things they mentioned:

wal_compression https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSION
Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Synthetic wal_compression and streaming replication wal size test https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/11
default_toast_compression https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION
ZFS https://en.wikipedia.org/wiki/ZFS
Our episode on TOAST https://postgres.fm/episodes/toast
On compression of everything in Postgres (talk by Andrey Borodin) https://learn.microsoft.com/en-us/shows/cituscon-an-event-for-postgres-2023/on-compression-of-everything-in-postgres-citus-con-2023
cstore_fdw https://citusdata.github.io/cstore_fdw/
Using Hydra columnar https://columnar.docs.hydra.so/concepts/using-hydra-columnar
About compression in Timescale https://docs.timescale.com/use-timescale/latest/compression/about-compression/
pg_tier https://github.com/tembo-io/pg_tier
pgBackRest https://pgbackrest.org/
WAL-G https://github.com/wal-g/wal-g
pg_dump https://www.postgresql.org/docs/current/app-pgdump.html
pg_dump compression specifications in PostgreSQL 16 (article by Pablo Glob from Cybertec) https://www.cybertec-postgresql.com/en/pg_dump-compression-specifications-postgresql-16/
Our episode on pgvector (with Jonathan Katz) https://postgres.fm/episodes/pgvector

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Out of disk Jul 19, 2024

Show notes

Nikolay and Michael discuss Postgres running out of disk space — including what happens, what can cause it, how to recover, and most importantly, how to prevent it from happening in the first place.
Here are some links to things they mentioned:

Disk Full (docs) https://www.postgresql.org/docs/current/disk-full.html
pgcompacttable https://github.com/dataegret/pgcompacttable
Our episode on massive deletes https://postgres.fm/episodes/massive-deletes
Getting Rid of Data (slides from VLDB 2019 keynote by Tova Milo)
pg_tier https://github.com/tembo-io/pg_tier
Data tiering in Timescale Cloud https://docs.timescale.com/use-timescale/latest/data-tiering/
Postgres is Out of Disk and How to Recover (blog post by Elizabeth Christensen) https://www.crunchydata.com/blog/postgres-is-out-of-disk-and-how-to-recover-the-dos-and-donts
max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE
Our episode on checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Aiven docs on full disk issues https://aiven.io/docs/products/postgresql/howto/prevent-full-disk

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Postgres startup ecosystem Jul 12, 2024

Show notes

Nikolay and Michael discuss the Postgres startup ecosystem — some recent closures, some recent fundraising announcements, and their thoughts on where things are going and what they'd like to see.
Here are some links to things they mentioned:

Prediction from Dax Raad https://x.com/thdxr/status/1808972166752580039
OtterTune shut down https://x.com/andy_pavlo/status/1801687420330770841
Snaplet shutting down https://www.snaplet.dev/post/snaplet-is-shutting-down
bit.io shut down https://blog.bit.io/whats-next-for-bit-io-joining-databricks-ace9a40bce0d?gi=8ef885454eef
Timescale acquired PopSQL https://www.timescale.com/blog/best-postgresql-gui-popsql-joins-timescale
Aiven https://aiven.io
Hasura https://hasura.io
Supabase https://supabase.com
Neon https://neon.tech
Tembo https://tembo.io
FerretDB https://www.ferretdb.com
Hydra https://www.hydra.so
pgEdge https://www.pgedge.com
Tembo raised $14m https://techcrunch.com/2024/07/08/database-startup-tembo-lands-new-cash-to-expand
Ry Walker’s Cybertruck with STARTUP license plate https://x.com/rywalker/status/1810061804380557516
Supabase acquired OrioleDB https://supabase.com/blog/supabase-acquires-oriole
Michael Stonebraker Turing Award Lecture https://www.youtube.com/watch?v=BbGeKi6T6QI
Microsoft acquired Citus https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scale
Crunchy Bridge https://www.crunchydata.com/products/crunchy-bridge
PeerDB https://www.peerdb.io
ParadeDB https://www.paradedb.com
pganalyze https://pganalyze.com
DBeaver https://dbeaver.io
Postico / Egger Apps https://eggerapps.at/postico2
Postgres Compare https://www.postgrescompare.com
Coroot https://coroot.com
okmeter https://okmeter.io
Slides from Nikolay’s talk on monitoring https://bit.ly/pg-monitoring
Nile https://www.thenile.dev
Ubicloud https://www.ubicloud.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!

~~~

Postgres FM is produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Four million TPS Jul 05, 2024

Show notes

Nikolay talks Michael through a recent experiment to find the current maximum transactions per second single-node Postgres can achieve — why he was looking into it, what bottlenecks occurred along the way, and ideas for follow up experiments.
Here are some links to things they mentioned:

How many TPS can we get from a single Postgres node? (Article by Nikolay) https://www.linkedin.com/pulse/how-many-tps-can-we-get-from-single-postgres-node-nikolay-samokhvalov-yu0rc
Chat history with Postgres AI bot https://postgres.ai/chats/01905a83-4573-7dca-b47a-bb60ce30fe6c
Our episode on the overhead of pg_stat_statements and pg_stat_kcache https://postgres.fm/episodes/overhead-of-pg_stat_statements-and-pg_stat_kcache
PostgreSQL 17 beta 2 is out https://www.postgresql.org/about/news/postgresql-17-beta-2-released-2885/
PostgreSQL and MySQL: Millions of Queries per Second (about the work by Sveta and Alexander) https://www.percona.com/blog/millions-queries-per-second-postgresql-and-mysql-peaceful-battle-at-modern-demanding-workloads
postgresql_cluster https://github.com/vitabaks/postgresql_cluster
Track on CPU events for pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling/pull/74
The year of the Lock Manager’s Revenge (post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability
Pluggable cumulative statistics (Postgres hackers thread started by Michael Paquier) https://www.postgresql.org/message-id/flat/Zmqm9j5EO0I4W8dx%40paquier.xyz

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Soft delete Jun 28, 2024

Show notes

Nikolay and Michael discuss soft deletion in Postgres — what it means, several use cases, some implementation options, and which implementations suit which use cases.
Here are some links to things they mentioned:

Soft deletion probably isn't worth it (blog post by Brandur) https://brandur.org/soft-deletion
Easy alternative soft deletion (blog post by Brandur) https://brandur.org/fragments/deleted-record-insert
Our episode on auditing https://postgres.fm/episodes/auditing
CREATE FUNCTION … SECURITY DEFINER (docs) https://www.postgresql.org/docs/current/sql-createfunction.html
Principle of least privilege https://en.wikipedia.org/wiki/Principle_of_least_privilege

~~~

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 produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Our TOPPODCAST Picks

Follow Us

Stay Connected

Related Podcasts

Links

Stay Connected