Nik and Michael discuss a listener question about archiving a database.
 
Here are some links to things they mentioned: 

• Listener request to talk about archiving https://www.youtube.com/watch?v=KFRK8PiIvTg&lc=UgyiFrO37gEgUaVhRgN4AaABAg 
• Our episode on “Is pg_dump a backup tool?” https://postgres.fm/episodes/is-pg_dump-a-backup-tool 

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael discuss max_connections, especially in the context of increasing it to solve problems like migrations intermittently failing(!)
 
Here are some links to things they mentioned: 

• max_connections https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
• Tweet about deployments vs connections issue https://x.com/brankopetric00/status/1991394329886077090
• Nik tweet in response https://x.com/samokhvalov/status/1991465573684027443
• Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/
• Exponential Backoff And Jitter (blog post by Marc Brooker) https://aws.amazon.com/blogs/architecture/exponential-backoff-and-jitter/

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael discuss the various changes to EXPLAIN that arrived in Postgres 18.
 
Here are some links to things they mentioned: 

• EXPLAIN (official docs) https://www.postgresql.org/docs/current/sql-explain.html
• Using EXPLAIN (official docs) https://www.postgresql.org/docs/current/using-explain.html 
• EXPLAIN glossary (pgMustard site) https://www.pgmustard.com/docs/explain
• Postgres 18 release notes https://www.postgresql.org/docs/release/18.0/
• Enable BUFFERS with EXPLAIN ANALYZE by default (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078eb
• Our (first) BUFFERS by default episode https://postgres.fm/episodes/buffers-by-default 
• Show index search count in EXPLAIN ANALYZE (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0fbceae841cb5a31b13d3f284ac8fdd19822eceb
• Our episode on Skip scan with Peter Geoghegan https://postgres.fm/episodes/skip-scan
• What do the new Index Searches lines in EXPLAIN mean? https://www.pgmustard.com/blog/what-do-index-searches-in-explain-mean
• pg_stat_plans presentation by Lukas Fittl https://www.youtube.com/watch?v=26coQV3f-wk
• Improve EXPLAIN's display of window functions (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b1b34254
• Show Parallel Bitmap Heap Scan worker stats in EXPLAIN ANALYZE (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a1e6df3b
• Add information about WAL buffers being full (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=320545bfc 

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik talks Michael through a recent benchmark he worked with Maxim Boguk on, to see how quickly they could provision a replica.
 
Here are some links to things they mentioned:

• Ultra-fast replica creation with pgBackRest (blog post by Maxim Boguk and Nik) https://postgres.ai/blog/20251105-postgres-marathon-2-012-ultra-fast-replica-creation-pgbackrest
• Copying a database episode https://postgres.fm/episodes/copying-a-database
• Add snapshot backup support for PostgreSQL in wal-g (draft PR by Andrey Borodin) https://github.com/wal-g/wal-g/pull/2101
• Multi-threaded pg_basebackup discussion 1: https://www.postgresql.org/message-id/flat/CAEHH7R4%3D_GN%2BLSsj0YZOXZ13yc%3DGk9umJOLNopjS%3DimK0c1mWA%40mail.gmail.com
• Multi-threaded pg_basebackup discussion 2: https://www.postgresql.org/message-id/flat/
• io_method https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-IO-METHOD 
• pgBackRest https://github.com/pgbackrest/pgbackrest
• Add sequence synchronization for logical replication (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5509055d6956745532e65ab218e15b99d87d66ce 
• Allow process priority to be set (pgBackRest feature added by David Steele) https://github.com/pgbackrest/pgbackrest/pull/2693 
• Hard limit on process-max (pgBackRest issue from 2019) https://github.com/pgbackrest/pgbackrest/issues/696 

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael discuss the concept of gapless sequences — when you might want one, why sequences in Postgres can have gaps, and an idea or two if you do want them.

And one quick clarification: changing the CACHE option in CREATE SEQUENCE can lead to even more gaps, the docs mention it explicitly.
 
Here are some links to things they mentioned:

• CREATE SEQUENCE https://www.postgresql.org/docs/current/sql-createsequence.html
• Sequence Manipulation Functions https://www.postgresql.org/docs/current/functions-sequence.html
• One, Two, Skip a Few (post by Pete Hamilton from Incident io) https://incident.io/blog/one-two-skip-a-few
• Postgres sequences can commit out-of-order (blog post by Anthony Accomazzo / Sequin) https://blog.sequinstream.com/postgres-sequences-can-commit-out-of-order
• Logical Replication of sequences (hackers thread) https://www.postgresql.org/message-id/flat/CAA4eK1LC%2BKJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ%40mail.gmail.com
• Synchronization of sequences to subscriber (patch entry in commitfest) https://commitfest.postgresql.org/patch/5111/
• Get or Create (episode with Haki Benita) https://postgres.fm/episodes/get-or-create
• German tank problem https://en.wikipedia.org/wiki/German_tank_problem

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael discuss lightweight locks in Postgres — how they differ to (heavier) locks, some occasions they can be troublesome, and some resources for working out what to do if you hit issues.
 
Here are some links to things they mentioned:

• Wait Events of Type LWLock https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE
• Our episode on (heavier) locks https://postgres.fm/episodes/locks
• Nik’s new marathon posts https://postgres.ai/blog/tags/postgres-marathon
• Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres-listen-notify-does-not-scale
• Explicit Locking https://www.postgresql.org/docs/current/explicit-locking.html
• pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
• Tuning with wait events for RDS for PostgreSQL https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.html
• MultiXact member exhaustion incidents (blog post by Cosmo Wolfe / Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025
• pg_index_pilot https://gitlab.com/postgres-ai/pg_index_pilot
• Myths and Truths about Synchronous Replication in PostgreSQL (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=PFn9qRGzTMc
• Postgres Indexes, Partitioning and LWLock:LockManager Scalability (blog post by Jeremy Schneider) https://ardentperf.com /2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael discuss user management in Postgres — how roles work, making administration easier, setting passwords, and avoiding them being logged.
 
Here are some links to things they mentioned:

• Roles https://www.postgresql.org/docs/current/user-manag.html 
• Privileges https://www.postgresql.org/docs/current/ddl-priv.html
• ALTER DEFAULT PRIVILEGES https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
• GRANT https://www.postgresql.org/docs/current/sql-grant.html
• REASSIGN OWNED https://www.postgresql.org/docs/current/sql-reassign-owned.html
• ALTER ROLE (including SET) https://www.postgresql.org/docs/current/sql-alterrole.html 
• CREATE ROLE https://www.postgresql.org/docs/current/sql-createrole.html
• Have I Been Pwned https://haveibeenpwned.com
• Pwned Passwords API https://haveibeenpwned.com/API/v3#PwnedPasswords
• Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/announcing-the-crunchy-data-postgresql-stig
• Our episode on auditing https://postgres.fm/episodes/auditing

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael discuss the newly released Postgres 18 — the bigger things it includes, some of their personal highlights, and some thoughts towards the future.
 
Here are some links to things they mentioned:

• Postgres 18 announcement https://www.postgresql.org/about/news/postgresql-18-released-3142
• Postgres 18 release notes https://www.postgresql.org/docs/18/release-18.html
• Skip scan episode with Peter Geoghegan https://postgres.fm/episodes/skip-scan
• Easier Postgres fine-tuning with online_advisor https://neon.com/blog/easier-postgres-fine-tuning-with-online_advisor
• pganalyze Index Advisor https://pganalyze.com/index-advisor
• BUFFERS by default https://postgres.fm/episodes/buffers-by-default
• Buffers II (the sequel) https://postgres.fm/episodes/buffers-ii-the-sequel
• Return of the BUFFERS https://postgres.fm/episodes/return-of-the-buffers
• UUID https://postgres.fm/episodes/uuid
• Partitioning by ULID https://postgres.fm/episodes/partitioning-by-ulid
• uuidv7 and uuid_extract_timestamp functions https://www.postgresql.org/docs/current/functions-uuid.html
• Add --no-policies option to pg_dump, pg_dumpall, and pg_restore https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cd3c45125
• Add ONLY support for VACUUM and ANALYZE https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=62ddf7ee9
• Make "vacuumdb --analyze-only" process partitioned tables (committed recently for Postgres 19) https://commitfest.postgresql.org/patch/5871/
• NOT VALID constraints https://postgres.fm/episodes/not-valid-constraints
• The year of the Lock Manager’s Revenge (post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability
• Increase the number of fast-path lock slots https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c4d5cb71d
• "enable" parameters will work differently in Postgres 18 https://www.pgmustard.com/blog/enable-parameters-work-differently-in-postgres-18
• logerrors https://github.com/munakoiso/logerrors

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael are joined by Harry Brundage from Gadget to talk about their recent zero-downtime major version upgrade, how they use Postgres more generally, their dream database, and some challenges of providing Postgres as an abstracted service at scale.
 
Here are some links to things they mentioned:

• Harry Brundage https://postgres.fm/people/harry-brundage
• Gadget https://gadget.dev
• Zero downtime Postgres upgrades using logical replication (blog post) https://gadget.dev/blog/zero-downtime-postgres-upgrades-using-logical-replication
• HOT updates https://www.postgresql.org/docs/current/storage-hot.html
• PgDog https://pgdog.dev
• Multigres https://multigres.com
• Neki https://www.neki.dev
• Running 10 Million PostgreSQL Indexes In Production (Heap blog post) https://www.heap.io/blog/running-10-million-postgresql-indexes-in-production
• pgwatch2 (Postgres.ai Edition) https://gitlab.com/postgres-ai/pgwatch2
• Advanced query insights on AlloyDB https://cloud.google.com/alloydb/docs/advanced-query-insights-overview
• OrioleDB https://www.orioledb.com
• Not discussed but relevant: Gadget have also now published a blog post about their sharding! https://gadget.dev/blog/sharding-our-core-postgres-database-without-any-downtime

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael are joined by Simon Eskildsen from turbopuffer — among other things, they discuss ANN index types, tradeoffs that can make sense for search workloads, and when it can make sense to move search out of Postgres.
 
Here are some links to things they mentioned:

• Simon Eskildsen https://postgres.fm/people/simon-eskildsen
• turbopuffer https://turbopuffer.com
• Use ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systems
• PostgreSQL 18 Release Candidate 1 https://www.postgresql.org/about/news/postgresql-18-rc-1-released-3130
• Understanding DiskANN (blog post by Junaid Ahmed) https://www.tigerdata.com/blog/understanding-diskann
• SPFresh: Incremental In-Place Update for Billion-Scale Vector Search (paper) https://arxiv.org/abs/2410.14452
• Amazon S3 adds new functionality for conditional writes https://aws.amazon.com/about-aws/whats-new/2024/11/amazon-s3-functionality-conditional-writes
• Amazon S3 Vectors https://aws.amazon.com/s3/features/vectors

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nik and Michael discuss when not to use Postgres — specifically use cases where it still makes sense to store data in another system.
 
Here are some links to things they mentioned:

• Just use Postgres (blog post by Ethan McCue) https://mccue.dev/pages/8-16-24-just-use-postgres
• Just Use Postgres for Everything (blog post by Stephan Schmidt) https://www.amazingcto.com/postgres-for-everything
• Real-time analytics episode https://postgres.fm/episodes/real-time-analytics
• Crunchy Data Joins Snowflake https://www.crunchydata.com/blog/crunchy-data-joins-snowflake
• Two sizes fit most: PostgreSQL and Clickhouse (blog post by Sid Sijbrandij) https://about.gitlab.com/blog/two-sizes-fit-most-postgresql-and-clickhouse
• pg_duckdb episode https://postgres.fm/episodes/pg_duckdb
• Cloudberry https://github.com/apache/cloudberry
• Time-series considerations episode https://postgres.fm/episodes/time-series-considerations
• Queues in Postgres episode https://postgres.fm/episodes/queues-in-postgres
• Large Objects https://www.postgresql.org/docs/current/largeobjects.html 
• PGlite https://pglite.dev
• ParadeDB https://www.paradedb.com
• ZomboDB https://github.com/zombodb/zombodb
• turbopuffer https://turbopuffer.com
• HNSW vs. DiskANN (blog post by Haziqa Sajid) https://www.tigerdata.com/learn/hnsw-vs-diskann
• SPANN: Highly-efficient Billion-scale Approximate Nearest Neighbor Search (paper) https://www.microsoft.com/en-us/research/wp-content/uploads/2021/11/SPANN_finalversion1.pdf
• Amazon S3 Vectors https://aws.amazon.com/s3/features/vectors
• Iterative Index Scans added to pgvector in 0.8.0 https://github.com/pgvector/pgvector/issues/678
• S3 FDW from Supabase https://github.com/supabase/wrappers/tree/main/wrappers/src/fdw/s3_fdw

~~~

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 credit to:

• Jessie Draws for the elephant artwork