Postgres FM

Nikolay and Michael discuss a listener request — special considerations for databases that are used in a read-only mode all day, and get an update at night with additional data.

Here are links to a few things we mentioned: 

• Index-only scans
• Vacuum
• UK Covid-19 dashboard
• pg_repack
• Partitioning
• Our episode on BRIN indexes
• Always load sorted data (blog post by Haki Benita)
• GIN indexes: the good and the bad (blog post by Lukas Fittl)
• Our episode on materialised views
• pg_buffercache
• Towards Millions TPS (blog post by Alexander Korotkov)
• Postgres WASM (by Snaplet and Supabase)
• Yugabyte
• AWS Aurora 
• Continuous Archiving and Point-in-Time Recovery (docs)
• Our episode on checkpoint tuning
• Our episode on partitioning
• PgQ
• Neon branching
• Database Lab Engine
• Cluster

~~~

What did you like or not like? What should we discuss next time? Let us know on social media, 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 

Nikolay and Michael discuss table partitioning — what it is, why and when it's helpful, and some considerations for your partition key.
 
Here are links to a few things we mentioned: 

• Partitioning docs
• pg_partman
• Index maintenance episode 
• Timescale partitioning
• pg_cron
• Xtreme PostgreSQL (talk by Christophe Pettus)
• Database Antipatterns (also by Christophe, slides 46-49)
• Understanding an outage (blog post by Duffel)

~~~

What did you like or not like? What should we discuss next time? Let us know on social media, 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 

This week we're sharing an edited version of Nikolay's recent interview with Peter Zaitsev from Percona — they discuss MySQL vs Postgres, Percona’s success, open source licenses, FerretDB, and databases on Kubernetes… phew!
 
And here are some links to a few things mentioned: 

• Percona
• pgCloudHacker browser extension 
• PMM
• Percona Distribution for PostgreSQL
• FerretDB
• Peter's Twitter profile
• Peter's LinkedIn profile

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

What did you like or not like? What should we discuss next time? Let us know on social media, 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 

Nikolay and Michael discuss command line and graphical user interfaces for Postgres — what they are, some tips and tricks for learning, and what we each use and prefer.
 
Here are links to a few things we mentioned: 

• psql (docs)
• psql is awesome! (talk by Lætitia Avrot)
• psql tips (site by Lætitia Avrot)
• pgAdmin
• Postico 
• DBeaver
• DataGrip
• PgManage (new Command Prompt fork of OmniDB) 
• PopSQL
• postgres_dba
• pspg
• Materialized views episode
• pgcli

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

What did you like or not like? What should we discuss next time? Let us know on social media, 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 

Nikolay and Michael discuss major and minor version Postgres upgrades — what they are, how often they come out, and how regularly we should be upgrading.
 
Here are links to a few things we mentioned: 

• Postgres versioning policy 
• why-upgrade (by depesz)
• postgresqlco.nf (by Ongres)
• postgresql.conf comparison (by Rustproof Labs) 
• pg_upgrade
• Logical replication 
• CHECKPOINT
• amcheck
• Locale data changes (e.g. glibc upgrades)
• ANALYZE
• Upgrades are hard (summary of panel discussion by Andreas 'ads' Scherbaum)
• spilo
• Recent pgsql hackers discussion about using logical and pg_upgrade together

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

What did you like or not like? What should we discuss next time? Let us know on social media, 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 

Nikolay and Michael discuss wait events — what they are, why we have them, and how to use them to help diagnose performance issues.
 
Here are links to a few things we mentioned: 

• Wait events table (docs)
• 9.6 release notes
• PostgreSQL Scalability (blog post by Alexander Korotkov)
• Wait event analysis in pganalyze
• auto_explain
• Database Lab Engine
• track_io_timing
• pg_test_timing
• pgBadger
• Coroot
• Okmeter
• pgwatch2 Postgres.ai Edition
• pg_wait_sampling
• pgsentinel
• Datadog
• AWS RDS docs for PostgreSQL wait events
• pgMustard newsletter
• PASH Viewer
• pgCenter
• Intro to query optimisation episode
• Monitoring checklist 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 

Nikolay and Michael discuss TOAST (The Oversized-Attribute Storage Technique) — what it is, how it works, and some general things to be aware of.
 
Here are links to a few things we mentioned: 

• TOAST docs
• TOAST wiki
• Hussein Nasser on rows per page (Twitter)
• Toasting in action (dbi services blog)
• Interview with Peter Zaitsev (Postgres TV)
• Building columnar compression in a row-oriented database (Timescale blog post)
• The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita)
• PostgreSQL at Scale: Saving Space Basically for Free (blog post by Braintree on column Tetris)
• postgres_dba alignment padding query 

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

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 

Nikolay and Michael discuss JSON — our options for storing it in Postgres, whether or when we should, as well as a brief intro and some tips for JSON functions available.
 
Here are links to a few things we mentioned: 

• hstore
• XML type
• XML functions
• JSON types
• JSON functions
• JSONB indexing
• NULLS episode
• Why Postgres is popular episode
• PostgreSQL 12 release notes
• What’s New in SQL:2016 (blog post by Markus Winand)
• SQL/JSON is postponed (blog post by depesz) 
• JSON[b] Roadmap (talk by Oleg Bartunov)
• Slides, with benchmarks
• RUM access method
• JSON in PostgreSQL: how to use it right (blog post by Laurenz Albe from Cybertec)
• pg_jsonschema
• TOAST_TUPLE_TARGET and TOAST_TUPLE_THRESHOLD 
• The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita) 
• Aggregate functions
• How to store and index json data (blog post by ScaleGrid)
• When to avoid JSONB (blog post by Heap)
• FerretDB

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

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 

Nikolay and Michael discuss real-time analytics — what it means, what the options are, and some tips if you're trying to implement it within Postgres.
 
Here are links to a few things we mentioned: 

• Loose index scan / skip scan with recursive CTE (wiki)
• Zheap (wiki)
• cstore_fdw (now part of Citus)
• Timescale compression docs
• Hydra founders interview (on Postgres TV)
• Materialised views episode 
• pg_ivm
• Timescale continuous aggregates docs
• Clickhouse
• Snowflake
• Replication episode
• Timescale bottomless storage on S3 (blog post)
• pg_partman
• Querying Postgres from DuckDB (blog post)
• Heap blog (filter by “Engineering”)
• Incremental View Maintenance (wiki)
• PostgreSQL HyperLogLog  
• Faster counting (by Joe Nelson on the Citus blog)

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

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 

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