I'd also add that a good design in the beginning is hard, especially when you don't know the full business context. It's good to keep things simple and extensible, + apply the mentioned tips. And ofc, don't be afraid to update later if needed.
The section on horizontal and vertical partitioning captures exactly what I’ve been applying in practice, but haven’t had the time to articulate systematically. Thank you for putting it into words. :)
During my tenure as DB Architect- I always felt that design phase is rushed. Representing design at a later phase (after app poc is developed) which makes it hard for teams to go back and implement db design change recommendations.
Here are few things I came across ..
Scheduled job to find unused indexes to analyze and drop if not used - reactive work though.
Missing indexes on Foreign Keys is another common issue(table lock) that slips during development.
For processing records based on STATUS column- functional index to ignore status values that are not needed anymore - for ex don’t index “Processed” or “Success” values.
In general, during design phase, I request team to only have PK, FK, UK indexes. Every other index should be based on sqls developed. Good index on a column of a table is expensive and useless if there is no sql using it.
I'd also add that a good design in the beginning is hard, especially when you don't know the full business context. It's good to keep things simple and extensible, + apply the mentioned tips. And ofc, don't be afraid to update later if needed.
Fair point.
Though in my experience, “we’ll fix it later” often becomes “we’ll live with it forever.”
A little extra thought early can save years of pain.
Really enjoy, completely agree.
Thanks!
great tips !
Glad to help!
Excellent tips! Indexing is an underrated performance trick.
When done right, it is a powerful tool.
The section on horizontal and vertical partitioning captures exactly what I’ve been applying in practice, but haven’t had the time to articulate systematically. Thank you for putting it into words. :)
++ Good Post, Also, start here stock market, AI research, Crash Courses, 100+ Most Asked ML System Design Case Studies and LLM System Design
AI/ML/LLM Engineer Interview
https://open.substack.com/pub/naina0405/p/launching-500k-job-meet-quantum-your?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
How to Build Tech
https://open.substack.com/pub/howtobuildtech/p/how-to-build-tech-04-how-to-actually?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/howtobuildtech/p/how-to-build-tech-03-how-to-actually?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/howtobuildtech/p/how-to-build-tech-01-the-heart-of?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/howtobuildtech/p/how-to-build-tech-02-how-to-actually?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
Crash Courses
https://open.substack.com/pub/crashcourses/p/crash-course-03-hands-on-crash-course?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/crashcourses/p/crash-course-02-a-complete-crash?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/crashcourses/p/crash-course-01-a-complete-crash?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
LLM System Design
https://open.substack.com/pub/naina0405/p/very-important-llm-system-design-7e6?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/naina0405/p/very-important-llm-system-design-67d?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/naina0405/p/most-important-llm-system-design-b31?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://naina0405.substack.com/p/launching-llm-system-design-large?r=14q3sp
https://naina0405.substack.com/p/launching-llm-system-design-2-large?r=14q3sp
[https://open.substack.com/pub/naina0405/p/llm-system-design-3-large-language?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/naina0405/p/important-llm-system-design-4-heart?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
System Design
https://open.substack.com/pub/naina0405/p/bookmark-most-asked-ml-system-design-611?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/naina0405/p/system-design-tech-case-study-pulse-862?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/naina0405/p/system-design-tech-case-study-pulse-b3c?r=14q3sp&utm_campaign=post&utm_medium=web
https://open.substack.com/pub/naina0405/p/system-design-tech-case-study-pulse-135?r=14q3sp&utm_campaign=post&utm_medium=web
https://open.substack.com/pub/naina0405/p/system-design-tech-case-study-pulse-007?r=14q3sp&utm_campaign=post&utm_medium=web
Stock Market
https://open.substack.com/pub/stockmarketanalysis04/p/important-stock-market-post-04-which?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/stockmarketanalysis04/p/important-stock-market-analysis-which?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/stockmarketanalysis04/p/important-stock-market-post-02-understand?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/stockmarketanalysis04/p/important-stock-market-post-03-this?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/stockmarketanalysis04/p/important-stock-market-post-06-i?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
AI/ML Research
https://open.substack.com/pub/airesearch04/p/ai-research-2-kimi-k2-thinking-a?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/airesearch04/p/ai-research-1-the-transformer-revolution?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
https://open.substack.com/pub/naina0405/p/very-important-llm-system-design-7e6?r=14q3sp&utm_campaign=post&utm_medium=web&showWelcomeOnShare=false
Great post!
During my tenure as DB Architect- I always felt that design phase is rushed. Representing design at a later phase (after app poc is developed) which makes it hard for teams to go back and implement db design change recommendations.
Here are few things I came across ..
Scheduled job to find unused indexes to analyze and drop if not used - reactive work though.
Missing indexes on Foreign Keys is another common issue(table lock) that slips during development.
For processing records based on STATUS column- functional index to ignore status values that are not needed anymore - for ex don’t index “Processed” or “Success” values.
In general, during design phase, I request team to only have PK, FK, UK indexes. Every other index should be based on sqls developed. Good index on a column of a table is expensive and useless if there is no sql using it.
Great post, Raul.
Optimizing something that is created messily can be difficult later on. But that's the story of most projects, isn't it :)
Insightful read!