Postgres FM

Latest Episodes:

Benchmarking Feb 10, 2023

Show notes

Nikolay and Michael discuss benchmarking — reasons to do it, and some approaches, tools, and resources that can help.
Here are links to a few things we mentioned:

Towards Millions TPS (blog post by Alexander Korotkov)
Episode on testing
Episode on buffers
pgbench
sysbench
Improving Postgres Connection Scalability (blog post by Andres Freund)
pgreplay
pgreplay-go
JMeter
pg_qualstats
pg_query
Database experimenting/benchmarking (talk by Nikolay, 2018)
Database testing (talk by Nikolay at PGCon, 2022)
Systems Performance (Brendan Gregg’s book, chapter 12)
fio
Netdata
Subtransactions Considered Harmful (blog post by Nikolay including Netdata exports)
WAL compression benchmarks (by Vitaly from Postgres.ai)
Dumping/restoring a 1 TiB database benchmarks (by Vitaly from Postgres.ai)
PostgreSQL on EXT3/4, XFS, BTRFS and ZFS (talk slides from Tomas Vondra)
Insert benchmark on ARM and x86 cloud servers (blog post by Mark Callaghan)

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

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

Default configuration Feb 03, 2023

Show notes

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:

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

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

Infra cost optimization Jan 27, 2023

Show notes

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

Auditing Jan 20, 2023

Show notes

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

Copying a database Jan 13, 2023

Show notes

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

Transaction ID wraparound Jan 06, 2023

Show notes

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

Postgres year in review 2022 Dec 30, 2022

Show notes

Here are links to a few things we mentioned:

1. Startups building momentum

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

4. Database branching is coming

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

Row estimates Dec 23, 2022

Show notes

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

Replication Dec 16, 2022

Show notes

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

Timestamps Dec 09, 2022

Show notes

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: