Lessons Learned: The Value of Investing in Dev Tools
I already knew that indexes are important for efficient queries, but the results of my experiment told me how important they really are. I wasn’t extremely rigorous in my testing (e.g., I didn’t run the query multiple times for a given number of records), but it’s extremely clear that an index gave me two giant advantages here:
- Constant time queries as a function of the number of DB records.
- Faster queries for any number of records.
I knew this would be unsurprising to most of the Very team, but it’s still important to reinforce. In addition, there was another big lesson lurking here:
A multi-index does not provide the same type of performance gains as a single column index.
Again, this may have been unsurprising, but if anyone was not aware, this would be an easy mistake to make. Multi-indices really only pay off if you are querying by every field in the index. The ordering of the fields matters, too, because you can get benefits from the multi-index if you query from the first fields in the index.
Lastly, the lesson that I wanted our team to internalize was that taking the time to build a very quick tool that simulates real pseudo-random device data allowed me to perform an experiment in less time than it took me to do this write-up. Investing in our developer tooling to help us perform quick experiments nearly always pays off.
Based on this information that I gathered, I felt confident in moving forward with only adding an index and perhaps caching some query results if needed in order to implement this feature (as opposed to a full restructuring of the schema used for storing our time series data).
Insights from the IoT Development Team
After I shared a write-up of the above challenge and solution in the #dev_chat channel, a few of our senior engineers made some great points on how to iterate on my approach and prepare for a good path forward in the project.
Specifically, they pointed out the importance of testing the write speed increases of adding an index because of the associated costs. They also noted that while this was the right approach for the situation at hand, using a star schema will provide a good path forward.
Check out a snippet of the conversation here — emojis and all:
Work With Expert IoT Developers
We treat every client project with the high level of precision and accountability that you see reflected in this post. Tell us about your project today.