84 episodes

A weekly podcast about all things PostgreSQL

Postgres FM Nikolay Samokhvalov and Michael Christofides

    • Technology
    • 4.9 • 16 Ratings

A weekly podcast about all things PostgreSQL

    Overhead of pg_stat_statements and pg_stat_kcache

    Overhead of pg_stat_statements and pg_stat_kcache

    Nikolay and Michael discuss the overhead of pg_stat_statements and pg_stat_kcache — mostly focusing on some interesting things Nikolay found while stress-testing some large spot instances up to and beyond 2m TPS(!)  Here are some links to things they mentioned:
    pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlpg_stat_kcache https://github.com/powa-team/pg_stat_kcachepg_stat_statements overhead estimate in pganalyze FAQs https://pganalyze.com/faqWhat is the performance impact of pg_stat_statements? (DBA Stack Exchange answer with several links) https://dba.stackexchange.com/questions/303503/what-is-the-performance-impact-of-pg-stat-statementsTowards Millions TPS (blog post by Alexander Korotkov) https://akorotkov.github.io/blog/2016/05/09/scalability-towards-millions-tpsPostgres.ai blog post with links to benchmarks https://postgres.ai/blog/20240127-postges-ai-botpostgresql_cluster https://github.com/vitabaks/postgresql_clusterpg_wait_sampling https://github.com/postgrespro/pg_wait_samplingpg_stat_kcache issue, discussion, and fast resolution https://github.com/powa-team/pg_stat_kcache/issues/41 log_statement_sample_rate https://postgresqlco.nf/doc/en/param/log_statement_sample_rate/ auto_explain.sample_rate https://www.postgresql.org/docs/current/auto-explain.html#id-1.11.7.14.5.3.13.1.3pg_stat_statements performance penalty on Intel much higher than on AMD https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/52
    ~~~
    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.aiMichael Christofides, founder of pgMustardWith special thanks to:
    Jessie Draws for the amazing artwork 

    • 34 min
    Modern SQL

    Modern SQL

    Michael is joined by Markus Winand, creator of use-the-index-luke.com and modern-sql.com, and author of SQL Performance Explained, to discuss Modern SQL — what Markus means by it, why it's important, some benefits, some examples, and at least one phrase that should be on a t-shirt. Here are some links to things they mentioned:
    Modern SQL (site) https://modern-sql.comUse the index, Luke! (site) https://use-the-index-luke.comSQL Performance Explained (book) https://sql-performance-explained.comOur episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownIS DISTINCT FROM (null-safe not equals comparison) https://modern-sql.com/caniuse/is-distinct-fromUNIQUE NULLS DISTINCT https://modern-sql.com/caniuse/unique-nulls-distinctModernes SQL ist mehr als SELECT * FROM (a German language podcast Markus was on recently) https://engineeringkiosk.dev/podcast/episode/99-modernes-sql-ist-mehr-als-select-from-mit-markus-winandMarkus’ website https://winand.at
    ~~~
    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 produced by:
    Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.ai
    With special thanks to:
    Jessie Draws for the elephant artwork 

    • 32 min
    Bloat

    Bloat

    Michael is joined by Chelsea Dole, Staff Software Engineer and Tech Lead of the Data Storage Team at Brex, to discuss bloat. They cover what it is, why it's a problem, strategies for minimising it, and the options when it's really bad. Here are some links to things they mentioned:
    Managing your tuple graveyard (talk by Chelsea) https://www.youtube.com/watch?v=gAgbzvGT6ckpgstattuple https://www.postgresql.org/docs/current/pgstattuple.html pg_class https://www.postgresql.org/docs/current/catalog-pg-class.html Bloat in PostgreSQL: a taxonomy (talk by Peter Geoghegan) https://youtube.com/watch?v=JDG4bMHxCH8 It's Not You, It's Me: Breaking Up with Massive Tables via Partitioning (talk by Chelsea) https://youtube.com/watch?v=TafwSuLNxe8 pg_repack https://github.com/reorg/pg_repackpg_squeeze https://github.com/cybertec-postgresql/pg_squeeze VACUUM https://www.postgresql.org/docs/current/sql-vacuum.htmlautovacuum https://www.postgresql.org/docs/current/runtime-config-autovacuum.html CLUSTER https://www.postgresql.org/docs/current/sql-cluster.html HOT updates https://www.postgresql.org/docs/current/storage-hot.html Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default Our episode on TOAST https://postgres.fm/episodes/toast Our episode on index maintenance https://postgres.fm/episodes/index-maintenance Chelsea's website: https://chelseadole.com/
    ~~~
    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 produced by:
    Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.ai
    With special thanks to:
    Jessie Draws for the amazing artwork 

    • 36 min
    pgvector

    pgvector

    Michael is joined by Jonathan Katz, PostgreSQL Core Team member, Principle Product Manager at AWS, and also pgvector contributor, to discuss pgvector. They cover what it is, why it's so popular all of a sudden, some tuning and tradeoff guidance, and a look to the future. Here are some links to things they mentioned:
    pgvector https://github.com/pgvector/pgvectorOur episode on companion databases https://postgres.fm/episodes/companion-databasesVectors are the new JSON (talk by Jonathan) https://www.youtube.com/watch?v=D_1zunKblAUpgvector 0.5.0 feature highlights and howtos (blog post by Jonathan) https://jkatz05.com/post/postgres/pgvector-overview-0.5.0HNSW indexing and searching (blog post by Jonathan) https://aws.amazon.com/blogs/database/accelerate-hnsw-indexing-and-searching-with-pgvector-on-amazon-aurora-postgresql-compatible-edition-and-amazon-rds-for-postgresqlOur episode on TOAST https://postgres.fm/episodes/toastpgvector changelog https://github.com/pgvector/pgvector/blob/master/CHANGELOG.mdHQANN paper https://arxiv.org/abs/2207.07940HNSW fast build branch https://github.com/pgvector/pgvector/tree/hnsw-fast-build pgconf.dev https://2024.pgconf.dev 
    ~~~
    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 produced by:
    Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.ai
    With special thanks to:
    Jessie Draws for the amazing artwork 

    • 49 min
    pgBadger

    pgBadger

    Michael is joined by Alicja Kucharczyk, Program Manager for Azure Database for PostgreSQL at Microsoft and organiser of the Warsaw PostgreSQL User Group, to discuss Alicja's favorite tool: pgBadger. They cover what it is, how and when it's useful, and some guidance for getting the most out of it (without causing an outage). Here are some links to things they mentioned:
    pgBadger website https://pgbadger.darold.net pgBadger repo https://github.com/darold/pgbadgerAn example pgBadger report https://pgbadger.darold.net/examples/sample.html pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlAzure Guide https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/how-to-generate-pgbadger-report-from-azure-database-for/ba-p/3756328Google Cloud SQL guide https://cloud.google.com/blog/products/databases/guide-to-the-database-observability-with-cloud-sqlRDS guide https://aws.amazon.com/blogs/database/optimizing-and-tuning-queries-in-amazon-rds-postgresql-based-on-native-and-external-tools/Community Insights on pgBadger (PGSQL Phriday Recap) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/community-insights-on-pgbadger-a-pgsql-phriday-010-recap/ba-p/3880911 PGSQL Phriday #010: Log analysis (blog post by Lætitia Avrot) https://mydbanotebook.org/post/log-analysis/ Nothing Compares To VACUUM/The Ballad of Bloat https://www.youtube.com/watch?v=2NxIngqq1y0 Explain Analyze (Feliz Navidad cover) https://www.youtube.com/watch?v=qznnzYZPdkM 
    ~~~
    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 produced by:
    Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.ai
    With special thanks to:
    Jessie Draws for the amazing artwork 

    • 31 min
    EXPLAIN

    EXPLAIN

    With Nikolay taking a well-earned break, Michael covers the basics of EXPLAIN — what it is, the parameters available, and a process he recommends for using it. Here are some links to the things mentioned:
    EXPLAIN (docs) https://www.postgresql.org/docs/current/sql-explain.htmlUsing EXPLAIN (docs) https://www.postgresql.org/docs/current/using-explain.htmlEXPLAIN Explained (talk by Josh Berkus https://www.youtube.com/watch?app=desktop&v=mCwwFAl1pBUBeyond joins and indexes (slides from talk by Bruce Momjian) https://momjian.us/main/writings/pgsql/beyond.pdfDepesz https://explain.depesz.comDalibo https://explain.dalibo.compgMustard https://www.pgmustard.com/A beginners guide to explain (talk by Michael) https://www.youtube.com/watch?v=31EmOKBP1PY&t=24sExplain beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4auto_explain episode https://postgres.fm/episodes/auto_explainRow estimates episode https://postgres.fm/episodes/row-estimatesSlow queries and slow transactions episode https://postgres.fm/episodes/slow-queries-and-slow-transactionsBenchmarking episode https://postgres.fm/episodes/benchmarkingEXPLAIN glossary by Michael (forgot to mention, oops!) https://www.pgmustard.com/docs/explain
    ~~~
    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.aiMichael Christofides, founder of pgMustardWith special thanks to:
    Jessie Draws for the amazing artwork 

    • 19 min

Customer Reviews

4.9 out of 5
16 Ratings

16 Ratings

Jim Robert ,

A+ Postgres content

If you use Postgres and care about your data or performance this show is a must-listen.

Lalunalalol ,

Extraordinary expertise and industry insights!

Impressive and always interesting

Top Podcasts In Technology

Boston Consulting Group BCG
Cool Zone Media
Lex Fridman
Jason Calacanis
BBC Radio 4
BG2Pod

You Might Also Like

Creston Jamison
Changelog Media
Changelog Media
Michael Kennedy and Brian Okken
Michael Kennedy (@mkennedy)
Changelog Media