Afleveringen

  • Performance cliffs

    Performance cliffs

    · Postgres FM

    Nikolay and Michael are joined by Tomas Vondra to discuss single query performance cliffs — what they are, why they happen, some things we can do to make them less likely or less severe, and some potential improvements to Postgres that could help.

    Here are some links to things they mentioned:

    Tomas Vondra https://postgres.fm/people/tomas-vondraWhere do performance cliffs come from? (Talk by Tomas) https://www.youtube.com/watch?v=UzdAelm-QSYWhere do performance cliffs come from? (Slides) https://vondra.me/pdf/performance-cliffs-posette-2024.pdfIncrease the number of fast-path lock slots (committed for Postgres 18) https://www.postgresql.org/message-id/flat/E1ss4gX-000IvX-63%40gemulon.postgresql.org San Francisco Bay Area Postgres meet-up with Tomas on 8th April (online) https://www.meetup.com/postgresql-1/events/306484787Our episode on Extended Statistics https://postgres.fm/episodes/extended-statisticsLogging plan of the currently running query (proposed patch by Rafael Thofehrn Castro and Atsushi Torikoshi) https://commitfest.postgresql.org/patch/5330Our episode with Peter Geoghegan on Skip Scan https://postgres.fm/episodes/skip-scanIndex Prefetching patch that Tomas is collaborating with Peter Geoghegan on https://commitfest.postgresql.org/patch/4351A generalized join algorithm, G-Join (paper by Goetz Graefe) https://dl.gi.de/server/api/core/bitstreams/ce8e3fab-0bac-45fc-a6d4-66edaa52d574/content Smooth Scan: Robust Access Path Selection without Cardinality Estimation (paper by R. Borovica, S. Idreos, A. Ailamaki, M. Zukowski, C. Fraser) https://stratos.seas.harvard.edu/sites/g/files/omnuum4611/files/stratos/files/smoothscan.pdfJust-in-Time Compilation (JIT) https://www.postgresql.org/docs/current/jit.htmlNotes from a pgconf.dev unconference session in 2024 about JIT (discusses issues) https://wiki.postgresql.org/wiki/PGConf.dev_2024_Developer_Unconference#JIT_compilationImplementing an alternative JIT provider for PostgreSQL (by Xing Guo) https://higuoxing.com/archives/implementing-jit-provider-for-pgsqlTomas’ Office Hours https://vondra.me/posts/office-hours-experiment

    ~~~

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

    PgDog

    · Postgres FM

    Nikolay and Michael are joined by Lev Kokotov to discuss PgDog — including whether or when sharding is needed, the origin story (via PgCat), what's already supported, and what's coming next.

    Here are some links to things they mentioned:

    Lev Kokotov https://postgres.fm/people/lev-kokotovPgDog https://github.com/pgdogdev/pgdogPgCat https://github.com/postgresml/pgcatAdopting PgCat (Instacart blog post) https://www.instacart.com/company/how-its-made/adopting-pgcat-a-nextgen-postgres-proxyPgDog discussion on Hacker News https://news.ycombinator.com/item?id=43364668Citus https://github.com/citusdata/citusSharding & IDs at Instagram (blog post) https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5cSharding pgvector (blog post by Lev) https://pgdog.dev/blog/sharding-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 produced by:

    Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.ai

    With special thanks to:

    Jessie Draws for the elephant artwork

  • Zijn er afleveringen die ontbreken?

    Klik hier om de feed te vernieuwen.

  • Snapshots

    Snapshots

    · Postgres FM

    Nikolay talks Michael through using cloud snapshots — how they can be used to reduce RTO for huge Postgres setups, also to improve provisioning time, and some major catches to be aware of.

    Here are some links to things they mentioned:

    Snapshots on RDS https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.htmlpgBackRest https://pgbackrest.orgWAL-G https://github.com/wal-g/wal-gpg_backup_start and pg_backup_stop (docs) https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP How to troubleshoot long Postgres startup (by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0003_how_to_troubleshoot_long_startup.mdRestoring to a DB instance (RDS docs mentioning lazy loading) https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html Amazon EBS fast snapshot restore https://docs.aws.amazon.com/ebs/latest/userguide/ebs-fast-snapshot-restore.htmlOur 100th episode “To 100TB, and beyond!” https://postgres.fm/episodes/to-100tb-and-beyond

    ~~~

    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 credit to:

    Jessie Draws for the elephant artwork
  • GIN Indexes

    GIN Indexes

    · Postgres FM

    Nikolay and Michael discuss GIN indexes in Postgres — what they are, what they're used for, and some limitations to be aware of.

    Here are some links to things they mentioned:

    GIN Indexes https://www.postgresql.org/docs/current/gin.htmlGeneralized Search Trees for Database Systems (Hellerstein, Naughton, Pfeffer) https://dsf.berkeley.edu/papers/vldb95-gist.pdf RUM extension https://pgxn.org/dist/rum/1.1.0/Understanding Postgres GIN Indexes: The Good and the Bad (Lukas Fittl) https://pganalyze.com/blog/gin-index


    ~~~

    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 credit to:

    Jessie Draws for the elephant artwork
  • Best Practices

    Best Practices

    · Postgres FM

    Nikolay and Michael use a recent "best practices" article as a prompt — giving a few tips each on the topics mentioned, like schema design, performance, backups, and more.

    Here are some links to things they mentioned:

    7 Crucial PostgreSQL Best Practices (recent blog post) https://speakdatascience.com/postgresql-best-practices“Don't do this” episode https://postgres.fm/episodes/dont-do-thisArticle discussion on Hacker News https://news.ycombinator.com/item?id=42992913Mozilla’s SQL Style Guide https://docs.telemetry.mozilla.org/concepts/sql_style“SQL vs NoSQL” episode with Franck Pachot https://postgres.fm/episodes/sql-vs-nosqlHA episode https://postgres.fm/episodes/high-availability


    ~~~

    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 credit to:

    Jessie Draws for the elephant artwork
  • Extended Statistics

    Extended Statistics

    · Postgres FM

    Nikolay and Michael discuss the CREATE STATISTICS feature in Postgres — what it's for, how often it's used, and how to spot cases where it would help.

    Here are some links to things they mentioned:

    CREATE STATISTICS https://www.postgresql.org/docs/current/sql-createstatistics.htmlcitext https://www.postgresql.org/docs/current/citext.htmlStatistics Used by the Planner https://www.postgresql.org/docs/current/planner-stats.htmldefault_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGETTomĂĄĆĄ Vondra on Postgres TV https://www.youtube.com/watch?v=8la-OWfD3VIRecent commit to Postgres 18 for pg_upgrade https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1fd1bd871012732e3c6c482667d2f2c56f1a9395Multivariate Statistics Examples https://www.postgresql.org/docs/current/multivariate-statistics-examples.htmlExtended statistics (README) https://github.com/postgres/postgres/blob/master/src/backend/statistics/READMEHow we used Postgres extended statistics to achieve a 3000x speedup (blog post by Jared Rulison) https://build.affinity.co/how-we-used-postgres-extended-statistics-to-achieve-a-3000x-speedup-ea93d3dcdc61


    ~~~

    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 credit to:

    Jessie Draws for the elephant artwork
  • SQL vs NoSQL

    SQL vs NoSQL

    · Postgres FM

    Nikolay and Michael are joined by Franck Pachot to discuss SQL vs NoSQL — did Franck change teams by joining MongoDB, normalisation vs denormalisation, developer experience, NULLs, and more!

    Here are some links to things they mentioned:

    Franck Pachot https://postgres.fm/people/franck-pachotFranck's workshop at PGConf India https://pgconf.in/conferences/pgconfin2025/program/proposals/958 PostgreSQL Conference Germany https://2025.pgconf.de"Schema Later" Considered Harmful by Michael Stonebraker and Álvaro Hernández https://www.enterprisedb.com/blog/schema-later-considered-harmfulComparison of JOINS by Michael Stonebraker and Álvaro Hernández https://www.enterprisedb.com/blog/comparison-joins-mongodb-vs-postgresql Franck’s post about why he joined MongoDB https://www.linkedin.com/pulse/2025-im-joining-mongodb-franck-pachot-e4shfEdgeDB https://www.edgedb.comNikolay’s tweet about a recent issue with NULLs https://x.com/samokhvalov/status/1889078097124999272PartiQL https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.htmlFerretDB https://www.ferretdb.comDocumentDB https://github.com/microsoft/documentdb

    ~~~

    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
  • Return of the BUFFERS

    Return of the BUFFERS

    · Postgres FM

    Nikolay and Michael return to the topic of BUFFERS for the third (and final?) time! They discuss the news that it'll be on by default with EXPLAIN ANALYZE in Postgres 18, and what effect that might have.

    Here are some links to things they mentioned:

    Our first BUFFERS episode https://postgres.fm/episodes/buffers-by-defaultOur second BUFFERS episode https://postgres.fm/episodes/buffers-ii-the-sequelBUFFERS enabled for EXPLAIN ANALYZE by default (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07https://explain.depesz.com/historyLatency Numbers Every Programmer Should Know https://gist.github.com/jboner/2841832Twitter conversation about flushing caches https://x.com/prochazkafilip/status/1881655337499205736pg_buffercache_evict https://www.postgresql.org/docs/current/pgbuffercache.html#PGBUFFERCACHE-PG-BUFFERCACHE-EVICTHow to compare the quality of SQL query plans (blog post by Andrei Lepikhov) https://danolivo.substack.com/p/whose-optimisation-is-betterWe were added as PostgreSQL Contributors! https://www.postgresql.org/about/news/new-postgresql-contributors-3006

    ~~~

    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
  • Reads causing writes

    Reads causing writes

    · Postgres FM

    Nikolay and Michael discuss a couple of surprising ways read queries (selects) can cause writes (shared buffers dirtied) in Postgres.

    Here are some links to things they mentioned:

    Reads causing writes in Postgres (post by Alex Jesipow) https://jesipow.com/blog/postgres-reads-cause-writes/Exploring how SELECT queries can produce disk writes (post by Nikolay Sivko) https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6f Hint Bits (wiki) https://wiki.postgresql.org/wiki/Hint_Bitspg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html pg_stat_kcache https://github.com/powa-team/pg_stat_kcache pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling BUFFERS by default (episode 4!) https://postgres.fm/episodes/buffers-by-default Page layout (docs) https://www.postgresql.org/docs/current/storage-page-layout.html From Reddit (user merlinm), PD_ALL_VISIBLE may be a third case https://www.reddit.com/r/PostgreSQL/comments/1hjuyfn/comment/m3e6e7v/ Coroot https://coroot.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 elephant artwork
  • NOT VALID constraints

    NOT VALID constraints

    · Postgres FM

    Nikolay and Michael discuss adding constraints in an online fashion, using NOT VALID and then VALIDATE CONSTRAINT.

    Here are some links to things they mentioned:

    ADD table_constraint NOT VALID https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINTOur episode on zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrationsVALIDATE CONSTRAINT https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-VALIDATE-CONSTRAINTALTER TABLE notes https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-NOTESpgroll 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 produced by:

    Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.ai

    With special thanks to:

    Jessie Draws for the elephant artwork
  • pg_squeeze

    pg_squeeze

    · Postgres FM

    Michael and Nikolay are joined by Antonín Houska to discuss pg_squeeze — what it is, how it started, some of its features, and hopes of getting the functionality into core.

    Here are some links to things they mentioned:

    AntonĂ­n Houska https://postgres.fm/people/antonin-houskapg_squeeze https://github.com/cybertec-postgresql/pg_squeezeCybertec https://www.cybertec-postgresql.compg_repack https://github.com/reorg/pg_repackIntroducing pg_squeeze (by Kaarel Moppel) https://www.cybertec-postgresql.com/en/introducing-pg_squeeze-a-postgresql-extension-to-auto-rebuild-bloated-tablespg_squeeze is included in the PostgreSQL APT packages https://wiki.postgresql.org/wiki/Aptpg_squeeze is included in the PostgreSQL Yum packages https://yum.postgresql.orgpg_rewrite https://github.com/cybertec-postgresql/pg_rewriteVACUUM FULL / CLUSTER CONCURRENTLY patch https://commitfest.postgresql.org/51/5117

    ~~~

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

    pg_duckdb

    · Postgres FM

    Michael and Nikolay are joined by Joe Sciarrino and Jelte Fennema-Nio to discuss pg_duckdb — what it is, how it started, what early users are using it for, and what they're working on next.

    Here are some links to things they mentioned:

    Joe Sciarrino https://postgres.fm/people/joe-sciarrinoJelte Fennema-Nio https://postgres.fm/people/jelte-fennema-niopg_duckdb https://github.com/duckdb/pg_duckdbHydra https://www.hydra.soMotherDuck https://motherduck.comThe problems and benefits of an elephant with a beak (lightning talk by Jelte) https://www.youtube.com/watch?v=ogvbKE4fw9A&list=PLF36ND7b_WU4QL6bA28NrzBOevqUYiPYq&t=1073spg_duckdb announcement post (by Jordan and Brett from MotherDuck) https://motherduck.com/blog/pg_duckdb-postgresql-extension-for-duckdb-motherduckpg_duckdb 0.2 release https://github.com/duckdb/pg_duckdb/releases/tag/v0.2.0

    ~~~

    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
  • RLS vs performance

    RLS vs performance

    · Postgres FM

    Nikolay and Michael discuss Row Level Security in Postgres, focussing on the performance side effects and some tips to avoid (or minimize) them.

    Here are some links to things they mentioned:

    Row Security Policies (docs) https://www.postgresql.org/docs/current/ddl-rowsecurity.html7+ million Postgres tables (recent talk by Kailash Nadh) https://www.youtube.com/watch?v=xhi5Q_wL9i0Row Level Security guide (Supabase docs) https://supabase.com/docs/guides/database/postgres/row-level-securitycurrent_setting function https://www.postgresql.org/docs/current/functions-admin.html#id-1.5.8.34.3.6.2.2.1.1.1.1Our slow count episode https://postgres.fm/episodes/slow-countRLS Performance and Best Practices (gist from Gary Austin) https://github.com/orgs/supabase/discussions/14576Everything you need to know about Postgres Row Level Security (talk by Paul Copplestone) https://www.youtube.com/watch?v=vZT1Qx2xUCoBUFFERS enabled for EXPLAIN ANALYZE by default (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07Add UUID version 7 generation function (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=78c5e141e9c139fc2ff36a220334e4aa25e1b0ebPostgres hacking session with Andrey and Kirk (for UUIDv7): https://www.youtube.com/watch?v=YPq_hiOE-N8

    ~~~

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

    jOOQ

    · Postgres FM

    Michael and Nikolay are joined by Lukas Eder, the creator of jOOQ, to discuss what it is, some nice developer experience features it has, and some fun things he's come across from a Postgres perspective.

    Here are some links to things they mentioned:

    Lukas Eder https://postgres.fm/people/lukas-ederjOOQ https://www.jooq.org/ DSL https://en.wikipedia.org/wiki/Domain-specific_language SQL Dialects https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/SQLDialect.htmlMERGE https://www.postgresql.org/docs/current/sql-merge.html match_recognize https://modern-sql.com/feature/match_recognize JOOQ, joy of SQL (talk by Kevin Davin) https://www.youtube.com/watch?v=8Ej47GZX9D8 BUFFERS enabled for EXPLAIN ANALYZE by default (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07 PostGIS https://postgis.net/ 10 SQL Tricks That You Didn’t Think Were Possible (blog post by Lukas) https://blog.jooq.org/10-sql-tricks-that-you-didnt-think-were-possible/ jOOQ questions on Stack Overflow https://stackoverflow.com/questions/tagged/jooq Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown

    ~~~

    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
  • Column Tetris

    Column Tetris

    · Postgres FM

    Nikolay and Michael discuss "Column Tetris" — what it is, why it matters, how to order columns for new tables, and how to re-organise existing ones.

    Here are some links to things they mentioned:

    “Column Tetris” by Erwin Brandstetter on Stack Overflow https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468Data Types https://www.postgresql.org/docs/current/datatype.htmlOrioleDB beta7 benchmarks https://www.orioledb.com/blog/orioledb-beta7-benchmarkspg_hexedit https://github.com/petergeoghegan/pg_hexeditSaving Space Basically for Free (blog post by James Coleman from Braintree) https://medium.com/paypal-tech/postgresql-at-scale-saving-space-basically-for-free-d94483d9ed9aOrdering Table Columns (GitLab https://docs.gitlab.com/ee/development/database/ordering_table_columns.htmlpostgres_dba alignment padding query https://github.com/NikolayS/postgres_dba/blob/master/sql/p1_alignment_padding.sqlGood explanation from Marco Slot of how alignment was used to fix a recent issue https://x.com/marcoslot/status/1858132850383421570pg_repack feature request discussion https://github.com/reorg/pg_repack/issues/101Our episode on bloat (with Chelsea Dole) https://postgres.fm/episodes/bloatOptimizing table layout for maximum efficiency (blog post by Renato Massaro) https://r.ena.to/blog/optimizing-postgres-table-layout-for-maximum-efficiency

    ~~~

    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
  • pg_stat_statements track_planning

    pg_stat_statements track_planning

    · Postgres FM

    Nikolay and Michael discuss the track_planning parameter of pg_stat_statements — what it is, how it affects performance, and when or whether you should switch it on.

    Here are some links to things they mentioned:

    pg_stat_statements.track_planning https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.40.9.2.4.1.3Our episode about pg_stat_statements https://postgres.fm/episodes/pg_stat_statementsPostgreSQL 13.0 release notes https://www.postgresql.org/docs/release/13.0/track_planning causing performance regression (thread on hackers during v13 beta) https://www.postgresql.org/message-id/flat/2895b53b033c47ccb22972b589050dd9%40EX13D05UWC001.ant.amazon.comOur episode on 4 million TPS https://postgres.fm/episodes/four-million-tpsObserver effect in pg_stat_statements and pg_stat_kcache (Postgres TV Hacking session with Andrey and Kirk) https://www.youtube.com/live/wHMNX-fHb2A?si=DPgmrPaSpPF6DxuS

    ~~~

    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
  • Is pg_dump a backup tool?

    Is pg_dump a backup tool?

    · Postgres FM

    Michael and Nikolay are joined by GĂŒlçin Yıldırım JelĂ­nek and Robert Haas to discuss both the technical question of whether or not pg_dump is a backup tool, as well as the tone and intent behind the statement "pg_dump is not a backup tool".

    Here are some links to things they mentioned:

    GĂŒlçin Yıldırım JelĂ­nek https://postgres.fm/people/gulcin-yildirim-jelinekRobert Haas https://postgres.fm/people/robert-haasWhy you should upgrade PostgreSQL today (blog post by GĂŒlçin) https://xata.io/blog/cve-2024-7348-postgres-upgradeIf pg_dump is not a backup tool, what is? (blog post by GĂŒlçin) https://xata.io/blog/pgdump-is-not-a-backup-toolIs pg_dump a backup tool? (blog post by Robert) https://rhaas.blogspot.com/2024/10/is-pgdump-backup-tool.html?m=1Why pg_dump is amazing (blog post by Robert) https://rhaas.blogspot.com/2024/11/why-pgdump-is-amazing.htmlAvoid too prominent use of "backup" on pg_dump man page (commit by Peter Eisentraut) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4f29394ea941f688fd4faf7260d2c198931ca797Is pg_dump a backup tool? (poll by Nikolay with options Yes / No / Define backup) https://x.com/samokhvalov/status/1847015453056786771What's the best way to make a backup (a recent example discussion on Reddit) https://www.reddit.com/r/PostgreSQL/comments/1gu4r05/whats_the_best_way_to_make_a_backup/Hackers mailing list https://www.postgresql.org/list/pgsql-hackers/ Praise, Criticism, and Dialogue (blog post by Robert) https://rhaas.blogspot.com/2023/12/praise-criticism-and-dialogue.html Out-of-cycle release scheduled for November 21, 2024 https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/ pgBackRest https://github.com/pgbackrest/pgbackrest Barman https://github.com/EnterpriseDB/barman Our previous episode on backups https://postgres.fm/episodes/backups

    ~~~

    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
  • Append-only tables

    Append-only tables

    · Postgres FM

    Nikolay and Michael discuss append-only tables in Postgres — what they are, some unique challenges they bring, and some options for compressing / removing / offloading the data eventually.

    Here are some links to things they mentioned:

    Append-only https://en.wikipedia.org/wiki/Append-onlyOur episode on BRIN indexes https://postgres.fm/episodes/brin-indexesTips to Improve Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance/ Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningautovacuum_vacuum_insert_scale_factor https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR Our episode about compression https://postgres.fm/episodes/compressionNew project announced recently called BemiDB https://x.com/samokhvalov/status/1854726389603484145pg_tier https://github.com/tembo-io/pg_tier

    ~~~

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

    Denormalization

    · Postgres FM

    Nikolay and Michael discuss denormalization in Postgres — when and why to denormalize things, and a couple of specific cases Nikolay came across recently.

    Here are some links to things they mentioned:

    Denormalization https://en.wikipedia.org/wiki/DenormalizationOur episode on materialized views https://postgres.fm/episodes/materialized-viewsOur episode on data model trade-offs https://postgres.fm/episodes/data-model-trade-offsOur episode with Markus Winand https://postgres.fm/episodes/modern-sqlUniversal Relation Data Modelling Considered Harmful (blog post by Michael Stonebraker and Álvaro Hernández) https://www.enterprisedb.com/blog/universal-relation-data-modelling-considered-harmfulBoyce–Codd normal form https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formOur episode on slow count https://postgres.fm/episodes/slow-countpg_ivm https://github.com/sraoss/pg_ivmdenorm https://github.com/rivethealth/denormPostgres Materialized Views, The Timescale Way (blog post by David Kohn) https://www.timescale.com/blog/materialized-views-the-timescale-way/PgQ https://github.com/pgq/pgqDatabases, types, and the relational model (by C.J. Date and Hugh Darwen) https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf

    ~~~

    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
  • Postgres online communities

    Postgres online communities

    · Postgres FM

    Nikolay and Michael discuss online Postgres communities — the ones they prefer, the types of conversations in each, and some other places to ask questions or follow news.

    Here are some links to things they mentioned:

    https://www.postgresql.org/communityMailing lists https://www.postgresql.org/listIRC https://www.postgresql.org/community/ircSlack https://pgtreats.info/slack-inviteStack Overflow https://stackoverflow.com/questions/tagged/postgresDBA Stack Exchange https://dba.stackexchange.com/questions/tagged/postgresReddit https://www.reddit.com/r/PostgreSQLDiscord https://discord.gg/bW2hsax8WeHow to run ANALYZE (merge request discussion) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/merge_requests/35This episode on YouTube https://postgresqlco.nfPlanet PostgreSQL https://planet.postgresql.orgPostgres Weekly https://postgresweekly.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 elephant artwork
  • Laat meer zien