Should we use foreign keys?

Postgres FM

Player FM - Internet Radio Done Right

37 subscribers

הוסף לפני three שנים

תוכן מסופק על ידי Michael Christofides and Nikolay Samokhvalov. כל תוכן הפודקאסטים כולל פרקים, גרפיקה ותיאורי פודקאסטים מועלים ומסופקים ישירות על ידי Michael Christofides and Nikolay Samokhvalov או שותף פלטפורמת הפודקאסט שלהם. אם אתה מאמין שמישהו משתמש ביצירה שלך המוגנת בזכויות יוצרים ללא רשותך, אתה יכול לעקוב אחר התהליך המתואר כאן https://he.player.fm/legal.

Via Podcast

1
The Southwest’s Wildest Outdoor Art: From Lightning Fields to Sun Tunnels 30:55

לפני 13 ימים30:55

הפעל מאוחר יותר

רשימות

לייק

אהבתי

30:55

A secret field that summons lightning. A massive spiral that disappears into a salt lake. A celestial observatory carved into a volcano. Meet the wild—and sometimes explosive—world of land art, where artists craft masterpieces with dynamite and bulldozers. In our Season 2 premiere, guest Dylan Thuras, cofounder of Atlas Obscura, takes us off road and into the minds of the artists who literally reshaped parts of the Southwest. These works aren’t meant to be easy to reach—or to explain—but they just might change how you see the world. Land art you’ll visit in this episode: - Double Negative and City by Michael Heizer (Garden Valley, Nevada) - Spiral Jetty by Robert Smithson (Great Salt Lake, Utah) - Sun Tunnels by Nancy Holt (Great Basin Desert, Utah) - Lightning Field by Walter De Maria (Catron County, New Mexico) - Roden Crater by James Turrell (Painted Desert, Arizona) Via Podcast is a production of AAA Mountain West Group.…

לפני שנה 51:21

MP3•בית הפרקים

Foreign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
Our episode about constraints: https://postgres.fm/episodes/constraints
GitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rb
Adding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188
Bloat, pg_repack, and deferred constraints (blog post by Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec
Postgres 17 draft release notes, server configuration section https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIG
Our 100th episode https://postgres.fm/episodes/to-100tb-and-beyond
Stop! Trigger Time (blog post by Michael) https://www.pgmustard.com/blog/trigger-time
Should I Create an Index on Foreign Keys? (Blog post by Percona) https://www.percona.com/blog/should-i-create-an-index-on-foreign-keys-in-postgresql
Avoid Postgres performance cliffs with MultiXact IDs and foreign keys (5 min video by Lukas Fittl) https://pganalyze.com/blog/5mins-postgres-multiXact-ids-foreign-keys-performance
Experiment to see basic overhead of foreign keys https://v2.postgres.ai/chats/01902ee6-8ed1-70ec-9345-5606305012f4
Experiment showing an extreme contention case https://v2.postgres.ai/chats/018fb28d-865f-788d-adb7-efa7ed3a48c4
Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
Notes on some PostgreSQL implementation details (blog post by Nelson Elhage that mentions “subtransactions are cursed”) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the amazing artwork

144 פרקים

#Tech #News #Tech News #Michael Christofides #Nikolay Samokhvalov #Postgres #PostgreSQL #Database #SQL #Technology

Should we use foreign keys?

Postgres FM

37 subscribers

published לפני שנה

שתפו

MP3•בית הפרקים

Foreign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
Our episode about constraints: https://postgres.fm/episodes/constraints
GitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rb
Adding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188
Bloat, pg_repack, and deferred constraints (blog post by Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec
Postgres 17 draft release notes, server configuration section https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIG
Our 100th episode https://postgres.fm/episodes/to-100tb-and-beyond
Stop! Trigger Time (blog post by Michael) https://www.pgmustard.com/blog/trigger-time
Should I Create an Index on Foreign Keys? (Blog post by Percona) https://www.percona.com/blog/should-i-create-an-index-on-foreign-keys-in-postgresql
Avoid Postgres performance cliffs with MultiXact IDs and foreign keys (5 min video by Lukas Fittl) https://pganalyze.com/blog/5mins-postgres-multiXact-ids-foreign-keys-performance
Experiment to see basic overhead of foreign keys https://v2.postgres.ai/chats/01902ee6-8ed1-70ec-9345-5606305012f4
Experiment showing an extreme contention case https://v2.postgres.ai/chats/018fb28d-865f-788d-adb7-efa7ed3a48c4
Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
Notes on some PostgreSQL implementation details (blog post by Nelson Elhage that mentions “subtransactions are cursed”) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the amazing artwork

144 פרקים

#Tech #News #Tech News #Michael Christofides #Nikolay Samokhvalov #Postgres #PostgreSQL #Database #SQL #Technology

כל הפרקים

1
Top ten dangerous issues 46:28

לפני 6 ימים46:28

46:28

Nikolay and Michael discuss ten dangerous Postgres related issues — ones that might be painful enough to get onto the CTO and even CEOs desk, and then what you can do proactively. The ten issues discussed are: Heavy lock contention Bloat control and index maintenance Lightweight lock contention Transaction ID wraparound 4-byte integer PKs hitting the limit Replication limits Hard limits Data loss Poor HA choice (split brain) Corruption of various kinds Some previous episodes they mentioned that cover the issues in more detail: PgDog https://postgres.fm/episodes/pgdog Performance cliffs https://postgres.fm/episodes/performance-cliffs Zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations Queues in Postgres https://postgres.fm/episodes/queues-in-postgres Bloat https://postgres.fm/episodes/bloat Index maintenance https://postgres.fm/episodes/index-maintenance Subtransactions https://postgres.fm/episodes/subtransactions Four million TPS https://postgres.fm/episodes/four-million-tps Transaction ID wraparound https://postgres.fm/episodes/transaction-id-wraparound pg_squeeze https://postgres.fm/episodes/pg_squeeze synchronous_commit https://postgres.fm/episodes/synchronous_commit Managed service support https://postgres.fm/episodes/managed-service-support And finally, some other things they mentioned: A great recent SQL Server-related podcast episode on tuning techniques https://kendralittle.com/2024/05/20/erik-darling-and-kendra-little-rate-sql-server-performance-tuning-techniques/ Postgres Indexes, Partitioning and LWLock:LockManager Scalability (blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/ Do you vacuum everyday? (talk by Hannu Krosing) https://www.youtube.com/watch?v=JcRi8Z7rkPg pg_stat_wal https://pgpedia.info/p/pg_stat_wal.html The benefit of lz4 and zstd for Postgres WAL compression (Small Datum blog, Mark Callaghan) https://smalldatum.blogspot.com/2022/05/the-benefit-of-lz4-and-zstd-for.html Split-brain in case of network partition (CloudNativePG issue/discussion) https://github.com/cloudnative-pg/cloudnative-pg/discussions/7462 ~~~ 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
synchronous_commit 50:53

לפני 13 ימים50:53

50:53

Nikolay and Michael discuss synchronous_commit — what it means on single node setups, for synchronous replication setups, and the pros and cons of the different options for each. Here are some links to things they mentioned: synchronous_commit https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT synchronous_commit history on pgPedia https://pgpedia.info/s/synchronous_commit.html Patroni’s maximum_lag_on_failover setting https://patroni.readthedocs.io/en/master/replication_modes.html#asynchronous-mode-durability wal_writer_delay https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-WRITER-DELAY Selective asynchronous commits in PostgreSQL - balancing durability and performance (blog post by Shayon Mukherjee) https://www.shayon.dev/post/2025/75/selective-asynchronous-commits-in-postgresql-balancing-durability-and-performance/ Asynchronous Commit https://www.postgresql.org/docs/current/wal-async-commit.html synchronous_standby_names https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES Jepson article about Amazon RDS multi-AZ clusters (by Kyle Kingsbury, aka "Aphyr”) https://jepsen.io/analyses/amazon-rds-for-postgresql-17.4 ~~~ 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
Managed service support 35:37

לפני 20 ימים35:37

35:37

Nikolay and Michael discuss managed service support — some tips on how to handle cases that aren't going well, tips for requesting features, whether to factor in support when choosing service provider, and whether to use one at all. Here are some links to things they mentioned: YugabyteDB’s new upgrade framework https://www.yugabyte.com/blog/postgresql-upgrade-framework Episode on Blue-green deployments https://postgres.fm/episodes/blue-green-deployments pg_createsubscriber https://www.postgresql.org/docs/current/app-pgcreatesubscriber.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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
Time-series considerations 42:42

לפני 5 weeks42:42

42:42

Nikolay and Michael discuss time-series considerations for Postgres — including when it matters, some tips for avoiding issues, performance considerations, and more. Here are some links to things they mentioned: Time series data https://en.wikipedia.org/wiki/Time_series TimescaleDB https://github.com/timescale/timescaledb 13 Tips to Improve PostgreSQL Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance Why we're leaving the cloud (37 Signals / Basecamp / David Heinemeier Hansson) https://world.hey.com/dhh/why-we-re-leaving-the-cloud-654b47e0 UUID v7 and partitioning (“how to” by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md pg_cron https://github.com/citusdata/pg_cron pg_partman https://github.com/pgpartman/pg_partman Our episode on BRIN indexes https://postgres.fm/episodes/brin-indexes Tutorial from Citus (Andres Freund and Marco Slot) including rollups https://www.youtube.com/watch?v=0ybz6zuXCPo IoT with PostgreSQL (talk by Chris Ellis) https://youtube.com/watch?v=KnUoDBGv4aw&t=58 pg_timeseries https://github.com/tembo-io/pg_timeseries DuckDB https://duckdb.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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
Performance cliffs 38:08

לפני 6 weeks38:08

38:08

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-vondra Where do performance cliffs come from? (Talk by Tomas) https://www.youtube.com/watch?v=UzdAelm-QSY Where do performance cliffs come from? (Slides) https://vondra.me/pdf/performance-cliffs-posette-2024.pdf Increase 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/306484787 Our episode on Extended Statistics https://postgres.fm/episodes/extended-statistics Logging plan of the currently running query (proposed patch by Rafael Thofehrn Castro and Atsushi Torikoshi) https://commitfest.postgresql.org/patch/5330 Our episode with Peter Geoghegan on Skip Scan https://postgres.fm/episodes/skip-scan Index Prefetching patch that Tomas is collaborating with Peter Geoghegan on https://commitfest.postgresql.org/patch/4351 A 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.pdf Just-in-Time Compilation (JIT) https://www.postgresql.org/docs/current/jit.html Notes from a pgconf.dev unconference session in 2024 about JIT (discusses issues) https://wiki.postgresql.org/wiki/PGConf.dev_2024_Developer_Unconference#JIT_compilation Implementing an alternative JIT provider for PostgreSQL (by Xing Guo) https://higuoxing.com/archives/implementing-jit-provider-for-pgsql Tomas’ 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork…

1
PgDog 48:34

לפני 7 weeks48:34

48:34

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-kokotov PgDog https://github.com/pgdogdev/pgdog PgCat https://github.com/postgresml/pgcat Adopting PgCat (Instacart blog post) https://www.instacart.com/company/how-its-made/adopting-pgcat-a-nextgen-postgres-proxy PgDog discussion on Hacker News https://news.ycombinator.com/item?id=43364668 Citus https://github.com/citusdata/citus Sharding & IDs at Instagram (blog post) https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c Sharding 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork…

1
Snapshots 44:05

לפני 8 weeks44:05

44:05

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.html pgBackRest https://pgbackrest.org WAL-G https://github.com/wal-g/wal-g pg_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.md Restoring 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.html Our 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
GIN Indexes 40:59

לפני 9 weeks40:59

40:59

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.html Generalized 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
Best Practices 40:49

לפני 10 weeks40:49

40:49

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-this Article discussion on Hacker News https://news.ycombinator.com/item?id=42992913 Mozilla’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-nosql HA 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
Extended Statistics 32:41

לפני 11 weeks32:41

32:41

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.html citext https://www.postgresql.org/docs/current/citext.html Statistics Used by the Planner https://www.postgresql.org/docs/current/planner-stats.html default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET Tomáš Vondra on Postgres TV https://www.youtube.com/watch?v=8la-OWfD3VI Recent commit to Postgres 18 for pg_upgrade https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1fd1bd871012732e3c6c482667d2f2c56f1a9395 Multivariate Statistics Examples https://www.postgresql.org/docs/current/multivariate-statistics-examples.html Extended statistics (README) https://github.com/postgres/postgres/blob/master/src/backend/statistics/README How 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork…

1
SQL vs NoSQL 50:01

לפני 13 weeks50:01

50:01

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-pachot Franck'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-harmful Comparison 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-e4shf EdgeDB https://www.edgedb.com Nikolay’s tweet about a recent issue with NULLs https://x.com/samokhvalov/status/1889078097124999272 PartiQL https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html FerretDB https://www.ferretdb.com DocumentDB 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork…

1
Return of the BUFFERS 51:46

לפני 14 weeks51:46

51:46

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-default Our second BUFFERS episode https://postgres.fm/episodes/buffers-ii-the-sequel BUFFERS enabled for EXPLAIN ANALYZE by default (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07 https://explain.depesz.com/history Latency Numbers Every Programmer Should Know https://gist.github.com/jboner/2841832 Twitter conversation about flushing caches https://x.com/prochazkafilip/status/1881655337499205736 pg_buffercache_evict https://www.postgresql.org/docs/current/pgbuffercache.html#PGBUFFERCACHE-PG-BUFFERCACHE-EVICT How to compare the quality of SQL query plans (blog post by Andrei Lepikhov) https://danolivo.substack.com/p/whose-optimisation-is-better We 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork…

1
Reads causing writes 26:48

לפני 16 weeks26:48

26:48

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_Bits pg_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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork…

לפני 2 years38:04

38:04

Nikolay and Michael discuss data model trade-offs — some cases where you might want to (gasp) de-normalize, and which PostgreSQL internals are at play. Here are some links to things they mentioned: Our episode on JSON https://postgres.fm/episodes/json PostgreSQL limits https://www.postgresql.org/docs/current/limits.html Boyce–Codd normal form https://en.wikipedia.org/wiki/Boyce–Codd_normal_form Our episode on over-indexing https://postgres.fm/episodes/over-indexing Heap-Only Tuples (HOT) optimisation https://www.postgresql.org/docs/current/storage-hot.html The Surprising Impact of Medium-Size Texts on PostgreSQL Performance https://hakibenita.com/sql-medium-text-performance Query planner settings (collapse limits and Genetic Query Optimizer) https://www.postgresql.org/docs/current/runtime-config-query.html WITH Queries (Common Table Expressions) https://www.postgresql.org/docs/current/queries-with.html Our episode on benchmarking https://postgres.fm/episodes/benchmarking The tests and benchmarks Nikolay mentioned https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc ! ~~~ Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork…

ברוכים הבאים אל Player FM!

Player FM סורק את האינטרנט עבור פודקאסטים באיכות גבוהה בשבילכם כדי שתהנו מהם כרגע. זה יישום הפודקאסט הטוב ביותר והוא עובד על אנדרואיד, iPhone ואינטרנט. הירשמו לסנכרון מנויים במכשירים שונים.