Voice of the DBA

Category: Editorial

  • Patterns and Potential Problems

    I saw a post recently from a developer that needed to refactor and rename a table in a live system. The post describes a pattern for doing so and gives the steps taken, though not the actual code. I like the pattern overall, and I think it can work well in many situations. It’s for a PostgreSQL table, so I don’t know what restrictions might be different from SQL Server, but this type of pattern can work for SQL Server as well.

    It also could be problematic. Using the famous “it depends”, there could be issues with this pattern, depending on your workload and how your application is structured. The triggers in use could also be an issue in some environments, as they create an additional load.

    The biggest concern I have with this pattern is the copying of the data. Likely this is something that always works on a developer’s machine with a few dozen or even hundreds of rows of data. If this is millions, or tens of millions, the copy could end up taking substantial time. There is also the issue of changing data, with data being added or changed in the table, separately from being copied out. Depending on your locking and concurrency schemes, you could miss data.

    Or you could just lock the table and cause issues for clients. Both things that might not show up in developer testing. The lesson here is that changes to big tables need to be tested in a big way.

    This isn’t to say the pattern is bad, but that you should be aware of the potential pitfalls and then develop mitigation strategies. One way to get around the data issue might be choosing a way of copying over new or changed data after the initial data movement, or maybe even a cleanup load later after the new table is online. There are many possible ways to mitigate issues, if you take some time to think about the potential issues and then come up with a solution.

    When we are looking to make changes to our system, patterns are important to help us and others adopt the processes that work well. As we find and develop patterns, we need to ensure that we understand the strengths and weaknesses, and choose what’s best for us, with mitigation strategies to get around the potential problems. There often isn’t a perfect solution, and ensuring you and your team understand the limits of your chosen approach, helps ensure that we deploy code that not only works, but is deployed without causing issues.

    Steve Jones

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

  • Live Your Life

    Summer is coming to an end. For many of you with kids in school, it’s likely already over.  The first Monday in September in the US is Labor Day, and that’s often when I’ve marked the end of summer. Hopefully you had the chance to get a break from life, the pandemic, and work with some vacation this summer.

    I’ve tried to work hard, be successful in my chosen career, and at the same time enjoy my life. That means time for me and my family away from work. It also means meeting responsibilities that I have, which include taking care of my home and animals. In other words, chores. All of this adds up to a lot of time necessary to accomplish my various goals, needs, and desires.

    There are plenty of things, both in life and work, that I might put off for a period of time, expecting to find a block of time to handle them later. I think this is a common idea that I see friends, family, and plenty of others doing, optimistically viewing the future as one where more time will appear.

    It might, but likely it won’t unless your life changes a lot. To me, that means that I need to find time today, make choices, delay some things, but tackle others, both responsibilities and desires, mixing them to create the life that I want. I was reminded of this again while watching this Abel Wang interview. It’s one of the last of his life, as Abel finally passed after a battle with cancer this summer.

    He chose what was important to him, which is great. He also chose to live his life, not waiting for a distant future time. While most of us might expect we have years to live, you never know if that period of time might be much shorter than you expect. That mysterious future where you can finally visit that place, spend time with someone, accomplish a task, or anything else might not materialize.

    I used to take stock of my life every year on my birthday. Was I doing the work I wanted? Are there things I wish I’d done the last year? Am I happy with life if I died today? That last one was a big one, and for years, I’ve felt I would be happy with how I’ve spent my time. These days my wife and I are getting older, and we evaluate the things we want to do on a shorter time frame, setting goals, making plans, and trying to ensure that we are really enjoying life every few months.

    That doesn’t mean we’re always playing. We do have some busy work times, but we work to remember that we can’t let life get away from us for more than a few weeks before we need to ensure we’re balancing out the things we are obligated to do and the things we want to do. Finding balance has been helpful to ensuring that we can cope with stress, while not letting it overrun all aspects of our lives.

    Steve Jones

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

  • Enhancing Client Experiences with Data and Analysis

    I love sports. The idea of competing and testing yourself against others (and against yourself) is something I’ve enjoyed for most of my life. Often it hasn’t even been important who wins; it’s the experience of competing. I don’t compete much anymore, but I enjoy watching and coaching sports. Teaching a group of younger people to work, train, win, lose, and enjoy sports is very fulfilling.

    I take statistics when I coach, and I provide those to my athletes regularly. The data is helpful for them to understand their performance and how they compare to others. The numbers don’t drive my decisions, or theirs, but they do provide feedback for them about overall performance. Sometimes stats help me keep perspective; sometimes they remind me of something I’ve forgotten. Sometimes they help me realize a mistake is an outlier and not where I ought to focus.

    I ran across an article on how data is being used in more professional sports. This post isn’t about the numbers of the game, but rather the ways in which data enhance the experience for fans. From resource needs to marketing to predictions, data is important to success in large events. The data can also make the fan experience better.

    Most of us are data professionals, and we know the power of data. Many business people know that data can be helpful, after all, they usually give us a never-ending queue reports to build. I think many of these business professionals can struggle with thinking more widely about how to proactively use data in planning, preparation, and prediction. These are things that data professionals have been doing for years, before the current AI/ML fad. We often used past data to help us prepare for the future. I’m not against AI/ML, but I don’t think they are necessary for all predictions.

    Data professionals know that we can use data in many areas of life outside of sports to enhance software for the client experience. I know I enjoy seeing some of the technology advancements that add to the experience from software, and I appreciate those that provide excitement to an experience, as well as those that just ensure things run smoothly. I look forward to seeing what the future might bring as some of the incredibly creative people out there begin to use data and technology to try out new ideas.

    Steve Jones

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

  • Where We Need Better AI Disclosure and Responsibility

    There are a lot of contract and gig jobs in the world today. It used to be this type of work was widely spread throughout programming and technology, but these days many types of jobs are commonly completed using contract workers. I like the flexibility of contract work, but I also think that these workers need to be better at saving and planning for the future because of less employment stability. Usually they are paid more, but they need to use that to reduce the risk of being unemployed.

    One trend that I’ve seen taking place in some of these positions is the use of software and AI to determine if a worker is doing an acceptable level of work on a regular basis. Amazon might be one of the highest-profile companies doing this, especially as they expand into the delivery business. They are using the power of computers to manage an army of workers rather than traditional human managers. This includes terminating them. There’s an article that talks about some of the experiences of their workers.

    I don’t know how their system works, but I do know the frustration of trying to work with a company that doesn’t use humans for many tasks. If you’ve ever tried to contact Google, you know that it’s incredibly difficult to actually communicate with a human. Google seems to think that its automated systems can handle all situations. They might handle many things, but they don’t do a good job in plenty of situations, and there is little recourse to have a human intervene.

    I do think that AI and ML can help our companies better interact with the world in many cases, but these systems are certainly looking for broad patterns. Maybe these patterns handle the middle 80% of cases, or maybe it’s more like 60%, but there are plenty of situations where humans ought to be involved. Maybe more important, when someone uses these systems to make decisions that impact human life, there should be some explanation and understanding of how the model impacts this specific situation. We want to know why the computer comes to its conclusion in medical care, employment, legal issues, or maybe anything other situation.

    There is work being done to try and explain how these models work. The important thing, however, is to ensure that while we may understand the model, we also need to disclose the reasoning to those affected by the systems. Any appeal process should include this explanation, and likely with a human involved at some point to help evaluate the model for accuracy, fairness, or any other measure that is relevant. To me, we ought to require this of companies using AI models in their business practices.

    Steve Jones

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