Postgres FM

Latest Episodes:

Companion databases Nov 17, 2023

Show notes

Nikolay and Michael discuss companion databases — when and why you might want to add another database management system to your stack (or not), and some specifics for analytics, timeseries, search, and vectors.
Here are some links to things they mentioned:

Heap were using Postgres + Citus for analytics as of 2022 https://www.heap.io/blog/juggling-state-machines-incident-response-and-data-soup-a-glimpse-into-heaps-engineering-culture
Heap recently moved their core analytics to SingleStore (we only spotted this after recording 🤦‍♂️) https://www.heap.io/blog/heaps-next-generation-data-platform
Posthog moved their analytics from Postgres to Clickhouse https://posthog.com/blog/clickhouse-vs-postgres
Timescale https://www.timescale.com/
Citus https://www.citusdata.com/
Hydra https://www.hydra.so/
Our episode on real-time analytics https://postgres.fm/episodes/real-time-analytics
Our episode on queues https://postgres.fm/episodes/queues-in-postgres
Our episode on logical replication https://postgres.fm/episodes/logical-replication
Full Text Search in PostgreSQL (docs) https://www.postgresql.org/docs/current/textsearch.html
ZomboDB https://www.zombodb.com/
ParadeDB https://www.paradedb.com/
PeerDB https://www.peerdb.io/
RUM indexes https://github.com/postgrespro/rum
Debezium https://debezium.io/
pgmq https://github.com/tembo-io/pgmq
Tembo https://tembo.io/
pgvector vs Pinecone (Supabase blog post) https://supabase.com/blog/pgvector-vs-pinecone
Jepsen analyses https://jepsen.io/analyses

~~~

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

Blue-green deployments Nov 10, 2023

Show notes

Nikolay and Michael discuss blue-green deployments — specifically an RDS blog post, how similar this is (or not) to what they understand to be blue-green deployments, and how applicable the methodology might be in the database world more generally.
Here are some links to things they mentioned:

Fully managed Blue/Green Deployment in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL https://aws.amazon.com/blogs/database/new-fully-managed-blue-green-deployment-in-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/
Blue-green deployment (blog post by Martin Fowler) https://martinfowler.com/bliki/BlueGreenDeployment.html
Our episode on logical replication https://postgres.fm/episodes/logical-replication
pgroll https://github.com/xataio/pgroll

~~~

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

Data model trade-offs Nov 03, 2023

Show notes

Nikolay and Michael discuss data model trade-offs — some cases where you might want to (gasp) de-normalize, and which PostgreSQL internals are at play.
Here are some links to things they mentioned:

Our episode on JSON https://postgres.fm/episodes/json
PostgreSQL limits https://www.postgresql.org/docs/current/limits.html
Boyce–Codd normal form https://en.wikipedia.org/wiki/Boyce–Codd_normal_form
Our episode on over-indexing https://postgres.fm/episodes/over-indexing
Heap-Only Tuples (HOT) optimisation https://www.postgresql.org/docs/current/storage-hot.html
The Surprising Impact of Medium-Size Texts on PostgreSQL Performance https://hakibenita.com/sql-medium-text-performance
Query planner settings (collapse limits and Genetic Query Optimizer) https://www.postgresql.org/docs/current/runtime-config-query.html
WITH Queries (Common Table Expressions) https://www.postgresql.org/docs/current/queries-with.html
Our episode on benchmarking https://postgres.fm/episodes/benchmarking
The tests and benchmarks Nikolay mentioned https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues

~~~

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

Under-indexing Oct 27, 2023

Show notes

Nikolay and Michael discuss under-indexing — how to tell if this applies to you, some general and specific cases, as well as some tricky ones.
Here are some links to things they mentioned:

Indexes (docs) https://www.postgresql.org/docs/current/indexes.html
random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/
Sequential scans https://www.pgmustard.com/docs/explain/sequential-scan
pg_stat_user_tables https://pgpedia.info/p/pg_stat_user_tables.html
pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
auto_explain https://www.postgresql.org/docs/current/auto-explain.html
Our episode on slow queries https://postgres.fm/episodes/slow-queries-and-slow-transactions
Our episode on query macro analysis https://postgres.fm/episodes/macro-query-analysis-intro
Running 10 Million PostgreSQL Indexes In Production (And Counting) https://www.heap.io/blog/running-10-million-postgresql-indexes-in-production
Faceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/
Our episode on over-indexing https://postgres.fm/episodes/over-indexing

~~~

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

Over-indexing Oct 20, 2023

Show notes

Nikolay and Michael discuss over-indexing — what we mean by it, the regular issues people discuss about it, as well as a novel one Nikolay has come across and benchmarked recently.
Here are some links to things they mentioned:

Nikolay’s tweet on over-indexing https://twitter.com/samokhvalov/status/1713101666629927112
Heap-Only Tuples (HOT) optimization https://www.postgresql.org/docs/current/storage-hot.html
Our episode on index maintenance https://postgres.fm/episodes/index-maintenance
PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0
Our episode on connection poolers https://postgres.fm/episodes/connection-poolers
Configurable FP_LOCK_SLOTS_PER_BACKEND (Hackers mailing list discussion) https://www.postgresql.org/message-id/flat/CAM527d-uDn5osa6QPKxHAC6srOfBH3M8iXUM%3DewqHV6n%3Dw1u8Q%40mail.gmail.com
LWLock:lock_manager (Amazon RDS docs) https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.lw-lock-manager.html

~~~

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

Query hints Oct 13, 2023

Show notes

Nikolay and Michael discuss query hints — what they are, what we do and don't have in PostgreSQL, and some other things in and around the ecosystem to be aware of.
Here are some links to some extra things they mentioned:

Query planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html
Statistics used by the planner (docs) https://www.postgresql.org/docs/current/planner-stats.html
default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET
Optimiser hints discussion (wiki) https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
An example mailing list thread from 2006 https://www.postgresql.org/message-id/flat/20061012151439.GT28647%40nasby.net
Peter Geoghegan tweet regarding invalid index https://twitter.com/petervgeoghegan/status/1599191964045672449
plantuner http://sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=blob;hb=HEAD;f=README.plantuner
pg_hint_plan https://github.com/ossc-db/pg_hint_plan
Aurora PostgreSQL query plan management https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html
Building automatic adviser & performance tuning tools - Julien Rouhaud & Tatsuro Yamada: PGCon 2020 (the completely misremembered presentation Michael mentioned 🙈) https://www.youtube.com/watch?v=LQZK6p3SwwA
hypopg https://github.com/HypoPG/hypopg

~~~

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

Stop and start Postgres faster Oct 07, 2023

Show notes

In this episode (recorded live on YouTube), Nikolay discusses Postgres shutdown and startup times – how to troubleshoot them and, when needed, optimize.
Some extra things mentioned in this episode:

#PostgresMarathon series – every day, Nikolay posts a new howto-style article https://twitter.com/hashtag/PostgresMarathon
Day 2: Postgres shutdown and restart attempts https://twitter.com/samokhvalov/status/1707147450044297673
Day 3: How to troubleshoot long Postgres startup https://twitter.com/samokhvalov/status/1707466169245171773
GitLab repo with #PostgresMarathon posts (markdown): https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos
Episode 010 "WAL and checkpoint tuning": https://postgres.fm/episodes/wal-and-checkpoint-tuning

~~~

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

Backups Sep 29, 2023

Show notes

Nikolay and Michael discuss Postgres backups — why we need them, what the options are, whether a dump is a backup or not, and some considerations for lowering RPO and RTO at scale.
Here are some links to some extra things they mentioned:

pg_dump https://www.postgresql.org/docs/current/app-pgdump.html
pg_basebackup https://www.postgresql.org/docs/current/app-pgbasebackup.html
pgBackRest https://github.com/pgbackrest/pgbackrest
WAL-G https://github.com/wal-g/wal-g
Barman https://github.com/EnterpriseDB/barman
Data loss at GitLab (2017) https://about.gitlab.com/blog/2017/02/01/gitlab-dot-com-database-incident/
Dev Deletes Entire Production Database, Chaos Ensues (YouTube video) https://www.youtube.com/watch?v=tLdRBsuvVKc
Our episode on corruption https://postgres.fm/episodes/corruption
DBLab Engine https://github.com/postgres-ai/database-lab-engine

~~~

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

Postgres 16 Sep 22, 2023

Show notes

Nikolay and Michael discuss the release of PostgreSQL 16 — the most important new features, what they mean for us as users, whether and when to upgrade, and more.

Here are some links to some extra things they mentioned:

Release notes https://www.postgresql.org/docs/current/release-16.html
New Features With Examples (PDF from Noriyoshi Shinoda of Hewlett Packard Enterprise Japan) https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL16Beta1_New_Features_en_20230528_1.pdf
Why Upgrade? (site by depesz) https://why-upgrade.depesz.com/
Waiting for PostgreSQL 16 (blog post series from Depesz) https://www.depesz.com/tag/pg16/
Our episode on favourite features https://postgres.fm/episodes/our-favourite-v16-feature
Our episode on logical replication https://postgres.fm/episodes/logical-replication
Active Active in Postgres 16 (blog post from Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16
AlloyDB adaptive autovacuum https://cloud.google.com/blog/products/databases/alloydb-for-postgresql-under-the-hood-adaptive-autovacuum
Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI
Our monitoring checklist episode https://postgres.fm/episodes/monitoring-checklist
pgvector https://github.com/pgvector/pgvector

~~~

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

Logical replication Sep 15, 2023

Show notes

Nikolay and Michael discuss logical replication — some history, initialization, change data capture, how to scale it, some limitiations, and ways that it is getting better.
Here are some links to some things they mentioned:

Logical replication https://www.postgresql.org/docs/current/logical-replication.html
GitLab upgraded multi-terabyte, heavily-loaded clusters with zero-downtime https://twitter.com/samokhvalov/status/1700574156222505276
pg_waldump https://www.postgresql.org/docs/current/pgwaldump.html
pg_dump and external snapshots (blog post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/
Failover of logical replication slots in Patroni (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=SllJsbPVaow
Our episode on replication https://postgres.fm/episodes/replication

~~~

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 brought to you by: