Postgres FM

Nikolay and Michael discuss the default config — some tools and principles you can use to customise it, as well as several parameters you probably always want to change.

Here are links to a few things we mentioned: 

• shared_buffers
• Andres Freund tweets about shared_buffers 
• PGTune Leopard 
• Cybertec Configurator
• pg_stat_statements
• JIT configuration
• postgresqlco.nf
• annotated.conf
• OtterTune
• work_mem
• random_page_cost
• max_connections
• What to log
• max_wal_size
• WAL and checkpoint tuning episode 
• effective_cache_size
• Intro to Performance Tuning and Optimization (EDB guide)
• max_parallel_workers_per_gather 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 

Here are links to a few things we mentioned: 

• The Cost of Cloud, a Trillion Dollar Paradox (blog post from Andreessen Horowitz)
• OVH
• Hetzner
• postgresql_cluster
• Why we're leaving the cloud (blog post by DHH from Basecamp)
• Managed services vs. DIY episode
• ec2instances.info
• Vantage
• Postgres TV episode with Everett Berry from Vantage
• Migrating to Aurora: easy except the bill (blog post by Kimberly Nicholls from Gridium)
• Database Lab Engine
• Postgres.ai consulting
• Netdata
• sysbench
• fio
• Query macro analysis episode
• Top queries by buffers (Gist from Michael)

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 

Here are links to a few things we mentioned: 

• Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG)
• pgAudit (site)
• pgAudit (repo)
• noset (extension)
• Splunk
• Kibana
• Timescale
• CREATE TRIGGER docs
• Transition table triggers (blog post by David Fetter)
• Table Audit (blog post by Lorenzo Alberton)
• Row change auditing options (blog post by CYBERTEC)
• Hydra founders interview (on Postgres TV)
• max_slot_wal_keep_size
• eBPF
• Building a perf-like tool for PostgreSQL (talk by Ronan Dunklau)
• Party tricks for PostgreSQL: perf, ftrace and bpftrace (talk by Dmitry Dolgov)

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 

Here are links to a few things we mentioned: 

• pg_dump 
• pg_restore  
• pgcopydb (tool by Dimitri Fontaine)  
• pg_dump and external snapshots (blog post by Michael Paquier) 
• Systems Performance (book by Brendan Gregg)  
• Performance troubleshooting methodologies (two part talk by Brendan Gregg)  
• pg_basebackup  
• wal-g 
• pgBackRest 
• Database Lab Engine  
• Database branching episode  

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 

Here are links to a few things we mentioned: 

• The Internals of PostgreSQL chapter 5 (book by Hironobu SUZUKI) 
• PostgreSQL 14 internals chapter 7 (book by Egor Rogov)  
• Transaction ID Wraparound (blog post from Sentry) 
• What We Learned from the Recent Mandrill Outage (blog post from Mailchimp) 
• How to simulate transaction ID wraparound (blog post by Prashant Dixit)
• Add 64-bit XIDs into PostgreSQL 16 (commitfest entry)  
• Partitioning (docs)  
• Consider using VACUUM’s INDEX_CLEANUP option (tweet by Peter Geoghan) 
• Add wraparound failsafe to VACUUM (commit) 
• Do you vacuum everyday? (talk by Hannu Krosing) 
• Multixacts wraparound monitoring (mailing list thread) 
• Subtransactions Considered Harmful (blog post by Nikolay) 
• Buffer management in PostgreSQL (talk by Alexander Korotkov) 
• OrioleDB 
• pageinspect
• pg_hexedit  
• pg_visibility  
• Visibility Map (docs)

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 elephant artwork

Here are links to a few things we mentioned: 

1. Startups building momentum

• Aiven raised $210m  
• Timescale raised $110m  
• Hasura raised $100m  
• Supabase raised $80m  
• Neon raised $30m 
• Hydra  
• OrioleDB  

2. Educational resources

• Postgres FM started 👋
• Postgres TV became more active (including topic playlists)  
• Tobias Petry tips on Twitter and SQL for Devs  
• Hussein Nasser YouTube channel (backend engineering) 
• Postgres Weekly (newsletter) 

3. Sharding progress

• Citus goes fully open source  
• SPQR  
• pgcat 
• Sharding Postgres at Notion (blog post) 
• PlanetScale (MySQL) 

4. Database branching is coming

• Database Lab Engine  
• Neon branching 
• OrioleDB branching  
• Crunchy Bridge 
• Database branching episode  

5. Postgres is everywhere

• All cloud providers
• AlloyDB announced 
• Kubernetes operators (comparison blog post) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 

Here are links to a few things we mentioned: 

• ANALYZE (docs)
• Autovacuum config (docs)
• Statistics used by the planner (docs) 
• CREATE STATISTICS (docs) 
• Row count estimates (pgMustard blog post) 
• pg_hint_plan 
• Optimizer methodology (talk by Robert Haas) 
• Tomáš Vondra on statistics and hints (an excellent interview we forgot to mention, sorry!) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 

Here are links to a few things we mentioned: 

• Replication (docs) 
• 9.0 release notes (including built-in replication) 
• Slony
• Londiste
• pg_is_in_recovery (pgPedia page) 
• Patroni 
• Multiple Databases with Active Record
• hot_standby_feedback (docs) 
• max_standby_archive_delay or max_standby_streaming_delay (for WAL-shipping and WAL-streaming respectively)
• synchronous_commit
• Synchronous replicas
• Database Lab Engine 
• Neon Branching  
• Past, Present, and Future of Logical Replication (Postgres TV with Amit Kapila)  
• Failover of logical replication slots in Patroni (Postgres TV with Alexander Kukushkin)  
• PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) 
• Why we spent the last month eliminating PostgreSQL subtransactions (blog post by GitLab) 
• Fivetran 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 elephant artwork

Here are links to a few things we mentioned: 

• Date/Time Types (docs) 
• Don’t use timestamp without time zone (wiki)  
• Date/Time functions and operators (docs)  
• Postgres AT TIME ZONE explained (blog post by Bruce Momjian) 
• Our episode on BRIN indexes  
• RUM indexes 
• allballs (mailing list thread) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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 

Here are links to a few things we mentioned: 

• PostgREST documentation
• Supabase
• No offset (blog post by Markus Winand)
• JSON functions and operators (PostgreSQL docs)
• Stored procedures (a previous Postgres FM episode)
• PostGraphile 
• Hasura 
• Parse, Inc. 
• Firebase
• PostgresML
• PgQ 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

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