Afleveringen

  • 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

  • Zijn er afleveringen die ontbreken?

    Klik hier om de feed te vernieuwen.

  • 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
  • Advanced psql

    Advanced psql

    · Postgres FM

    Nikolay and Michael discuss some cool things you can do with psql, the official CLI that ships with Postgres.

    Here are some links to things they mentioned:

    psql docs https://www.postgresql.org/docs/current/app-psql.html Our episode on psql vs GUIs https://postgres.fm/episodes/psql-vs-guispostgres_dba https://github.com/NikolayS/postgres_dbaOur episode on massive deletes https://postgres.fm/episodes/massive-deletesPostgres hacking session on \watch with limited number of loops https://www.youtube.com/watch?v=vTV8XhWf3mo pspg https://github.com/okbob/pspg Our episode on Postgres gotchas https://postgres.fm/episodes/postgres-gotchascurrent_setting() and set_config() docs https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SETpsql tips (site by LĂŠtitia Avrot) https://psql-tips.org

    ~~~

    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 Gotchas

    Postgres Gotchas

    · Postgres FM

    Nikolay and Michael discuss some Postgres Gotchas, things you might expect to work one way in fact working another way.

    Here are some links to things they mentioned:

    Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownPostgres Gotchas (list by Ian Barwick) https://sql-info.de/postgresql/postgres-gotchas.htmlOur episode on slow count https://postgres.fm/episodes/slow-countDiscussion on X about major version upgrades and statistics https://x.com/samokhvalov/status/1844593601638260850Our episode on upgrades https://postgres.fm/episodes/upgradesStatistics Import and Export (commitfest entry which link to email thread) https://commitfest.postgresql.org/50/4538/vacuumdb https://www.postgresql.org/docs/current/app-vacuumdb.htmlvacuum_cost_delay https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-VACUUM-COST-DELAYZero-downtime Postgres schema migrations need lock_timeout and retries (blog post by Nikolay) https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retriesPostgres Hacking 101: adding params, prevent_unqualified_deletes and prevent_unqualified_updates https://www.youtube.com/watch?v=samLkrC5xQA PostgREST suggestion to use pg-safeupdate https://docs.postgrest.org/en/v12/integrations/pg-safeupdate.html#pg-safeupdateDBOS (new company co-founded by Michael Stonebraker) https://www.dbos.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 elephant artwork
  • Advanced EXPLAIN

    Advanced EXPLAIN

    · Postgres FM

    Nikolay and Michael discuss some more advanced topics around EXPLAIN, including some tips for complex query plans, some recent improvements, and an idea or two that might make it even better.

    Here are some links to things they mentioned:

    Michael’s solo episode on EXPLAIN basics https://postgres.fm/episodes/explainOur episode on auto_explain https://postgres.fm/episodes/auto_explainWhich cloud providers support auto_explain? (Blog post by Michael) https://www.pgmustard.com/blog/which-cloud-providers-support-auto-explainpsychopg https://www.psycopg.orgOur first episode on BUFFERS https://postgres.fm/episodes/buffers-by-defaultOur second episode on BUFFERS https://postgres.fm/episodes/buffers-ii-the-sequeltrack_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMINGcompute_query_id https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID EXPLAIN: beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4

    ~~~

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

    Patroni

    · Postgres FM

    Michael and Nikolay are joined by Alexander Kukushkin, PostgreSQL contributor and maintainer of Patroni, to discuss all things Patroni — what it is, how it works, recent improvements, and more.

    Here are some links to things they mentioned:

    Alexander Kukushkin https://postgres.fm/people/alexander-kukushkinPatroni https://github.com/patroni/patroniSpilo https://github.com/zalando/spilo Zalando Postgres Operator https://github.com/zalando/postgres-operatorCrunchy Data Postgres Operator https://github.com/CrunchyData/postgres-operatorSplit-brain https://en.wikipedia.org/wiki/Split-brain_(computing)repmgr https://github.com/EnterpriseDB/repmgrCloudNativePG https://github.com/cloudnative-pg/cloudnative-pgPatroni release notes https://patroni.readthedocs.io/en/latest/releases.htmlCitus & Patroni talk and demo by Alexander (at Citus Con 2023) https://www.youtube.com/watch?v=Mw8O9d0ez7E

    ~~~

    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 17

    Postgres 17

    · Postgres FM

    Nikolay and Michael discuss the fresh new Postgres 17 release! They cover several performance improvements, favourite new features, and some considerations for upgrading.

    Here are some links to things they mentioned:

    Postgres 17 release notes https://www.postgresql.org/docs/17/release-17.htmltransaction_timeout episode https://postgres.fm/episodes/transaction_timeoutVACUUM improvements discussed towards end of episode with Melanie Plageman https://postgres.fm/episodes/getting-started-with-benchmarkingB-tree improvements discussed in episdode with Peter Geoghegan https://postgres.fm/episodes/skip-scanAs Rails developers, why we are excited about PostgreSQL 17 (blog post by Benoit Tigeot) https://benoittgt.github.io/blog/postgres_17_rails/ Real World Performance Gains With Postgres 17 B-tree Bulk Scans (blog post by Brandur Leach) https://www.crunchydata.com/blog/real-world-performance-gains-with-postgres-17-btree-bulk-scansMERGE RETURNING came up towards end of episode with Haki Benita https://postgres.fm/episodes/get-or-createuuid_extract_timestamp and uuid_extract_version functions https://www.postgresql.org/docs/current/functions-uuid.htmlEpisode on UUID https://postgres.fm/episodes/uuidPartitioning by ULID https://postgres.fm/episodes/partitioning-by-ulidWhy Upgrade? (site by depesz) https://why-upgrade.depesz.comWhy we spent the last month eliminating PostgreSQL subtransactions (GitLab blog post) https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactionsSynchronization of sequences to subscriber (patch that needs review!) https://commitfest.postgresql.org/49/5111

    ~~~

    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
  • Planning time

    Planning time

    · Postgres FM

    Nikolay and Michael discuss planning time in Postgres — what it is, how to spot issues, its relationship to things like partitioning, and some tips for avoiding issues.

    Here are some links to things they mentioned:

    Query Planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.htmlAre there limits to partition counts? (Blog post by depesz) https://www.depesz.com/2021/01/17/are-there-limits-to-partition-countsNikolays recent experiment https://postgres.ai/chats/01920004-a982-7896-b8cb-dfd2406359b0PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0“The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalabilitypg_stat_statements.track_planning https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.42.9.2.4.1.3pg_hint_plan https://github.com/ossc-db/pg_hint_plan

    ~~~

    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
  • Slow count

    Slow count

    · Postgres FM

    Nikolay and Michael discuss why counting can be slow in Postgres, and what the options are for counting things quickly at scale.

    Here are some links to things they mentioned:

    Aggregate functions (docs) https://www.postgresql.org/docs/current/functions-aggregate.htmlPostgREST https://github.com/PostgREST/postgrest Get rid of count by default in PostgREST https://github.com/PostgREST/postgrest/issues/273 Faster PostgreSQL Counting (by Joe Nelson on the Citus blog) https://www.citusdata.com/blog/2016/10/12/count-performance Our episode on Index-Only Scans https://postgres.fm/episodes/index-only-scansPostgres HyperLogLog https://github.com/citusdata/postgresql-hllOur episode on Row estimates https://postgres.fm/episodes/row-estimates Our episode about dangers of NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown Aggregate expressions, including FILTER https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATESSpread writes for counter cache (tip from Tobias Petry) https://x.com/tobias_petry/status/1475870220422107137pg_ivm extension (Incremental View Maintenance) https://github.com/sraoss/pg_ivm pg_duckdb announcement https://motherduck.com/blog/pg_duckdb-postgresql-extension-for-duckdb-motherduckOur episode on Queues in Postgres https://postgres.fm/episodes/queues-in-postgresOur episode on Real-time analytics https://postgres.fm/episodes/real-time-analyticsClickHouse acquired PeerDB https://clickhouse.com/blog/clickhouse-acquires-peerdb-to-boost-real-time-analytics-with-postgres-cdc-integrationTimescale Continuous Aggregates https://www.timescale.com/blog/materialized-views-the-timescale-wayTimescale editions https://docs.timescale.com/about/latest/timescaledb-editionsLoose indexscan https://wiki.postgresql.org/wiki/Loose_indexscan

    ~~~

    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
  • Skip scan

    Skip scan

    · Postgres FM

    Michael and Nikolay are joined by Peter Geoghegan, major contributor and committer to Postgres, to discuss adding skip scan support to PostgreSQL over versions 17 and 18.

    Here are some links to things they mentioned:

    Peter Geoghegan https://postgres.fm/people/peter-geogheganPeter’s previous (excellent) interview on Postgres TV https://www.youtube.com/watch?v=iAPawr1DxhMEfficient Search of Multidimensional B-Trees (1995 paper by Harry Leslie, Rohit Jain, Dave Birdsall, and Hedieh Yaghmai) https://vldb.org/conf/1995/P710.PDFIndex Skip Scanning in Oracle https://oracle-base.com/articles/9i/index-skip-scanningPeter’s introductory email to the hackers mailing list about adding skip scan https://www.postgresql.org/message-id/CAH2-Wzmn1YsLzOGgjAQZdn1STSG_y8qP__vggTaPAYXJP+G4bw@mail.gmail.comLoose Indexscan versus Index Skip Scan (PostgreSQL wiki) https://wiki.postgresql.org/wiki/Loose_indexscanTom Lane will be on the Talking Postgres podcast on October 9th https://aka.ms/TalkingPostgres-Ep20-calBenoit Tigeot feedback and repro (originally reported via Slack) https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d?permalink_comment_id=4597410#gistcomment-4597410Summary video and blog post about the v17 work by Lukas from pganalyze (not mentioned but great) https://pganalyze.com/blog/5mins-postgres-17-faster-btree-index-scansUnderstanding HNSW + filtering (pgvector repo discussion) https://github.com/pgvector/pgvector/issues/259btree_gin https://www.postgresql.org/docs/current/btree-gin.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 produced by:

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

    With special thanks to:

    Jessie Draws for the elephant artwork
  • Postgres Emergency Room

    Postgres Emergency Room

    · Postgres FM

    Nikolay and Michael discuss PostgreSQL emergencies — both the psychological side of incident management, and some technical aspects too.

    Here are some links to things they mentioned:

    Site Reliability Engineering resources from Google https://sre.googleGitLab Handbook SRE https://handbook.gitlab.com/job-families/engineering/infrastructure/site-reliability-engineerKeeping Customers Streaming — The Centralized Site Reliability Practice at Netflix https://netflixtechblog.com/keeping-customers-streaming-the-centralized-site-reliability-practice-at-netflix-205cc37aa9fbOur monitoring checklist episode https://postgres.fm/episodes/monitoring-checklistHannu Krosing talk on Postgres TV — Do you vacuum everyday? https://www.youtube.com/watch?v=JcRi8Z7rkPgOur episode on corruption https://postgres.fm/episodes/corruptionNikolay’s episode on stopping and starting Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-fasterOur episode on out of disk https://postgres.fm/episodes/out-of-diskThe USE method (Brendan Gregg) https://www.brendangregg.com/usemethod.html Thundering herd problem https://en.wikipedia.org/wiki/Thundering_herd_problempgwatch2 Postgres AI edition https://gitlab.com/postgres-ai/pgwatch2

    ~~~

    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
  • Get or Create

    Get or Create

    · Postgres FM

    Michael and Nikolay are joined by Haki Benita, a technical lead and database enthusiast who writes an excellent blog and gives popular talks and training sessions too, to discuss the surprisingly complex topic of trying to implement “get or create” in PostgreSQL — handling issues around idempotency, concurrency, and bloat.

    Here are some links to things they mentioned:

    Haki Benita https://hakibenita.com How to Get or Create in PostgreSQL (blog post by Haki) https://hakibenita.com/postgresql-get-or-create "Find-or-insert" using a single query (how-to guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0036_find-or-insert_using_a_single_query.md?ref_type=heads Is SELECT or INSERT in a function prone to race conditions? (Answer by Erwin Brandstetter) https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions/15950324#15950324 get_or_create() in Django https://docs.djangoproject.com/en/5.1/ref/models/querysets/#get-or-create Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful MERGE (Postgres documentation) https://www.postgresql.org/docs/current/sql-merge.htmlHidden dangers of duplicate key violations in PostgreSQL and how to avoid them (blog post by Divya Sharma and Shawn McCoy from the RDS team) https://aws.amazon.com/blogs/database/hidden-dangers-of-duplicate-key-violations-in-postgresql-and-how-to-avoid-them/ One, Two, Skip a Few... (blog post by Pete Hamilton from Incident) https://incident.io/blog/one-two-skip-a-few

    ~~~

    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
  • Getting started with benchmarking

    Getting started with benchmarking

    · Postgres FM

    Michael and Nikolay are joined by Melanie Plageman, database internals engineer at Microsoft and major contributor and committer to PostgreSQL, to discuss getting started with benchmarking — how it differs for users and developers of Postgres, how and when it comes up during development, some tools and lessons, as well as what she's working on at the moment.

    Here are some links to things they mentioned:

    Melanie Plageman https://postgres.fm/people/melanie-plagemanMeanie’s Introduction to Benchmarking With pgbench talk slides https://postgresql.us/events/pgconfnyc2023/schedule/session/1410-introduction-to-benchmarking-with-pgbench/#slidesMelanie’s Visualizing Postgres I/O Performance for Development talk recording https://www.youtube.com/watch?v=CxyPZHG5beIMelanie’s Visualizing Postgres I/O Performance for Development talk slides https://speakerdeck.com/melanieplageman/o-performance-for-developmentpgbench https://www.postgresql.org/docs/current/pgbench.htmlMark Callaghan’s blog https://smalldatum.blogspot.comSome of Tomas Vondra’s blog posts https://www.2ndquadrant.com/en/blog/author/tomas-vondraSome of Andres Freund’s blog posts https://www.citusdata.com/blog/authors/andres-freund/An example of Alexander Lakhin’s work https://www.postgresql.org/message-id/b32bed1b-0746-9b20-1472-4bdc9ca66d52%40gmail.comSimplifying the TPC Benchmark C, an OLTP Workload (talk by Mark Wong) https://www.youtube.com/watch?v=qi0I74urLoYMatplotlib https://matplotlib.orgpandas https://pandas.pydata.orgpg_stat_io https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEWpg_stat_io commit https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a9c70b46dbe152e094f137f7e6ba9cd3a638ee25 pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.htmltmpfs https://docs.kernel.org/filesystems/tmpfs.htmlEager page freeze criteria mailing list thread https://www.postgresql.org/message-id/CAAKRu_b3tpbdRPUPh1Q5h35gXhY%3DspH2ssNsEsJ9sDfw6%3DPEAg%40mail.gmail.comThe path to using AIO in postgres (talk by Andres Freund) https://www.youtube.com/watch?v=qX50xrHwQa4Improve dead tuple storage for lazy vacuum (Masahiko Sawada) https://www.postgresql.org/message-id/flat/CAD21AoAfOZvmfR0j8VmZorZjL7RhTiQdVttNuC4W-Shdc2a-AA@mail.gmail.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
  • Index-Only Scans

    Index-Only Scans

    · Postgres FM

    Nikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice.

    Here are some links to things they mentioned:

    Index-Only Scans and Covering Indexes (docs) https://www.postgresql.org/docs/current/indexes-index-only-scans.htmlDiscussion on Twitter about JIT and Parallel Query defaults https://x.com/jer_s/status/1819749688184373742Postgres Wiki on Index-Only Scans https://wiki.postgresql.org/wiki/Index-only_scansHeap Fetches https://www.pgmustard.com/docs/explain/heap-fetchesRows Removed By Filter https://www.pgmustard.com/docs/explain/rows-removed-by-filterrandom_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/pg_dump docs change https://x.com/samokhvalov/status/1820539826363588755Crunchy Bridge changed random_page_cost to 1.1 for new servers https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1Autovacuum Tuning Basics (updated blog post by Tomas Vondra) https://www.enterprisedb.com/blog/autovacuum-tuning-basicsOur episode on over-indexing https://postgres.fm/episodes/over-indexingOur episode on HOT updates https://postgres.fm/episodes/hot-updatesOur episode on partitioning https://postgres.fm/episodes/partitioning

    ~~~

    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
  • Why Postgres?

    Why Postgres?

    · Postgres FM

    Nikolay and Michael discuss why they chose Postgres — as users, for their businesses, for their careers, as well as some doubts.

    Here are some links to things they mentioned:

    Our episode on why Postgres become popular https://postgres.fm/episodes/why-is-postgres-popularDatabase Systems: The Complete Book (by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom) http://infolab.stanford.edu/~ullman/dscb.htmlOur episode on the Postgres startup ecosystem https://postgres.fm/episodes/postgres-startup-ecosystemWill Postgres Live Forever? (talk by Bruce Momjian) https://www.youtube.com/watch?v=iYVxWpyaGpA Constitutional Peasants from Monty Python and the Holy Grail https://www.youtube.com/watch?v=t2c-X8HiBngThe Cathedral and the Bazaar: Musings on Linux and Open Source by an Accidental Revolutionary (book by Eric S. Raymond) https://en.wikipedia.org/wiki/The_Cathedral_and_the_Bazaar

    ~~~

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

    Compression

    · Postgres FM

    Nikolay and Michael discuss compression in Postgres — what's available natively, newer algorithms in recent versions, and several extensions with compression features.

    Here are some links to things they mentioned:

    wal_compression https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSIONOur episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningSynthetic wal_compression and streaming replication wal size test https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/11default_toast_compression https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION ZFS https://en.wikipedia.org/wiki/ZFSOur episode on TOAST https://postgres.fm/episodes/toastOn compression of everything in Postgres (talk by Andrey Borodin) https://learn.microsoft.com/en-us/shows/cituscon-an-event-for-postgres-2023/on-compression-of-everything-in-postgres-citus-con-2023cstore_fdw https://citusdata.github.io/cstore_fdw/Using Hydra columnar https://columnar.docs.hydra.so/concepts/using-hydra-columnarAbout compression in Timescale https://docs.timescale.com/use-timescale/latest/compression/about-compression/pg_tier https://github.com/tembo-io/pg_tierpgBackRest https://pgbackrest.org/WAL-G https://github.com/wal-g/wal-gpg_dump https://www.postgresql.org/docs/current/app-pgdump.htmlpg_dump compression specifications in PostgreSQL 16 (article by Pablo Glob from Cybertec) https://www.cybertec-postgresql.com/en/pg_dump-compression-specifications-postgresql-16/Our episode on pgvector (with Jonathan Katz) https://postgres.fm/episodes/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
  • Out of disk

    Out of disk

    · Postgres FM

    Nikolay and Michael discuss Postgres running out of disk space — including what happens, what can cause it, how to recover, and most importantly, how to prevent it from happening in the first place.

    Here are some links to things they mentioned:

    Disk Full (docs) https://www.postgresql.org/docs/current/disk-full.htmlpgcompacttable https://github.com/dataegret/pgcompacttable Our episode on massive deletes https://postgres.fm/episodes/massive-deletes Getting Rid of Data (slides from VLDB 2019 keynote by Tova Milo)pg_tier https://github.com/tembo-io/pg_tier Data tiering in Timescale Cloud https://docs.timescale.com/use-timescale/latest/data-tiering/ Postgres is Out of Disk and How to Recover (blog post by Elizabeth Christensen) https://www.crunchydata.com/blog/postgres-is-out-of-disk-and-how-to-recover-the-dos-and-donts max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE Our episode on checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning Aiven docs on full disk issues https://aiven.io/docs/products/postgresql/howto/prevent-full-disk

    ~~~

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

    Postgres startup ecosystem

    · Postgres FM

    Nikolay and Michael discuss the Postgres startup ecosystem — some recent closures, some recent fundraising announcements, and their thoughts on where things are going and what they'd like to see.

    Here are some links to things they mentioned:

    Prediction from Dax Raad https://x.com/thdxr/status/1808972166752580039OtterTune shut down https://x.com/andy_pavlo/status/1801687420330770841Snaplet shutting down https://www.snaplet.dev/post/snaplet-is-shutting-downbit.io shut down https://blog.bit.io/whats-next-for-bit-io-joining-databricks-ace9a40bce0d?gi=8ef885454eefTimescale acquired PopSQL https://www.timescale.com/blog/best-postgresql-gui-popsql-joins-timescaleAiven https://aiven.ioHasura https://hasura.ioSupabase https://supabase.comNeon https://neon.techTembo https://tembo.ioFerretDB https://www.ferretdb.comHydra https://www.hydra.sopgEdge https://www.pgedge.comTembo raised $14m https://techcrunch.com/2024/07/08/database-startup-tembo-lands-new-cash-to-expandRy Walker’s Cybertruck with STARTUP license plate https://x.com/rywalker/status/1810061804380557516 Supabase acquired OrioleDB https://supabase.com/blog/supabase-acquires-orioleMichael Stonebraker Turing Award Lecture https://www.youtube.com/watch?v=BbGeKi6T6QIMicrosoft acquired Citus https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scaleCrunchy Bridge https://www.crunchydata.com/products/crunchy-bridgePeerDB https://www.peerdb.ioParadeDB https://www.paradedb.compganalyze https://pganalyze.comDBeaver https://dbeaver.ioPostico / Egger Apps https://eggerapps.at/postico2Postgres Compare https://www.postgrescompare.comCoroot https://coroot.comokmeter https://okmeter.ioSlides from Nikolay’s talk on monitoring https://bit.ly/pg-monitoring Nile https://www.thenile.devUbicloud https://www.ubicloud.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