Postgres FM

Latest Episodes:

Queues in Postgres Apr 21, 2023

Show notes

Nikolay and Michael discuss queues in Postgres — the pros and cons vs dedicated queuing tools, and some tips for scaling.
A couple of apologies-in-advance:

Near the end, we incorrectly say "idempotent" when we meant "stateless", and also 50 TPS instead of 500 TPS
We also had a couple of audio issues, sorry!

Here are links to a few things we mentioned:

Recent discussion on Hacker News
PgQ
What is SKIP LOCKED (blog post by Craig Ringer)
autovacuum
Postgres queues (blog post by Brandur)
pg_repack
Our episode on partitioning
Nikolay’s Twitter poll
Subtransactions Considered Harmful (blog post by Nikolay)

~~~

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

Read-only considerations Apr 14, 2023

Show notes

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

Partitioning Apr 07, 2023

Show notes

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

Peter Zaitsev Mar 31, 2023

Show notes

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:

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

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

psql vs GUIs Mar 24, 2023

Show notes

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

Upgrades Mar 17, 2023

Show notes

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

Wait events Mar 10, 2023

Show notes

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:

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

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

TOAST Mar 03, 2023

Show notes

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

JSON Feb 24, 2023

Show notes

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

Real-time analytics Feb 17, 2023

Show notes

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: