Postgres FM

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 

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 

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 

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 

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 

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 

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 

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 

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 

Nikolay and Michael discuss foreign keys in Postgres — what they are, their benefits, their overhead, some edge cases to be aware of, some improvements coming, and whether or not they generally recommend using them.
 
Here are some links to things they mentioned:

• Foreign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
• Our episode about constraints: https://postgres.fm/episodes/constraints
• GitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rb
• Adding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188
• Bloat, pg_repack, and deferred constraints (blog post by Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec
• Postgres 17 draft release notes, server configuration section https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIG
• Our 100th episode https://postgres.fm/episodes/to-100tb-and-beyond
• Stop! Trigger Time (blog post by Michael) https://www.pgmustard.com/blog/trigger-time
• Should I Create an Index on Foreign Keys? (Blog post by Percona) https://www.percona.com/blog/should-i-create-an-index-on-foreign-keys-in-postgresql
• Avoid Postgres performance cliffs with MultiXact IDs and foreign keys (5 min video by Lukas Fittl) https://pganalyze.com/blog/5mins-postgres-multiXact-ids-foreign-keys-performance
• Experiment to see basic overhead of foreign keys https://v2.postgres.ai/chats/01902ee6-8ed1-70ec-9345-5606305012f4
• Experiment showing an extreme contention case https://v2.postgres.ai/chats/018fb28d-865f-788d-adb7-efa7ed3a48c4
• Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
• Notes on some PostgreSQL implementation details (blog post by Nelson Elhage that mentions “subtransactions are cursed”) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details

~~~

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