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.
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.
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!