Postgres FM

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

Nik and Michael discuss disks in relation to Postgres — why they matter, how saturation can happen, some modern nuances, and how to prepare to avoid issues.
 
Here are some links to things they mentioned:

• Nik’s tweet demonstrating a NOTIFY hot spot https://x.com/samokhvalov/status/1959468091035009245
• Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres-listen-notify-does-not-scale
• track_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING
• pg_test_timing https://www.postgresql.org/docs/current/pgtesttiming.html 
• PlanetScale for Postgres https://planetscale.com/blog/planetscale-for-postgres
• Out of disk episode https://postgres.fm/episodes/out-of-disk
• 100TB episode https://postgres.fm/episodes/to-100tb-and-beyond 
• Latency Numbers Every Programmer Should Know https://gist.github.com/jboner/2841832
• Fio https://github.com/axboe/fio

~~~

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 multi-column indexes in Postgres — what they are, how to think about them, and some guidance around using them effectively.
 
Here are some links to things they mentioned:

• Multicolumn Indexes (docs) https://www.postgresql.org/docs/current/indexes-multicolumn.html
• Our episode on Index-only scans https://postgres.fm/episodes/index-only-scans
• Combining Multiple Indexes (docs) https://www.postgresql.org/docs/current/indexes-bitmap-scans.html
• Enable BUFFERS with EXPLAIN ANALYZE by default https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07
• “PostgreSQL includes an implementation of the standard btree […] The only limitation is that an index entry cannot exceed approximately one-third of a page” https://www.postgresql.org/docs/current/btree.html
• Our episode on HOT updates https://postgres.fm/episodes/hot-updates
• Our episode on LIMIT vs Performance https://postgres.fm/episodes/limit-vs-performance

~~~

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

Nikolay and Michael discuss self-driving Postgres — what it could mean, using self-driving cars as a reference, and ideas for things to build and optimize for in this area.
 
Here are some links to things they mentioned:

• Nikolay’s blog post on Self-driving Postgres https://postgres.ai/blog/20250725-self-driving-postgres
• SAE J3016 levels of driving automation https://www.sae.org/news/2019/01/sae-updates-j3016-automated-driving-graphic
• Oracle Autonomous Database https://www.oracle.com/uk/autonomous-database/
• Self-Driving Database Management Systems (2017 paper) https://db.cs.cmu.edu/papers/2017/p42-pavlo-cidr17.pdf
• PGTune https://pgtune.leopard.in.ua/
• pg_index_pilot https://gitlab.com/postgres-ai/pg_index_pilot/
• [Vibe] Hacking Postgres with Andrey, Kirk, Nik – index bloat, btree page merge https://www.youtube.com/watch?v=D1PEdDcvZTw

~~~

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

Nikolay and Michael discuss case-insensitive data — when we want to treat columns as case-insensitive, and the pros and cons of using citext, functions like lower(), or a custom collation.
 
Here are some links to things they mentioned:

• citext https://www.postgresql.org/docs/current/citext.html
• Our episode on over-indexing https://postgres.fm/episodes/over-indexing
• Nondeterministic collations https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC
• How to migrate from Django’s PostgreSQL CI Fields to use a case-insensitive collation (blog post by Adam Johnson) https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation
• The collation versioning problem with ICU 73 (blog post by Daniel Vérité) https://postgresql.verite.pro/blog/2023/10/20/icu-73-versioning.html
• amcheck https://www.postgresql.org/docs/current/amcheck.html

~~~

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

Nikolay talks to Michael about Postgres AI's new monitoring tool — what it is, how its different to other tools, and some of the thinking behind it.
 
Here are some links to things they mentioned:

• postgres_ai monitoring https://gitlab.com/postgres-ai/postgres_ai
• DB Lab 4.0 announcement https://github.com/postgres-ai/database-lab-engine/releases/tag/v4.0.0
• pganalyze https://pganalyze.com
• postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup
• Percona Monitoring and Management (PMM) https://github.com/percona/pmm
• pgwatch https://github.com/cybertec-postgresql/pgwatch
• pgwatch Postgres AI Edition https://gitlab.com/postgres-ai/pgwatch2
• libpg_query https://github.com/pganalyze/libpg_query
• The Four Golden Signals https://sre.google/sre-book/monitoring-distributed-systems/#xref_monitoring_golden-signals
• 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