Table Clustering in MySQL and Postgres - The pros and cons
ฝัง
- เผยแพร่เมื่อ 2 มิ.ย. 2024
- In this episode of the backend engineering show, I discuss database clustering. This is also known as table clustering, clustered index or Index organized table all names represents the same thing. I will talk about the benefits of clustering and also the disadvantages of implementing clustering. This feature is also implicitly implemented in certain databases.
More readings
www.postgresql.org/docs/14/sq...
oracle-base.com/articles/8i/i...
docs.microsoft.com/en-us/sql/...
dev.mysql.com/doc/refman/5.7/...
Become a Member on TH-cam
/ @hnasr
🔥 Members Only Content
• Members-only videos
Support my work on PayPal
bit.ly/33ENps4
🧑🏫 Courses I Teach
husseinnasser.com/courses
🏭 Backend Engineering Videos in Order
backend.husseinnasser.com
💾 Database Engineering Videos
• Database Engineering
🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/6...
🎙️ Mic Gear
Shure SM7B Cardioid Dynamic Microphone
amzn.to/3o1NiBi
Cloudlifter
amzn.to/2RAeyLo
XLR cables
amzn.to/3tvMJRu
Focusrite Audio Interface
amzn.to/3f2vjGY
📷 Camera Gear
Canon M50 Mark II
amzn.to/3o2ed0c
Micro HDMI to HDMI
amzn.to/3uwCxK3
Video capture card
amzn.to/3f34pyD
AC Wall for constant power
amzn.to/3eueoxP
Stay Awesome,
Hussein - วิทยาศาสตร์และเทคโนโลยี
Hurray for backend show! Lol its 2:13 AM where I am and I'm managing my new aws servers rollout in the terminal. Great timing!
Perfectly explained Hussein! Thanks a lot. I really want to work people like you who I can converse with on multitude of topics.
Beautifully explained man, one thing that I got to wonder is that does AWS Redshift leverage users' querying patterns to recluster the tables to optimize performance. seems like the only way to do so based on your explanation.
Great video as usual. I had couple of questions. As you have mentioned that we can have a clustering index on the grade column, which I am assuming is not unique, does this affect when we are inserting the data in the table or do databases have a mechanism to create each entry in the grade column as unique, something which SQL Server does. And is it best practice to have Clustered index on a unique and non-null column or it depends upon the queries the user is going to use?
Love the hair! Awesome content as usual
Thanks !
Hey man tnx for the vid. Can you point out resources to know more about internals of db as a backend dev? (perf tuning, trade-offs, indexing, etc.)
You make backend engineering feel like philosophy.
I like the content, I am having a hard time understanding what a page means, page == a file in disk?
6:44 there is no link to B tree video
is it katana in the back?
Do I see a Katana on the book rack?
Every one showing mysql clustering on youtube(percona xtradb ,galera,group replication,etc).
But i want to do simple write and read replicas and also integrate with demo with frontend+backend app!.
Load balance with all them .
Can some one show me the demo.Oh i am frustrated.!!!!
Please give me proper guide someone...
I liked your old hairstyle. 😅
Check out my udemy Introduction to Database Engineering course
husseinnasser.com/courses
Learn the fundamentals of database systems to understand and build performant backend apps
Finally first
You cluster your data in a table and then Postgre will keep appending inserts?! What the... I thought that the reason behind the clustering was to have data ordered based on some index and KEEP IT that way... I would be ok if they offered a "Append inserts" functionality as a optional behaviour after clustering if you are ok with re-clusteding data once in a while but to do it by default?! If this is the case then what was the point of clustering when you cannot keep it/
I think even a one time clustering could benefit you bc it will rewrite like rows into the same page. So that’s less pages to read which is less IO
"Some of them will get 10" 🤒