{"type":"rich","version":"1.0","provider_name":"Transistor","provider_url":"https://transistor.fm","author_name":"Postgres FM","title":"Should we use foreign keys?","html":"<iframe width=\"100%\" height=\"180\" frameborder=\"no\" scrolling=\"no\" seamless src=\"https://share.transistor.fm/e/82dc378e\"></iframe>","width":"100%","height":180,"duration":3081,"description":"Nikolay and Michael discuss foreign keys in Postgres — what they are, their benefits, their overhead, some edge cases to be aware of, some improvements coming, and whether or not they generally recommend using them.\n \nHere are some links to things they mentioned:\nForeign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK\nOur episode about constraints: https://postgres.fm/episodes/constraints\nGitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rb\nAdding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188\nBloat, pg_repack, and deferred constraints (blog post by Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec\nPostgres 17 draft release notes, server configuration section https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIG\nOur 100th episode https://postgres.fm/episodes/to-100tb-and-beyond\nStop! Trigger Time (blog post by Michael) https://www.pgmustard.com/blog/trigger-time\nShould 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\nAvoid 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\nExperiment to see basic overhead of foreign keys https://v2.postgres.ai/chats/01902ee6-8ed1-70ec-9345-5606305012f4\nExperiment showing an extreme contention case https://v2.postgres.ai/chats/018fb28d-865f-788d-adb7-efa7ed3a48c4\nSubtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful\nNotes on some PostgreSQL implementation details (blog post by Nelson Elhage that mentions “subtransactions are cursed”)...","thumbnail_url":"https://img.transistorcdn.com/NFbJlGhGV5mzIU1kM0iZ823A69pjZUNX40LszVO5LKI/rs:fill:0:0:1/w:400/h:400/q:60/mb:500000/aHR0cHM6Ly9pbWct/dXBsb2FkLXByb2R1/Y3Rpb24udHJhbnNp/c3Rvci5mbS9zaG93/LzMyMTQ3LzE3MTA3/OTEzODMtYXJ0d29y/ay5qcGc.webp","thumbnail_width":300,"thumbnail_height":300}