Postgres index bloat | The Backend Engineering Show

Postgresql database implements MVCC by creating a new row version for any update/delete/or insert. While this is a sound implementation to support concurrent transactions reading old versions of the rows, it does have some side effects and this is what I want to discuss in this episode of the backend engineering show.

Get my Introduction to Database Engineering course

https://database.husseinnasser.com

0:00 Intro

1:00 Postgres MVCC design

6:42 MVCC on other databases

11:15 Side-effects of Row-versioning

17:00 Postgres HOT optimization

19:50 How Index bloat affects Performance

24:20 How Postgres 14 Addresses index bloat

Cards:

14:12 b-tree https://www.youtube.com/watch?v=a1Z40OC553Y

Resources

https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION

Become a Member on YouTube

https://www.youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join

🔥 Members Only Content

https://www.youtube.com/playlist?list=UUMO_ML5xP23TOWKUcc-oAE_Eg

Support my work on PayPal

https://bit.ly/33ENps4

🧑‍🏫 Courses I Teach

https://husseinnasser.com/courses

🏭 Backend Engineering Videos in Order

https://backend.husseinnasser.com

💾 Database Engineering Videos

https://www.youtube.com/playlist?list=PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2

🎙️Listen to the Backend Engineering Podcast

https://husseinnasser.com/podcast

Gears and tools used on the Channel (affiliates)

🖼️ Slides and Thumbnail Design

Canva

https://partner.canva.com/c/2766475/647168/10068

🎙️ Mic Gear

Shure SM7B Cardioid Dynamic Microphone

https://amzn.to/3o1NiBi

Cloudlifter

https://amzn.to/2RAeyLo

XLR cables

https://amzn.to/3tvMJRu

Focusrite Audio Interface

https://amzn.to/3f2vjGY

📷 Camera Gear

Canon M50 Mark II

https://amzn.to/3o2ed0c

Micro HDMI to HDMI

https://amzn.to/3uwCxK3

Video capture card

https://amzn.to/3f34pyD

AC Wall for constant power

https://amzn.to/3eueoxP

Stay Awesome,

Hussein