Avsnitt
-
Nikolay and Michael discuss Row Level Security in Postgres, focussing on the performance side effects and some tips to avoid (or minimize) them.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Saknas det avsnitt?
-
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.
“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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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".
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss denormalization in Postgres — when and why to denormalize things, and a couple of specific cases Nikolay came across recently.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss some cool things you can do with psql, the official CLI that ships with Postgres.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss some Postgres Gotchas, things you might expect to work one way in fact working another way.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss the fresh new Postgres 17 release! They cover several performance improvements, favourite new features, and some considerations for upgrading.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss why counting can be slow in Postgres, and what the options are for counting things quickly at scale.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss PostgreSQL emergencies — both the psychological side of incident management, and some technical aspects too.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
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.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice.
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
Here are some links to things they mentioned:~~~
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.aiWith special thanks to:
Jessie Draws for the elephant artwork - Visa fler
