Voice of the DBA

Category: Editorial

  • A Case for Document Storage

    One of the challenges for both database developers and administrators is doing more, often with less. Many companies continue to grow their database estate, both in width with more platforms, and in depth with more instances of the platforms they have.  Some companies will look to shrink their staff, especially when adopting a cloud platform, while others may add more databases, but not increase staffing to match the additional load.

    In either case, what many have found over the years is that the cost of labor is high. Both for developers that write code against databases, and administrators that manage those platforms. While licensing can seem to be a large number, compared to the cost of labor, it isn’t usually a significant number.

    Often it seems administrators would prefer more of the same database platform. Developers often seem to ask for new types of database platforms, often some type of NoSQL data store. I ran across an article that makes a case for adding in document storage data stores to your environment, instead of just choosing am RDBMS. Labor is one of the big reasons for doing this. The other one is that for a given workload, the hardware cost is lower.

    The article opens talking about the object/relational mapping problems. There is some truth to the time and effort to map an object in an application to a table (or set of tables) in an RDBMS. There is some knowledge required to do this, but I also think it’s an important skill for many developers. The same type of object mapping to a serialized JSON document is shown as being easier, and it is.

    However, if you add or change your object, the application code to handle the document from the data store gets complex. Over time, you will have lots of “new” fields that don’t exist in older documents. How do you handle those? It’s not hard, but labor is required to write this code. And this code has to be maintained over time.

    The other argument is that less hardware is needed, made by noting all the data you may need can be co-located with your object. This is what we would call denormalization in an RDBMS and leads to data duplication? Whether that is a problem or not depends on the amount of duplication. Certainly the structure of an application that often works to send or retrieve singleton rows is easier in a document database.

    However, non trivial queries, which the author postulates are hard to write for developers, are likely hard to run for a document database. The load of querying across lots of rows, or updating them, is much higher in a document database. Depending on how often you update data, this can be an issue, and require more hardware.

    Which is better? The classic “it depends” applies here. Database modeling is important in both cases. As I’ve worked with people that move to NoSQL databases, I find they struggle to model in that world as much as many of us struggle to model in the RDBMS world. I also find that a NoSQL database often is going to require some sort of data warehouse or other structure that is built for reporting across documents.

    I’m not against the various types of NoSQL databases, but I also don’t think they are a panacea of any sort that magically makes building and operating an application easier.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • AI Concerns

    I hosted a webinar a few weeks ago about Artificial Intelligence and how it might affect data professionals. It was an interesting discussion with Kellyn Pot’Vin-Gorman and Brian Randell, with all of us having slightly different perspectives. Overall, we all agree AI is amazing and can be a useful tool for data professionals.

    This week I’ve been in Redmond at the MVP Summit, and AI has been a topic among many MVPs. Lots of jokes have been told, no shortage of which dealt with getting rid of staff. Not that these MVPs want to see less staff, but they know that executives and managers might see all the AI hype about how GitHub CoPilot writes code and think they need less developers.

    I don’t know if that is really how executives will view the world, especially as most IT departments have more work than resources to complete their list of tasks. I can see AI helping get more done, which might mean less hiring (or slower hiring) in the future.

    There was an article this week talking about ways to protect your job in the age of AI hype. It was interesting in that the suggestions all revolve around bringing more value to your job. The suggestions about working in specialized areas, complex areas, being a better employee with documentation, these are all things that I’ve done in my career. It’s what I also see from many MVPs.

    Maybe the most interesting item was to think about incorporating AI into your work now. It’s not necessarily an expert on your work, but it is a tool. As the models absorb more information and become better trained, they can be a level to help you get more work done. They can assist you in tedious work, which is something many of us can benefit from.

    I am looking forward to getting access to CoPilot and keeping an AI tool up on my desktop, learning how it might help me, and maybe more importantly, where it won’t help me.

    I can’t stop this trend, but I can better understand it and learn how it might fit with my daily work. Knowing that helps me better understand how I might have an informed and rational discussion with management about the advantages or disadvantages of this new technology.

    Steve Jones

  • A Third Break

    One of the most amazing benefits of working at Redgate Software is the ability to take a sabbatical every five years. One of our staff wrote about this recently, and I found myself reflecting back on mine, as well as thinking forward.

    The article notes that many people either learn or travel during theirs. That was somewhat of my experience, where I spent my first one learning skills and volunteering at home. My one-year look back is still interesting to revisit today. Unfortunately, my flagpole base failed in strong winds (sad face) and broke the pole. It’s still on my list to rebuild a new one. I still look back on my volunteer time with fondness and try to get back to Habitat every year.

    My second was avoiding travel, since I’d traveled a lot the year before. I ended up with the last sabbatical before the pandemic, coming back to work as our office closed. I stayed home, worked on learning and projects, though I did take a trip to Las Vegas to celebrate my wife’s birthday.

    Six weeks away from work seems like a lot. Before Redgate, I’d have thought that this was a huge burden on the employer and fellow employees. However, we’ve had multiple people on sabbatical and we cope. We pick up the slack, and things continue to run. As with maternity (and paternity) leave, it’s not as big a burden as this American used to think.

    It is very refreshing, and each time I’ve felt rejuvenated. I’ve been ready to get back to work, talking with Redgate customers and speaking at events. To me, this is a great way to encourage retention among loyal employees, as well as a way that can create more diversity of thought among your employees. Where they travel, the things they learn, even the change of pace in their mind often bring them back to work with new perspectives and ideas.

    I just crossed my fifteenth year at Redgate, so I’m due for my third sabbatical. I haven’t thought about it, and I am not likely to take it this year. This does take some planning, both in my personal life and at work, so I have found I usually need 5-6 months to decide on something and get plans in place.

    What will I do this time? I’m not sure. What would you suggest? I am thinking to travel this time for part of the trip. My wife and I had an amazing travel time in 2022, and there are so many amazing places in the world that I’d like to visit. I am also tempted to try and fit in some learning as well, perhaps a week spent in some sort of educational endeavor.

    No matter what I decide, I am grateful for the opportunity and look forward to another break that helps my work-life balance, balanced.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Data Modeling Information

    Data modeling is something that we should all be doing when altering the schema in our databases. I’d like to think that most people spend time here, but I don’t think that’s the case. I think plenty of people think “I need to store a piece of data” and they pick a string or numeric datatype and start stuffing in values. If in doubt, just pick a string. It’s why I think we have lots of dates stored in string columns because that was someone’s first thought.

    There was a post recently that talked about storing data in its highest form. It was interesting to me because these are the type of decisions I try to make when designing a table. What is the best form in which to store data? The authors talk about picking not only a type that easily converts, but the fields that make it easiest to work with the data in different ways.

    I do think that the aggregations or calculations that we need to perform should influence your data type. If you are measuring something, use a numeric. In fact, in their example of movie times, integer is probably the best type. While many databases and languages have time datatypes, some represent a measure of time (timespan), while others represent a clock (T-SQL time). Either might work for movies, but in aggregations, the T-SQL time will have issues beyond 24 hours. An integer is a better choice, assuming we don’t care about seconds.

    The second part of the post looks at multiple values, in this case customer loyalty points earned and redeemed. A simple running sum is what we might store in a database, though the application class might need two fields. Of course, modern software often totals these things for a customer as part of gamification and inducement to engage more, so maybe a data store would also want to store the title earned and redeemed, with a calculation to show the balance.

    The one thing that I might add for developers to a post about modeling is the need to consider operations at scale. While using a bit more or less storage often doesn’t matter for any row or any operation on a singleton set of data, when we scale across millions of rows, little things matter. Consider how your data might be aggregated and what happens if you have millions of rows to work on. There a better design decision can out perform a poor one by many orders of magnitude.

    That and generate lots of data to test. You ought to know how to quickly mock up a million rows to check your queries. You might have a million rows in production.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.