Postgres FM

Nik and Michael discuss long-running transactions, including when they're harmless, when they cause issues, and how to mitigate those issues.
 
Here are some links to things they mentioned: 
transaction_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT
Our episode on transaction_timeout https://postgres.fm/episodes/transaction_timeout
Our episode on slow queries (which was also our first ever episode!) https://postgres.fm/episodes/slow-queries-and-slow-transactions
Our episode on locks https://postgres.fm/episodes/locks
lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT
Transaction Isolation levels https://www.postgresql.org/docs/current/transaction-iso.html
pg_current_xact_id_if_assigned() https://pgpedia.info/p/pg_current_xact_id_if_assigned.html
Monitor xmin horizon to prevent XID/MultiXID wraparound and high bloat (how-to guide by Nik) https://postgres.ai/docs/postgres-howtos/performance-optimization/monitoring/how-to-monitor-xmin-horizon
idle_replication_slot_timeout https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-IDLE-REPLICATION-SLOT-TIMEOUT
PREPARE TRANSACTION https://www.postgresql.org/docs/current/sql-prepare-transaction.html
log_autovacuum_min_duration https://www.postgresql.org/docs/devel/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION
PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
statement_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT
idle_in_transaction_session_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT

~~~
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 Regina Obe and Paul Ramsey to discuss PostGIS.
 
Here are some links to things they mentioned:
Regina Obe https://postgres.fm/people/regina-obe
Paul Ramsey https://postgres.fm/people/paul-ramsey
PostGIS https://postgis.net
MobilityDB https://github.com/MobilityDB/MobilityDB
pgRouting https://github.com/pgRouting/pgrouting
Google BigQuery GIS public alpha blog post https://cloud.google.com/blog/products/data-analytics/whats-happening-bigquery-integrated-machine-learning-maps-and-more
PostGIS Day 2025 talk recordings https://www.youtube.com/watch?v=wuNO_cW2g-0&list=PLavJpcg8cl1EkQWoCbczsOjFTe-SHg_8m
pg_lake https://github.com/Snowflake-Labs/pg_lake
GeoParquet https://geoparquet.org
ST_DWithin https://postgis.net/docs/ST_DWithin.html
Postgres JSONB Columns and TOAST: A Performance Guide https://www.snowflake.com/en/engineering-blog/postgres-jsonb-columns-and-toast
FOSS4G https://foss4g.org
OpenStreetMap https://www.openstreetmap.org
PgDay Boston https://2026.pgdayboston.org
SKILL.md file https://github.com/postgis/postgis/blob/68dde711039986b47eb62feda45bb24b13b0ea37/doc/SKILL.md
Production query plans without production data (blog post by Radim Marek) https://boringsql.com/posts/portable-stats
PostgreSQL: Up and Running, 4th Edition (by Regina Obe, Leo Hsu) https://www.oreilly.com/library/view/postgresql-up-and/9798341660885

~~~
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 query plan flips in Postgres — what they are, some causes, mitigations, longer term solutions, and the recent outage at Clerk.
 
Here are some links to things they mentioned: 
Recent postmortem from Clerk https://clerk.com/blog/2026-02-19-system-outage-postmortem
The real cost of random I/O (blog post by Tomas Vondra) https://vondra.me/posts/the-real-cost-of-random-io
autovacuum_analyze_scale_factor https://www.postgresql.org/docs/current/runtime-config-vacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR
default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET
pg_hint_plan https://github.com/ossc-db/pg_hint_plan
Aurora PostgreSQL query plan management https://docs.aws.amazon.comAmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Start.html
pg_stat_plans https://github.com/pganalyze/pg_stat_plans
pg_plan_alternatives https://jnidzwetzki.github.io/2026/03/04/pg-plan-alternatives.html
Waiting for Postgres 19: Better Planner Hints with Path Generation Strategies https://pganalyze.com/blog/5mins-postgres-19-better-planner-hints

~~~
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 pg_ash — a new tool (not extension!) from Nik that samples and stores wait events from pg_stat_activity.
 
Here are some links to things they mentioned: 
pg_ash https://github.com/NikolayS/pg_ash
pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
Amazon RDS performance insights https://aws.amazon.com/rds/performance-insights
Our episode on wait events https://postgres.fm/episodes/wait-events
pg-flight-recorder https://github.com/dventimisupabase/pg-flight-recorder
pg_profile https://github.com/zubkov-andrei/pg_profile
pg_cron https://github.com/citusdata/pg_cron

~~~
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 query level comments, object level comments, and another way of adding object level metadata.
 
Here are some links to things they mentioned: 
Object comments https://www.postgresql.org/docs/current/sql-comment.html
Query comment syntax (from an old version of the docs) https://www.postgresql.org/docs/7.0/syntax519.htm
SQL Comments, Please! (Post by Markus Winand) https://modern-sql.com/caniuse/comments
“While C-style block comments are passed to the server for processing and removal, SQL-standard comments are removed by psql.” https://www.postgresql.org/docs/current/app-psql.html
marginalia https://github.com/basecamp/marginalia
track_activity_query_size https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE
Custom Properties for Database Objects Using SECURITY LABELS (post by Andrei Lepikhov) https://www.pgedge.com/blog/custom-properties-for-postgresql-database-objects-without-core-patches

~~~
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