Voice of the DBA

Category: Editorial

  • Keep. It. Simple.

    I get a tech newsletter most days, which has news that I enjoy, but interspersed among the news and ads are projects, frameworks, or repos, most of which I’ve never heard of before. I used to read these, but it seems that there is an endless list of these, which all have marketing descriptions that somehow claim this set of code solves problems that others don’t or that this code is easy to use and integrate with, or well, I don’t know what other promises. I’m usually turned off by the end of the first sentence.

    The thing I’ve noticed is that there are so many projects out there. Even in the database space, if I happen to read a discussion on some aspect of databases, such as database deployment frameworks, I’ll see links to technologies I’ve never heard of in my life. Some are small projects, and some are small companies, but there is an amazing variety of solutions for any tech problem. I’m not sure most of them are much different from the others, but the Not Invented Here syndrome seems to be everywhere. These observations also remind me of just how vast the world is and how little I see of it on a daily basis.

    I ran across an article recently on simplifying the way we build software. In this instance, the authors focused on the complexity of JavaScript frameworks, with a number of authors/developers/consultants saying they limit their use of frameworks and try to keep to simple technologies. There is a quote that I find myself agreeing with and disagreeing with at the same time from David Heinemeier Hansson: “The merchants of complexity will try to convince you that you can’t do anything yourself these days. You can’t do auth, you can’t do scale, you can’t run a database, you can’t connect a computer to the internet. You’re a helpless peon who should just buy their wares. No. Reject.”

    I do think that many frameworks, products, etc. are very complex and there is a cost to using them. They can be overwhelming and hard to teach to others, and those costs can outweigh the benefits. The costs could be actual money or they could be the time it takes to learn, integrate, and teach the technology to others. Any change you make to your architecture will have impacts that you haven’t considered, and it will slow down your agility. Adding new technology does the same thing.

    At the same time, some things are hard. Auth is hard. Lots of people struggle with this and Troy Hunt has made a good living at https://haveibennpwned.com/ pointing out all the poor decisions people have made. Including a bunch of them that use the auth provided by some tech stack. Running databases is hard. Can you run one? Sure. Will you run it well as your app grows? Experience shows me most of you can’t. Can you set up a simple backup strategy? Of course, but that doesn’t matter. You being able to restore things is what matters. There are plenty more examples of tech problems or failures that companies run into as they build and scale software. There are also plenty of examples where using a framework saves a bunch of time for development teams. Look at mobile apps, most of them are based on some framework because learning all the ins and outs of iOS and Android is too hard for most people.

    For. Most. People.

    DNN can do a lot of things that most people can’t. He’s very talented and smart. Lots of organizations have one or two very talented people, but they also have a lot of average people. They struggle to teach and train them things, and ultimately, they don’t want to spend time teaching everyone about how to manage CSS or auth a user from a service or deploy changes to a database. They want a framework that makes it simple for the ever-changing staff to get work done related to their business, which is more often than not building software. They need to build software, but they need the software built to serve their customers in some way. That’s their goal, serving customers.

    I am all for keeping things simple. I want to minimize the number of technologies we use, and I want to limit the training time it takes to get people productive. However, I don’t want to build everything myself because I’m not sure I can, and I certainly know I don’t to go to that much effort.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • The Load of Real Time Data Warehouses

    If you have a data warehouse, what do you think your ratio of reads to writes is on any given day? Do you think 1:1, as in one read for each write? Is it 10:1, with 10 reads for each write? 100:1? Do you track this in any way?

    One would think that most of the databases we work on in the transactional world have many more reads than writes. I’d have assumed the ratios might be higher for data warehouses, where we load data that is queried (read) as the primary use case. After all, I expect that there are lots of people querying data that is loaded into this warehouse, with relatively few changes.

    I saw a presentation recently of a paper from Amazon that analyzed Redshift workloads (Why TPC is Not Enough). Redshift is the Amazon AWS data warehousing platform that has proven to be very popular in many companies as a cloud warehouse. One interesting piece of analysis is that there are more reads than writes, but barely. There is a lot of insert/update/delete activity, which is different than the TPC benchmarks for warehouses (TPC-H and TPC-DS). Those benchmarks tend to be 75+% reads. Hence the paper and analysis.

    However, Redshift isn’t like that in the real world. I had a chance to chat with one of the authors, and with another ETL specialist and they noted that the current pipelines that many companies use aren’t running in batch mode, but run in constant mode, moving data almost constantly. Combining this with relatively few queries from smaller groups of people results in a fairly close ratio of 1:1 for reads to writes. That was quite surprising to me.

    Presumably, some of this is because people will run relatively large queries for a report, and then spend time analyzing the data while the ETL/ELT processes continue to move data to the warehouse. Much of the design for storing data in warehouses, especially columnar ones, is with the idea that the data doesn’t change much. Certainly, columnstore indexes perform better when they are being read, not necessarily when they are updated.

    I wonder how much of this architecture of constant writes and updates has driven the world towards a Lakehouse architecture where data is written out and then transformed into cleaner versions that are copies, albeit some of them smaller than the original. Often this data is also written to separate delta/parquet files as well, which means new writes usually occur to discrete objects rather than a managed database structure. From a logical thought standpoint, that seems to make more sense to me, even though I still tend to think most of us could use a relational structure for warehousing data.

    If you have a real-time warehouse being updated, perhaps you want to measure your ratio of reads to writes and possibly rethink how to manage the system. If you don’t have a specific warehouse, which is most of us, you probably like querying the real-time updates of your transactional data in the same place as it’s stored. Either way, it’s interesting to think about the impact of a real-time workload on the performance your users experience.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Where’s the Printer Friendly Option?

    When is the last time you read an article/blog/etc. on the Internet and saw a button for a print friendly version? That used to be something on every page, and one people often shared on social media (or email) because it didn’t have all the advertisements in it. I remember having to help code this feature on SQL Server Central when we started as plenty of people wanted to print articles out and read them later. That desire led to Andy brainstorming that we should release The Best of books each year.

    I was reading about how the Internet has changed many things in our lives and I thought about these links. I searched a number of places I visit often and there are no more printer links. I’m guessing with mobile devices and various save services, most people have gotten used to using digital technology to consume information?

    I still print things at times, though fairly rarely. I don’t often consume anything on physical media anymore, including books. I’ve tried to read a few times on paper, but it’s inconvenient to me now. I have to remember to pack something or carry it, I need a light often, it just doesn’t work as well.

    I rarely see paper in use in meetings anymore at all. Whether I’m at a Redgate office or a customer site, most people seem to have monitors, projectors, sharing apps, and more so paper is just rarely used.

    At the same time, it’s not completely out of date. It works well and it’s simple. I see it used for announcements, for small handouts, signs, and menus. Quite a few of us don’t like the digital menus from QR codes and it seems most restaurants I’ve visited still create physical menus. Signs and announcements are the places I still see paper in use regularly. I will say I’ve seen a few people (a very few) using e-ink devices, which is something I’m tempted to use. I do find writing helps me remember things better.

    The world continues to create more and more data, while finding more numerous and novel ways to disseminate it. For much of the time, the paperless office exists, and I see less and less use of paper for distributing information, but it hasn’t completely disappeared. Except, perhaps, from the web.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Cleaning Up Your Database

    How many of you have objects in your database that aren’t being used? What about something in a schema with a _old in the name? Or _2 or _3 or _delete? There is a lot of old, deprecated stuff I see in production databases. In fact, I’ve been somewhat amazed as I work with clients that many of the scripts we can build from a database with SQL Compare won’t actually execute on an empty database because the script is full of broken code.

    I also find plenty of DBAs that want to clean things up, but they don’t. Sometimes they’re afraid they’ll break something, which is certainly possible. Sometimes they can never find the time. Often they might ask a manager, who usually says this isn’t important and don’t bother.

    Is it worth it to clean up your databases?

    Brent says no for old code. I say maybe for tables and code.

    For a lot of code, Brent is right, your boss doesn’t care and it doesn’t necessarily help you. After all, it’s in production now, and if it’s being used, you’re going to just create problems with a DROP. Where is the business value for removing old code (assuming it isn’t being used)? What benefits do your clients get? Not you being happier there are less objects, but what is the business benefit.

    That’s the key. Is there a business benefit.  What I’d say is that if you have broken code, it needs to be removed. Because this does impact your software development process, especially when trying to match lower environments. For broken stuff, save the code in your VCS (you do version control database code, right?) and then delete this stuff from prod. It’s broken.

    Or fix it.

    For tables, I would want to get rid of old tables as well. Why? Well, this is real costs in storage and potential reading of old data. If we moved data to table_old and someone decided they needed to read this for a report at the time, they might still be reading old data. I’d first rename these objects as object_delete_date with the date being a month away. Then I’d set a reminder for that date. On that date, bcp out the data, then drop the table. Period.

    Two other things. First, make sure you know how to recreate the table (see the VCS comment above) and bcp in the data. Two, this is low-priority work. If you want to clean the database, know this is a long term, baby step process that will take months or years, and may never end.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.